Max Function
-
Is there any other way to find the maximum of a field in a table other than the max() so that it is faster than max().
Thanks
Rohini
-
With proper indexing max() should be as fast as the following, which would otherwise be the fastest alternative:
SELECT TOP 1 column
FROM table
ORDER BY column DESC
-
Can you put an index on the relevant field? That will speed up the MAX() function.
If you are doing MAX(non-indexed-field) I'd EXPECT it to be slow
Check the execution plan - it's probably doing a scan. -
Thanks for ur repsonse
Actually my query goes like this
Select Max(InpassNo) from TbInpass where CompanyID='FF' and Finyear='2005-2006' .
The fields InpassNo,CompanyID and Finyear are Primary keys.But still its performance is not good. Anyother suggestions instead of MAX().
-
What exactly do you mean with "The fields InpassNo,CompanyID and Finyear are Primary keys"?
To execute this query as fast as possible you should have a non-clustered index defined on (CompanyID, Finyear, InpassNo), or (Finyear, CompanyID, InpassNo) depending on the distribution of values in the CompanyID and Finyear columns. This index could be used as a covering index.
-
No the indexing is all properly given.
I want to know, an alternative function or something like that ,instead of MAX().
-
Like I said, MAX is about the fastest you can get. TOP 1 is an alternative, but performance will normally be very similar. With the index I specified above your MAX query will be very fast. If it is not then you do not have that index.
Please specify DDL (including indexes) if you want more details.
-
What do you mean by
"No the indexing is all properly given.
I want to know, an alternative function or something like that ,instead of MAX()"
Additional index can and should be added when you have different queries.
Look at it this way, with MAX and an index like Chris said you probably need to read less then 10 blocks to get the MAX. Without it, you will need to do a clustered index scan or table scan.
The PK although implying an index doesn't work for you in this scenario because you are filtering based on
CompanyID and FinYear, then sorting based on InPassNo to get the MAX. The order is incorrect.
The ORDER of the index is as important as having the index. If the leading edge of the index (in your case, the InPassNo) is not part of the query's selection criteria then the optimizer would not be able to use the index.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply