SQLServerCentral / SQL Server 7,2000 / Performance Tuning / Stored procedure slower then query / Latest Posts

RE: Stored procedure slower then query

GilaMonster — Wed, 26 Sep 2007 09:55:06 GMT

Try also leaving the parameters and marking the proc for recompile every time it runs. (CREATE PROCEDURE ... WITH RECOMPILE)Using variables, the optimiser can not make any conclusions about the number of rows that will be affected by any query. I believe it will make a guess at about 1/3 of the table. Hence, you're unlikely to get very good performance but you are guarenteed to never get very bad performance. Essentially, you'll get average performanceUsing recompile, you'll get the best possible plan for each run of the procedure at a cost of a slight CPU overhead for the compile.Give it a try and see what's best for you.I will recommend that you don't switch parameters for variables unless you know you are having this kind of problem with a particular proc. It's called parameter sniffing and, in general, it's a good thing.

RE: Stored procedure slower then query

Kevin Brown-242904 — Mon, 24 Sep 2007 13:05:12 GMT

THANK YOU!!!!!! I have been fighting with this stored procedure for about 3 days now. I was sure this was going to kill the last bit of motivation I still had left to optimize this beast.

I had 4 parameters going in to the Stored procedure. In the query I had declared and set them at the top so I could easy copy/paste when I finished.

I prefixed all the variables coming in to the stored procedure with an o declared the variables in the stored procedure and set them equal to the o prefixed variables and the stored procedure ran as expected.

again THANK YOU. I'll have to try to remember this in case I ever run into it again. For now I'm off to fix the o variables to make sense.

[quote][b]Lynn Pettis (9/24/2007)[/b][hr]Without seeing the code for the stored procedure, it is a guess. It could be parameter sniffing at work. Are you using the variables declared in the header of the sproc in the query? If so, declare local variables inside the procedure, set those variables to the values you are sending in, and use the local variables in your query.

:cool:[/quote]

RE: Stored procedure slower then query

Lynn Pettis — Mon, 24 Sep 2007 12:37:17 GMT

Without seeing the code for the stored procedure, it is a guess. It could be parameter sniffing at work. Are you using the variables declared in the header of the sproc in the query? If so, declare local variables inside the procedure, set those variables to the values you are sending in, and use the local variables in your query.

:cool:

Stored procedure slower then query

Kevin Brown-242904 — Mon, 24 Sep 2007 12:25:33 GMT

This is an odd one I just ran into today. I rebuilt an older query that aggregated together a bunch of information. The query had slowed down to the point that it was regularly timing out.

The primary table that is being accessed is about 80k records so it's not a huge data set.

To optimize the query I removed a table variable that worked similar to this (with more column)

select Type, OrigionalLogID, (select top 1 b.field from table b where b.type=a.type and b.OrigionalLogID=a.OrigionalLogID and TranType in ('these', 'change') order by b.field desc), .... into @table from table a where .... order by OrigionalLogID

I built a view that used min/max(case ...) to build the same data. Doing that dropped a considerable amount of time off of the execution time of the query and simplified things considerably.

I then rebuilt fields that were selected as (select) as fieldname into count(case ...) statements to speed things even further. I managed to drop the query that ran for 60 seconds for a month of data to run in about 2 seconds for a month, and about 7 seconds for a full history.

I then took the query I had built and copied it into a stored procedure created the procedure and executed with the same parameters I was using for test and the stored procedure takes over 2 min to execute (it generates the exact same execution plan)

Does anyone have any idea what could be breaking or how I could fix it?

thanks,

Kevin