Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Stored procedure runs very slow or times out Expand / Collapse
Author
Message
Posted Wednesday, July 17, 2013 9:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 18, 2013 2:23 PM
Points: 2, Visits: 4
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]
Post #1474686
Posted Wednesday, July 17, 2013 9:31 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:04 PM
Points: 13,086, Visits: 12,553
Parameter sniffing.

http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1474689
Posted Wednesday, July 17, 2013 9:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:04 PM
Points: 13,086, Visits: 12,553
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1474699
Posted Wednesday, July 17, 2013 4:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 18, 2013 2:23 PM
Points: 2, Visits: 4
Thank you so much...
Post #1474827
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse