CODEWITHSUNDEEP
sql-serverdatabasesql-server-2008
Tiyyob
Tiyyob

Reputation: 89

Query is taking long time in spite of indexes

SELECT id,
    file_name As 'Document',
    ref_number AS 'Ref Number',
    entered_by As 'Entered By',
    created_date As 'Created Date',
    manpower,
    user_path,
    owner_path
FROM t_document_holder
WHERE 1 = 1
    AND incoming_to = 'Criteria'
    AND incoming_from = 'Criteria'
    AND CONTAINS (
        user_path,
        'Criteria'
        )
ORDER BY created_date,
    entered_by

Indexes:

id = Clustered Index
ref_number = Non Clustered
entered_by = Non Clustered Index
manpower = Non Clustered Index
user_path and owner_path = Full Text Search Index
incoming_to and incoming_from = Non Clustered Index

To summarize, most of the columns (all in WHERE condition) are part of an index but still this query is taking 25 seconds to return 527 rows out of total 115,000 rows in table.

Execution PlanQuery Execution Plan

can someone kindly advice that why it is taking so long to return these rows.

Query Execution Plan for Non-Clustered Index

enter image description here

Upvotes: 0

Views: 1440

Answers (1)

Tiyyob
Tiyyob

Reputation: 89

After Hours of googling, i found the solution. Solution was to rebuild all indexes on this particular table and it worked like charm. For more details consult this article.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/044316b5-32d7-4264-bde4-1ebfb5633262/order-by-extremely-slow-in-sql-2008-r2?forum=transactsql

Upvotes: 1

Related Questions