Stored Procedure Performance Problem

[Bobbber]

Guys,

I have a stored procedure along the following lines :

Create Procedure ProcedureName

AS

Create Table yyy (
  IDField int,
  Field1 varchar(50),
  Field2 varchar(50),
  Field3 varchar(50),
  Fieldx... 
  etc...
)

Insert into Table yyy (IDField) SELECT ID from tblPPP where Surname='Smith'

UPDATE Table yyy SET Field1=value FROM tblQQQ WHERE tblQQQ.ID=IDField

UPDATE Table yyy SET Field2=value FROM tblRRR WHERE tblRRR.ID=IDField

UPDATE Table yyy SET Field3=value FROM tblSSS WHERE tblSSS.ID=IDField

UPDATE Table yyy SET Fieldx... 

etc...

SELECT * FROM yyy

DROP TABLE yyy

If I run each line of code in Query Analyzer, it takes 1min 40seconds in TOTAL (i.e. adding the times together that it takes to run each line).

But EXECuting the complete code as a stored procedure takes 25 minutes!!!!

Any ideas why?

Thanks!

Bob

 

[SQLDenis]

instead of doing 4 updates do 1
instead of this

UPDATE Table yyy SET Field1=value FROM tblQQQ WHERE tblQQQ.ID=IDField
UPDATE Table yyy SET Field2=value FROM tblRRR WHERE tblRRR.ID=IDField
UPDATE Table yyy SET Field3=value FROM tblSSS WHERE tblSSS.ID=IDField
UPDATE Table yyy SET Fieldx...

do something like this

declare @id int,@v1 int,@v2 int,@v3 int.....
select @ID = ID from tblPPP where Surname='Smith'

select @v1 =value FROM tblQQQ WHERE ID=@ID
select @v2 =value FROM tblRRR WHERE ID=@ID
select @v3 =value FROM tblSSS WHERE ID=@ID

UPDATE Table yyy 
SET Field1=@v1,
Field2=@v2,
Field3=@v3 
WHERE ID=@ID

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

In addtion to Denis's comments...

I notice that you are creating a 'real' table to use for temporary purposes. You may get better performance if you create a temp table instead.

Change...
Create Table yyy
To
Create Table #yyy

And then change (every where)...
yyy
To
#yyy


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Bobbber]

Hi Denis,

Thanks for the fast reply.

But the table tblPPP contains many surnames of 'smith' and therefore the table yyy is populated with a couple of thousand rows.

This surely only updates one row? And doesn't explain why this is many times slower as a stored procedure than executing each line independently in QA.

Bob

 

[Bobbber]

George,

Using a temporary table or a "real" one makes little difference to the processing times I'm getting.

Bob

 

[SQLDenis]

did you run the proc more than 1 time?
what happens if you run the proc twice, it should be much faster because of compilation

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Bobbber]

Yes - a second or third run of the procedure makes little difference.

Sorry! I know you're trying!

Bob

 

[gmmastros]

It looks like you are trying to pull information from several tables. The stored procedure returns the data.

Does this query work for you....

Select	tblPPP.IdField,
		tblQQQ.Value As Field1,
		tblRRR.Value As Field2,
		tblSSS.Value As Field3
From	tblPPP
		Inner Join tblQQQ On tblPPP.IdField = tblQQQ.IdField
		Inner Join tblRRR On tblPPP.IdField = tblRRR.IdField
		Inner Join tblSSS On tblPPP.IdField = tblSSS.IdField
Where   tblPPP.Surname = 'Smith'

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Bobbber]

C'mon guys....

My question is "why it takes longer in a stored procedure than in QA"...

Thanks!

Bob

 

[SQLDenis]

Take a look at this (

http://sqljunkies.com/WebLog/simons/archive/2006/01/27/17916.aspx)

It might explain the difference in execution time

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/