Subscribe to this blog
Briefcase
Print
Thank you, David C, for your comment! Please see below to the answers to your questions.
The table structure is:
CREATE TABLE [dbo].[Table_NoChange] (
[ID] INT NOT NULL,
[Time]
SMALLDATETIME NOT NULL,
[Currency] SMALLMONEY,
[Flag]
SMALLINT,
[Currency_Max] SMALLMONEY,
[Note] VARCHAR(500))
There is only one index:
ALTER TABLE [dbo].[Table_NoChange] ADD CONSTRAINT [PK_Table_NOCHANGE] PRIMARY KEY CLUSTERED ([ID], [Time])
Originally, I did not create the table using the same partition scheme as the index. Thank you for pointing that out. I have made the update to the partitioning (added ON PS_Table([Time]) to the end of the table creation statement). The index creation statement already had the ON PS_Table ([Time]) option.
These are my results (Executed DBCC DROPCLEANBUFFERS before each Query script):
| Query | Description | CPU | Elapsed | Scan | Logical | Physical | Read-Ahead | Query Cost | Execution Count |
| Query1 | No Change | 0 | 16 | 1 | 5 | 2 | 2 | 7% | |
| Query1 | Partitioned By Year (Page) and Month (Row) | 0 | 154 | 15 | 23 | 14 | 32 | 79% | |
| Query1 | Page | 0 | 28 | 1 | 4 | 3 | 8 | 6% | |
| Query1 | Row | 0 | 19 | 1 | 5 | 3 | 2 | 8% | |
| Query2 | No Change | 0 | 18 | 1 | 6 | 2 | 3 | 7% | 11,077 |
| Query2 | Partitioned By Year (Page) and Month (Row) | 0 | 30 | 15 | 24 | 14 | 32 | 79% | 11,077 |
| Query2 | Page | 0 | 19 | 1 | 5 | 3 | 2 | 6% | 11,077 |
| Query2 | Row | 0 | 19 | 1 | 5 | 3 | 2 | 8% | 11,077 |
| Query3 | No Change | 496 | 1648 | 410 | 1892 | 448 | 2545 | 24% | 2 |
| Query3 | Partitioned By Year (Page) and Month (Row) | 265 | 6514 | 6150 | 9540 | 1320 | 8880 | 27% | 2 |
| Query3 | Page | 93 | 1987 | 410 | 1739 | 493 | 2519 | 24% | 2 |
| Query3 | Row | 204 | 2412 | 410 | 1826 | 588 | 2455 | 24% | 2 |
| Query4 | No Change | 5955 | 1744 | 25 | 78674 | 476 | 78249 | 36% | 20 |
| Query4 | Partitioned By Year (Page) and Month (Row) | 8392 | 3331 | 27 | 32744 | 209 | 32261 | 20% | 20 |
| Query4 | Page | 14480 | 896 | 25 | 31532 | 264 | 30918 | 16% | 20 |
| Query4 | Row | 7787 | 1399 | 25 | 62282 | 591 | 61217 | 29% | 20 |
| Query5 | No Change | 3976 | 1737 | 25 | 80042 | 484 | 78273 | 35% | 4,053 |
| Query5 | Partitioned By Year (Page) and Month (Row) | 6083 | 2662 | 34 | 34116 | 224 | 32261 | 22% | 4,053 |
| Query5 | Page | 8442 | 751 | 25 | 32904 | 296 | 30918 | 15% | 4,053 |
| Query5 | Row | 5348 | 1352 | 25 | 63642 | 618 | 61216 | 28% | 4,053 |
| Query6 | No Change | 0 | 13 | 0 | 3 | 2 | 0 | 6% | 1,594,377 |
| Query6 | Partitioned By Year (Page) and Month (Row) | 0 | 161 | 15 | 17 | 14 | 0 | 83% | 1,594,377 |
| Query6 | Page | 0 | 26 | 0 | 3 | 3 | 0 | 6% | 1,594,377 |
| Query6 | Row | 0 | 22 | 0 | 3 | 3 | 0 | 6% | 1,594,377 |
Testing led me to think that Row Compression is still best for this table. Partitioning continues to be more costly to implement.
To those kind enough to leave me their input, would you agree with my conclusion? Do you have any other thoughts/observations from my testing?
Posted by Jason Brimhall on 5 May 2011
Thanks for sharing with us.
Posted by David C on 9 May 2011
Hi James,
No problem. I obviously don't know what types of queries you're testing with, but I tried creating 4 versions of your table; 1 normal table, 1 partitioned, 1 compressed (ROW), and 1 partitioned and compressed (ROW). I then populated each table with the same 100,000 rows of dummy data.
My partition function was created on your [time] column, and was partitioned by calendar year. I then added the same clustered primary key that you specified to each table.
On the partitioned tables I created them using the partition function, and obviously compressed them for the compressed tables. I used ROW compression for no reason other than its what you said you thought performed better in your scenario.
I ran several simple queries (mostly performing range searches) and found the I/O's consistantly much lower for the partitioned & compressed table (as I would've guessed). The partitioned table was slightly higher (very marginally), with the compressed table next best at about 10% better than the normal (non-partitioned/non-compressed) table. So in other words I got very different results to you, which is interesting. I've got the demo setup scripts available if you'd like them.
Cheers
Dave
Posted by dajonx on 10 May 2011
Thank you David! That is quite interesting because I thought the partitioned and compressed table would have performed better as well.
Can you please send me the demo scripts that you've used to try out in my environment?
Again, thank you very much for doing all of the work to validate my results.
Posted by dajonx on 10 May 2011
Oops, my email address is dajonx@gmail.com.