Post reply

Select query is slow for Non clustered index

  • November 3, 2020 at 9:21 am

    #3803813

    I am using SQL Server 2008 R2. Select query is slow when non cluster index is used in the table  compared to table without index.

    Query used:

    select * from table order by customer_name

    It is  too slow (more than 1 minute for 40000 rows)

    Error was due to non-clustered index , without index  query executed in 5 sec.

    How did indexing caused  problem?

    The query plan:

    For without index is : https://www.brentozar.com/pastetheplan/?id=rJZtQfltv

    For with index is : https://www.brentozar.com/pastetheplan/?id=Bkrg8zgKD

    Queries with Different conditions executed and results updated in below Document.

    Query plan  for Query  with order by : https://www.brentozar.com/pastetheplan/?id=ByOxkbpYP

    Query plan  for Query  without order by : https://www.brentozar.com/pastetheplan/?id=S1WB1bpYD

    Summery :

    1. column [sl_no] is clustered index
    2. column [Customer_Name] is non-clustered index
    3. For the queries column with large text used
    4. The elapsed time for  :

    • With Order by Query1 - 12ms
    • With Order by  Query2- 1sec
    • Without Order by Query1-0ms
    • Without Order by Query2- 3sec
    • Query with order by with non clustered index on column -5 sec

    Attachments:
    You must be logged in to view attached files.

  • November 3, 2020 at 9:59 am

    #3803823

    Oh, where is my crystal ball again? Couldn't you at least tell us on which column you had the non-clustered index? Even better, share the actual query plans for the two scenarios. Without that information, your question cannot be answered. We can only make wild guesses.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply