473,242 Members | 1,461 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,242 software developers and data experts.

Slow Stored Procedure - Easy located but wtf?

Hi,

Plz, I need some info (SQL2000) :)

A stored procedure is like this:

"Select table1.id, table1.txt, (select table2.nr from table2 where
table2.fk_table1=table1.id) as nr where table1.id<>10"

The essence here is that "select table2.nr from table2 where
table2.fk_table1=table1.id" returns either the integer in table2.nr, or NULL
if there isnt a match. The whole sentence runs EXTREMELY slow...3-4 sec.
What is wrong?

"select table2.nr from table2 where table2.fk_table1=table1.id" runs quickly
outside the stored procedure. The original sentence without the "nr" (Select
table1.id, table1.txt where table1.id<>10) runs quickly too...

But together it slows down dramatically..why? I should mention that the
sub-query could return NULL if theres no match in table2...But i cant see
why that should slow things down (remember - it runs fine outside the SP)?

Thx,
PipHans
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.518 / Virus Database: 316 - Release Date: 11-09-2003
Jul 20 '05 #1
3 2951
[posted and mailed, please reply in news]

PipHans (pi*****@hotmail.com) writes:
A stored procedure is like this:

"Select table1.id, table1.txt, (select table2.nr from table2 where
table2.fk_table1=table1.id) as nr where table1.id<>10"

The essence here is that "select table2.nr from table2 where
table2.fk_table1=table1.id" returns either the integer in table2.nr, or
NULL if there isnt a match. The whole sentence runs EXTREMELY slow...3-4
sec.


3-4 extremely slow? Hah! You should see some of the queries, I have
been running lately. :-) (Sorry, just couldn't resist.)

Anyway, without knowledge about your table, their indexes and how
much data they have I cannot give any good answer. You might use
the option Show Execution Plan in Query Analyzer to get an idea.

This query might give the same result. I have no idea if it will
perform any better:

Select t1.id, t1.txt, table2.nr
from table1 t1
left join table2 t2 ON t1.id = t2.fk_table1
where t1.id<>10

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
Erland Sommarskog wrote:
3-4 extremely slow? Hah! You should see some of the queries, I have
been running lately. :-) (Sorry, just couldn't resist.)


:)

Well, I thought of using a left join instead as you recommend. Afaik joins
is very fast on SQL2000.
But I think its more of a problem wioth the indexes or perhaps an error
(code?) that makes SQL compile the SP every time its called....? Dunno.

I'll try with a left join tomorrow and post the result.
Thx,

--
PipHans
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.518 / Virus Database: 316 - Release Date: 11-09-2003
Jul 20 '05 #3
PipHans (pi*****@hotmail.com) writes:
Well, I thought of using a left join instead as you recommend. Afaik joins
is very fast on SQL2000.
Depends on your indexes. :-)
But I think its more of a problem wioth the indexes or perhaps an error
(code?) that makes SQL compile the SP every time its called....? Dunno.


There might be a recompilation problem, but to say anything about that
I need to see the code for the entire procedure.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4

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

Similar topics

5
by: Shay | last post by:
essentially I am trying to do some counts based on some assumptions in the recordset. So I get the RS back, put the values into a variable, move to the next record in the RS and compare what is in...
16
by: mamo74 | last post by:
Hello. I am administering a SQL Server (Enterprise Edition on Windows 2003) from some month and can't understand what is going on in the latest week (when the db grow a lot). The DB is around...
3
by: N. Shamsundar | last post by:
A stored procedure (listed below) that loads fine on Windows XP with DB2 V8.1.4 Express fails to load on Linux DB2 Workgroup server V8.1, with the following message: > sh-2.05a$ db2 -td@ -f...
2
by: Josh Strickland | last post by:
I am attempting to create an Access database which uses forms to enter data. The issue I am having is returning the query results from the Stored Procedure back in to the Access Form. ...
0
by: Amber | last post by:
Stored procedures are faster and more efficient than in-line SQL statements. In this article we will look at two SQL Server stored procedures; one using an input parameter and one not, and see how...
3
by: Jennyfer J Barco | last post by:
In my application I have a datagrid. The code calls a Stored procedure and brings like 200 records. I created a dataset and then a dataview to bind the results of the query to my grid using ...
12
by: Jason Huang | last post by:
Hi, In my C# Windows Form application project, I have done all queries on my codes. Now I wanna try using the Stored Procedure. But I am not clear about why using the stored procedure. Would...
7
by: E11esar | last post by:
Hi there. I have written a C# web service that calls an Oracle stored procedure. The SP is a simple select-max query and the table it is getting the value from has about 2.8 million rows in it. ...
2
by: IuliaS | last post by:
Hello everyone! I want to create a stored procedure, so I can more easily, and transparent retrieve data from db2. Long story short: when a user wants to put some data in the DB, he also creates...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...

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.