Stored Proc vs Query
-
I have a SP which takes way too long to run (more than 8 hours). So, I got to kill it after several hours. But when I run it as query (just highlight the whole SP), it runs in less than 30 minutes. I tried it from 2 different servers and several times. It consistently takes too long to run (PAGEIOLATCH_SH) and never finishes when run as a SP and runs quite fine when executed as query.
Does anyone experienced this? Any ideas?
Give me a fish, you feed me for a day! Teach me to fish, you feed me for a life time.
-
Any thoughts! Reply please.
Give me a fish, you feed me for a day! Teach me to fish, you feed me for a life time.
-
Here's a thought, how about you provide some more details on what you're doing. Table schemas, stored procedure code, type of hardware the server is running on.
Th emore details you provide the more likely you are going to get an authoritive answer, rather than just a guess.
--------------------
Colt 45 - the original point and click interface
-
Are you aware of stored procedure "parameter sniffing" ? That is, when the stored procedure is first invoked, the query plan is created based on the values of the passed parameters. If there are variable that are set in the stored procedure that are then referenced in a later table access SQL statement, the query plan optimizer will guess for the values. This often results in poorly performing stored procedures.
Solutions include:
Do not set variables in stored procedures - always have the needed values passed as parameters.
Create 2 stored procedures: One containing the table access SQL , only passed parameters and does not have any declared variables. The second has passed and declared variables, sets any variables and then invokes the other procedure.
SQL = Scarcely Qualifies as a Language
-
I ran this situation in three different servers. All of them running SQL 2K sp3, Win 2K.
I believe I am running into some kind of parameter sniffing issue. This SP has 2 parameters and a bunch of local variables. These local variables are used in a Where clause. I removed these local variables from the Where clause and substituted it with a select stmt in the where clause. Still, I am having issues.
But when I run the whole SP as a code setting the parameters as local variables and assigning values, it runs quite good. Any thoughts?
Give me a fish, you feed me for a day! Teach me to fish, you feed me for a life time.
-
I removed the parameter from the SP and executed the SP (declare local variable and assign value instead) and it ran fine. I got the results in about 7 minutes.
I tested this change in one server and I am testing it in other servers now. I will let you know. Thanks.
Give me a fish, you feed me for a day! Teach me to fish, you feed me for a life time.
-
Hi,
I was reading all the post related your SP Vs. Quey Issue ! After reading your final post in which you metioned that you achieved the results within 7 miniutes and the was earlier taken hours !!.
Since we all are using lot of parameterized SPs and that contains lot of local variable (offcourse local variable can not be avoided most of the cases !) as well.
Since you have achieved trenendous results upon changing of SPs, I really appreciate if you could let us know the changes you have done to achive this. Really appreciate if you could past your "old SP" and "new SP" codes for better reference and follow the same in order to have better performance on our application.
Many thanks in advance..
Regards,
-
I second that. I wasn't aware of the "parameter sniffing" concept. It would be nice to see the problem and solution illustrated.
-
I have used a method to pass parameters to a stored procedure which in turn dynamically creates another (Static) stored procedure from which I run to obtain my results. The "target" stored procedure gets dropped and recreated each time I run the process and I have the parameters used in the last execution for analysis.
My performance was increased tremendously due to this.
-
So how about posting some example code so everyone can benefit from this??
--------------------
Colt 45 - the original point and click interface -
OK, I've simplified this for purposes of length...don't wanna bore anyone.
First create the stored procedure that will create and execute another stored procedure. This is where you'll supply your parameters.
CREATE PROCEDURE dbo.usp_Create_Test_SP
(
@startdate DATETIME,
@enddate DATETIME
)
AS
SET NOCOUNT ON
DECLARE @stmt1 VARCHAR(4000)
SET @stmt1 = ''
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_TargetSP]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
SET @stmt1 = 'drop procedure [dbo].[usp_TargetSP]'
EXECUTE(@stmt1)
END
SET @stmt1 = 'CREATE PROCEDURE dbo.usp_TargetSP'
SET @stmt1 = @stmt1 + CHAR(13) + CHAR(10) + 'AS
SET NOCOUNT ON
SELECT
*
FROM
sysobjects
WHERE
crdate BETWEEN ''' + CAST(@startdate AS VARCHAR) + ''' AND ''' + CAST(@enddate AS VARCHAR) + '''' + CHAR(13) + CHAR(10)
EXECUTE(@stmt1)
EXECUTE dbo.usp_TargetSP
GO
Execute this sp as follows (and you will get a dependency error..no big deal);
execute usp_Create_Test_SP '01/01/2005','01/31/2005'
This will create the following sp;
CREATE PROCEDURE dbo.usp_TargetSP
AS
SET NOCOUNT ON
SELECT
*
FROM
sysobjects
WHERE
crdate BETWEEN 'Jan 1 2005 12:00AM' AND 'Jan 31 2005 12:00AM'
GO
Once created, execute again with different parameters such as;
execute usp_Create_Test_SP '01/01/2004','01/31/2004'
Each execution will drop and recreate the dbo.usp_TargetSP procedure with new parameters then execute it. For very complex queries, you will see a significant difference in the query execution plan. Where the parameters are static, the plan is very efficient hence the speed advantage. This also allows you to index tune the sp very conveniently.
I hope this helps.
-
Sorry, but this is way too simple and doesn't show any difference in the execution plan at all. Based on this example there is no way that anyone would in their right mind take this round-about route
Maybe a more conclusive example would be forthcoming if rubakuma could post his query/procedure/schema.
--------------------
Colt 45 - the original point and click interface -
<
First create the stored procedure that will create and execute another stored procedure. This is where you'll supply your parameters.
>
If your intent is to force a new execution plan for each run, would it not be simplier to specify the "WITH RECOMPILE" option with the stored procedure definition ?
CREATE PROCEDURE dbo.usp_TargetSP
( @StartDate datetime
, @EndDAte datetime)
WITH RECOMPILE
AS
SET NOCOUNT ON
SELECT *
FROM sysobjects
WHERE crdate BETWEEN @StartDate AND @EndDAte
SQL = Scarcely Qualifies as a Language
-
If my memory serves me well, this topic was about parameter sniffing. You asked for an example of my method which I provided. Oddly enough "its not good enoough" .... the concept is lost. Since I cannot provide you the exact scenario in which I avoid the initial problem of different execution plans, and you cannot use your imagination on how this could apply to you if you find yourself in a similar situation then answer the original question.
So much for sharing knowledege (isn't this what it's about?).
No longer motivated.
Later
-
Finally, I took the parameter as it is and declared local variables to each of those parameters and assigned the parameter values to these local variables in my SP. Then used these local variables in my query instead of parameters. That worked good. From hours and hours of running, I was able to run it within 7 minutes. Thanks.
Give me a fish, you feed me for a day! Teach me to fish, you feed me for a life time.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply