Join on Multiple Columns Index Question

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.
Who is Participating?
DireOrbAntConnect With a Mentor Commented:
Bookmark lookup on what? Also, have you tried running the wueries without any NOLOCK hints?
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
99TimesAuthor Commented:
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.
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

why do you need to convert fkID_107, fkID_108  to varchar(150)??
what data types are they..?
What takes 78%? Table Scan, Index Scan? Copy that part.
99TimesAuthor Commented:
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

OBJECT:([Consolidata].[dbo].[CDTrimOptions].[PK_CDTrimOptions] AS [TT]), WHERE:([TT].[var_DSCategory]='EC')
If you can, make the clustered index a non clustered index and make a clustered index on var_DSCategory
99TimesAuthor Commented:

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.
99TimesAuthor Commented:
remove the clustered, rather
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...
99TimesAuthor Commented:
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.
>>>var_DSCategory, var_optionname
In that order or var_optionname, var_DSCategory ?
99TimesAuthor Commented:

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%
99TimesAuthor Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.