Big table issue

SVA 116 Reputation points
2025-01-21T16:06:19.7066667+00:00

Hi,

What is the best method to optimize a 100 million row table. Which is slow even after creating the index

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,483 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Michael Taylor 56,951 Reputation points
    2025-01-21T16:42:20.7933333+00:00

    There is nothing wrong with have a 100 million row table. If your DB can't handle that then there is a problem. The problem comes in when you try to query that 100 million row table with a query that is poorly written, retrieves too much data or queries on things that aren't optimized. That is where your DBA comes in. Have them run the query profiler while you run your query. That will identify what is slow in your query. Then have the DBA optimize that.

    Without seeing your query(ies) that is/are slow and understanding the table(s) structure it is going to be very hard to provide any sort of guidance. As an example, if you're using a WHERE clause on a column that isn't indexed then it will probably be slow. If you're running a WHERE clause on a column that is indexed but you're using a UDF or something to filter then an index is not going to be as useful. If you're joining across tables but your joins end up pulling a lot of rows from differing tables then your query will suffer irrelevant unless you filter down to the subset you actually need. Again, without the query(ies) and understanding of your DB structure it is hard to say what is wrong.

    0 comments No comments

  2. LiHongMSFT-4306 30,751 Reputation points
    2025-01-22T02:48:57.7666667+00:00

    Hi @SVA

    Considering that is a 100 million row table, there might be multiple causes for slow performance.

    You claim that the index has been created. However, as experts comment above, whether it is properly created depends on your query.

    Besides of the index, there are also other causes of the slow performance, like: the query optimization itself, memory and I/O tuning, block issue, and execution plans, etc.

    Here is a troubleshooting doc you might refer to: Troubleshoot slow-running queries in SQL Server.

    Note that it's important to test any changes in a test environment before applying them to production systems.

    Best regards,

    Cosmog


    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".

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.