Stored Procedure slower than Select statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Stored Procedure slower than Select statement

Author

Topic

GSWilliams1906
Starting Member

4 Posts

Posted - 2007-08-03 : 11:46:58

I have a stored prodecure that updates a table from another database:

basically it looks like this:

DELETE FROM DatabaseOld
INSERT INTO (fields from DatabaseNEW)
SELECT (fiels from various tables in DatabaseOld I use some functions out of DatabaseNew and some other tables from Database new.);

Now in Query Analzyer I run the select by itself it. I have four parameters Datestart, DateEnd, Fee1 and Fee2.

DECLARE DateStart Datetime, etc the Fees are varchar(30)'s.

Set the values of the parameters, and execute. 23 seconds to return the data.

I even performed the insert and that took 23 seconds to return the data.

So everything is working the way I want. I then create the stored procedure using by copying the above to:

CREATE StoreProdecure (The above parameters...)
AS
then copy the stuff that ran in 23 seconds

So now I open a new window and do:
exec storedprocedure 'param1','param2','param3', 'param4'

and.......................................

This #$%^@$#%$ thing takes FOREVER to run!!!! I'm it never returns anything basically. I longest I let it run was 17 mins. I generall stop it after 2 to 6 mins.

My question is why does it run exponentially faster as a query than it does as a stored procedure? I'm stumped! It doesn't make any sense. What is it doing differently once I put it into a stored procedure?

The only thing different is that when I edit the SPROC in query analyzer it adds the

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
at the beginning and
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

at the end. Other than that it is a copy paste. WHich I've done a hundred times in creating SPROCS?

Could anyone give me suggestions to get my 23 seconds in a SPROC?

GSWilliams1906
Starting Member

4 Posts

Posted - 2007-08-03 : 12:03:05

Nevermind. I fixed it. I've been working on this for about 3 days. and what I did was assigned my parameters to varibles inside the SPROC and then used the varibles in my query instead of the SPROC parameters. Avg runtime now, 24 to 30 secs.

I guess this is why I'm going on vacation starting Monday!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources