Solved

Join on Multiple Columns Index Question

Posted on 2006-07-18
14
354 Views
Last Modified: 2008-02-01
I have a query that is performing very poorly, the root cause is the CDTrimOptions table.  I have tried a few indexes and none have seemed to help.  ITW throws an error when I try to tune it unless I take out the 2nd and 3rd join on CDImagesColorized.  Some kind of limitation in ITW that I wasn't aware of.

Could someone offer an index based on the following information or advice on how to speed this up.  Thanks.

SELECT LEFT(IC.originalPath,(LEN(IC.originalPath) + 1 - CHARINDEX('\',REVERSE(IC.OriginalPath)))) AS partImagePath,TT.var_optionimagename as imageName
FROM Consolidata.dbo.CDTrimOptions TT WITH(NOLOCK)
INNER JOIN Consolidata.dbo.CDImagesColorized IC WITH(NOLOCK) ON IC.fkID_107 = TT.fkID_107 AND IC.fkID_108 = TT.fkID_108 AND TT.option_ID = IC.optionID
INNER JOIN Consolidata.dbo.CDTrimLevels TL WITH(NOLOCK) ON CONVERT(VARCHAR(150),IC.fkID_107) + CONVERT(VARCHAR(150),IC.fkID_108) = TL.JatoKey
INNER JOIN DealerskinsVersion2.dbo.Inventory I WITH(NOLOCK) ON TL.ID = I.fkCDTrimLevels
WHERE I.fkSites = 1367
AND (I.stocknum='56226')
AND TT.var_DSCategory = 'EC'
ORDER BY TT.var_optionname

Table 'CDTrimLevels'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'Inventory'. Scan count 1, logical reads 310, physical reads 0, read-ahead reads 0.
Table 'CDImagesColorized'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'CDTrimOptions'. Scan count 3, logical reads 10830, physical reads 36, read-ahead reads 10161.
0
Comment
Question by:99Times
  • 7
  • 6
14 Comments
 
LVL 26

Expert Comment

by:DireOrbAnt
ID: 17130888
How big is CDTrimOptions ?
Do you have an index on var_DSCategory. What about fkID_107, fkID_108 and option_ID? Where is the clustered index at?
How big are the other tables?

In Query Analyzer, pick show execution plan and post where the high % is used. Check for any type of table/index scans
0
 
LVL 3

Author Comment

by:99Times
ID: 17130994
CDTrimOptions is 375,000 Rows
CDTrimLevels is 9,000
CDImagesColorized is 67,000
Inventory is 330,000

The estimated execution plan has CDTrimOptions at 78% of the estimated cost.  I have a non-clustered index on fkID_107, fkID_108 and option_ID.  And one on var_DSCategory, var_optionname.  However the optimizer is using the Clustered Primary key index, which is just on an identity column.  I can post the Show Plan Text if it would help.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 17131195
why do you need to convert fkID_107, fkID_108  to varchar(150)??
what data types are they..?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 26

Expert Comment

by:DireOrbAnt
ID: 17131256
What takes 78%? Table Scan, Index Scan? Copy that part.
0
 
LVL 3

Author Comment

by:99Times
ID: 17131492
They are VARCHARs.  I am doing that to concatenate.

               Clustered Index Scan
Scanning a clustered Index, entirely or only a range

Physical Operation:               Clustered Index Scan
Logical Operation:                Clustered Index Scan
Estimated Row Count:            72,479
Estimated Row Size:              213
Estimated I/O Cost:                8.04
Estimated CPU Cost:               0.205
Estimated number of executes:1.0
Estimated cost                         8.249353(78%)
Estimated subtreee cost:          8.25

Argument:
OBJECT:([Consolidata].[dbo].[CDTrimOptions].[PK_CDTrimOptions] AS [TT]), WHERE:([TT].[var_DSCategory]='EC')
0
 
LVL 26

Expert Comment

by:DireOrbAnt
ID: 17133136
If you can, make the clustered index a non clustered index and make a clustered index on var_DSCategory
0
 
LVL 3

Author Comment

by:99Times
ID: 17133268

That is not an option, since to remove the non-clustered I would have drop the PK contraint and I can't do that because the table is being replicated.
0
 
LVL 3

Author Comment

by:99Times
ID: 17133278
remove the clustered, rather
0
 
LVL 26

Expert Comment

by:DireOrbAnt
ID: 17133301
Hum, you don't want to drop it, just make it non clustered. Now, I'm not sure what effect this would have to the replication :( The index would still be there, just not as clustered.

You said you have an index on var_DSCategory? If not, you need one. If you do, unsure why SQL is scanning the custered index...
0
 
LVL 3

Author Comment

by:99Times
ID: 17133438
I get this message:

Server: Msg 1925, Level 16, State 2, Line 1
Cannot convert a clustered index to a nonclustered index using the DROP_EXISTING option.


And yes to the second question.  The index is on var_DSCategory, var_optionname.

Would it help to remove the other column from the index?

And BTW, I ran dbcc dbreindex on this table last night, so I know the indexes aren't fragmented.
0
 
LVL 26

Expert Comment

by:DireOrbAnt
ID: 17133495
>>>var_DSCategory, var_optionname
In that order or var_optionname, var_DSCategory ?
0
 
LVL 3

Author Comment

by:99Times
ID: 17133533

No, it is the first one - >>>var_DSCategory, var_optionname

When I force the index.  CDTrimOptions cost goes to 0%, but it adds a Bookmark Lookup that costs 99%
0
 
LVL 26

Accepted Solution

by:
DireOrbAnt earned 500 total points
ID: 17134282
Bookmark lookup on what? Also, have you tried running the wueries without any NOLOCK hints?
0
 
LVL 3

Author Comment

by:99Times
ID: 17180007
I found out what the problem on this was....The firmware on my RAID controller was slightly out-of-date causing the cache battery to de-charge.  That was causing the very high I/O.

I'll give you the points for helping.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question