EF Datafirst approach taking 50 sec. in local host/dev env. to retrieve the data from single table.

Question

User-1609546788 posted

Hi Team,

EF 6 Datafirst approach taking 50 sec. in local host/dev env. to retrieve the data from single table.

I am using this data with Asp.net MVC +  Angular js UI.

How i can reduce this time? as i am not using any joins.

Answer 1

User-821857111 posted

What takes 50 seconds? The query? Or the whole operation (query plus rendering)? Does it happen every time you run the query or just the first time? How long does the query take when you run it directly in SSMS? How much data are you fetching?

Answer 2

User-1609546788 posted

The whole operation query plus rendering taking 50 sec.

Yes it is happen every time.

In SSMS it is taking 16 sec.

Total Records in sql -25347 

i have checked for 500 records or 25347  records it is taking 50 sec for  the whole operation query plus rendering.

Answer 3

User753101303 posted

Hi,

You need first to understand exactly what is slow. For example a common catch especially if you come from a desktop dev world is trying to show thousands or millions of rows which creates a huge html page which is slow to download while the EF query itself is actually quite fast.

If confirmed you should use pagination or allow the user to filter data even before showing something.

Answer 4

User1120430333 posted

The whole operation query plus rendering taking 50 sec.

Yes it is happen every time.

In SSMS it is taking 16 sec.

Total Records in sql -25347 

i have checked for 500 records or 25347  records it is taking 50 sec for  the whole operation query plus rendering.

SSMS is running straight T-SQL that involves no object materialization like an ORM such as EF does that takes time to materialize the objects.

Then there is the performance factor of rendering a HTML control that uses the collection of objects that takes time too, like a grid with no paging being rendered..

https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/ef/performance-considerations

Answer 5

User753101303 posted

So it seems to confirm the issue. You never show that many rows to a user. I assume he can then use a filtering UI ? You could let them use this UI before showing anything so that what you show the user is really what he needs to see.

Another option is to use paging (ie showing n rows at a time with being able to go through pages) but here you'll need of course at least a filtering UI anyway.

Answer 6

User-821857111 posted

In SSMS it is taking 16 sec.

If it's taking 16 seconds to execute the query directly against the database, you need to look at optimising your indexes etc. If you can create a query in SQL that runs faster than the SQL generated by EF, then get EF to execute that instead.

https://www.mikesdotnetting.com/article/299/entity-framework-code-first-and-stored-procedures

Answer 7

User-1609546788 posted

I am using angularjs html table for rendering records.

for 500 records also it is taking 50 sec.

Used filter as well. Still the same time taking.

Answer 8

User753101303 posted

Humm. Could it be some kind of intentional slowdown testing then  that was not removed ? It is weird it it really takes 50 seconds regardless of the situation.

You would need really to use debug tools or write time values to the debug console to try to better narrow down where this time is spent.

Answer 9

User-1609546788 posted

We are not doing any intentional slowdown testing.Taking the data from EF Data first approach from One table and rendering in angular js table.

Answer 10

User753101303 posted

I find weird that you see 50 seconds for both 500 or 25000 rows. You are really sure you changed the correct location and that changes are taken into account on the site you are testing.

What if using  Take(1) to return a single row. Which difference do you see then !? Also it happens on the first run or it always happen ?

Answer 11

User-1609546788 posted

I am checking/testing on local(Localhost / Development Env.) environment .

For Single row also it is taking 50 Sec.

It is happening every-time.

Answer 12

User753101303 posted

Seems really weird. Either the change you are doing is not really at the right place (for example keeping a single row from all those returned by the Ajax call which would save a small rendering time). Or you have some code that wits somewhere to reach an elapsed time.

Try perhaps to check/rebuild again you code step by step but it doesn't make sense that you still have the same execution time regardless of those changes...

Answer 13

User-1609546788 posted

i am passing filter criteria with four input parameter from angularjs ajax request. that time for one records also it is taking 50 sec.

When i removed Four input Parameters then for one records it is coming faster.

Answer 14

User-943250815 posted

Sounds a huge table without proper index for query, take a look in Display Execution Plan on SSMS, so you can see where your query is taking too much time

Answer 15

User-1609546788 posted

I am using filter criteria for filtering the records.In that i have added Contains , Date filter .

may be i need to improve my filter criteria

If i removed and simply added .Take(1) it is taking less time.

Answer 16

User1520731567 posted

Hi andry18,

Yes,As mentioned above, regarding performance issues, either improve SQL queries(using stored procedures...) or improve EF linq queries,or divide big data into small batch operations...

To improve the overall performance of queries in the Entity Framework by using the following strategies:

1.Pre-generate views

2.Consider using the NoTracking merge option for queries

3.Return the correct amount of data

4.Limit the scope of the ObjectContext

5.Consider opening the database connection manually

Best Regards.

Yuki Tao

Answer 17

User409696431 posted

andry18

I am using angularjs html table for rendering records.

for 500 records also it is taking 50 sec.

Used filter as well. Still the same time taking.

If you are using Angular JS's "filter" method, you are filtering on the client side, not on the server.  You are fetching and downloading all the records, then filtering what is shown to the user.  If that's the case, it will take the same time with filters and without, other than additional time for actually rendering more records in the page.

You need to limit the number of records actually fetched from the database, filtering on the server side.

Answer 18

User-1609546788 posted

Hello Team,

I have changed ToList to IQueryable, now for 500 records it is taking less time.but for that i have to remove my from date date and to date(in String Format)  filter as  date in database also is in string format and we cannot use convert.Todate for  IQueryable  Filter. 

//Filter based on Dates
if (!string.IsNullOrEmpty(FromStartDate) && !string.IsNullOrEmpty(ToStartDate) && (FromStartDate) != (ToStartDate))
{
IqurableList = IqurableList.Where(jb => Convert.ToDateTime(jb.DBDate).Date >= Convert.ToDateTime(FromStartDate).Date
&& (Convert.ToDateTime(jb.DBDate).Date <= Convert.ToDateTime(ToStartDate).Date));
}

Getting the exception as :-

LINQ to Entities does not recognize the method 'System.DateTime ToDateTime(System.String)' method, and this method cannot be translated into a store expression.

Answer 19

User753101303 posted

It is translated from C# to SQL which can't work regardless of what you are doing. If using properly typed values you shold be able to use something such as:

IqurableList.Where(jb => jb.DBDate>=startDate && jb.DBDate<endDate); // endDate could be the very beginning of the next day

If jb.DbDate is a string there is liekly something wrong with your db design.

Edit: seems your original issue could be that you always loaded all data before filtering them ??? It mgiht explain why the execution time was roughtly the same regardless of what you were doing ????

Answer 20

User-1609546788 posted

 seems your original issue could be that you always loaded all data before filtering them ???  Yes

It mgiht explain why the execution time was roughtly the same regardless of what you were doing ???? Yes

I have changed To List to iqueryable and changed the dates in database to datetime and apply the filter at the time of getting record from database.  Now it is taking less time.