CODEWITHSUNDEEP
sqlsql-serverperformance
ArveZ
ArveZ

Reputation: 43

Clustered key lookup high cost

I'm using Microsoft SQL Server Enterprise Edition (64-bit). Why is my query executing so slowly? It's taking 10 minutes.

Here are some screenshots of the execution plans which places cost most

enter image description here

enter image description here

SELECT 
    1_code
    ,count(DISTINCT 2_code) AS viso
FROM
    docum(NOLOCK)
INNER JOIN 
    for_vers(NOLOCK) ON doc = fv
INNER JOIN 
    for(NOLOCK) ON fv = f
INNER JOIN 
    persons(NOLOCK) ON doc = p
INNER JOIN 
    tax ti(NOLOCK) ON p = ti
INNER JOIN 
    person(NOLOCK) ON pm = p
WHERE 
    pm_ = 14
    AND (pm_date IS NULL OR pm_date > getdate())
    AND (pm_till IS NULL OR pm_till > getdate())
    AND start_date >= '2015-01-01'
    AND end_date <= '2015-12-31'
    AND code = 1
    AND code IN (25)
    AND dprt_code IN (20, 21, 22, 23, 24, 25, 30)
GROUP BY
    code

Upvotes: 3

Views: 5086

Answers (2)

Rousonur Jaman
Rousonur Jaman

Reputation: 1261

If possible then try to reduce those inner joins. If not possible then try to inner join at the end of the script. It saves huge time.

And for removing Clustered key lookup you should use Covering Index. If you properly create Covering Index then there will be no Clustered key lookup.

Upvotes: 2

Ivan Sivak
Ivan Sivak

Reputation: 7488

Consider updating the statistics. Your execution plan shows a big difference between actual rows number (as you claim 52,082,116 rows) and the estimated rows number shown by execution plan. To find out more about statistics see this link. To quote:

The query optimizer uses statistics to create query plans that improve query performance.

You can call the update statistics query as mentioned in the documentation but please keep in mind when and where you run this command (it will affect the performance). Be carefull and read the docs. Consider perhaps the Update Statistics Task in the Maintenance Plan. There's also a nice article which explains more about statistics and performance.

As others pointed out in the comments, consider some sort of Denormalization and double check if your isolation level is really alright (your nolock).

Check these hints for understanding the execution plan.

Upvotes: 3

Related Questions