Search code examples
sqlsql-serversqlperformance

Getting record with max date per user and inserting it to temp table - performance issue

I need to get the maxdate per useraccount from the statement table and insert into temp table. Statement tables have more than 40 million records. I tried the following query and it took more than 4 mins. Is there any better way to do this?

select useraccount, max(date)
into #temptable
from statement
group by useraccount
Solution

  • Grouping can be expensive. And 4 minutes does not seem that bad for processing and creating a large table. But if you have an index on (useraccount, date), you could try:

     select useraccount, date
 into #temptable
 from statement s
 where date = (select max(s2.date) from statement s2 where s2.useraccount = s.useraccount);