Solved

Join on Multiple Columns Index Question

Posted on 2006-07-18
14
352 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
Comment Utility
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
Comment Utility
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
Comment Utility
why do you need to convert fkID_107, fkID_108  to varchar(150)??
what data types are they..?
0
 
LVL 26

Expert Comment

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

Author Comment

by:99Times
Comment Utility
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
Comment Utility
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
Comment Utility

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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 3

Author Comment

by:99Times
Comment Utility
remove the clustered, rather
0
 
LVL 26

Expert Comment

by:DireOrbAnt
Comment Utility
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
Comment Utility
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
Comment Utility
>>>var_DSCategory, var_optionname
In that order or var_optionname, var_DSCategory ?
0
 
LVL 3

Author Comment

by:99Times
Comment Utility

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
Comment Utility
Bookmark lookup on what? Also, have you tried running the wueries without any NOLOCK hints?
0
 
LVL 3

Author Comment

by:99Times
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

743 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now