473,883 Members | 1,602 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Same query - different execution plans??

Hi,

We are trying to solve a real puzzle. We have a stored procedure that
exhibits *drastically* different execution times depending on how its
executed.

When run from QA, it can take as little as 3 seconds. When it is
called from an Excel vba application, it can take up to 180 seconds.
Although, at other times, it can take as little as 20 seconds from
Excel.

Here's a little background. The 180 second response time *usually*
occurs after a data load into a table that is referenced by the stored
procedure.

A check of DBCC show_statistics shows that the statistics DO get
updated after a large amount of data is loaded into the table.

*** So, my first question is, does the updated statistics force a
recompile of the stored procedure?

Next, we checked syscacheobjects to see what was going on with the
execution plan for this stored procedure. What I expected to see was
ONE execution plan for the stored procedure.

This is not the case at all. What is happening is that TWO separate
COMPILED PLANs are being created, depending on whether the sp is run
from QA or from Excel.

In addition, there are several EXECUTABLE PLANs that correspond to the
two COMPILED PLANs. Depending on *where* the sp is run, the usecount
increases for the various EXECUTABLE PLANS.

To me, this does not make any sense! Why are there *multiple* compile
and executable plans for the SAME sp?

One theory we have is, that we need to call the sp with the dbo
qualifier, ie) EXEC dbo.sp

Has anyone seen this? I just want to get to the bottom of this and
find out why sometimes the query takes 180 seconds and other times
only takes 3 seconds!!

Please help.

Thanks much

Jul 16 '07 #1
5 10764
sqlgirl (to*******@gmai l.com) writes:
This is not the case at all. What is happening is that TWO separate
COMPILED PLANs are being created, depending on whether the sp is run
from QA or from Excel.
Yes, that is actually to be expected. :-)

Here is the scoop: certain SET commands affects the query plan so much,
that different combinations of SET commands yields different cache
entries. When you connect from Excel, you use an API that issues
turns on a number of SET commands. In QA you can set up under Options->
Connection Properties how to connect. But the QA default includes
SET ARITHABORT ON, which is not set when you connect from a client
application.

That explains why you see different plans. It does not explain why
you get such difference in performance. It may just be bad luck, but
there is also a real possibility: somewhere there is an indexed view
or an indexed computed columns. For these indexes, six SET commands
must be on and a seventh must be OFF. And one of the six are SET
ARITHABORT ON.

Try to get your VBA application to issue SET ARITHABORT ON and see
what happens.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jul 16 '07 #2
Hi, thanks for the information. Is ARITHABORT the only setting we need
to be concerned with?

We ran a test. Cleared out the cache entry for our sp by altering it.
Verified that it was no longer listed in syscacheobjects .

Next, we ran the query from QA, it added an entry to syscacheobjects .
Then, we ran it from excel. It still creates a second entry in
syscacheobjects .

Which still leaves me scratching my head.
Jul 16 '07 #3
sqlgirl (to*******@gmai l.com) writes:
Hi, thanks for the information. Is ARITHABORT the only setting we need
to be concerned with?

We ran a test. Cleared out the cache entry for our sp by altering it.
Verified that it was no longer listed in syscacheobjects .

Next, we ran the query from QA, it added an entry to syscacheobjects .
Then, we ran it from excel. It still creates a second entry in
syscacheobjects .
And by then you had added SET ARITHABORT ON to the VBA code?

There are indeed other options that matter, but ARITHABORT is typically
the tricky one.

You can look at the column setopts in syscacheobjects , to see if there is
any difference. Unfortunately, Books Online does not specify which bit
that corresponds to which setting. (I assume that since you look in
syscacheobjects , that you are on SQL 2000. In SQL 2005 this information
is available in a more digestable form.)

You can also examine the SET option, by looking at the processes from
Profiler, keep an eye on Current connections, and Audit:Login, as well
as StmtCompleted.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jul 17 '07 #4
It is not explicitly stated, so I'll ask:

Does each environment call the stored procedures with the exact same
parameters?

Does each environment call the stored procedure after a set of data is
loaded?

Do you have the execution plan for each scenario?

If you explicitly update statistics for the system after the new data
is loaded, does the execution time improve?

Jul 17 '07 #5
In addition to the other responses: when using stored procedures,
parameter sniffing can influence the query plan. So if two execution
plans are cached (because of differing connection settings), then the
parameter values of the first call will determine the query plan. If
these differ, the plan can differ.

Gert-Jan
sqlgirl wrote:
>
Hi,

We are trying to solve a real puzzle. We have a stored procedure that
exhibits *drastically* different execution times depending on how its
executed.

