Faster way to retrieve large value in column

Question

I am using MVC, EF & Repository, and database in Azure. I have a table, that has only 5 columns. ID, name, message, Exceptions & otherdetails. Exceptions & otherdetails, both are a varchar(max) datatype. Now, Exceptions column max string length is a 2300 word & otherdetails column max string length is 1500 word.

can anyone tell me, the faster way to find whole data? because now I am searching one-day data, so it took almost 20sec or more to execute. I executed the same query in SQL. but it took the same time. Also, I tried to use table view but the same time to execute. here is my code -

public IEnumerable<ErrorLogModel> GetErrorLogData(DateTime? startDate, DateTime? endDate)
    {
        return _unitOfWork.ErrorLogRepository.Get(e.Date >= startDate && e.Date <= endDate).Select(e => new ErrorLogModel
        {
            ID= e.ID,
            name = e.name,
            message= e.message,
            Exceptions = e.Exceptions,
            otherdetails = e.otherdetails
        });

    }

I think your problem is not the large data in the column, but in the number of records. Have you tried an index on `Date`? — Nick, Dec 18 '18 at 11:32
Yes: don't pull the large strings, only `message` and any of the larger strings only on request. — Gert Arnold, Dec 18 '18 at 16:02

score 0 · Accepted Answer · answered Dec 18 '18 at 15:43

0

Indexing can increase performance of your query, now it scans all the table so query works really slow.

EF 6.1 supports indexing, try to use it like this: https://learn.microsoft.com/ru-ru/ef/ef6/modeling/code-first/data-annotations#index

[Index]
public DateTime ExceptionDate { get; set; }

If you prefer FluentAPI and use EF 6.2 you can use this answer: How to create index in Entity Framework 6.2 with code first

answered Dec 18 '18 at 15:43

Sleepy Panda

458
3
9

Indexing is the solution. I just forget it. ;-) – Raj Khandekar Dec 19 '18 at 14:08

score 0 · Answer 2 · answered Dec 18 '18 at 22:10

The issue isn't so much the field size as it is the total data size based on the number of records returned. For an entire day, how many records can come back? 10's, 1000's? You're attempting to pull ~4k per record, so depending on the # of records this will have a big impact on the amount of time needed to retrieve that data. At a minimum if you do need all of that data you should implement server-side paging to load pages of 10-50 records at a time.

Also, what does ErrorLogRepository.Get return, IQueryable<ErrorLog> or IEnumerable<ErrorLog>? If you're returning IQueryable and the repository is just returning the result of your .Where() criteria without calling .ToList() or the like then all should be good, but if you're returning IEnumerable then you would be doubling up the memory use on your app server. The repository would effectively do a "SELECT *" to load the entities into memory, then your follow-up .Select would create a new set of view models. (2x the amount of memory needed.)

Faster way to retrieve large value in column

2 Answers2