Improving Query Performance of a Partitioned Table

Olumuyiwa Sobayo 1 Reputation point
2022-11-15T12:32:10.633+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,336 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,599 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Bjoern Peters 8,856 Reputation points
    2022-11-15T17:38:45.48+00:00

    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.

    0 comments No comments

  2. Dan Guzman 9,226 Reputation points
    2022-11-15T18:20:28.903+00:00

    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);
    
    0 comments No comments