When run from QA, it can take as little as 3 seconds. When it is
called from an Excel vba application, it can take up to 180 seconds.
Although, at other times, it can take as little as 20 seconds from
Excel.

Here's a little background. The 180 second response time *usually*
occurs after a data load into a table that is referenced by the stored
procedure.

A check of DBCC show_statistics shows that the statistics DO get
updated after a large amount of data is loaded into the table.

*** So, my first question is, does the updated statistics force a
recompile of the stored procedure?

Next, we checked syscacheobjects to see what was going on with the
execution plan for this stored procedure. What I expected to see was
ONE execution plan for the stored procedure.

This is not the case at all. What is happening is that TWO separate
COMPILED PLANs are being created, depending on whether the sp is run
from QA or from Excel.

In addition, there are several EXECUTABLE PLANs that correspond to the
two COMPILED PLANs. Depending on *where* the sp is run, the usecount
increases for the various EXECUTABLE PLANS.

To me, this does not make any sense! Why are there *multiple* compile
and executable plans for the SAME sp?

One theory we have is, that we need to call the sp with the dbo
qualifier, ie) EXEC dbo.sp

Has anyone seen this? I just want to get to the bottom of this and
find out why sometimes the query takes 180 seconds and other times
only takes 3 seconds!!

Please help.

Thanks much
Jul 17 '07 #6

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

Similar topics

3
5233
by: Paul Mateer | last post by:
Hi, I have been running some queries against a table in a my database and have noted an odd (at least it seems odd to me) performance issue. The table has approximately 5 million rows and includes the following columns: DocID (INTEGER, PRIMARY KEY, CLUSTERED) IsRecord (INTEGER, NONCLUSTERED)
10
3754
by: Thomas R. Hummel | last post by:
I have a stored procedure that suddenly started performing horribly. The query plan didn't look right to me, so I copy/pasted the code and ran it (it's a single SELECT statement). That ran pretty well and used a query plan that made sense. Now, I know what you're all thinking... stored procedures have to optimize for variable parameters, etc. Here's what I've tried to fix the issue: 1. Recompiled the stored procedure 2. Created a new,...
3
2491
by: James Walker | last post by:
Hi there - hoping someone can help me here! I have a database that has been underperforming on a number of queries recently - in a test environment they take only a few seconds, but on the live data they take up to a minute or so to run. This is using the same data. Every evening a copy of the live data is copied to a backup 'snapshot' database on the same server and also, on this copy the queries only take a second or so to run....
2
2182
by: jc | last post by:
Hi. A question I have is with regard to the use of views with SQL2000. If I have a view called "A_view" and used in the following manner; ---------------- SELECT ... FROM A_View WHERE .... UNION
5
6352
by: sql-db2-dba | last post by:
We have DB2 UDB v8.1 fixpak3 on AIX 5. Production and Development configuarations (at least for DB2) are identical albeit production is a 2-way server while development has only one processor. Tables and indexes have the same schema. In fact, the dev database was taken from a prod backup recently. Size of the tables differ slightly. Yet, on a given query (with 4 tables joined), it took 30-50 times longer to run in prod than on development....
22
3327
by: Marc Mones | last post by:
Hello, I'working with IBM DB2 V8.1 and CLI/ODBC. I've got a problem with the following statement: ******************************************************************************** SELECT S_ART, S_SPRACHE, S_MANDANT, S_NR, S_SUB, S_OWNER, S_SATZ FROM SY0001_00005 WHERE S_ART = ? AND S_SPRACHE = ? AND S_MANDANT = ? AND S_NR = ? AND
5
3726
by: vijaylohkare | last post by:
HI All, I want to know the command that will display all the execution plans which are generated by the parser in PostgreSQL. Is there any command to instruct PostgreSQL to choose a specific query plan. Regards Vijay
2
4871
by: Luke.Schollmeyer | last post by:
I found an unusual problem between 2000 and 2005 I haven't been able to decipher from any documentation. The query structure is as follows: select * from tableA a join tableB b ON a.somekey = b.somekey
12
3405
by: Yash | last post by:
My SQL query is like: SELECT ..... FROM tblCLAIM C INNER join tblXXX .... INNER join tblYYY ....(5 more joins) WHERE C.created_date between @start_date and @end_date AND C.claimant_id=@claimant_id or @claimant_id=-1 AND (more conditions like the claimant condition) This query is in a stored proc which takes parameters @start_date,
0
9933
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
11114
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10835
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,...
0
10407
tracyyun
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...
0
7114
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();...
0
5787
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...
0
5982
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4605
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
3
3230
bsmnconsultancy
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.