Optimizing Query Performance for Large Table

Question

Optimizing Query Performance for Large Table

PS 401

I need help with improving performance on a large table with close to 90 million records. The primary key is alphanumeric and the table is hosted in SQL Managed Instance. Any suggestions or advice would be appreciated.

Thank you.

Olaf Helper 47,436 Reputation points

2023-10-18T05:17:14.4633333+00:00

Any suggestions or advice would be appreciated.

With that less on informations it's not possible to give yo an advice.

Do you have any current problem; if, please describe it in detail
PS 401 Reputation points

2023-10-18T14:47:38.65+00:00
Thank you @Olaf Helper for your response. My apologies for not initially listing the current issues with that object. Here is a list of the problems:

Increased query duration for the table.

Extended time required for data loads.

Prolonged weekly maintenance for index rebuild and statistics updates.

Please let me know if you needed any more details which would help you help me.
SSingh-MSFT 16,371 Reputation points Moderator

2023-10-20T06:47:57.57+00:00

Hi PS •,

Could you please refer to Erland's reply and provide more information so that assistance could be provided.

Thanks

Accepted answer

1 additional answer

Your answer

Olaf Helper 47,436 Reputation points

2023-10-18T05:17:14.4633333+00:00

Any suggestions or advice would be appreciated.

With that less on informations it's not possible to give yo an advice.

Do you have any current problem; if, please describe it in detail
PS 401 Reputation points

2023-10-18T14:47:38.65+00:00

Thank you @Olaf Helper for your response. My apologies for not initially listing the current issues with that object. Here is a list of the problems:

Increased query duration for the table.

Extended time required for data loads.

Prolonged weekly maintenance for index rebuild and statistics updates.

Please let me know if you needed any more details which would help you help me.
SSingh-MSFT 16,371 Reputation points Moderator

2023-10-20T06:47:57.57+00:00

Hi PS •,

Could you please refer to Erland's reply and provide more information so that assistance could be provided.

Thanks

Answer 1

Erland Sommarskog 121.4K MVP Volunteer Moderator

Partitioning is not really a feature for better performance.

But we need to see queries, query plans etc to give any assistance. What you have provided is far too little information.

Answer 2

LiHongMSFT-4306 31,566

Hi @PS

One way is using SQL table and index partitioning.

Please refer to this doc for more details: Partitioned tables and indexes.

Best regards,

Cosmog Hong

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

PS 401 Reputation points

2023-10-18T14:50:40.1466667+00:00

Thank you @LiHongMSFT-4306 for the reference. I'll delve into the specifics, and I'm also interested in determining whether partitioning based on an alphanumeric field will produce improved results. Please share your thoughts.

Share via

Optimizing Query Performance for Large Table

1 additional answer

Your answer