Timeline for Why does selecting top 1 from composite index DESC also used to partition by month not select the top value?
Current License: CC BY-SA 3.0
15 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Jun 24, 2013 at 7:34 | vote | accept | Adrian Pillinger | ||
| Jun 20, 2013 at 12:47 | answer | added | Paul White♦ | timeline score: 6 | |
| Jun 19, 2013 at 16:24 | comment | added | Martin Smith | @PaulWhite - Ah thanks very much for that. | |
| Jun 19, 2013 at 16:13 | comment | added | Paul White♦ | @MartinSmith Itzik Ben-Gan wrote a very clear explanation and workaround here | |
| Jun 17, 2013 at 12:41 | comment | added | Martin Smith |
@AdrianPillinger - Yes maybe SQL Server does not currently produce the plan that I described in my first comment. It would probably be possible to use an APPLY on sys.partition_range_values to get this plan though without being dependant on the assumption that timeSampled and id are always strictly correlated (though maybe this is a safe enough assumption in your case)
|
|
| Jun 17, 2013 at 8:24 | comment | added | Adrian Pillinger | Adding an order by id does not improve performance for some reason. | |
| Jun 14, 2013 at 11:22 | comment | added | JoseTeixeira |
as @MartinSmith said, you'd need a order by for your top 1 to bring the max
|
|
| Jun 14, 2013 at 10:56 | comment | added | Adrian Pillinger | So, as I stated, we have a solution to our problem by forcing a max select from specific partitions. It would however be nice to know why the query I was using in my initial question to select the top 1 id did not work. It seems strange that it does not select the max from all partitions. | |
| Jun 14, 2013 at 10:54 | comment | added | Adrian Pillinger | @user16484 That would be ok if we didn't have a huge amount of data in the database with only short maintenance periods. Thank you for your suggestion though | |
| Jun 14, 2013 at 10:50 | comment | added | Adrian Pillinger | @MartinSmith, we have discovered a trick to specify the partition to select max from.... SELECT MAX(id) AS maxId FROM dbo.TableA WHERE $partition.TableAPartitionFunction_Monthly](timeSampled) = partitionNumber And this seems to perform well and correctly identify the partition to use. | |
| Jun 14, 2013 at 10:49 | comment | added | Adrian Pillinger | Martin Smith, unfortunately the max query does perform slowly. The table is filling at a rate of 3-6 million rows per day and will store data for 6 months before we archive it off. | |
| Jun 14, 2013 at 10:45 | comment | added | JoseTeixeira | This might be a heavy change, but can't you make the PK index clustered, once that ids and date order increment accordingly? It would most probably speed up your SELECT MAX(id) FROM tableA enough | |
| Jun 14, 2013 at 9:32 | comment | added | Martin Smith |
TOP 1 without ORDER BY is undefined. Have you actually tried SELECT MAX(id) FROM tableA and determined that it does not perform well? If you have how many partitions do you have and what does the plan for that look like? In principle it ought to be able to use the index in each partition to get the single MAX quickly then find the MAX of those. Do you not get that plan?
|
|
| Jun 14, 2013 at 8:36 | review | First posts | |||
| Jun 14, 2013 at 10:30 | |||||
| Jun 14, 2013 at 8:20 | history | asked | Adrian Pillinger | CC BY-SA 3.0 |