CODEWITHSUNDEEP
sql-serverentity-frameworklinqgroup-by
AskYous
AskYous

Reputation: 4740

GroupBy() running extremely slow

I have a table in my database which has a date field (VisitDate). When I execute the following SQL query, I receive the results in 1 second:

select year(VisitDate), month(visitDate) 
from GABrowser 
group by year(VisitDate), month(visitDate)

However, in my ASP.Net code (in Visual Basic), the follow takes 12 seconds to execute:

Dim stats = db.GABrowsers.GroupBy(Function(s) New With {s.VisitDate.Value.Year, s.VisitDate.Value.Month})

'(Takes 12 seconds to enter in the For loop)
For Each stat In stats 

Next

I don't know why it takes so long. It's causing my web pages to load extremely slow.

Upvotes: 0

Views: 230

Answers (1)

Gert Arnold
Gert Arnold

Reputation: 109185

The SQL query and the LINQ query are not equivalent.

The SQL query only returns year(VisitDate) and month(visitDate). GroupBy in LINQ is different though, it returns full entities that are grouped by the grouping condition.

In the LINQ result you'll see db.GABrowsers entities in groups of identical s.VisitDate.Value.Year and s.VisitDate.Value.Month. You'll see that the SQL query generated by the LINQ statement is much wider (in fields) than the first SQL query.

Besides that (a larger SQL result set) it takes time for EF to process the data from the database and materialize entity objects.

Upvotes: 3

Related Questions