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 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.