February 4, 2009 at 7:34 am
I have a stored procedure that:
Sets a bunch of local variables
Truncates a table
Inserts data into that table using a SELECT
When I run the code in Query Analyzer outside of the stored procedure, the code executes in a couple of seconds. But when I run EXEC usp_StoredProc, it takes like an hour. It is the same exact code, so why is the proc call taking so much longer? FYI: I have a couple of input parameters, and no BEGIN TRAN - COMMIT TRAN blocks in the code.
What do I need to do to get the stored procedure call to execute faster?
Any insight into this would be greatly appreciated!
Thanks in advance,
Cat
February 4, 2009 at 7:38 am
Post d code and we can take a look.
I have been in the same situation and could probably help.
AL
February 4, 2009 at 7:51 am
Here is the procedure:
CREATE PROCEDURE usp_NetCollectedByCollector (
@Merchant_ID char(5) = NULL,
@BegDate datetime = NULL,
@EndDate datetime = NULL,
@User_IsCollectionsRep bit = NULL
)
AS
DECLARE @CurrDate datetime
SET @CurrDate = Convert(varchar(32),getdate(),101) + ' 00:00:00'
IF @EndDate IS NULL
IF (SELECT datepart(weekday, @CurrDate + @@datefirst - 1) - 4) > 1
OR (SELECT datepart(weekday, @CurrDate + @@datefirst - 1) - 4) = 1
SET @EndDate = dateadd(dd, -4, @CurrDate)
ELSE
SET @EndDate = dateadd(dd, -6, @CurrDate)
SET @BegDate = dateadd(dd, (datepart(dd, @EndDate)*-1)+1, @EndDate)
IF @User_IsCollectionsRep IS NULL
SET @User_IsCollectionsRep = 1
TRUNCATE TABLE tbl_NetCollectedByCollectorReport
INSERT INTO tbl_NetCollectedByCollectorReport
SELECT vw.User_Created,
vw.NetCollected,
vw.Merchant_ID,
vw.Pmt_Date,
vw.Pmt_Amt,
vw.ReturnedAmt
FROM BankACustom.dbo.vw_NetCollectByCollector vw
WHERE vw.Merchant_ID = @Merchant_ID
AND vw.Pmt_Date between @BegDate and @EndDate
AND vw.User_IsCollectionsRep = @User_IsCollectionsRep
ORDER BY vw.User_Created ASC
When I execute the following in Query Analyzer:
EXEC usp_NetCollectedByCollector '57202'
it takes an hour, but when I run the code:
--CREATE PROCEDURE usp_NetCollectedByCollector (
declare
@Merchant_ID char(5),
@BegDate datetime,
@EndDate datetime,
@User_IsCollectionsRep bit
select
@Merchant_ID = '57202'
--)
--AS
DECLARE @CurrDate datetime
SET @CurrDate = Convert(varchar(32),getdate(),101) + ' 00:00:00'
IF @EndDate IS NULL
IF (SELECT datepart(weekday, @CurrDate + @@datefirst - 1) - 4) > 1
OR (SELECT datepart(weekday, @CurrDate + @@datefirst - 1) - 4) = 1
SET @EndDate = dateadd(dd, -4, @CurrDate)
ELSE
SET @EndDate = dateadd(dd, -6, @CurrDate)
SET @BegDate = dateadd(dd, (datepart(dd, @EndDate)*-1)+1, @EndDate)
IF @User_IsCollectionsRep IS NULL
SET @User_IsCollectionsRep = 1
TRUNCATE TABLE tbl_NetCollectedByCollectorReport
INSERT INTO tbl_NetCollectedByCollectorReport
SELECT vw.User_Created,
vw.NetCollected,
vw.Merchant_ID,
vw.Pmt_Date,
vw.Pmt_Amt,
vw.ReturnedAmt
FROM BankACustom.dbo.vw_NetCollectByCollector vw
WHERE vw.Merchant_ID = @Merchant_ID
AND vw.Pmt_Date between @BegDate and @EndDate
AND vw.User_IsCollectionsRep = @User_IsCollectionsRep
ORDER BY vw.User_Created ASC
It only takes about 10 seconds.
Thanks for your help.
Cat
February 4, 2009 at 8:07 am
I am not 100% sure but I have been in this situation before.
It would appear that the Query Analyzer is being fooled by something in your procedure and forced to R-BAR. Perhaps the date formatting are throwing it off. If you have the index names for where the datefrom dateto are used, try and force a query hint with it's name to see if that makes things better.
ie.
INSERT INTO tbl_NetCollectedByCollectorReport
SELECT vw.User_Created,
vw.NetCollected,
vw.Merchant_ID,
vw.Pmt_Date,
vw.Pmt_Amt,
vw.ReturnedAmt
FROM BankACustom.dbo.vw_NetCollectByCollector vw WITH (INDEX([cover index here]))
WHERE vw.Merchant_ID = @Merchant_ID
AND vw.Pmt_Date between @BegDate and @EndDate
AND vw.User_IsCollectionsRep = @User_IsCollectionsRep
ORDER BY vw.User_Created ASC
Although it appears that you are using VIEWS and not sure if you can force a query hint on a view. Try using the data table names instead if possible.
Hope this helps.
AL
February 4, 2009 at 8:41 am
I think the issue you are hitting is called "parameter sniffing"; when the code is compiled into a procedure, the compiler makes a best guess on the parameters when it makes an exececution plan....when that execution plan is way off, you get the performance whack you are seeing.
As i remember, there are two ways to fix this: force the procedure to recompile every time,
ie CREATE PROCEDURE WHATEVER () WITH RECOMPILE
or reassign the passed in parameters to local parameters inside the proc, and have the proc use just the local parameters.
lame example:
CREATE PROCEDURE usp_NetCollectedByCollector (
@pMerchant_ID char(5) = NULL,
@pBegDate datetime = NULL,
@pEndDate datetime = NULL,
@pUser_IsCollectionsRep bit = NULL
)
DECLARE
@Merchant_ID char(5) ,
@BegDate datetime ,
@EndDate datetime ,
@User_IsCollectionsRep bit
SET @Merchant_ID = @pMerchant_ID,
@BegDate =@pBegDate,
@EndDate =@pEndDatee ,
@User_IsCollectionsRep =@pUser_IsCollectionsRep
....
Lowell
February 4, 2009 at 8:44 am
Right on! for some versions of SQL Server 2000 and SQL Server 2005.
However, this problem was resolved in SQL Server 2005 SP1 if I remember correctly or is it still a problem?
AL
February 4, 2009 at 8:53 am
yeah, i don't know if it can be resolved...SP1 doesn't prevent a a bad execution from being built....SQL2005 still parameter sniffs, right?
Lowell
February 4, 2009 at 8:56 am
We have SQL Server 2005 SP2 and I am not sure if it still happening. I am about to try and reproduce with a and old proc that had the problem.
AL
February 4, 2009 at 8:57 am
Hi All,
The WITH RECOMPILE didn't seem to help, but I moved my parameters into local variables and (Woohoo!) it's running fast!
Thanks for all of your help! I really appreciate it!
Cat
February 4, 2009 at 8:59 am
Could you send us the version # of your SQL instance?
select @@version
Mine:
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
Thanks,
AL
February 4, 2009 at 9:00 am
Excellent! glad we could help!
Lowell
February 4, 2009 at 9:16 am
Sure, here it is:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
Have a great day!
Cat <-(Still doing a little dance! 🙂
February 4, 2009 at 9:24 am
Thanks and Yes this is a problem in SQL Server 2000 and I think still a problem with SQL 2005 even SP2.
Dance away!:hehe:
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply