Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

Problem: Trivial?


Hi!

I have a Stored Procedure which takes 48 parameters and have a few hundreds
of SQL-code lines. The SP uses few temporary tables, 10-15 selects, some
aggregate functions, groupings etc...

Case 1:
When I execute SP's SQL-code as a 'simple' query (instead of input
parameters I declare all the variables that procedure uses and set values to
all of them, and just copy-paste the rest of SP's SQL-code (SP's 'body') -
query execution time is about 60 times faster then other way (case 2 - SP
call).

Case 2:
SP call: again - declaring the variables, setting the values to exactly same
values, but this time really executing SP (EXECUTE spName @p1, @p2, ...,
@p48) using variables as arguments.

Question:
Could anyone tell me what's the different between this two tests and what
should I do to speed up the SP execution?

I suspect it might be something trivial - I just can't get it!?
Same code, same parameters (number and values), same result - but first way
is that much faster (case 1: 2-3 sec vs. case 2: 2-3 min)...

DBMS is (off course) SQL Server 2005, and I also tried '... WITH RECOMPILE'
option.

(Excuse my bad English and thanks for any possible answers.)

Google "parameter sniffing".

--
   Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON   Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau

"Bobby" <bobby_orla@yahoo.com> wrote in message

news:f3i8ko$ji8$1@ss408.t-com.hr...
Hi!

I have a Stored Procedure which takes 48 parameters and have a few hundreds
of SQL-code lines. The SP uses few temporary tables, 10-15 selects, some
aggregate functions, groupings etc...

Case 1:
When I execute SP's SQL-code as a 'simple' query (instead of input
parameters I declare all the variables that procedure uses and set values to
all of them, and just copy-paste the rest of SP's SQL-code (SP's 'body') -
query execution time is about 60 times faster then other way (case 2 - SP
call).

Case 2:
SP call: again - declaring the variables, setting the values to exactly same
values, but this time really executing SP (EXECUTE spName @p1, @p2, ...,
@p48) using variables as arguments.

Question:
Could anyone tell me what's the different between this two tests and what
should I do to speed up the SP execution?

I suspect it might be something trivial - I just can't get it!?
Same code, same parameters (number and values), same result - but first way
is that much faster (case 1: 2-3 sec vs. case 2: 2-3 min)...

DBMS is (off course) SQL Server 2005, and I also tried '... WITH RECOMPILE'
option.

(Excuse my bad English and thanks for any possible answers.)

-----------------------------------------------Reply-----------------------------------------------

Where did you use WITH RECOMPILE?
In the proc definition? When calling the proc? As a hint to individual
statements in the proc?

--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com

"Bobby" <bobby_orla@yahoo.com> wrote in message

news:f3i8ko$ji8$1@ss408.t-com.hr...

"Tom Moreau" <t@dont.spam.me.cips.ca> wrote in message

news:uV1z2IkoHHA.4124@TK2MSFTNGP02.phx.gbl...

> Google "parameter sniffing".

Can you please tell me how I can implement:

if (data is atypical)

if (data has significantly changed)

"Parameter sniffing", recompiles blablabla...really means
Mr./Ms. user it's your problem. The real issue is how
naive people are to swallow this non-sense. Where there
is no shame arrogance apparently rules. If enough users
get out their 'return to sender' stamp the design mess
might get straigthened out.

You say Google "parameter sniffing".
I say Google "gullible".

http://beyondsql.blogspot.com

-----------------------------------------------Reply-----------------------------------------------

"Kalen Delaney" <replies@public_newsgroups.com> wrote in message

news:%232DIxOkoHHA.1244@TK2MSFTNGP04.phx.gbl...

> Where did you use WITH RECOMPILE?
> In the proc definition? When calling the proc?

I tried both (not in the same time).

> As a hint to individual statements in the proc?

This I did not try.

-----------------------------------------------Reply-----------------------------------------------

"Tom Moreau" <t@dont.spam.me.cips.ca> wrote in message

news:uV1z2IkoHHA.4124@TK2MSFTNGP02.phx.gbl...

> Google "parameter sniffing".

Your hint has simply enlightened me! ;-)

All input parameters had (and still have) some typical default values, so I
guess optimizer and 'parameters sniffing' have caused me that huge
performance delay.

At first - I tried to remove the default values, but it didn't change
performance time at all (!?).

Finally I declared new variables (inside SP); set them all to their
associated input values (left original parameter defaults) and let the rest
of the procedure use only internal variables.

(Off course - I used old parameter identifiers for variable names and
renamed parameters.)

It works just perfect now!

Thanks a lot...

Regards!

-----------------------------------------------Reply-----------------------------------------------

"Steve Dassin" <rac4sqlnospam@net> wrote in message

news:em4GWMloHHA.3856@TK2MSFTNGP03.phx.gbl...

> You say Google "parameter sniffing".
> I say Google "gullible".

Thanks for your support - but Tom's hint has actually helped me.
:-)
Regards!

-----------------------------------------------Reply-----------------------------------------------
Happy to help.  :-)

--
   Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON   Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau

"Bobby" <bobby_orla@yahoo.com> wrote in message

news:f3l2cd$dqg$1@ss408.t-com.hr...

"Tom Moreau" <t@dont.spam.me.cips.ca> wrote in message

news:uV1z2IkoHHA.4124@TK2MSFTNGP02.phx.gbl...

> Google "parameter sniffing".

Your hint has simply enlightened me! ;-)

All input parameters had (and still have) some typical default values, so I
guess optimizer and 'parameters sniffing' have caused me that huge
performance delay.

At first - I tried to remove the default values, but it didn't change
performance time at all (!?).

Finally I declared new variables (inside SP); set them all to their
associated input values (left original parameter defaults) and let the rest
of the procedure use only internal variables.

(Off course - I used old parameter identifiers for variable names and
renamed parameters.)

It works just perfect now!

Thanks a lot...

Regards!

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc