2

I have a huge table with 3B of rows containing around 6 months of data running on Microsoft SQL server 2017 (RTM), which is partitioned day by day on a date column (each day on a separate filegroup and each filegroup has 1 data file).

this table has an identity field which is bigint.

I have two indexes : a clustered index on date and id a non clustered index on date

I'm trying to run the following query :

select top 500000 * from table with(nolock) where id>@certain_id order by id

but the query is taking a lot of time. I tried to create an non clustered index on id field , but nothing changes!

the weird part I was able to run the same query with no issues and with fast response in the past. But due to some circumstances I had to format the server and re-attach the database containing the partitioned table, and I'm now having this issue.

any hint is much appreciated.

Improve this question
10
  • I suppose this query worked fast when you had non-partitioned index on id but if your indexes are not aligned with partitions then partitioning has no sense at all. The question is - do you really need to run such query? Does id ordered the same way as date column?
    – NikitaSerbskiy
    Jul 25, 2020 at 15:26
  • most of the queries on the table don't include the id field , but I have to run this query for a specific task. what happen if I create a non-partitioned index on id ?
    – user123
    Jul 25, 2020 at 15:34
  • you will not be able to switch/truncate partitions for data loading/deletion
    – NikitaSerbskiy
    Jul 25, 2020 at 15:36
  • is there a way to run this query fast without creating the non partitioned index?
    – user123
    Jul 25, 2020 at 15:42
  • Does id ordered the same way as date column?
    – NikitaSerbskiy
    Jul 25, 2020 at 15:43

1 Answer 1

Reset to default
2

Let's try this query:

SELECT TOP 1000 A.id
FROM  sys.partitions AS P
   CROSS APPLY ( SELECT TOP 1000 T1.id
          FROM dbo.table AS T1
          WHERE $PARTITION.PF1(T1.date) = P.partition_number 
           AND T1.id > @certain_id
           ORDER BY T1.id ) AS A
  WHERE P.object_id = OBJECT_ID('dbo.table')  
  AND P.index_id = INDEXPROPERTY( OBJECTID('dbo.table'), 'idx_c1', 'INDEXID')
    ORDER BY A.id;

from here: https://support.microsoft.com/en-in/help/2965553/decreased-performance-for-sql-server-when-you-run-a-top-max-or-min-agg

Improve this answer
9
  • the table stores CDRs (call detail records) of a tier 1 voice carrier , so there is dozens of records every second. it's not efficient to select distinct date as there will be millions of distinct dates. In any way I tried the query and it's slow.
    – user123
    Jul 25, 2020 at 17:27
  • please add table definitions to the quesiton (I supposed date is a date, not datetime)
    – NikitaSerbskiy
    Jul 25, 2020 at 17:37
  • @user123 I modified the query to select TOP N rows from each partition directly but you need partitioned index on id
    – NikitaSerbskiy
    Jul 25, 2020 at 17:56
  • thank you!! I tried the query , and it's fast! but I'm worried about a thing , if the top 500000 are in two different partitions, will it be a problem ?
    – user123
    Jul 25, 2020 at 18:41
  • @user123 I think you need just to try it ) also it seems I forget to add the condition "id>@certain_id". I added it now and with this condition it should work faster
    – NikitaSerbskiy
    Jul 25, 2020 at 18:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.