CODEWITHSUNDEEP
sql-server
Navyseal
Navyseal

Reputation: 901

MAX keyword taking a lot of time to select a value from a column

Well, I have a table which is 40,000,000+ records but when I try to execute a simple query, it takes ~3 min to finish execution. Since I am using the same query in my c# solution, which it needs to execute over 100+ times, the overall performance of the solution is deeply hit.

This is the query that I am using in a proc

 DECLARE @Id bigint    
 SELECT @Id = MAX(ExecutionID) from ExecutionLog where TestID=50881    
 select @Id

Any help to improve the performance would be great. Thanks.

Upvotes: 0

Views: 836

Answers (2)

Callie J
Callie J

Reputation: 31306

What indexes do you have on the table? It sounds like you don't have anything even close to useful for this particular query, so I'd suggest trying to do:

CREATE INDEX IX_ExecutionLog_TestID ON ExecutionLog (TestID, ExecutionID)

...at the very least. Your query is filtering by TestID, so this needs to be the primary column in the composite index: if you have no indexes on TestID, then SQL Server will resort to scanning the entire table in order to find rows where TestID = 50881.

It may help to think of indexes on SQL tables in the same way as those you'd find in the back of a big book that are hierarchial and multi-level. If you were looking for something, then you'd manually look under 'T' for TestID then there'd be a sub-heading under TestID for ExecutionID. Without an index entry for TestID, you'd have to read through the entire book looking for TestID, then see if there's a mention of ExecutionID with it. This is effectively what SQL Server has to do.

If you don't have any indexes, then you'll find it useful to review all the queries that hit the table, and ensure that one of those indexes is a clustered index (rather than non-clustered).

Upvotes: 4

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239714

Try to re-work everything into something that works in a set based manner.

So, for instance, you could write a select statement like this:

;With OrderedLogs as (
     Select ExecutionID,TestID,
       ROW_NUMBER() OVER (PARTITION BY TestID ORDER By ExecutionID desc) as rn
     from ExecutionLog
)
select * from OrderedLogs where rn = 1 and TestID in (50881, 50882, 50883)

This would then find the maximum ExecutionID for 3 different tests simultaneously.

You might need to store that result in a table variable/temp table, but hopefully, instead, you can continue building up a larger, single, query, that processes all of the results in parallel.

This is the sort of processing that SQL is meant to be good at - don't cripple the system by iterating through the TestIDs in your code.

If you need to pass many test IDs into a stored procedure for this sort of query, look at Table Valued Parameters.

Upvotes: 1

Related Questions