SQL Server Performance Forum – Threads Archive

Query kill CPU

This query is killing my CPU. I canÂ´t find any index to improve it. May be there is a better way to write it. Any clue? select it.coditm, tc.descripcion from itemsmedcol it join tabcoloresdet tcd on it.codcol = tcd.codcol join tabcolores tc on tcd.codtabcol = tc.codtabcol group by tc.descripcion, it.coditm order by coditm |–Sort(DISTINCT ORDER BY<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[it].[CODITM] ASC, [tc].[DESCRIPCION] ASC)) |–Hash Match(Inner Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[tcd].[CODCOL])=([it].[CODCOL]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[it].[CODCOL]=[tcd].[CODCOL])) |–Merge Join(Inner Join, MERGE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[tc].[CODTABCOL])=([tcd].[CODTABCOL]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[tcd].[CODTABCOL]=[tc].[CODTABCOL])) | |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[JHR].[dbo].[TABCOLORES].[PK_TABCOLORES] AS [tc]), ORDERED FORWARD) | |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[JHR].[dbo].[TABCOLORESDET].[pk_tabcoloresdet] AS [tcd]), ORDERED FORWARD) |–Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[JHR].[dbo].[ITEMSMEDCOL].[ITEMSMEDCOL_CODITM] AS [it])) Luis Martin Moderator SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important Bertrand Russell All postings are provided â€œAS ISâ€ with no warranties for accuracy.

1. Try to force nonclustered index (if you have any, if not make it nonclustered and test) on tabColoresDet.codcol
2. Do you have an composite index on itemsMedCol columns codCol and coditm? If so try to force that index to be used.
3. Try forcing different join alghorithms and maybe force specific join order. It is hard to give you more precise suggestions without knowlege of table structure, data distribution and the purpose of each table. Is table tabColores lookup table containing colors? What can be color detail if it is the meaning of tabColoresDet? What is in itemsMedCol table? How many rows do you have in each?

Mmarovic: 1) Ok.
2) Yes.
3) TabColores CodTabCol Descripcion
000 Marron-Negro
E17 Azul-Amarillo
… …………. 2940 Rows TabColoresDet CodTabcol CodCol Secuencia
000 MAR 1
000 NGO 2
E17 AMA 2
E17 AZL 1
… … . 7568 Rows ItemsMedcol CodItm CodItemAlternativo Codtal CodCol
ABE00014 NULL CAN NGO
…… …. … … 13596 Rows. Now, I rewrite the query in the following way: select it.coditm,
tc.descripcion
from itemsmedcol it with (nolock),
tabcoloresdet tcd,
tabcolores tc
WHERE COALESCE(tcd.codtabcol, tcd.codtabcol) = tc.codtabcol
AND it.codcol = tcd.codcol
group by tc.descripcion,
it.coditm
order by coditm
OPTION (LOOP JOIN) And now, after create noncluster Tabcoleresdet on CodCol, Codtabcol
and noncluster ItemsMedcol on Codcol, Coditm It run 40% faster and don’t kill my CPU. But I’m not happy yet. Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

I forgot: I can’t change any database table (3rd software) and I’m helping people who are designing web using that database, so any help with index, code is welcome.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

What is performance of original query with new indexes? Edited: I think i missunderstood table structure, ignore my previous comment.

CodItm is not unique, right? Otherwise query wouldn’t make sense.
I would try reverse column order too: nonclustered ItemsMedcol on Coditm, Codcol in that order, that should eliminate sorting. I would rather force original query, something like:
select it.coditm, tc.descripcion
from itemsmedcol it (index = idx_coditm_codcol)
inner loop join tabcoloresdet tcd on it.codcol = tcd.codcol
inner loop join tabcolores tc on tcd.codtabcol = tc.codtabcol
group by tc.descripcion, it.coditm
order by coditm
option (force order)

Will try, tomorrow I back with news.
Thanks. Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Your query run in 1m 42s and: (1516516 row(s) affected) Table ‘TABCOLORES’. Scan count 4547210, logical reads 9110659, physical reads 0, read-ahead reads 0.
Table ‘TABCOLORESDET’. Scan count 13586, logical reads 35360, physical reads 0, read-ahead reads 0.
Table ‘ITEMSMEDCOL’. Scan count 1, logical reads 101, physical reads 0, read-ahead reads 0.
My query run in 35s and: (1516516 row(s) affected) Table ‘ITEMSMEDCOL’. Scan count 5530, logical reads 29587, physical reads 0, read-ahead reads 0.
Table ‘TABCOLORES’. Scan count 7568, logical reads 15176, physical reads 0, read-ahead reads 0.
Table ‘TABCOLORESDET’. Scan count 1, logical reads 11, physical reads 0, read-ahead reads 0. Thanks again.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

I forgot to put noLock hint too. Anyway, can you please post the execution plan of both queries? I must admit I’m confused right now. The data model is strange and data distribution is not what I expected. Joining 3 tables up to 14000 rows and grouping by query returns more then 1.5 M rows. Also I don’t understand the need for coalesce function.

select it.coditm, tc.descripcion
from itemsmedcol it
inner join tabcoloresdet tcd
inner join tabcolores tc
on tcd.codtabcol = tc.codtabcol
on it.codcol = tcd.codcol
group by tc.descripcion, it.coditm
order by coditm

Mmarovic: Your plan: |–Sort(DISTINCT ORDER BY<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[it].[CODITM] ASC, [tc].[DESCRIPCION] ASC)) |–Nested Loops(Inner Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[tcd].[CODTABCOL])) |–Nested Loops(Inner Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[it].[CODCOL]) WITH PREFETCH) | |–Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[JHR].[dbo].[ITEMSMEDCOL].[IXCCTP050719_ITEMSMEDCOL_CodItm_CodCol_Power01] AS [it])) | |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[JHR].[dbo].[TABCOLORESDET].[IXCCTP050719_TABCOLORESDET_CodCol_CodTabcol_Power01] AS [tcd]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[tcd].[CODCOL]=[it].[CODCOL]) ORDERED FORWARD) |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[JHR].[dbo].[TABCOLORES].[PK_TABCOLORES] AS [tc]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[tc].[CODTABCOL]=[tcd].[CODTABCOL]) ORDERED FORWARD) My plan: |–Stream Aggregate(GROUP BY<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[it].[CODITM], [tc].[DESCRIPCION])) |–Sort(ORDER BY<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[it].[CODITM] ASC, [tc].[DESCRIPCION] ASC)) |–Nested Loops(Inner Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[tcd].[CODCOL]) WITH PREFETCH) |–Hash Match(Aggregate, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[tcd].[CODCOL], [tc].[DESCRIPCION]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[tcd].[CODCOL]=[tcd].[CODCOL] AND [tc].[DESCRIPCION]=[tc].[DESCRIPCION])) | |–Nested Loops(Inner Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[tcd].[CODTABCOL])) | |–Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[JHR].[dbo].[TABCOLORESDET].[IXCCTP050719_TABCOLORESDET_CodCol_CodTabcol_Power01] AS [tcd])) | |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[JHR].[dbo].[TABCOLORES].[PK_TABCOLORES] AS [tc]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[tc].[CODTABCOL]=If 1 then [tcd].[CODTABCOL] else [tcd].[CODTABCOL]) ORDERED FORWARD) |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[JHR].[dbo].[ITEMSMEDCOL].[IXCCTP050719_ITEMSMEDCOL_CodItm_CodCol_Power01] AS [it]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[it].[CODCOL]=[tcd].[CODCOL]) ORDERED FORWARD) Luis Martin Moderator SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important Bertrand Russell All postings are provided â€œAS ISâ€ with no warranties for accuracy.

]]>