473,836 Members | 1,610 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

tuning stored procedure, variables and different optimisers

I've heard 2 things recently, can I confirm if their true/false?

(1) If you have a stored procedure and you want to optimise it you can
call exec proc1,
you could also use define/set for each of the variables and copy the
code into query analyser,
this then makes it easier to tune. However the optimiser works
differently for these variables than it does for variables passed into
the query via exec and will produce a less optimal

(2) There is a different optimiser used in query analyser than that
used otherwise? A colleague
had a problem where a stored procedure called from dotnet code was
running slowly but
one run from query analyser via exec, with exactly the same arguments,
was running quickly

Nov 15 '07 #1
1 1993
On Thu, 15 Nov 2007 13:42:36 -0800 (PST), co**********@go oglemail.com
>There is some truth here. When the value of the parameters is
available to the optimizer at compile time

but what I mean is not the parameters, but something like this

declare @var int
set @var = 1

select * from table where somecolumn=@var
as opposed to a stored procedure where @var is a parameter

If @var is a locally declared variable, its value is not yet known at
execution time (since the whole batch is compiled at once, before
execution starts). So the optimization will be based on general
statistics on the distribution of somecolumn.

If @var is a parameter to a stored procedure, its value is known at
execution time. The proc is compiled the first time it is called, and at
that time the value for the parameter is known. The optimizer will
create a plan that is optimized for the specific value. Note that in a
large majority of cases, the end result will be the same plan - but not

In both cases, the plan is retained in the procedure cache, and reused
when an identical batch is executed in the first case, or when the same
proc is executed in the second case. The former is not a problem, the
latter *usually* neither - but in some cases, the execution plan that is
optimal for @var = 1 might be very slow for @var = 2. In those cases,
you'll see extremely slow execution if the proc happens to be called
with @var = 1 first, and with @var = 2 later.

This feature is called parameter sniffing. You'll find lots more info
when you search for it on google. And though I doon't have my copy of
Inside SQL Server at hand, I think that this is what you are referring
I'd be interested in knowing what
Oracle\mySQL do.
I guess you'll have to ask that in another group.

Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Nov 15 '07 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: Shaun Stuart | last post by:
I've got a webpage that calls some stored procedures with input variables. The procedures return recordsets and also some output variables. We're trying to get the values of the output variables. I've done this using the method I found in MSDN, as shown in the code below. The problem is that we believe doing it this way involves the use of the Microsoft Transaction Server (IIS transaction server). Is this true? (The SQL Server and IIS...
by: Peanut044 | last post by:
Hi all! I am in need of writing a few stored procedures. The first one is to create a stored procedure to recover a database from backup and the second one is to create a stored procedure to execute a transaction log backup (even though I know this can be done through a maintainence plan). Any help would be greatly appreciated. I know the commands to recover and backup -- but I dont know how to formulate them into a stored procedure
by: serge | last post by:
I have an SP that is big, huge, 700-800 lines. I am not an expert but I need to figure out every possible way that I can improve the performance speed of this SP. In the next couple of weeks I will work on preparing SQL statements that will create the tables, insert sample record and run the SP. I would hope people will look at my SP and give me any hints on how I can better write the SP.
by: Philip Mette | last post by:
I am begginner at best so I hope someone that is better can help. I have a stored procedure that updates a view that I wrote using 2 cursors.(Kind of a Inner Loop) I wrote it this way Because I couldn't do it using reqular transact SQL. The problem is that this procedure is taking longer and longer to run. Up to 5 hours now! It is anaylizing about 30,000 records. I think partly because we add new records every month. The procedure...
by: Rob Meade | last post by:
Hi all, I was wondering if anyone could give me a small example of running a stored procedure with both output and input parameters via asp.net (vb). I have tried a couple of things, one of which worked, but now I'm trying to change my code so that its the 'proper' way of doing things.. My stored proc expects the following:
by: Michael | last post by:
Running DB2 v7 UDB ("DB2 v7.1.0.93", "n031208" and "WR21333") on Windows XP, I am unable to find out why the "Build for Debug" option within Stored Procedure Builder is not enabled on Java stored procedures. It is enabled for SQL stored procedures. It is possible to "Build" and "Run" the Java SPs, it just isn't possible to click on the "Build for Debug" option. Thanks for any help in advance. Michael
by: Medhatithi | last post by:
Hi, I have been in several ways benefiited from this site. I would like to share some sql tuning techniques(simple, but effective) with you all. SQL Tuning Tips Oracle Tips Session #6 3/31/98 Kathy Gleeson ________________________________________
by: barmatt80 | last post by:
I am stumped on the error reporting with sql server. I was told i need to return @SQLCode(code showing if successful or not) and @ErrMsg(and the message returned). I am clueless on this. I wrote this procedure: ALTER PROCEDURE . @Emp_SSN int, @Annual_Forward decimal(10,2),
by: SOI_0152 | last post by:
Hi all! Happy New Year 2008. Il hope it will bring you love and happyness I'm new on this forum. I wrote a stored procedure on mainframe using DB2 7.1.1 and IBM language c. Everything works fine. Now we decided to move from mainframe IMS-DB2 to Windows 2003 server-DB2 UDB for LUW 9.5.
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.