I'm trying to do a performance comparison of a table with partitioning and the same table without partitioning.
So we have Subcriber
and SubscriberPartitioned
tables.
Tables structure is:
SubscriberId | Name | Email | Telephone | UserId (partition column)
Data: I'm using SQL Data Generator:
- to fill the tables with 10 million rows each
- the
UserId
range goes from 1-200
Partitioning: I have partitioned the table SubcriberPartioned
in 2 according to the UserId
, which goes from 1 to 200. So we have approx 5 million rows in each partition.
Performance measure: I'm using SQL Server Profiler to measure the query times.
Why does SQL Server differ so much in query times?
For example the query
select * from subcriber where SubscriberId = 1000
... will the first time take like 40 seconds, and if I re-execute it will take less than a second. If I try with another Id it also will take less than a second. But the first time took a lot of time.
So, is there any optimization or caching being done automatically in the background by SQL Server?
I was expecting the same query to take the same time if repeated over time...
Which do you think would be the best queries to run and how to measure them in order to test the partitioning of the table and to see the optimization gained with the partition strategy instead of the normal table.
EDIT:
- After answers on this post I made some cold start tests, and found that the non partitioned table was performing faster than the partitioned table.
Specially this query caught my attention:
select * from [table] where IdUser = 100
Why doesn't the partitioned table improve the performance on it? The above query includes a filter by the partition column, wouldn't that focus the engine to scan only half of the records on the partitioned approach?
EDIT 2:
I have performed the tests again. Assigning the second partition a different file group, and I have reached a performance improvement of 50 % in the mentioned query.