Improving Query Performance of a Partitioned Table

Question

Hi everyone,

I need a little help with working with partitioned tables. Table partitioning is a bit new concept for me. I am however able to successfully implement it on one of my very large tables. The table is partitioned on a datetime column on a month basis.

The Outcome:

• Queries that use the datetime column only in a WHERE clause were extremely fast.
• Queries that use the datetime column and other columns in a WHERE clause result in an extremely slow response. (This is always going to be the case as I need to use the other columns in a Group By or WHERE clause ). An attempt at solving the problem:
• I tried to create a non-clustered index for the other columns. (see below)
  create index idx_itemid on very_large_table(itemid,date_time) on PS_PartitionScheme(date_time);
  create index idx_itemid_status on very_large_table(itemid,date_time,item_status)on PS_PartitionScheme(date_time);

But this didn't seem to fix it.

My Query looks like this:
SELECT itemid, item_status, count(*) as 'cnt'
FROM very_large_table WHERE date_time >= '2022-01-01 00:00:00' AND date_time <= '2022-03-31 23:59:00'
GROUP BY itemid,item_status

Any advice on what to do will be really helpful.
Thanks

Answer 1

Hi @Olumuyiwa Sobayo

as far as I understood... you have implemented partioning on an existing table with existing structure... so everything existed and you "just" splited it up into several partitions.

You have to tell those additional indexes also that they are based on a partition to perform accordingly!

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql?view=sql-server-ver15#on-partition_scheme_name--column_name-

Actually all those "old" indexes don't know anything about those partitions, you have to tell them that there is something going on ;-)

Here is an example:
https://learn.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql?view=sql-server-ver15#j-create-a-partitioned-index

Hope this helps you to understand how to proceed.

Answer 2

For this particular query, I suggest changing this index:

create index idx_itemid_status on very_large_table(itemid,date_time,item_status) on PS_PartitionScheme(date_time);  

to the version Below to better support the GROUP BY clause. Partition elimination should take care of the date_time filter criteria without it being a key column and avoiding touching partitions for other dates.

create index idx_itemid_status on very_large_table(itemid,item_status) on PS_PartitionScheme(date_time) WITH(DROP_EXISTING=ON);

Answer 3

Hi @Olumuyiwa Sobayo ,

You can refer here:
https://learn.microsoft.com/en-us/sql/relational-databases/partitions/partitioned-tables-and-indexes?view=sql-server-ver16
https://learn.microsoft.com/en-us/sql/t-sql/statements/create-partition-scheme-transact-sql?view=sql-server-ver16

Best regards
Niko