Need advice with SELECT MAX and MIN | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Need advice with SELECT MAX and MIN

Hi there, I have a rather large table that contains approx 160 million records. In the table are two columns that I need to utilise to return some values. (Desc and BookDate). Both of which are indexed. Now if I run:- 1) SELECT MIN(BookDate)
FROM Table or 2) SELECT MAX(BookDate)
FROM Table or even 3) SELECT MIN(BookDate)
FROM Table
WHERE Desc = [Given Value] The resulting record set is instant. If however I run:- 4) SELECT MAX(BookDate)
FROM Table
WHERE Desc = [Given Value] The resulting record (which is obviously 1 record) takes a very long time to return. I’ve tried things like:- 5) SELECT Desc, MAX(BookDate)
FROM Table
WHERE Desc = [Given Value]
GROUP BY Desc 6) SELECT TOP 1 Desc, BookDate
FROM Table
WHERE Desc = [Given Value]
ORDER BY Desc, BookDate DESC but with no luck. Interestingly enough a quick look at the query plan shows the same for the min and max queries 3 & 4 (but with MAX using ORDERED BACKWARD on the index scan against BookDate and min using ORDERED FORWARD) Any ideas how I can speed up that last select would be greatly appreciated.
Do you have an index on the Desc column? Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
I suppose the index Fora said is BookDate, Desc.
What both execution plan inform? Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
Having an index on (desc, bookdate) would be the most efficient way of executing this query… presumably the difference is that the description you want in the max query is one who’se dates happen to be near one end of the bookdates (if you look at bookdates across the whole table) Cheers
Twan
Hi, Many thanks for all the replies. I have one index on [Desc] and one index on [BookDate]. What I don’t have as suggested here is an index across the pair of columns [Desc, BookDate]. I have tried forcing QA to use both indexes and either / or index with index hinting but to no avail. I’ll give the [Desc, BookDate] index a go and see if that works. Will post results back here later today, once the index has been added. Might be a while as there’s some 160 million records in this table. Oh re-reading my post "last select" really refers to numbers 4, 5 and 6 I am looking to improve the speed of. (IE: They all will achieve the same thing for me, as in I want to get at the MAX(Bookdate) for each [Desc] value or a specific [Desc] value quickly. With the existing two single column indexes I can already get at the MIN(BookDate) quickly.) Cheers. Fora.
Ok added the new index across both columns and it made as I suspected absolutely no differnce with the exception that both the queries:- 3) SELECT MIN(BookDate)
FROM Table
WHERE Desc = [Given Value] and 4) SELECT MAX(BookDate)
FROM Table
WHERE Desc = [Given Value] are now using the new index instead of the bookdate index. Again the MIN select gives an instant answer, the max select takes forever and a day. Gonna try one more thing on this then I’m gonna have to drop it as I’ve spent enough time on it already. Gonna try adding an index on BookDate with a Descending order value to see what impact that has on it. Again will post back the results in a couple of hours. EDIT:- Update… Nope Index (desc) didn’t work either, right spent way too much time on this now so I’m gonna drop it there. Once again thanks for everyones help. Fora.
If you put a clustered index on book date, I would think it would be fast for either min or max. Chris
Maybe DESC is not selective enough. BTW, if you are using the same DESC for both min and max, what happens when you run SELECT MAX(BookDate), MIN(BookDate)
FROM table
WHERE Desc = [Given Value]
— OPTION (MAXDOP 1) if there are more than one processor… Otherwise, you could try an indexed view (probably with group by for different DESC values), though I don’t know if it suits your situation. if table is subjected to a lot of modifications it might not be the best idea…
Bambola, thanks for your comments. I suspect you are right in that Desc is not selective enough. Same thing happens with SELECT .. MAX & MIN as the SELECT MAX (on it’s own). IE: As soon as you include MAX it goes off into space for awhile before coming back with the answer. MAXDOP 1 did not affect the performance in any way at all far as I could tell. What I did was similar to your view idea in that I created a table with all the min-max values per desc, which like you mentioned I am using. Thanks again. Fora.
]]>