July 17, 2013 at 9:28 am
Hi, Help! I am new to SQL...
The SP below times out with certain parm and is very slow at other times. This is
a Sp to retrieve Operation codes that go with certain part#s/models: I was told
by our DBA it had a bad exec plan when he traced it..
Any ideas would be hightly appreciated..
select * from t1
where SUBSTRING(OPCD, 1, 5) = SUBSTRING('39210', 1, 5)
and MODL='MD**A'
SELECT 'SELECT ' AS [Selection], C.[OPCD] AS [OpCode], C.[DSCT] AS [Description]
FROM t2 A,
t1 B,
t3 C,
t4 D,
t5 E
WHERE A.[PART] LIKE SUBSTRING('39210', 1, 5) + '%'
AND A.[EFFM] <= '20130716'
AND A.[EFTO] >= '20130716'
AND A.[FLAG] <> 'U'
AND A.[OPCD] = C.[OPCD]
AND C.[TYPE] = 'MAIN'
AND C.[OPCD] = B.[OPCD]
AND B.[MODL] = 'MD**A'
AND B.[EFFT] <= '20130716'
AND B.[EFTT] >= '20130716'
AND B.[MODL] = D.[PRTMLTSM]
AND D.[PRTM] = E.[ModelCode]
AND E.[PartNo] LIKE RTRIM(A.[PART]) + '%'
UNION
SELECT 'SELECT ' AS [Selection], A.[OPCD] AS [OpCode], A.[DSCT] AS [Description]
FROM t3 A,
t1 B,
t4 C,
t5 D
WHERE SUBSTRING(A.[OPCD], 1, 5) = SUBSTRING('39210', 1, 5)
AND A.[TYPE] = 'MAIN'
AND A.[OPCD] = B.[OPCD]
AND B.[MODL] = 'MD**A'
AND B.[EFFT] <= '20130716'
AND B.[EFTT] >= '20130716'
AND B.[MODL] = C.[LTSM]
AND C.[PRTM] = D.[ModelCode]
AND D.[PartNo] LIKE SUBSTRING('39210', 1, 5) + '%'
AND NOT EXISTS(SELECT * FROM t2
WHERE [OPCD] = A.[OPCD]
AND [PART] = '39210'
AND [EFFM] <= '20130716'
AND [EFTO] >= '20130716'
AND [FLAG] = 'U')
ORDER BY [Description]
July 17, 2013 at 9:31 am
Parameter sniffing.
http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/[/url]
Also, you should consider using the ansi-92 style joins instead of the older style joins. You also have a number of nonSARGable predicates.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 17, 2013 at 9:48 am
Here is a rough stab at converting this to the newer join syntax.
SELECT 'SELECT ' AS [Selection], C.[OPCD] AS [OpCode], C.[DSCT] AS [Description]
FROM t2 A
join t3 C on A.[OPCD] = C.[OPCD]
join t1 B on C.[OPCD] = B.[OPCD]
join t4 D on B.[MODL] = D.[PRTMLTSM]
join t5 E on D.[PRTM] = E.[ModelCode]
AND E.[PartNo] LIKE RTRIM(A.[PART]) + '%' --nonSARGable
WHERE A.[PART] LIKE '39210%' --There is no need for a substring here, you are getting all 5 characters anyway
AND A.[EFFM] <= '20130716'
AND A.[EFTO] >= '20130716'
AND A.[FLAG] <> 'U' --nonSARGable
AND C.[TYPE] = 'MAIN'
AND B.[MODL] = 'MD**A'
AND B.[EFFT] <= '20130716'
AND B.[EFTT] >= '20130716'
UNION --ALL??? From the data it looks like you can't get duplicates here so union all would help performance because it doesn't care about duplicates
SELECT 'SELECT ' AS [Selection], A.[OPCD] AS [OpCode], A.[DSCT] AS [Description]
FROM t3 A
join t1 B on A.[OPCD] = B.[OPCD]
join t4 C on B.[MODL] = C.[LTSM]
join t5 D on C.[PRTM] = D.[ModelCode]
WHERE SUBSTRING(A.[OPCD], 1, 5) = '39210'
AND A.[TYPE] = 'MAIN'
AND B.[MODL] = 'MD**A'
AND B.[EFFT] <= '20130716'
AND B.[EFTT] >= '20130716'
AND D.[PartNo] LIKE '39210%'
AND NOT EXISTS
(
SELECT * FROM t2
WHERE [OPCD] = A.[OPCD]
AND [PART] = '39210'
AND [EFFM] <= '20130716'
AND [EFTO] >= '20130716'
AND [FLAG] = 'U'
)
ORDER BY [Description]
I am guessing that what you posted you converted the parameters to hardcoded values? This makes it a lot harder to figure out what your actual code looks like and offer much help.
FWIW, I am not a big fan of aliasing tables in alphabetical order (A, B, C...). I find it makes the query a lot more difficult to read and understand. Consider just your query here. in the first query, t3 is C but in the second query it is A. This makes it far more confusing than it should be.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 17, 2013 at 4:09 pm
Thank you so much...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply