Say I have an orders.Order
table that is partitioned on CreatedWhen
(a datetime column set to GetDate()
when the row is created).
The table has over 10 million rows and the partition is quarterly (or some such regular interval).
If I run a query like:
SELECT ord.OrderId, ord.ClientId, ord.ReceptId, ord.Cost, agt.State
FROM orders.Order ord
join personnel.Agent agt
on ord.AgentId = agt.AgentId
WHERE agt.FirstName = 'Bob'
and LastName = 'Whiely'
and ord.Cost > 135
Am I going to see any benefit from the partition for this query?
I don't see how there could be a benefit because I am not using the partition value in my query and all of the table still needs to be searched.
Note: The results of the query will all be in the most recent partition. But I don't see how that would help.
Second Note: The partitions are all on the same SAN drive (Sys Admins won't let it be any other way), so the disk IO will not be parallel.
Is there a better way to partition so that random queries, like the one above, will perform better? (Maybe on the primary key?)
Third Note: The query above is made up. I am using it to show that I have many queries that hit many random columns (which are properly indexed).
Best Answer
Partitioning is not typically a performance enhancer. Normally, partitioning is employed to enable efficient data management. For instance, you can employ partitioning to easily swap old rows out of a table into an archive table, one partition at a time.
Querying against a partitioned table where your query does not include the partitioning key forces SQL Server to look at the entire table. This is less efficient than simply scanning or seeking into a non-partitioned table because each partition is essentially a table, necessitating a
UNION ALL
style query against all the partitions. This is not particularly obvious when looking at a query plan for a partitioned table, although you can see it if you look carefully at the pop-up properties for an index or table seek or scan:To make this more obvious, lets set up a simple test rig in tempdb, with a date-based partitioning key:
This will populate the table with 1,000,000 rows with randomly generated data evenly spread over all partitions:
Let's go ahead and create a non-clustered index using our partitioning scheme:
Now, let's look at three "typical" queries you might execute (the first query just gets an actual value out of the table, which is then used in the
WHERE
clause for the three queries):Here is a quick screenshot of the execution plans so you can see the difference adding the partitioning key to the
WHERE
clause can make:The "query costs relative to the batch" numbers are instructive here. The first query consumes 50% of the batch cost since it performed a scan of all 13 partitions. It does that since it has no idea where in the table the particular value of
Data1
exists. The second query consumes only 23% by virtue of the "Start Date" parameter, which can eliminate half the partitions. Finally, the third query only consumes 4% since it only needs to look at a single partition, because we included a "start date" and an "end date".The "statistics" for the 3 queries are:
What if we add one of the other columns to these queries:
The execution plans:
The statistics for those:
So, lets just do away with the partitioning, and re-run all those tests:
Now, the first set of queries:
And the plans:
And the stats:
All three queries now have the same "cost", which is substantially lower than the partitioned version.
The queries with
Data1
andData2
columns:and the stats:
Clearly, if you can't do the queries the correct way, by including the partitioning key, then you are going to see worse performance with partitioning than without.