Slow Stored Procedure - Easy located but wtf?

[PipHans]

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

[Erland Sommarskog]

[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

[PipHans]

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

[Erland Sommarskog]

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