Stored procedures are slower than queries

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 procedures are slower than queries

Author

Topic

joke_dst
Starting Member

8 Posts

Posted - 2005-07-07 : 05:20:31

I am working on a project where I'm doing some rather complex SQL joins, and one thing I have noticed is taking the exactly same code, exchanging the

"
create Procedure ui_getCRcolumns
(
@userId INT,
@fromPeriod INT,
@toPeriod INT,
@cfuId INT = NULL,
@createdPeriod INT = NULL
)
"

with

"
declare @userId int set @userId=52
declare @cfuId int set @cfuId=null
declare @fromPeriod int set @fromPeriod=24060
declare @toPeriod int set @toPeriod=24074
declare @createdPeriod int set @createdPeriod=24066
"

and run it as a single query from the application (obviously exchanging the numbers to the current numbers) the query runs MUCH faster compared to running the stored proc. It's always faster, no matter how the query looks, sometimes the difference is 4 sec vs. 2 minutes! The queries are ususally quite large, a few pages big. But still storing them as a string (currently in asp.net) and sending the whole thing to the server is much faster than just sending the parameters and execute the procedure.

I've checked the execution plans and they differ slightly. Can someone explain to me why this is so? I'd rather use procedures if I could, but with this performance its quite impossible.

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-07-07 : 05:34:25

Sounds as though the optimiser is using an inefficient cached plan. Try running DBCC FREEPROCCACHE or including the WITH RECOMPILE option in the stored proc definition. This will give you a more level playing field.

joke_dst
Starting Member

8 Posts

Posted - 2005-07-07 : 05:40:34

No difference... :( The execution plans stayed exactly the same. Thanks for the idea though.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-07-07 : 06:13:33

Before you give up on the sproc, run both DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS before you test both approaches. Data caching will skew the performance just as much as caching a bad plan, DROPCLEANBUFFERS will flush the data cache. Obviously you should only run these commands during testing. If your sproc is still giving you a sub-optimal plan, you can tweak it a little:

http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx

And as a last resort, you can also use query hints to push the optimizer towards the optimal plan. If you do use hints, MAKE SURE TO TEST THEM THOROUGHLY, and make sure they actually improve the query.

joke_dst
Starting Member

8 Posts

Posted - 2005-07-07 : 07:28:49

I've already tried clearing all buffers, it doesn't help :(
Also the tweak described in the article doesn't really apply since I always run the procedure with "typical" values...

I really don't know why it behaves this way. I guess I'll just have to live with it for now and use the USE PLAN command once SQL 2005 goes final...

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-07-07 : 07:40:04

Create the proc without default values for parameters, then test again.

joke_dst
Starting Member

8 Posts

Posted - 2005-07-07 : 07:56:10

That actually helped... but the query is still 6 times faster.

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-07-07 : 08:00:33

Do you assign value to local variable then use it in where clause (or join)?

joke_dst
Starting Member

8 Posts

Posted - 2005-07-07 : 08:12:32

yes, I do a 'declare @fromPeriod int' then a 'set @fromPeriod=24066', then I use the @fromPeriod variable several times in the query.

In the proc @fromPeriod is a parameter.

joke_dst
Starting Member

8 Posts

Posted - 2005-07-07 : 08:14:57

OOOHHHH Thanx mmarovic, I fixed it! By putting this at the top of the procedure:

ALTER Procedure ui_getCRcolumns
(
@xuserId INT,
@xfromPeriod INT,
@xtoPeriod INT,
@xcfuId INT ,
@xcreatedPeriod INT ,
@xcurrency char(3),
@xpfuid int --ignored(?)
)
AS
declare @userId int set @userId=@xuserId
declare @cfuId int set @cfuId=@xcfuId
declare @fromPeriod int set @fromPeriod=@xfromPeriod
declare @toPeriod int set @toPeriod=@xtoPeriod
declare @createdPeriod int set @createdPeriod=@xcreatedPeriod
declare @currency char(3) set @currency=@xcurrency

the proc now behaves just like the query! Excellent! Odd though...

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-07-07 : 08:15:23

I was thinking about procs local variable that is not parameter. Does any parameter change the value inside proc?

joke_dst
Starting Member

8 Posts

Posted - 2005-07-07 : 08:20:52

yes, at the beginning there are a few test for illegal values that alters the parameters if they are wrong.

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-07-07 : 08:25:08

That should be done on client. Test with correct values only.