?
Solved

Join on Multiple Columns Index Question

Posted on 2006-07-18
14
Medium Priority
?
363 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
2017 Webroot Threat Report

MSPs: Get the facts you need to protect your clients.
The 2017 Webroot Threat Report provides a uniquely insightful global view into the analysis and discoveries made by the Webroot® Threat Intelligence Platform to provide insights on key trends and risks as seen by our users.

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

762 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