Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sbushway
Starting Member
18 Posts |
 Posted - 2006-07-05 : 09:25:08
|
| Hi,I've got a query (I won't post it now, but I can if I need to) that displays the result set within 2 seconds when I run it in Query Analyzer. But when I put that exact query into a stored procedure and run that sp from Query Analyzer, it takes 18 minutes to complete.The query is exactly the same in both the query and in the sp - the only difference is that the sp has the "Create Procedure" and "Go" statements. Everything else is the same.Any ideas?Thanks in advance,Suzanne |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-07-05 : 09:35:54
|
| If you have the CREATE PROCEDURE in the code when running in QA, are you really getting a resultset back from server, or are you creating the stored procedure in less than 2 seconds?Peter LarssonHelsingborg, Sweden |
 |
|
|
sbushway
Starting Member
18 Posts |
Posted - 2006-07-05 : 09:49:30
|
| Nope - I don't have the CREATE PROCEDURE statement in the code I'm running in QA. It's just straight SQL running in QA - the exact SQL that's in my SP.Thanks,Suzanne |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2006-07-05 : 09:59:15
|
| search here for the term 'parameter sniffing'....and see it's effect on performance. |
|
|
|
essamughal
Starting Member
4 Posts |
Posted - 2006-07-05 : 11:32:10
|
| Hi; Would you mind posting the query, i want to see what is the difference in both cases. ThanksMuhammad Essa MughalMCTS: SQL Server 2005Toronto, Canada |
|
|
|
sbushway
Starting Member
18 Posts |
Posted - 2006-07-05 : 12:48:53
|
| Sure - it's a bit long, so bear with me:----------------------------------------Stored Procedure - takes 18 minutes----------------------------------------CREATE PROCEDURE SPSLS_NEASRptsASDECLARE @nUIC char(5)DECLARE @nRptID char(9)DECLARE @nIsAdmin char(1)DECLARE @Parent char(1)DECLARE @nAll char(1)SET @nUIC = '03363'SET @nRptID = '77487'SET @nIsAdmin = 'Y'SET @nAll = 'N'SET @Parent = (SELECT CASE WHEN EXISTS (SELECT * FROM orders.dbo.tbl_Addresses WHERE (Parent_UIC = @nUIC) and (Parent_UIC <> UIC)) THEN 'P' ELSE '' END) SELECT T.UIC as TUIC, @Parent AS PARENT, @nIsAdmin as isAdmin FROM orders.dbo.tbl_Addresses T WHERE T.UIC = @nUIC --FOR XML AUTO SELECT DISTINCT R.rptid, R.rptdiary, R.rptuic, R.rptcycle, R.rptpg, R.rptdate, U.UIC, CASE WHEN EXISTS (SELECT * FROM orders.dbo.tbl_Addresses T WHERE (T.Parent_UIC = U.UIC) and (T.Parent_UIC <> T.UIC)) THEN 'P' ELSE '' END AS PARENT, U.PLAD AS ShortTitle, CC.RAAUTH AS CC_RAAUTH, (UPPER(LEFT(DATENAME(MONTH,(LEFT(CC.EXAMDATE,2)+'/'+SUBSTRING(CC.EXAMDATE,3,2)+'/'+RIGHT(CC.EXAMDATE,2))),3))+' '+RIGHT(CC.EXAMDATE,2)) as CC_FSTMOPR, C.cycle, speedie_date1, diary_ctrl, uic_ctrl, ra_sort_code AS rsc, date_of_adv AS doa, epg, drate, nam, ssn, erate1, series, serial, brclgroup, brcl_prof, prate1, tir, pc, nec_grp_cde AS necg, pro_gain_dup AS pgd, puic, dspg, std_score AS ss, perfac1, perf_mark1, tir_factor1 AS tirf1, awards, pna_factor1 AS pnaf1, cc_pna_ss1 AS cps1, cc_pna_perf1 AS cpp1, cc_pna_sum AS cps, strik_cut, final_mult1 AS fm1, fm_cut1, erlyind, pc1_cyc+pc2_cyc+pc3_cyc+pc4_cyc+pc5_cyc AS prevcyc, pc1_pts+pc2_pts+pc3_pts+pc4_pts+pc5_pts AS prevpts, total_pna_cc1 AS tpc1, rs1+rs2+rs3+rs4+rs5+rs6+rs7+rs8+rs9+rs10+rs11+rs12+rs13+rs14+rs15+rs16+rs17+rs18+rs19+rs20 AS raw_score, perc1+perc2+perc3+perc4+perc5+perc6+perc7+perc8+perc9+perc10+perc11+perc12+perc13+perc14+perc15+perc16+perc17+perc18+perc19+perc20 AS persent, arate1, spec_grp_ind AS sgi, sipg, tas, dtis, school_code AS sc, cuic, speedie_type AS sdt, speedie_datime AS sdd, ovrall_pct AS oap, status AS st, status1 AS s1, status2 AS s2, status3 AS s3, status4 AS s4, status5 AS s5, status6 AS s6, YY FROM NEASRPTSINDEX R JOIN orders.dbo.tbl_Addresses U ON ( R.rptuic = U.UIC ) or ((@nAll = 'y' ) and (@nUIC = U.Parent_UIC)) RIGHT OUTER JOIN orders.dbo.cyclemaster CC ON ( CC.CYCLE = R.rptcycle ) and (((R.rptpg = '7' ) and (CC.PAYGRADE = 'E7' )) or ((R.rptpg = '_' ) and (CC.Cycle+CC.PAYGRADE IN (Select cycle+paygrade from orders.dbo.cyclemaster where (paygrade <> 'E7') and (RESULTSVIEW <> (Select case @nIsAdmin when 'Y' then '' else 'NV' end)))))) LEFT OUTER JOIN NEASRPTS C ON ( C.cycle = R.rptcycle ) and ( speedie_date1 = R.rptdate ) and ( uic_ctrl = U.UIC ) and (((epg <> '7' ) and (epg LIKE R.rptpg )) or ((epg = '7' ) and (R.RPTPG = '7' ))) WHERE R.rptid = @nRptID ORDER BY U.UICGO------------------------------------Query run in QA - takes 2 seconds------------------------------------DECLARE @nUIC char(5)DECLARE @nRptID char(9)DECLARE @nIsAdmin char(1)DECLARE @Parent char(1)DECLARE @nAll char(1)SET @nUIC = '03363'SET @nRptID = '77487'SET @nIsAdmin = 'Y'SET @nAll = 'N'SET @Parent = (SELECT CASE WHEN EXISTS (SELECT * FROM orders.dbo.tbl_Addresses WHERE (Parent_UIC = @nUIC) and (Parent_UIC <> UIC)) THEN 'P' ELSE '' END) SELECT T.UIC as TUIC, @Parent AS PARENT, @nIsAdmin as isAdmin FROM orders.dbo.tbl_Addresses T WHERE T.UIC = @nUIC --FOR XML AUTO SELECT DISTINCT R.rptid, R.rptdiary, R.rptuic, R.rptcycle, R.rptpg, R.rptdate, U.UIC, CASE WHEN EXISTS (SELECT * FROM orders.dbo.tbl_Addresses T WHERE (T.Parent_UIC = U.UIC) and (T.Parent_UIC <> T.UIC)) THEN 'P' ELSE '' END AS PARENT, U.PLAD AS ShortTitle, CC.RAAUTH AS CC_RAAUTH, (UPPER(LEFT(DATENAME(MONTH,(LEFT(CC.EXAMDATE,2)+'/'+SUBSTRING(CC.EXAMDATE,3,2)+'/'+RIGHT(CC.EXAMDATE,2))),3))+' '+RIGHT(CC.EXAMDATE,2)) as CC_FSTMOPR, C.cycle, speedie_date1, diary_ctrl, uic_ctrl, ra_sort_code AS rsc, date_of_adv AS doa, epg, drate, nam, ssn, erate1, series, serial, brclgroup, brcl_prof, prate1, tir, pc, nec_grp_cde AS necg, pro_gain_dup AS pgd, puic, dspg, std_score AS ss, perfac1, perf_mark1, tir_factor1 AS tirf1, awards, pna_factor1 AS pnaf1, cc_pna_ss1 AS cps1, cc_pna_perf1 AS cpp1, cc_pna_sum AS cps, strik_cut, final_mult1 AS fm1, fm_cut1, erlyind, pc1_cyc+pc2_cyc+pc3_cyc+pc4_cyc+pc5_cyc AS prevcyc, pc1_pts+pc2_pts+pc3_pts+pc4_pts+pc5_pts AS prevpts, total_pna_cc1 AS tpc1, rs1+rs2+rs3+rs4+rs5+rs6+rs7+rs8+rs9+rs10+rs11+rs12+rs13+rs14+rs15+rs16+rs17+rs18+rs19+rs20 AS raw_score, perc1+perc2+perc3+perc4+perc5+perc6+perc7+perc8+perc9+perc10+perc11+perc12+perc13+perc14+perc15+perc16+perc17+perc18+perc19+perc20 AS persent, arate1, spec_grp_ind AS sgi, sipg, tas, dtis, school_code AS sc, cuic, speedie_type AS sdt, speedie_datime AS sdd, ovrall_pct AS oap, status AS st, status1 AS s1, status2 AS s2, status3 AS s3, status4 AS s4, status5 AS s5, status6 AS s6, YY FROM NEASRPTSINDEX R JOIN orders.dbo.tbl_Addresses U ON ( R.rptuic = U.UIC ) or ((@nAll = 'y' ) and (@nUIC = U.Parent_UIC)) RIGHT OUTER JOIN orders.dbo.cyclemaster CC ON ( CC.CYCLE = R.rptcycle ) and (((R.rptpg = '7' ) and (CC.PAYGRADE = 'E7' )) or ((R.rptpg = '_' ) and (CC.Cycle+CC.PAYGRADE IN (Select cycle+paygrade from orders.dbo.cyclemaster where (paygrade <> 'E7') and (RESULTSVIEW <> (Select case @nIsAdmin when 'Y' then '' else 'NV' end)))))) LEFT OUTER JOIN NEASRPTS C ON ( C.cycle = R.rptcycle ) and ( speedie_date1 = R.rptdate ) and ( uic_ctrl = U.UIC ) and (((epg <> '7' ) and (epg LIKE R.rptpg )) or ((epg = '7' ) and (R.RPTPG = '7' ))) WHERE R.rptid = @nRptID ORDER BY U.UIC |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
sbushway
Starting Member
18 Posts |
Posted - 2006-07-05 : 13:24:27
|
Sorry about that - I didn't include the result set at first since I get the same (correct) result set for both cases. It's just that there's a huge difference in the time it takes to return the result set (2 sec vs. 18 minutes)TUIC PARENT isAdmin ----- ------ ------- 00001 Yrptid rptdiary rptuic rptcycle rptpg rptdate UIC PARENT ShortTitle CC_RAAUTH CC_FSTMOPR cycle speedie_date1 diary_ctrl uic_ctrl rsc doa epg drate nam ssn erate1 series serial brclgroup brcl_prof prate1 tir pc necg pgd puic dspg ss perfac1 perf_mark1 tirf1 awards pnaf1 cps1 cpp1 cps strik_cut fm1 fm_cut1 erlyind prevcyc prevpts tpc1 raw_score persent arate1 sgi sipg tas dtis sc cuic sdt sdd oap st s1 s2 s3 s4 s5 s6 YY -------------------- -------- ------ -------- ----- ---------- ----- ------ ------------------------------------------------------- --------- ---------- ----- ------------- ---------- -------- ---- ------ ---- ----- --------------- --------- ------ ------ ------ --------- --------- ------ ---- ---- ---- ---- ----- ---- ---- ------- ---------- ----- ------ ----- ---- ---- ---- --------- ----- ------- ------- --------------- -------------------- ---- ------------------------------------------------------------ ---------------------------------------- ------ ---- ---- ---- ---- ---- ----- ---- ------------ ---- ----------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---- 00001 00000 00000 186 7 0606140836 00000 TEST NAME// 405 ABC 05 000 0600000000 00000 00000 000000 7 ABC DOE JOHN1 000000000 ABC 100 0373 U USN ABC 0400 11 6000 00000 380 0 00 00 000 00000 10940 09559 000 14 16 8 7 8 3 8 7 8 6 6 5 4 5 4 0000000000000000000000000 ABC 0400 1201 0000 N 00000 84 ADV 00 JUL 0000 1. DATA DATA ADVANCEMENT. P00001 00000 00000 186 7 0606140836 00000 TEST NAME// 405 ABC 05 000 0600000000 00000 00000 000000 7 ABC DOE JOHN2 000000000 ABC 100 0007 U USN ABC 0306 12 5307 00000 385 0 00 00 000 00000 10312 09707 000 10 10 9 7 8 9 13 9 11 9 15 6 0000000000000000000000000 ABC 0306 1707 0000 A 00000 62 ADV 00 AUG 0000 1. DATA DATA ADVANCEMENT. P00001 00000 00000 186 7 0606140836 00000 TEST NAME// 405 ABC 05 000 0600000000 00000 00000 000000 7 ABC DOE JOHN3 000000000 ABC 100 0013 U USN ABC 0306 12 6703 00000 400 0 00 00 000 00000 11903 09648 000 10 15 6 10 12 12 14 11 9 9 13 8 0000000000000000000000000 ABC 0306 1105 0000 A 00000 96 ADV 00 AUG 0000 1. DATA DATA ADVANCEMENT. P00001 00000 00000 186 7 0606140836 00000 TEST NAME// 405 ABC 05 000 0600000000 00000 00000 000000 7 ABC DOE JOHN4 000000000 ABC 100 0934 U USN ABC 0400 12 PG 00000 5411 00000 373 0 00 00 000 00000 10260 09648 000 8 14 1 6 7 7 10 12 14 10 13 8 0000000000000000000000000 ABC 0400 1511 0000 A 00000 66 ADV 00 AUG 0000 1. DATA DATA ADVANCEMENT. P00001 00000 00000 186 7 0606140836 00000 TEST NAME// 405 ABC 05 000 0600000000 00000 00000 000000 7 ABC DOE JOHN5 000000000 ABC 100 0029 U USN ABC 0306 12 5479 00000 400 0 00 00 000 00000 10679 09648 000 9 12 3 6 7 6 13 14 10 12 13 6 0000000000000000000000000 ABC 0306 1305 0000 C 00000 68 ADV 00 AUG 0000 1. DATA DATA ADVANCEMENT. P00001 00000 00000 186 7 0606140836 00000 TEST NAME// 405 ABC 05 000 0600000000 00000 00000 000000 7 ABC DOE JOHN6 000000000 ABC 100 0006 U USN ABC 0500 12 PG 00000 4744 00000 386 0 00 00 000 00000 09762 09731 000 7 6 7 7 6 7 6 3 9 9 7 8 12 5 0000000000000000000000000 ABC 0500 1603 0000 A 00000 40 ADV 00 AUG 0000 1. DATA DATA ADVANCEMENT. P00001 00000 00000 186 7 0606140836 00000 TEST NAME// 405 ABC 05 000 0600000000 00000 00000 000000 7 ABC DOE JOHN7 000000000 ABC 100 0004 U USN ABC 0306 12 5318 00000 393 0 00 00 000 00000 10427 09608 000 8 4 6 1 6 5 11 13 13 10 10 14 7 0000000000000000000000000 ABC 0306 1904 0000 C 00000 62 ADV 00 AUG 0000 1. DATA DATA ADVANCEMENT. P00001 00000 00000 186 7 0606140836 00000 TEST NAME// 405 ABC 05 000 0600000000 00000 00000 000000 7 ABC DOE JOHN8 000000000 ABC 100 0077 U USN ABC 0200 12 00000 4690 00000 400 0 00 00 000 00000 09890 09755 000 8 6 4 10 2 4 5 6 11 12 11 6 11 4 0000000000000000000000000 ABC 0200 1500 0000 N 00000 38 ADV 00 AUG 0000 1. DATA DATA ADVANCEMENT. P00001 00000 00000 186 7 0606140836 00000 TEST NAME// 405 ABC 05 000 0600000000 00000 00000 000000 7 ABC DOE JOHN9 000000000 ABC 100 1052 U USN ABC 0200 12 6182 00000 400 0 00 00 000 00000 11382 09755 000 16 7 6 13 5 5 7 8 11 8 9 9 15 5 0000000000000000000000000 ABC 0200 1200 0000 N 00000 88 ADV 00 AUG 0000 1. DATA DATA ADVANCEMENT. P00001 00000 00000 186 7 0606140836 00000 TEST NAME// 405 ABC 05 000 0600000000 00000 00000 000000 7 ABC DOE JOHN10 000000000 ABC 100 0014 U USN ABC 0306 12 5959 00000 400 0 00 00 000 00000 11159 09672 E 000 3 4 6 7 4 8 6 4 14 12 15 8 14 7 0000000000000000000000000 ABC 0306 0801 0000 A 00000 83 ADV 00 AUG 0000 1. DATA DATA ADVANCEMENT. P00001 00000 00000 186 7 0606140836 00000 TEST NAME// 405 ABC 05 000 0600000000 00000 00000 000000 7 ABC DOE JOHN11 000000000 ABC 100 0007 U USN ABC 0306 12 5151 00000 395 0 00 00 000 00000 10286 09704 000 3 6 10 9 7 5 8 4 14 12 8 6 13 7 0000000000000000000000000 ABC 0306 1110 0000 N 00000 56 ADV 00 AUG 0000 1. DATA DATA ADVANCEMENT. P00001 00000 00000 186 7 0606140836 00000 TEST NAME// 405 ABC 05 000 0600000000 00000 00000 000000 7 ABC DOE JOHN12 000000000 ABC 100 0002 U USN ABC 0300 12 PG 00000 6780 00000 400 0 00 00 000 00000 11980 09621 E 000 12 10 3 6 7 7 4 4 9 11 11 9 14 12 6 0000000000000000000000000 ABC 0300 0610 0000 A 00000 96 ADV 00 AUG 0000 1. DATA DATA ADVANCEMENT. P00001 00000 00000 186 7 0606140836 00000 TEST NAME// 405 ABC 05 000 0600000000 00000 00000 000000 7 ABC DOE JOHN13 000000000 ABC 100 0596 U USN ABC 0300 12 2 PG 00000 5724 00000 400 0 00 00 000 00000 10924 09812 000 5 8 3 3 4 10 8 6 4 14 9 14 7 15 5 0000000000000000000000000 ABC 0300 1709 0000 N 62604 77 ADV 00 AUG 0000 1. DATA DATA ADVANCEMENT. P |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
sbushway
Starting Member
18 Posts |
Posted - 2006-07-05 : 15:27:46
|
| I did some research on Parameter Sniffing and tried a solution I found:I made a dynamic query and did an exec(@SQL) at the end of my SP and it ran just as quick as the query in QA.And I thought about the results being cached in QA, so I repeatedly changed the parameter values and the result set consistently came back within a second or two.Thanks for everyone's input,Suzanne |
|
|
|
|
|
|
|
|
Query runs in Query Analyzer but not in SPROC