CODEWITHSUNDEEP
c#sqlsql-serverlinqentity-framework
Amir Jalali
Amir Jalali

Reputation: 3212

GroupBy query is slow what are the other options?

I need a query to select rows with minimum Insert Date Time. but groupby query is very slow

what can I do instead of groupby?

How can improve the performance of the query?

Is there any other way to write this query using linq?

Number of rows in my table is around 600,000 rows and growing

fullsimnumber isn't indexed but isdeleted and insertdatetime are indexed.

fullsimnumber is a column which consist of three indexed coloumn prec+cod+subscribec.

my problem is with linq queries which always gives timeout exception.

I changed fullsimnumber to groupby prec,cod,subscribec which are indexed but still getting tiemout exception

Im using linq to EF, (code first style) and my query in sql is:

SELECT *
FROM dbo.Sim AS t1
    JOIN (SELECT FullSimNumber,MIN(InsertDateTime) AS insd
          FROM dbo.Sim 
          GROUP BY FullSimNumber) AS t2
    ON t1.FullSimNumber = t2.FullSimNumber AND t1.InsertDateTime = t2.insd
WHERE t1.IsDeleted = 0

and my query in linq

from s in ADbContext.Numbers
where !s.IsDeleted
group s by s.FullSimNumber
into g
let sd = g.OrderBy(x => x.InsertDateTime).FirstOrDefault()
select sd;

Upvotes: 0

Views: 543

Answers (1)

Tristan
Tristan

Reputation: 1024

without any data on indexes and amounts of rows and such it is hard to know what exactly is needed to make your query faster. One thing which is worthwhile to try out is this:

SELECT *
FROM dbo.Sim AS t1
CROSS APPLY (
    SELECT TOP 1 FullSimNumber
        , InsertDateTime AS insd
    FROM dbo.Sim t2
    WHERE t1.FullSimNumber = t2.FullSimNumber
        AND t1.InsertDateTime = t2.insd
    ORDER BY InsertDateTime DESC
    ) AS t2
WHERE t1.IsDeleted = 0

Again this could very well be worse! test it and compare the execution times and load.

Upvotes: 1

Related Questions