[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Store Procedure Running slower gradually day after day

royHe

3/20/2007 4:11:00 PM

I have a store procedure running everyday (a Job excute this store
procedure). Everytime after I re-boot the server, it only take less than 1
minute to run. Day after day, it running slower by a few seconds or more,
eventually it took more than an hour to run. And it slow down the whole
system.

Here is the store procedures:

=====================================================
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[AM_Create_EFT_File]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[AM_Create_EFT_File]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO




/*
PURPOSE:
REQUIRES:
AUTHOR: Roy He
DATE CREATED: Sep 14, 2006
CALLED BY: Schedule Job
MOD HISTORY:
Name Date Comments
*/

CREATE PROCEDURE AM_Create_EFT_File --'2006-12-01', '2006-12-19'
@paramStartDate varchar(20) = '15 April 1999',
@paramEndDate varchar(20) = '15 April 1999'
AS


DECLARE @startDate datetime,
@endDate datetime


SELECT @startDate = CONVERT(datetime, NULLIF(@paramStartDate,'')),
@endDate = CONVERT(datetime, NULLIF(@paramEndDate,''))




--Generate Air Miles EFT Files
Delete Air_Miles_EFT_Header Where Starting_RBR_Date= @startDate and
Ending_RBR_Date =@endDate



Declare @dtToday datetime
Declare @File_Name varchar(13)
Declare @Transaction_Type_Header varchar(2)
Declare @Originator_ID varchar(10)
Declare @File_Creation_Number int

--Transaciton Detail
Declare @Transaction_Type_Detail varchar(2)
Declare @SponsorNumber varchar(2)
Declare @CustomerNumber varchar(4)
Declare @CompanyID varchar(25)




--Header
Select @dtToday=getdate()
Select @Transaction_Type_Header='00'

SELECT @File_Name= dbo.SystemSettingValues.SettingValue
FROM dbo.SystemSetting INNER JOIN
dbo.SystemSettingValues ON
dbo.SystemSetting.SettingID = dbo.SystemSettingValues.SettingID
WHERE (dbo.SystemSetting.SettingName =
'AirMilesEFT') AND (dbo.SystemSettingValues.ValueName = 'FileName')
SELECT @Originator_ID= dbo.SystemSettingValues.SettingValue
FROM dbo.SystemSetting INNER JOIN
dbo.SystemSettingValues ON
dbo.SystemSetting.SettingID = dbo.SystemSettingValues.SettingID
WHERE (dbo.SystemSetting.SettingName =
'AirMilesEFT') AND (dbo.SystemSettingValues.ValueName = 'OriginatorID')

Insert into Air_Miles_EFT_Header
(
Starting_RBR_Date ,
Ending_RBR_Date,
File_Name,
Transaction_Type,
Originator_ID,
File_CreatetionDate
)
SELECT @startDate,
@endDate,
@File_Name,
@Transaction_Type_Header,
@Originator_ID,
@dtToday
--convert(varchar(2),day(@dtToday))+convert(varchar(2),month(@dtToday))+
substring(convert(varchar(4),year(@dtToday)),3,2) as File_Creation_Date


Select @File_Creation_Number=@@IDENTITY


--Detail
Select @Transaction_Type_Detail='65'

SELECT @SponsorNumber= dbo.SystemSettingValues.SettingValue
FROM dbo.SystemSetting INNER JOIN
dbo.SystemSettingValues ON
dbo.SystemSetting.SettingID = dbo.SystemSettingValues.SettingID
WHERE (dbo.SystemSetting.SettingName =
'AirMilesEFT') AND (dbo.SystemSettingValues.ValueName = 'SponsorNumber')
SELECT @CustomerNumber= dbo.SystemSettingValues.SettingValue
FROM dbo.SystemSetting INNER JOIN
dbo.SystemSettingValues ON
dbo.SystemSetting.SettingID = dbo.SystemSettingValues.SettingID
WHERE (dbo.SystemSetting.SettingName =
'AirMilesEFT') AND (dbo.SystemSettingValues.ValueName = 'CustomerNumber')
SELECT @CompanyID=Code from dbo.Lookup_Table
WHERE (dbo.Lookup_Table.Category = 'BudgetBC Company ')


Delete Air_Miles_EFT_Detail Where (RBR_Date > @startDate) and (RBR_Date <
@endDate+1)

--Temporary solution for the slow response

Select * into #AM_Contract_TnM from dbo.AM_Contract_TnM_vw where
(dbo.AM_Contract_TnM_vw.Checkin_RBR_Date >= @startDate) and
(dbo.AM_Contract_TnM_vw.Checkin_RBR_Date < @endDate+1)

Insert into Air_Miles_EFT_Detail

SELECT @File_Creation_Number as File_Creation_Number,
Right(convert(varchar(15),(dbo.Contract.Contract_Number +10000000)),7) AS
Invoice_Number,
Contract_TnM .Business_Transaction_ID,
Contract_TnM .RBR_Date,
@CustomerNumber as Customer_Number,
dbo.Location.StationNumber AS Store_Number,
'0001' as Terminla_Number,
@Transaction_Type_Detail as Transaction_Type,
dbo.Contract.FF_Member_Number as Card_Number,
(Case When Contract_TnM .TnM_Amount>=0 then '00'
Else '30'
End) AS AMTM_Tran_Type,
(Case when dbo.Contract.FF_Swiped=1 then 'S'
Else 'M'
End) AS Entry_Mode,

Right(CONVERT(varchar(3), DATEPART(hh, Contract_TnM
..Transaction_Date)+100),2) + Right(CONVERT(varchar(3), DATEPART(mi,
Contract_TnM .Transaction_Date)+100),2) AS Transaction_Time,

Right(CONVERT(varchar(3), DAY(Contract_TnM .Transaction_Date)+100),2) +
RIGHT(CONVERT(varchar(3), MONTH(Contract_TnM .Transaction_Date)+100),2) +
RIGHT(CONVERT(varchar(4), YEAR(Contract_TnM .Transaction_Date)), 2)
AS Transaction_Date,
(Case when Payment_Type='Credit Card' then '3'
when Payment_Type='Cash' then '1'
Else '1'
End) as Payment_Type,
@SponsorNumber as Sponsor_Number,
'01' AS Base_Offer_Code,
Contract_TnM .TnM_Amount AS Sales_Amount,
CONVERT(INT, Contract_TnM .TnM_Amount / 10) AS Mile_Points,
1 AS Multiply_Factor,
0 AS Multipler_Miles,
(Case
When dbo.Contract.Pick_Up_On<'2007-03-16' then '9813'
Else '0000'
End) AS BONUS_OFFER_CODE,

(Case
When dbo.Contract.Pick_Up_On<'2007-03-16' then 1
Else 0
End) AS Offer_Quantity,

(Case
When dbo.Contract.Pick_Up_On<'2007-03-16' then CONVERT(INT,
Contract_TnM .TnM_Amount / 10)*2
Else 0
End) AS Bonus_Miles,

2 AS Offer_Type

FROM dbo.Contract
INNER JOIN dbo.Frequent_Flyer_Plan
ON dbo.Contract.Frequent_Flyer_Plan_ID =
dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan_ID
INNER JOIN #AM_Contract_TnM Contract_TnM
ON dbo.Contract.Contract_Number = Contract_TnM .Contract_Number
INNER JOIN dbo.Location
ON dbo.Contract.Pick_Up_Location_ID = dbo.Location.Location_ID
LEFT OUTER JOIN
dbo.Contract_Payment_Item ON dbo.Contract.Contract_Number =
dbo.Contract_Payment_Item.Contract_Number and Sequence=0



WHERE (dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan = 'Air Miles') AND
(Contract_TnM .Checkin_RBR_Date >= @startDate) and (Contract_TnM
..Checkin_RBR_Date < @endDate+1)
and dbo.Location.Owning_Company_ID=@CompanyID and
dbo.Contract.Pick_Up_On>='2007-01-08'
and len(dbo.Contract.FF_Member_Number )=11


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

===================================================
in order to find out the problem
I simply the procedure to the following statement:

DECLARE @startDate datetime,
@endDate datetime


SELECT @startDate = '2007-03-15'
@endDate = '2007-03-15'




SELECT dbo.AM_Contract_TnM_vw.*
FROM dbo.Contract
INNER JOIN dbo.Frequent_Flyer_Plan
ON dbo.Contract.Frequent_Flyer_Plan_ID =
dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan_ID
INNER JOIN dbo.AM_Contract_TnM_vw
ON dbo.Contract.Contract_Number = dbo.AM_Contract_TnM_vw.Contract_Number
INNER JOIN dbo.Location
ON dbo.Contract.Pick_Up_Location_ID = dbo.Location.Location_ID
LEFT OUTER JOIN
dbo.Contract_Payment_Item ON dbo.Contract.Contract_Number =
dbo.Contract_Payment_Item.Contract_Number and Sequence=0

WHERE (dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan = 'Air Miles') AND
(dbo.AM_Contract_TnM_vw.Checkin_RBR_Date >= @startDate) and
(dbo.AM_Contract_TnM_vw.Checkin_RBR_Date < @endDate+1)
and dbo.Location.Owning_Company_ID=7425 and
dbo.Contract.Pick_Up_On>='2007-01-08'
and len(dbo.Contract.FF_Member_Number )=11



I found the above statement runs as slow as the procedure. However, I have
two interesting finds here:

1. If I replace the date range creteria with a hard coded date value
instead of using variable, it took only a few seconds to run, even I run it
repeatedly.

SELECT dbo.AM_Contract_TnM_vw.*
FROM dbo.Contract
INNER JOIN dbo.Frequent_Flyer_Plan
ON dbo.Contract.Frequent_Flyer_Plan_ID =
dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan_ID
INNER JOIN dbo.AM_Contract_TnM_vw
ON dbo.Contract.Contract_Number = dbo.AM_Contract_TnM_vw.Contract_Number
INNER JOIN dbo.Location
ON dbo.Contract.Pick_Up_Location_ID = dbo.Location.Location_ID
LEFT OUTER JOIN
dbo.Contract_Payment_Item ON dbo.Contract.Contract_Number =
dbo.Contract_Payment_Item.Contract_Number and Sequence=0

WHERE (dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan = 'Air Miles') AND
(dbo.AM_Contract_TnM_vw.Checkin_RBR_Date >= '2007-03-15') and
(dbo.AM_Contract_TnM_vw.Checkin_RBR_Date < '2007-03-16')
and dbo.Location.Owning_Company_ID=7425 and
dbo.Contract.Pick_Up_On>='2007-01-08'
and len(dbo.Contract.FF_Member_Number )=11

2. Using temp table, it runs as fast as hardcoded date


DECLARE @startDate datetime,
@endDate datetime


SELECT @startDate = '2007-03-15',
@endDate = '2007-03-15'




--Generate Air Miles EFT Files
drop table #AM_Contract_TnM

Select * into #AM_Contract_TnM from dbo.AM_Contract_TnM_vw where
(dbo.AM_Contract_TnM_vw.Checkin_RBR_Date >= @startDate) and
(dbo.AM_Contract_TnM_vw.Checkin_RBR_Date < @endDate+1)



SELECT Contract_TnM.*
FROM dbo.Contract
INNER JOIN dbo.Frequent_Flyer_Plan
ON dbo.Contract.Frequent_Flyer_Plan_ID =
dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan_ID
INNER JOIN #AM_Contract_TnM Contract_TnM
ON dbo.Contract.Contract_Number = Contract_TnM .Contract_Number
INNER JOIN dbo.Location
ON dbo.Contract.Pick_Up_Location_ID = dbo.Location.Location_ID
LEFT OUTER JOIN
dbo.Contract_Payment_Item ON dbo.Contract.Contract_Number =
dbo.Contract_Payment_Item.Contract_Number and Sequence=0



WHERE (dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan = 'Air Miles') AND
(Contract_TnM .Checkin_RBR_Date >= @startDate) and (Contract_TnM
..Checkin_RBR_Date < @endDate+1)
and dbo.Location.Owning_Company_ID=7425 and
dbo.Contract.Pick_Up_On>='2007-01-08'
and len(dbo.Contract.FF_Member_Number )=11

Tempory solution for me now is using temp tale, I will watch to see if it
will get slower again.

I will appreciate if anyone can explain to me why the above sp running
slower day after day.

Thanks
9 Answers

Alejandro Mesa

3/20/2007 5:21:00 PM

0

royHe,

I haven't go through the whole of the sp, but I will suggest to avoid using
variables in the filter expression used in the where "clause", unless you are
using this approach to fight "parameter sniffing". Better to use the
parameters directly. This way (using variables) SQL Server will not use the
histogram of the distribution statistics related to the index in case it
exists.

Try:

> CREATE PROCEDURE AM_Create_EFT_File --'2006-12-01', '2006-12-19'
> @paramStartDate varchar(20) = '15 April 1999',
> @paramEndDate varchar(20) = '15 April 1999'

CREATE PROCEDURE AM_Create_EFT_File
@paramStartDate datetime = '19990415',
@paramEndDate datetime = '19990415'
....

> Delete Air_Miles_EFT_Header Where Starting_RBR_Date= @startDate and
> Ending_RBR_Date =@endDate

Delete
dbo.Air_Miles_EFT_Header
Where
Starting_RBR_Date = coalesce(@paramStartDate, '19990415') and
Ending_RBR_Date = coalesce(@paramEndDate, '19990415')
....

Be sure to catch that @paramEndDate is greater than or equal to
@paramStartDate.

AMB

"royHe" wrote:

> I have a store procedure running everyday (a Job excute this store
> procedure). Everytime after I re-boot the server, it only take less than 1
> minute to run. Day after day, it running slower by a few seconds or more,
> eventually it took more than an hour to run. And it slow down the whole
> system.
>
> Here is the store procedures:
>
> =====================================================
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[AM_Create_EFT_File]') and OBJECTPROPERTY(id,
> N'IsProcedure') = 1)
> drop procedure [dbo].[AM_Create_EFT_File]
> GO
>
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
>
>
>
>
> /*
> PURPOSE:
> REQUIRES:
> AUTHOR: Roy He
> DATE CREATED: Sep 14, 2006
> CALLED BY: Schedule Job
> MOD HISTORY:
> Name Date Comments
> */
>
> CREATE PROCEDURE AM_Create_EFT_File --'2006-12-01', '2006-12-19'
> @paramStartDate varchar(20) = '15 April 1999',
> @paramEndDate varchar(20) = '15 April 1999'
> AS
>
>
> DECLARE @startDate datetime,
> @endDate datetime
>
>
> SELECT @startDate = CONVERT(datetime, NULLIF(@paramStartDate,'')),
> @endDate = CONVERT(datetime, NULLIF(@paramEndDate,''))
>
>
>
>
> --Generate Air Miles EFT Files
> Delete Air_Miles_EFT_Header Where Starting_RBR_Date= @startDate and
> Ending_RBR_Date =@endDate
>
>
>
> Declare @dtToday datetime
> Declare @File_Name varchar(13)
> Declare @Transaction_Type_Header varchar(2)
> Declare @Originator_ID varchar(10)
> Declare @File_Creation_Number int
>
> --Transaciton Detail
> Declare @Transaction_Type_Detail varchar(2)
> Declare @SponsorNumber varchar(2)
> Declare @CustomerNumber varchar(4)
> Declare @CompanyID varchar(25)
>
>
>
>
> --Header
> Select @dtToday=getdate()
> Select @Transaction_Type_Header='00'
>
> SELECT @File_Name= dbo.SystemSettingValues.SettingValue
> FROM dbo.SystemSetting INNER JOIN
> dbo.SystemSettingValues ON
> dbo.SystemSetting.SettingID = dbo.SystemSettingValues.SettingID
> WHERE (dbo.SystemSetting.SettingName =
> 'AirMilesEFT') AND (dbo.SystemSettingValues.ValueName = 'FileName')
> SELECT @Originator_ID= dbo.SystemSettingValues.SettingValue
> FROM dbo.SystemSetting INNER JOIN
> dbo.SystemSettingValues ON
> dbo.SystemSetting.SettingID = dbo.SystemSettingValues.SettingID
> WHERE (dbo.SystemSetting.SettingName =
> 'AirMilesEFT') AND (dbo.SystemSettingValues.ValueName = 'OriginatorID')
>
> Insert into Air_Miles_EFT_Header
> (
> Starting_RBR_Date ,
> Ending_RBR_Date,
> File_Name,
> Transaction_Type,
> Originator_ID,
> File_CreatetionDate
> )
> SELECT @startDate,
> @endDate,
> @File_Name,
> @Transaction_Type_Header,
> @Originator_ID,
> @dtToday
> --convert(varchar(2),day(@dtToday))+convert(varchar(2),month(@dtToday))+
> substring(convert(varchar(4),year(@dtToday)),3,2) as File_Creation_Date
>
>
> Select @File_Creation_Number=@@IDENTITY
>
>
> --Detail
> Select @Transaction_Type_Detail='65'
>
> SELECT @SponsorNumber= dbo.SystemSettingValues.SettingValue
> FROM dbo.SystemSetting INNER JOIN
> dbo.SystemSettingValues ON
> dbo.SystemSetting.SettingID = dbo.SystemSettingValues.SettingID
> WHERE (dbo.SystemSetting.SettingName =
> 'AirMilesEFT') AND (dbo.SystemSettingValues.ValueName = 'SponsorNumber')
> SELECT @CustomerNumber= dbo.SystemSettingValues.SettingValue
> FROM dbo.SystemSetting INNER JOIN
> dbo.SystemSettingValues ON
> dbo.SystemSetting.SettingID = dbo.SystemSettingValues.SettingID
> WHERE (dbo.SystemSetting.SettingName =
> 'AirMilesEFT') AND (dbo.SystemSettingValues.ValueName = 'CustomerNumber')
> SELECT @CompanyID=Code from dbo.Lookup_Table
> WHERE (dbo.Lookup_Table.Category = 'BudgetBC Company ')
>
>
> Delete Air_Miles_EFT_Detail Where (RBR_Date > @startDate) and (RBR_Date <
> @endDate+1)
>
> --Temporary solution for the slow response
>
> Select * into #AM_Contract_TnM from dbo.AM_Contract_TnM_vw where
> (dbo.AM_Contract_TnM_vw.Checkin_RBR_Date >= @startDate) and
> (dbo.AM_Contract_TnM_vw.Checkin_RBR_Date < @endDate+1)
>
> Insert into Air_Miles_EFT_Detail
>
> SELECT @File_Creation_Number as File_Creation_Number,
> Right(convert(varchar(15),(dbo.Contract.Contract_Number +10000000)),7) AS
> Invoice_Number,
> Contract_TnM .Business_Transaction_ID,
> Contract_TnM .RBR_Date,
> @CustomerNumber as Customer_Number,
> dbo.Location.StationNumber AS Store_Number,
> '0001' as Terminla_Number,
> @Transaction_Type_Detail as Transaction_Type,
> dbo.Contract.FF_Member_Number as Card_Number,
> (Case When Contract_TnM .TnM_Amount>=0 then '00'
> Else '30'
> End) AS AMTM_Tran_Type,
> (Case when dbo.Contract.FF_Swiped=1 then 'S'
> Else 'M'
> End) AS Entry_Mode,
>
> Right(CONVERT(varchar(3), DATEPART(hh, Contract_TnM
> .Transaction_Date)+100),2) + Right(CONVERT(varchar(3), DATEPART(mi,
> Contract_TnM .Transaction_Date)+100),2) AS Transaction_Time,
>
> Right(CONVERT(varchar(3), DAY(Contract_TnM .Transaction_Date)+100),2) +
> RIGHT(CONVERT(varchar(3), MONTH(Contract_TnM .Transaction_Date)+100),2) +
> RIGHT(CONVERT(varchar(4), YEAR(Contract_TnM .Transaction_Date)), 2)
> AS Transaction_Date,
> (Case when Payment_Type='Credit Card' then '3'
> when Payment_Type='Cash' then '1'
> Else '1'
> End) as Payment_Type,
> @SponsorNumber as Sponsor_Number,
> '01' AS Base_Offer_Code,
> Contract_TnM .TnM_Amount AS Sales_Amount,
> CONVERT(INT, Contract_TnM .TnM_Amount / 10) AS Mile_Points,
> 1 AS Multiply_Factor,
> 0 AS Multipler_Miles,
> (Case
> When dbo.Contract.Pick_Up_On<'2007-03-16' then '9813'
> Else '0000'
> End) AS BONUS_OFFER_CODE,
>
> (Case
> When dbo.Contract.Pick_Up_On<'2007-03-16' then 1
> Else 0
> End) AS Offer_Quantity,
>
> (Case
> When dbo.Contract.Pick_Up_On<'2007-03-16' then CONVERT(INT,
> Contract_TnM .TnM_Amount / 10)*2
> Else 0
> End) AS Bonus_Miles,
>
> 2 AS Offer_Type
>
> FROM dbo.Contract
> INNER JOIN dbo.Frequent_Flyer_Plan
> ON dbo.Contract.Frequent_Flyer_Plan_ID =
> dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan_ID
> INNER JOIN #AM_Contract_TnM Contract_TnM
> ON dbo.Contract.Contract_Number = Contract_TnM .Contract_Number
> INNER JOIN dbo.Location
> ON dbo.Contract.Pick_Up_Location_ID = dbo.Location.Location_ID
> LEFT OUTER JOIN
> dbo.Contract_Payment_Item ON dbo.Contract.Contract_Number =
> dbo.Contract_Payment_Item.Contract_Number and Sequence=0
>
>
>
> WHERE (dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan = 'Air Miles') AND
> (Contract_TnM .Checkin_RBR_Date >= @startDate) and (Contract_TnM
> .Checkin_RBR_Date < @endDate+1)
> and dbo.Location.Owning_Company_ID=@CompanyID and
> dbo.Contract.Pick_Up_On>='2007-01-08'
> and len(dbo.Contract.FF_Member_Number )=11
>
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> ===================================================
> in order to find out the problem
> I simply the procedure to the following statement:
>
> DECLARE @startDate datetime,
> @endDate datetime
>
>
> SELECT @startDate = '2007-03-15'
> @endDate = '2007-03-15'
>
>
>
>
> SELECT dbo.AM_Contract_TnM_vw.*
> FROM dbo.Contract
> INNER JOIN dbo.Frequent_Flyer_Plan
> ON dbo.Contract.Frequent_Flyer_Plan_ID =
> dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan_ID
> INNER JOIN dbo.AM_Contract_TnM_vw
> ON dbo.Contract.Contract_Number = dbo.AM_Contract_TnM_vw.Contract_Number
> INNER JOIN dbo.Location
> ON dbo.Contract.Pick_Up_Location_ID = dbo.Location.Location_ID
> LEFT OUTER JOIN
> dbo.Contract_Payment_Item ON dbo.Contract.Contract_Number =
> dbo.Contract_Payment_Item.Contract_Number and Sequence=0
>
> WHERE (dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan = 'Air Miles') AND
> (dbo.AM_Contract_TnM_vw.Checkin_RBR_Date >= @startDate) and
> (dbo.AM_Contract_TnM_vw.Checkin_RBR_Date < @endDate+1)
> and dbo.Location.Owning_Company_ID=7425 and
> dbo.Contract.Pick_Up_On>='2007-01-08'
> and len(dbo.Contract.FF_Member_Number )=11
>
>
>
> I found the above statement runs as slow as the procedure. However, I have
> two interesting finds here:
>
> 1. If I replace the date range creteria with a hard coded date value
> instead of using variable, it took only a few seconds to run, even I run it
> repeatedly.
>
> SELECT dbo.AM_Contract_TnM_vw.*
> FROM dbo.Contract
> INNER JOIN dbo.Frequent_Flyer_Plan
> ON dbo.Contract.Frequent_Flyer_Plan_ID =
> dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan_ID
> INNER JOIN dbo.AM_Contract_TnM_vw
> ON dbo.Contract.Contract_Number = dbo.AM_Contract_TnM_vw.Contract_Number
> INNER JOIN dbo.Location
> ON dbo.Contract.Pick_Up_Location_ID = dbo.Location.Location_ID
> LEFT OUTER JOIN
> dbo.Contract_Payment_Item ON dbo.Contract.Contract_Number =
> dbo.Contract_Payment_Item.Contract_Number and Sequence=0
>
> WHERE (dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan = 'Air Miles') AND
> (dbo.AM_Contract_TnM_vw.Checkin_RBR_Date >= '2007-03-15') and
> (dbo.AM_Contract_TnM_vw.Checkin_RBR_Date < '2007-03-16')
> and dbo.Location.Owning_Company_ID=7425 and
> dbo.Contract.Pick_Up_On>='2007-01-08'
> and len(dbo.Contract.FF_Member_Number )=11
>
> 2. Using temp table, it runs as fast as hardcoded date
>
>
> DECLARE @startDate datetime,
> @endDate datetime
>
>
> SELECT @startDate = '2007-03-15',
> @endDate = '2007-03-15'
>
>
>
>
> --Generate Air Miles EFT Files
> drop table #AM_Contract_TnM
>
> Select * into #AM_Contract_TnM from dbo.AM_Contract_TnM_vw where

Tibor Karaszi

3/20/2007 5:22:00 PM

0

> 1. If I replace the date range creteria with a hard coded date value
> instead of using variable, it took only a few seconds to run, even I run it
> repeatedly.

Google for parameter sniffing. If you are on 2005, check out the OPTIMIZE FOR hint and the RECOMPILE
query hint.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/d...
http://sqlblog.com/blogs/tib...


"royHe" <royHe@discussions.microsoft.com> wrote in message
news:E1BFB66D-D57A-4BB7-BDEE-ACA1E9691FDF@microsoft.com...
>I have a store procedure running everyday (a Job excute this store
> procedure). Everytime after I re-boot the server, it only take less than 1
> minute to run. Day after day, it running slower by a few seconds or more,
> eventually it took more than an hour to run. And it slow down the whole
> system.
>
> Here is the store procedures:
>
> =====================================================
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[AM_Create_EFT_File]') and OBJECTPROPERTY(id,
> N'IsProcedure') = 1)
> drop procedure [dbo].[AM_Create_EFT_File]
> GO
>
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
>
>
>
>
> /*
> PURPOSE:
> REQUIRES:
> AUTHOR: Roy He
> DATE CREATED: Sep 14, 2006
> CALLED BY: Schedule Job
> MOD HISTORY:
> Name Date Comments
> */
>
> CREATE PROCEDURE AM_Create_EFT_File --'2006-12-01', '2006-12-19'
> @paramStartDate varchar(20) = '15 April 1999',
> @paramEndDate varchar(20) = '15 April 1999'
> AS
>
>
> DECLARE @startDate datetime,
> @endDate datetime
>
>
> SELECT @startDate = CONVERT(datetime, NULLIF(@paramStartDate,'')),
> @endDate = CONVERT(datetime, NULLIF(@paramEndDate,''))
>
>
>
>
> --Generate Air Miles EFT Files
> Delete Air_Miles_EFT_Header Where Starting_RBR_Date= @startDate and
> Ending_RBR_Date =@endDate
>
>
>
> Declare @dtToday datetime
> Declare @File_Name varchar(13)
> Declare @Transaction_Type_Header varchar(2)
> Declare @Originator_ID varchar(10)
> Declare @File_Creation_Number int
>
> --Transaciton Detail
> Declare @Transaction_Type_Detail varchar(2)
> Declare @SponsorNumber varchar(2)
> Declare @CustomerNumber varchar(4)
> Declare @CompanyID varchar(25)
>
>
>
>
> --Header
> Select @dtToday=getdate()
> Select @Transaction_Type_Header='00'
>
> SELECT @File_Name= dbo.SystemSettingValues.SettingValue
> FROM dbo.SystemSetting INNER JOIN
> dbo.SystemSettingValues ON
> dbo.SystemSetting.SettingID = dbo.SystemSettingValues.SettingID
> WHERE (dbo.SystemSetting.SettingName =
> 'AirMilesEFT') AND (dbo.SystemSettingValues.ValueName = 'FileName')
> SELECT @Originator_ID= dbo.SystemSettingValues.SettingValue
> FROM dbo.SystemSetting INNER JOIN
> dbo.SystemSettingValues ON
> dbo.SystemSetting.SettingID = dbo.SystemSettingValues.SettingID
> WHERE (dbo.SystemSetting.SettingName =
> 'AirMilesEFT') AND (dbo.SystemSettingValues.ValueName = 'OriginatorID')
>
> Insert into Air_Miles_EFT_Header
> (
> Starting_RBR_Date ,
> Ending_RBR_Date,
> File_Name,
> Transaction_Type,
> Originator_ID,
> File_CreatetionDate
> )
> SELECT @startDate,
> @endDate,
> @File_Name,
> @Transaction_Type_Header,
> @Originator_ID,
> @dtToday
> --convert(varchar(2),day(@dtToday))+convert(varchar(2),month(@dtToday))+
> substring(convert(varchar(4),year(@dtToday)),3,2) as File_Creation_Date
>
>
> Select @File_Creation_Number=@@IDENTITY
>
>
> --Detail
> Select @Transaction_Type_Detail='65'
>
> SELECT @SponsorNumber= dbo.SystemSettingValues.SettingValue
> FROM dbo.SystemSetting INNER JOIN
> dbo.SystemSettingValues ON
> dbo.SystemSetting.SettingID = dbo.SystemSettingValues.SettingID
> WHERE (dbo.SystemSetting.SettingName =
> 'AirMilesEFT') AND (dbo.SystemSettingValues.ValueName = 'SponsorNumber')
> SELECT @CustomerNumber= dbo.SystemSettingValues.SettingValue
> FROM dbo.SystemSetting INNER JOIN
> dbo.SystemSettingValues ON
> dbo.SystemSetting.SettingID = dbo.SystemSettingValues.SettingID
> WHERE (dbo.SystemSetting.SettingName =
> 'AirMilesEFT') AND (dbo.SystemSettingValues.ValueName = 'CustomerNumber')
> SELECT @CompanyID=Code from dbo.Lookup_Table
> WHERE (dbo.Lookup_Table.Category = 'BudgetBC Company ')
>
>
> Delete Air_Miles_EFT_Detail Where (RBR_Date > @startDate) and (RBR_Date <
> @endDate+1)
>
> --Temporary solution for the slow response
>
> Select * into #AM_Contract_TnM from dbo.AM_Contract_TnM_vw where
> (dbo.AM_Contract_TnM_vw.Checkin_RBR_Date >= @startDate) and
> (dbo.AM_Contract_TnM_vw.Checkin_RBR_Date < @endDate+1)
>
> Insert into Air_Miles_EFT_Detail
>
> SELECT @File_Creation_Number as File_Creation_Number,
> Right(convert(varchar(15),(dbo.Contract.Contract_Number +10000000)),7) AS
> Invoice_Number,
> Contract_TnM .Business_Transaction_ID,
> Contract_TnM .RBR_Date,
> @CustomerNumber as Customer_Number,
> dbo.Location.StationNumber AS Store_Number,
> '0001' as Terminla_Number,
> @Transaction_Type_Detail as Transaction_Type,
> dbo.Contract.FF_Member_Number as Card_Number,
> (Case When Contract_TnM .TnM_Amount>=0 then '00'
> Else '30'
> End) AS AMTM_Tran_Type,
> (Case when dbo.Contract.FF_Swiped=1 then 'S'
> Else 'M'
> End) AS Entry_Mode,
>
> Right(CONVERT(varchar(3), DATEPART(hh, Contract_TnM
> .Transaction_Date)+100),2) + Right(CONVERT(varchar(3), DATEPART(mi,
> Contract_TnM .Transaction_Date)+100),2) AS Transaction_Time,
>
> Right(CONVERT(varchar(3), DAY(Contract_TnM .Transaction_Date)+100),2) +
> RIGHT(CONVERT(varchar(3), MONTH(Contract_TnM .Transaction_Date)+100),2) +
> RIGHT(CONVERT(varchar(4), YEAR(Contract_TnM .Transaction_Date)), 2)
> AS Transaction_Date,
> (Case when Payment_Type='Credit Card' then '3'
> when Payment_Type='Cash' then '1'
> Else '1'
> End) as Payment_Type,
> @SponsorNumber as Sponsor_Number,
> '01' AS Base_Offer_Code,
> Contract_TnM .TnM_Amount AS Sales_Amount,
> CONVERT(INT, Contract_TnM .TnM_Amount / 10) AS Mile_Points,
> 1 AS Multiply_Factor,
> 0 AS Multipler_Miles,
> (Case
> When dbo.Contract.Pick_Up_On<'2007-03-16' then '9813'
> Else '0000'
> End) AS BONUS_OFFER_CODE,
>
> (Case
> When dbo.Contract.Pick_Up_On<'2007-03-16' then 1
> Else 0
> End) AS Offer_Quantity,
>
> (Case
> When dbo.Contract.Pick_Up_On<'2007-03-16' then CONVERT(INT,
> Contract_TnM .TnM_Amount / 10)*2
> Else 0
> End) AS Bonus_Miles,
>
> 2 AS Offer_Type
>
> FROM dbo.Contract
> INNER JOIN dbo.Frequent_Flyer_Plan
> ON dbo.Contract.Frequent_Flyer_Plan_ID =
> dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan_ID
> INNER JOIN #AM_Contract_TnM Contract_TnM
> ON dbo.Contract.Contract_Number = Contract_TnM .Contract_Number
> INNER JOIN dbo.Location
> ON dbo.Contract.Pick_Up_Location_ID = dbo.Location.Location_ID
> LEFT OUTER JOIN
> dbo.Contract_Payment_Item ON dbo.Contract.Contract_Number =
> dbo.Contract_Payment_Item.Contract_Number and Sequence=0
>
>
>
> WHERE (dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan = 'Air Miles') AND
> (Contract_TnM .Checkin_RBR_Date >= @startDate) and (Contract_TnM
> .Checkin_RBR_Date < @endDate+1)
> and dbo.Location.Owning_Company_ID=@CompanyID and
> dbo.Contract.Pick_Up_On>='2007-01-08'
> and len(dbo.Contract.FF_Member_Number )=11
>
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> ===================================================
> in order to find out the problem
> I simply the procedure to the following statement:
>
> DECLARE @startDate datetime,
> @endDate datetime
>
>
> SELECT @startDate = '2007-03-15'
> @endDate = '2007-03-15'
>
>
>
>
> SELECT dbo.AM_Contract_TnM_vw.*
> FROM dbo.Contract
> INNER JOIN dbo.Frequent_Flyer_Plan
> ON dbo.Contract.Frequent_Flyer_Plan_ID =
> dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan_ID
> INNER JOIN dbo.AM_Contract_TnM_vw
> ON dbo.Contract.Contract_Number = dbo.AM_Contract_TnM_vw.Contract_Number
> INNER JOIN dbo.Location
> ON dbo.Contract.Pick_Up_Location_ID = dbo.Location.Location_ID
> LEFT OUTER JOIN
> dbo.Contract_Payment_Item ON dbo.Contract.Contract_Number =
> dbo.Contract_Payment_Item.Contract_Number and Sequence=0
>
> WHERE (dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan = 'Air Miles') AND
> (dbo.AM_Contract_TnM_vw.Checkin_RBR_Date >= @startDate) and
> (dbo.AM_Contract_TnM_vw.Checkin_RBR_Date < @endDate+1)
> and dbo.Location.Owning_Company_ID=7425 and
> dbo.Contract.Pick_Up_On>='2007-01-08'
> and len(dbo.Contract.FF_Member_Number )=11
>
>
>
> I found the above statement runs as slow as the procedure. However, I have
> two interesting finds here:
>
> 1. If I replace the date range creteria with a hard coded date value
> instead of using variable, it took only a few seconds to run, even I run it
> repeatedly.
>
> SELECT dbo.AM_Contract_TnM_vw.*
> FROM dbo.Contract
> INNER JOIN dbo.Frequent_Flyer_Plan
> ON dbo.Contract.Frequent_Flyer_Plan_ID =
> dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan_ID
> INNER JOIN dbo.AM_Contract_TnM_vw
> ON dbo.Contract.Contract_Number = dbo.AM_Contract_TnM_vw.Contract_Number
> INNER JOIN dbo.Location
> ON dbo.Contract.Pick_Up_Location_ID = dbo.Location.Location_ID
> LEFT OUTER JOIN
> dbo.Contract_Payment_Item ON dbo.Contract.Contract_Number =
> dbo.Contract_Payment_Item.Contract_Number and Sequence=0
>
> WHERE (dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan = 'Air Miles') AND
> (dbo.AM_Contract_TnM_vw.Checkin_RBR_Date >= '2007-03-15') and
> (dbo.AM_Contract_TnM_vw.Checkin_RBR_Date < '2007-03-16')
> and dbo.Location.Owning_Company_ID=7425 and
> dbo.Contract.Pick_Up_On>='2007-01-08'
> and len(dbo.Contract.FF_Member_Number )=11
>
> 2. Using temp table, it runs as fast as hardcoded date
>
>
> DECLARE @startDate datetime,
> @endDate datetime
>
>
> SELECT @startDate = '2007-03-15',
> @endDate = '2007-03-15'
>
>
>
>
> --Generate Air Miles EFT Files
> drop table #AM_Contract_TnM
>
> Select * into #AM_Contract_TnM from dbo.AM_Contract_TnM_vw where
> (dbo.AM_Contract_TnM_vw.Checkin_RBR_Date >= @startDate) and
> (dbo.AM_Contract_TnM_vw.Checkin_RBR_Date < @endDate+1)
>
>
>
> SELECT Contract_TnM.*
> FROM dbo.Contract
> INNER JOIN dbo.Frequent_Flyer_Plan
> ON dbo.Contract.Frequent_Flyer_Plan_ID =
> dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan_ID
> INNER JOIN #AM_Contract_TnM Contract_TnM
> ON dbo.Contract.Contract_Number = Contract_TnM .Contract_Number
> INNER JOIN dbo.Location
> ON dbo.Contract.Pick_Up_Location_ID = dbo.Location.Location_ID
> LEFT OUTER JOIN
> dbo.Contract_Payment_Item ON dbo.Contract.Contract_Number =
> dbo.Contract_Payment_Item.Contract_Number and Sequence=0
>
>
>
> WHERE (dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan = 'Air Miles') AND
> (Contract_TnM .Checkin_RBR_Date >= @startDate) and (Contract_TnM
> .Checkin_RBR_Date < @endDate+1)
> and dbo.Location.Owning_Company_ID=7425 and
> dbo.Contract.Pick_Up_On>='2007-01-08'
> and len(dbo.Contract.FF_Member_Number )=11
>
> Tempory solution for me now is using temp tale, I will watch to see if it
> will get slower again.
>
> I will appreciate if anyone can explain to me why the above sp running
> slower day after day.
>
> Thanks
>
>
>
>
>

Gert-Jan Strik

3/20/2007 7:17:00 PM

0

In addition to the other replies: make sure the table statistics are up
to date. You may want to check if autocreate statistics and auto update
statistics are on (which they are by default).

If the problem persists, you could always experiment with the WITH
RECOMPILE clause of the CREATE PROCEDURE command, or periodically run
"sp_recompile AM_Create_EFT_File".

HTH,
Gert-Jan


royHe wrote:
>
> I have a store procedure running everyday (a Job excute this store
> procedure). Everytime after I re-boot the server, it only take less than 1
> minute to run. Day after day, it running slower by a few seconds or more,
> eventually it took more than an hour to run. And it slow down the whole
> system.
>
> Here is the store procedures:
>
> =====================================================
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[AM_Create_EFT_File]') and OBJECTPROPERTY(id,
> N'IsProcedure') = 1)
> drop procedure [dbo].[AM_Create_EFT_File]
> GO
>
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
>
> /*
> PURPOSE:
> REQUIRES:
> AUTHOR: Roy He
> DATE CREATED: Sep 14, 2006
> CALLED BY: Schedule Job
> MOD HISTORY:
> Name Date Comments
> */
>
> CREATE PROCEDURE AM_Create_EFT_File --'2006-12-01', '2006-12-19'
> @paramStartDate varchar(20) = '15 April 1999',
> @paramEndDate varchar(20) = '15 April 1999'
> AS
>
> DECLARE @startDate datetime,
> @endDate datetime
>
> SELECT @startDate = CONVERT(datetime, NULLIF(@paramStartDate,'')),
> @endDate = CONVERT(datetime, NULLIF(@paramEndDate,''))
>
> --Generate Air Miles EFT Files
> Delete Air_Miles_EFT_Header Where Starting_RBR_Date= @startDate and
> Ending_RBR_Date =@endDate
>
> Declare @dtToday datetime
> Declare @File_Name varchar(13)
> Declare @Transaction_Type_Header varchar(2)
> Declare @Originator_ID varchar(10)
> Declare @File_Creation_Number int
>
> --Transaciton Detail
> Declare @Transaction_Type_Detail varchar(2)
> Declare @SponsorNumber varchar(2)
> Declare @CustomerNumber varchar(4)
> Declare @CompanyID varchar(25)
>
> --Header
> Select @dtToday=getdate()
> Select @Transaction_Type_Header='00'
>
> SELECT @File_Name= dbo.SystemSettingValues.SettingValue
> FROM dbo.SystemSetting INNER JOIN
> dbo.SystemSettingValues ON
> dbo.SystemSetting.SettingID = dbo.SystemSettingValues.SettingID
> WHERE (dbo.SystemSetting.SettingName =
> 'AirMilesEFT') AND (dbo.SystemSettingValues.ValueName = 'FileName')
> SELECT @Originator_ID= dbo.SystemSettingValues.SettingValue
> FROM dbo.SystemSetting INNER JOIN
> dbo.SystemSettingValues ON
> dbo.SystemSetting.SettingID = dbo.SystemSettingValues.SettingID
> WHERE (dbo.SystemSetting.SettingName =
> 'AirMilesEFT') AND (dbo.SystemSettingValues.ValueName = 'OriginatorID')
>
> Insert into Air_Miles_EFT_Header
> (
> Starting_RBR_Date ,
> Ending_RBR_Date,
> File_Name,
> Transaction_Type,
> Originator_ID,
> File_CreatetionDate
> )
> SELECT @startDate,
> @endDate,
> @File_Name,
> @Transaction_Type_Header,
> @Originator_ID,
> @dtToday
> --convert(varchar(2),day(@dtToday))+convert(varchar(2),month(@dtToday))+
> substring(convert(varchar(4),year(@dtToday)),3,2) as File_Creation_Date
>
>
> Select @File_Creation_Number=@@IDENTITY
>
> --Detail
> Select @Transaction_Type_Detail='65'
>
> SELECT @SponsorNumber= dbo.SystemSettingValues.SettingValue
> FROM dbo.SystemSetting INNER JOIN
> dbo.SystemSettingValues ON
> dbo.SystemSetting.SettingID = dbo.SystemSettingValues.SettingID
> WHERE (dbo.SystemSetting.SettingName =
> 'AirMilesEFT') AND (dbo.SystemSettingValues.ValueName = 'SponsorNumber')
> SELECT @CustomerNumber= dbo.SystemSettingValues.SettingValue
> FROM dbo.SystemSetting INNER JOIN
> dbo.SystemSettingValues ON
> dbo.SystemSetting.SettingID = dbo.SystemSettingValues.SettingID
> WHERE (dbo.SystemSetting.SettingName =
> 'AirMilesEFT') AND (dbo.SystemSettingValues.ValueName = 'CustomerNumber')
> SELECT @CompanyID=Code from dbo.Lookup_Table
> WHERE (dbo.Lookup_Table.Category = 'BudgetBC Company ')
>
> Delete Air_Miles_EFT_Detail Where (RBR_Date > @startDate) and (RBR_Date <
> @endDate+1)
>
> --Temporary solution for the slow response
>
> Select * into #AM_Contract_TnM from dbo.AM_Contract_TnM_vw where
> (dbo.AM_Contract_TnM_vw.Checkin_RBR_Date >= @startDate) and
> (dbo.AM_Contract_TnM_vw.Checkin_RBR_Date < @endDate+1)
>
> Insert into Air_Miles_EFT_Detail
>
> SELECT @File_Creation_Number as File_Creation_Number,
> Right(convert(varchar(15),(dbo.Contract.Contract_Number +10000000)),7) AS
> Invoice_Number,
> Contract_TnM .Business_Transaction_ID,
> Contract_TnM .RBR_Date,
> @CustomerNumber as Customer_Number,
> dbo.Location.StationNumber AS Store_Number,
> '0001' as Terminla_Number,
> @Transaction_Type_Detail as Transaction_Type,
> dbo.Contract.FF_Member_Number as Card_Number,
> (Case When Contract_TnM .TnM_Amount>=0 then '00'
> Else '30'
> End) AS AMTM_Tran_Type,
> (Case when dbo.Contract.FF_Swiped=1 then 'S'
> Else 'M'
> End) AS Entry_Mode,
>
> Right(CONVERT(varchar(3), DATEPART(hh, Contract_TnM
> .Transaction_Date)+100),2) + Right(CONVERT(varchar(3), DATEPART(mi,
> Contract_TnM .Transaction_Date)+100),2) AS Transaction_Time,
>
> Right(CONVERT(varchar(3), DAY(Contract_TnM .Transaction_Date)+100),2) +
> RIGHT(CONVERT(varchar(3), MONTH(Contract_TnM .Transaction_Date)+100),2) +
> RIGHT(CONVERT(varchar(4), YEAR(Contract_TnM .Transaction_Date)), 2)
> AS Transaction_Date,
> (Case when Payment_Type='Credit Card' then '3'
> when Payment_Type='Cash' then '1'
> Else '1'
> End) as Payment_Type,
> @SponsorNumber as Sponsor_Number,
> '01' AS Base_Offer_Code,
> Contract_TnM .TnM_Amount AS Sales_Amount,
> CONVERT(INT, Contract_TnM .TnM_Amount / 10) AS Mile_Points,
> 1 AS Multiply_Factor,
> 0 AS Multipler_Miles,
> (Case
> When dbo.Contract.Pick_Up_On<'2007-03-16' then '9813'
> Else '0000'
> End) AS BONUS_OFFER_CODE,
>
> (Case
> When dbo.Contract.Pick_Up_On<'2007-03-16' then 1
> Else 0
> End) AS Offer_Quantity,
>
> (Case
> When dbo.Contract.Pick_Up_On<'2007-03-16' then CONVERT(INT,
> Contract_TnM .TnM_Amount / 10)*2
> Else 0
> End) AS Bonus_Miles,
>
> 2 AS Offer_Type
>
> FROM dbo.Contract
> INNER JOIN dbo.Frequent_Flyer_Plan
> ON dbo.Contract.Frequent_Flyer_Plan_ID =
> dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan_ID
> INNER JOIN #AM_Contract_TnM Contract_TnM
> ON dbo.Contract.Contract_Number = Contract_TnM .Contract_Number
> INNER JOIN dbo.Location
> ON dbo.Contract.Pick_Up_Location_ID = dbo.Location.Location_ID
> LEFT OUTER JOIN
> dbo.Contract_Payment_Item ON dbo.Contract.Contract_Number =
> dbo.Contract_Payment_Item.Contract_Number and Sequence=0
>
> WHERE (dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan = 'Air Miles') AND
> (Contract_TnM .Checkin_RBR_Date >= @startDate) and (Contract_TnM
> .Checkin_RBR_Date < @endDate+1)
> and dbo.Location.Owning_Company_ID=@CompanyID and
> dbo.Contract.Pick_Up_On>='2007-01-08'
> and len(dbo.Contract.FF_Member_Number )=11
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> ===================================================
> in order to find out the problem
> I simply the procedure to the following statement:
>
> DECLARE @startDate datetime,
> @endDate datetime
>
> SELECT @startDate = '2007-03-15'
> @endDate = '2007-03-15'
>
> SELECT dbo.AM_Contract_TnM_vw.*
> FROM dbo.Contract
> INNER JOIN dbo.Frequent_Flyer_Plan
> ON dbo.Contract.Frequent_Flyer_Plan_ID =
> dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan_ID
> INNER JOIN dbo.AM_Contract_TnM_vw
> ON dbo.Contract.Contract_Number = dbo.AM_Contract_TnM_vw.Contract_Number
> INNER JOIN dbo.Location
> ON dbo.Contract.Pick_Up_Location_ID = dbo.Location.Location_ID
> LEFT OUTER JOIN
> dbo.Contract_Payment_Item ON dbo.Contract.Contract_Number =
> dbo.Contract_Payment_Item.Contract_Number and Sequence=0
>
> WHERE (dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan = 'Air Miles') AND
> (dbo.AM_Contract_TnM_vw.Checkin_RBR_Date >= @startDate) and
> (dbo.AM_Contract_TnM_vw.Checkin_RBR_Date < @endDate+1)
> and dbo.Location.Owning_Company_ID=7425 and
> dbo.Contract.Pick_Up_On>='2007-01-08'
> and len(dbo.Contract.FF_Member_Number )=11
>
> I found the above statement runs as slow as the procedure. However, I have
> two interesting finds here:
>
> 1. If I replace the date range creteria with a hard coded date value
> instead of using variable, it took only a few seconds to run, even I run it
> repeatedly.
>
> SELECT dbo.AM_Contract_TnM_vw.*
> FROM dbo.Contract
> INNER JOIN dbo.Frequent_Flyer_Plan
> ON dbo.Contract.Frequent_Flyer_Plan_ID =
> dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan_ID
> INNER JOIN dbo.AM_Contract_TnM_vw
> ON dbo.Contract.Contract_Number = dbo.AM_Contract_TnM_vw.Contract_Number
> INNER JOIN dbo.Location
> ON dbo.Contract.Pick_Up_Location_ID = dbo.Location.Location_ID
> LEFT OUTER JOIN
> dbo.Contract_Payment_Item ON dbo.Contract.Contract_Number =
> dbo.Contract_Payment_Item.Contract_Number and Sequence=0
>
> WHERE (dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan = 'Air Miles') AND
> (dbo.AM_Contract_TnM_vw.Checkin_RBR_Date >= '2007-03-15') and
> (dbo.AM_Contract_TnM_vw.Checkin_RBR_Date < '2007-03-16')
> and dbo.Location.Owning_Company_ID=7425 and
> dbo.Contract.Pick_Up_On>='2007-01-08'
> and len(dbo.Contract.FF_Member_Number )=11
>
> 2. Using temp table, it runs as fast as hardcoded date
>
> DECLARE @startDate datetime,
> @endDate datetime
>
> SELECT @startDate = '2007-03-15',
> @endDate = '2007-03-15'
>
> --Generate Air Miles EFT Files
> drop table #AM_Contract_TnM
>
> Select * into #AM_Contract_TnM from dbo.AM_Contract_TnM_vw where
> (dbo.AM_Contract_TnM_vw.Checkin_RBR_Date >= @startDate) and
> (dbo.AM_Contract_TnM_vw.Checkin_RBR_Date < @endDate+1)
>
>
> SELECT Contract_TnM.*
> FROM dbo.Contract
> INNER JOIN dbo.Frequent_Flyer_Plan
> ON dbo.Contract.Frequent_Flyer_Plan_ID =
> dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan_ID
> INNER JOIN #AM_Contract_TnM Contract_TnM
> ON dbo.Contract.Contract_Number = Contract_TnM .Contract_Number
> INNER JOIN dbo.Location
> ON dbo.Contract.Pick_Up_Location_ID = dbo.Location.Location_ID
> LEFT OUTER JOIN
> dbo.Contract_Payment_Item ON dbo.Contract.Contract_Number =
> dbo.Contract_Payment_Item.Contract_Number and Sequence=0
>
> WHERE (dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan = 'Air Miles') AND
> (Contract_TnM .Checkin_RBR_Date >= @startDate) and (Contract_TnM
> .Checkin_RBR_Date < @endDate+1)
> and dbo.Location.Owning_Company_ID=7425 and
> dbo.Contract.Pick_Up_On>='2007-01-08'
> and len(dbo.Contract.FF_Member_Number )=11
>
> Tempory solution for me now is using temp tale, I will watch to see if it
> will get slower again.
>
> I will appreciate if anyone can explain to me why the above sp running
> slower day after day.
>
> Thanks

Alejandro Mesa

3/20/2007 7:36:00 PM

0

Gert-Jan Strik,

> In addition to the other replies: make sure the table statistics are up
> to date. You may want to check if autocreate statistics and auto update
> statistics are on (which they are by default).

I should have started saying that :)

AMB


"Gert-Jan Strik" wrote:

> In addition to the other replies: make sure the table statistics are up
> to date. You may want to check if autocreate statistics and auto update
> statistics are on (which they are by default).
>
> If the problem persists, you could always experiment with the WITH
> RECOMPILE clause of the CREATE PROCEDURE command, or periodically run
> "sp_recompile AM_Create_EFT_File".
>
> HTH,
> Gert-Jan
>
>
> royHe wrote:
> >
> > I have a store procedure running everyday (a Job excute this store
> > procedure). Everytime after I re-boot the server, it only take less than 1
> > minute to run. Day after day, it running slower by a few seconds or more,
> > eventually it took more than an hour to run. And it slow down the whole
> > system.
> >
> > Here is the store procedures:
> >
> > =====================================================
> > if exists (select * from dbo.sysobjects where id =
> > object_id(N'[dbo].[AM_Create_EFT_File]') and OBJECTPROPERTY(id,
> > N'IsProcedure') = 1)
> > drop procedure [dbo].[AM_Create_EFT_File]
> > GO
> >
> > SET QUOTED_IDENTIFIER ON
> > GO
> > SET ANSI_NULLS ON
> > GO
> >
> > /*
> > PURPOSE:
> > REQUIRES:
> > AUTHOR: Roy He
> > DATE CREATED: Sep 14, 2006
> > CALLED BY: Schedule Job
> > MOD HISTORY:
> > Name Date Comments
> > */
> >
> > CREATE PROCEDURE AM_Create_EFT_File --'2006-12-01', '2006-12-19'
> > @paramStartDate varchar(20) = '15 April 1999',
> > @paramEndDate varchar(20) = '15 April 1999'
> > AS
> >
> > DECLARE @startDate datetime,
> > @endDate datetime
> >
> > SELECT @startDate = CONVERT(datetime, NULLIF(@paramStartDate,'')),
> > @endDate = CONVERT(datetime, NULLIF(@paramEndDate,''))
> >
> > --Generate Air Miles EFT Files
> > Delete Air_Miles_EFT_Header Where Starting_RBR_Date= @startDate and
> > Ending_RBR_Date =@endDate
> >
> > Declare @dtToday datetime
> > Declare @File_Name varchar(13)
> > Declare @Transaction_Type_Header varchar(2)
> > Declare @Originator_ID varchar(10)
> > Declare @File_Creation_Number int
> >
> > --Transaciton Detail
> > Declare @Transaction_Type_Detail varchar(2)
> > Declare @SponsorNumber varchar(2)
> > Declare @CustomerNumber varchar(4)
> > Declare @CompanyID varchar(25)
> >
> > --Header
> > Select @dtToday=getdate()
> > Select @Transaction_Type_Header='00'
> >
> > SELECT @File_Name= dbo.SystemSettingValues.SettingValue
> > FROM dbo.SystemSetting INNER JOIN
> > dbo.SystemSettingValues ON
> > dbo.SystemSetting.SettingID = dbo.SystemSettingValues.SettingID
> > WHERE (dbo.SystemSetting.SettingName =
> > 'AirMilesEFT') AND (dbo.SystemSettingValues.ValueName = 'FileName')
> > SELECT @Originator_ID= dbo.SystemSettingValues.SettingValue
> > FROM dbo.SystemSetting INNER JOIN
> > dbo.SystemSettingValues ON
> > dbo.SystemSetting.SettingID = dbo.SystemSettingValues.SettingID
> > WHERE (dbo.SystemSetting.SettingName =
> > 'AirMilesEFT') AND (dbo.SystemSettingValues.ValueName = 'OriginatorID')
> >
> > Insert into Air_Miles_EFT_Header
> > (
> > Starting_RBR_Date ,
> > Ending_RBR_Date,
> > File_Name,
> > Transaction_Type,
> > Originator_ID,
> > File_CreatetionDate
> > )
> > SELECT @startDate,
> > @endDate,
> > @File_Name,
> > @Transaction_Type_Header,
> > @Originator_ID,
> > @dtToday
> > --convert(varchar(2),day(@dtToday))+convert(varchar(2),month(@dtToday))+
> > substring(convert(varchar(4),year(@dtToday)),3,2) as File_Creation_Date
> >
> >
> > Select @File_Creation_Number=@@IDENTITY
> >
> > --Detail
> > Select @Transaction_Type_Detail='65'
> >
> > SELECT @SponsorNumber= dbo.SystemSettingValues.SettingValue
> > FROM dbo.SystemSetting INNER JOIN
> > dbo.SystemSettingValues ON
> > dbo.SystemSetting.SettingID = dbo.SystemSettingValues.SettingID
> > WHERE (dbo.SystemSetting.SettingName =
> > 'AirMilesEFT') AND (dbo.SystemSettingValues.ValueName = 'SponsorNumber')
> > SELECT @CustomerNumber= dbo.SystemSettingValues.SettingValue
> > FROM dbo.SystemSetting INNER JOIN
> > dbo.SystemSettingValues ON
> > dbo.SystemSetting.SettingID = dbo.SystemSettingValues.SettingID
> > WHERE (dbo.SystemSetting.SettingName =
> > 'AirMilesEFT') AND (dbo.SystemSettingValues.ValueName = 'CustomerNumber')
> > SELECT @CompanyID=Code from dbo.Lookup_Table
> > WHERE (dbo.Lookup_Table.Category = 'BudgetBC Company ')
> >
> > Delete Air_Miles_EFT_Detail Where (RBR_Date > @startDate) and (RBR_Date <
> > @endDate+1)
> >
> > --Temporary solution for the slow response
> >
> > Select * into #AM_Contract_TnM from dbo.AM_Contract_TnM_vw where
> > (dbo.AM_Contract_TnM_vw.Checkin_RBR_Date >= @startDate) and
> > (dbo.AM_Contract_TnM_vw.Checkin_RBR_Date < @endDate+1)
> >
> > Insert into Air_Miles_EFT_Detail
> >
> > SELECT @File_Creation_Number as File_Creation_Number,
> > Right(convert(varchar(15),(dbo.Contract.Contract_Number +10000000)),7) AS
> > Invoice_Number,
> > Contract_TnM .Business_Transaction_ID,
> > Contract_TnM .RBR_Date,
> > @CustomerNumber as Customer_Number,
> > dbo.Location.StationNumber AS Store_Number,
> > '0001' as Terminla_Number,
> > @Transaction_Type_Detail as Transaction_Type,
> > dbo.Contract.FF_Member_Number as Card_Number,
> > (Case When Contract_TnM .TnM_Amount>=0 then '00'
> > Else '30'
> > End) AS AMTM_Tran_Type,
> > (Case when dbo.Contract.FF_Swiped=1 then 'S'
> > Else 'M'
> > End) AS Entry_Mode,
> >
> > Right(CONVERT(varchar(3), DATEPART(hh, Contract_TnM
> > .Transaction_Date)+100),2) + Right(CONVERT(varchar(3), DATEPART(mi,
> > Contract_TnM .Transaction_Date)+100),2) AS Transaction_Time,
> >
> > Right(CONVERT(varchar(3), DAY(Contract_TnM .Transaction_Date)+100),2) +
> > RIGHT(CONVERT(varchar(3), MONTH(Contract_TnM .Transaction_Date)+100),2) +
> > RIGHT(CONVERT(varchar(4), YEAR(Contract_TnM .Transaction_Date)), 2)
> > AS Transaction_Date,
> > (Case when Payment_Type='Credit Card' then '3'
> > when Payment_Type='Cash' then '1'
> > Else '1'
> > End) as Payment_Type,
> > @SponsorNumber as Sponsor_Number,
> > '01' AS Base_Offer_Code,
> > Contract_TnM .TnM_Amount AS Sales_Amount,
> > CONVERT(INT, Contract_TnM .TnM_Amount / 10) AS Mile_Points,
> > 1 AS Multiply_Factor,
> > 0 AS Multipler_Miles,
> > (Case
> > When dbo.Contract.Pick_Up_On<'2007-03-16' then '9813'
> > Else '0000'
> > End) AS BONUS_OFFER_CODE,
> >
> > (Case
> > When dbo.Contract.Pick_Up_On<'2007-03-16' then 1
> > Else 0
> > End) AS Offer_Quantity,
> >
> > (Case
> > When dbo.Contract.Pick_Up_On<'2007-03-16' then CONVERT(INT,
> > Contract_TnM .TnM_Amount / 10)*2
> > Else 0
> > End) AS Bonus_Miles,
> >
> > 2 AS Offer_Type
> >
> > FROM dbo.Contract
> > INNER JOIN dbo.Frequent_Flyer_Plan
> > ON dbo.Contract.Frequent_Flyer_Plan_ID =
> > dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan_ID
> > INNER JOIN #AM_Contract_TnM Contract_TnM
> > ON dbo.Contract.Contract_Number = Contract_TnM .Contract_Number
> > INNER JOIN dbo.Location
> > ON dbo.Contract.Pick_Up_Location_ID = dbo.Location.Location_ID
> > LEFT OUTER JOIN
> > dbo.Contract_Payment_Item ON dbo.Contract.Contract_Number =
> > dbo.Contract_Payment_Item.Contract_Number and Sequence=0
> >
> > WHERE (dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan = 'Air Miles') AND
> > (Contract_TnM .Checkin_RBR_Date >= @startDate) and (Contract_TnM
> > .Checkin_RBR_Date < @endDate+1)
> > and dbo.Location.Owning_Company_ID=@CompanyID and
> > dbo.Contract.Pick_Up_On>='2007-01-08'
> > and len(dbo.Contract.FF_Member_Number )=11
> >
> > GO
> > SET QUOTED_IDENTIFIER OFF
> > GO
> > SET ANSI_NULLS ON
> > GO
> >
> > ===================================================
> > in order to find out the problem
> > I simply the procedure to the following statement:
> >
> > DECLARE @startDate datetime,
> > @endDate datetime
> >
> > SELECT @startDate = '2007-03-15'
> > @endDate = '2007-03-15'
> >
> > SELECT dbo.AM_Contract_TnM_vw.*
> > FROM dbo.Contract
> > INNER JOIN dbo.Frequent_Flyer_Plan
> > ON dbo.Contract.Frequent_Flyer_Plan_ID =
> > dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan_ID
> > INNER JOIN dbo.AM_Contract_TnM_vw
> > ON dbo.Contract.Contract_Number = dbo.AM_Contract_TnM_vw.Contract_Number
> > INNER JOIN dbo.Location
> > ON dbo.Contract.Pick_Up_Location_ID = dbo.Location.Location_ID
> > LEFT OUTER JOIN
> > dbo.Contract_Payment_Item ON dbo.Contract.Contract_Number =
> > dbo.Contract_Payment_Item.Contract_Number and Sequence=0
> >
> > WHERE (dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan = 'Air Miles') AND
> > (dbo.AM_Contract_TnM_vw.Checkin_RBR_Date >= @startDate) and
> > (dbo.AM_Contract_TnM_vw.Checkin_RBR_Date < @endDate+1)
> > and dbo.Location.Owning_Company_ID=7425 and
> > dbo.Contract.Pick_Up_On>='2007-01-08'
> > and len(dbo.Contract.FF_Member_Number )=11
> >
> > I found the above statement runs as slow as the procedure. However, I have
> > two interesting finds here:
> >
> > 1. If I replace the date range creteria with a hard coded date value
> > instead of using variable, it took only a few seconds to run, even I run it
> > repeatedly.
> >
> > SELECT dbo.AM_Contract_TnM_vw.*
> > FROM dbo.Contract
> > INNER JOIN dbo.Frequent_Flyer_Plan
> > ON dbo.Contract.Frequent_Flyer_Plan_ID =
> > dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan_ID
> > INNER JOIN dbo.AM_Contract_TnM_vw
> > ON dbo.Contract.Contract_Number = dbo.AM_Contract_TnM_vw.Contract_Number
> > INNER JOIN dbo.Location
> > ON dbo.Contract.Pick_Up_Location_ID = dbo.Location.Location_ID
> > LEFT OUTER JOIN
> > dbo.Contract_Payment_Item ON dbo.Contract.Contract_Number =
> > dbo.Contract_Payment_Item.Contract_Number and Sequence=0
> >
> > WHERE (dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan = 'Air Miles') AND
> > (dbo.AM_Contract_TnM_vw.Checkin_RBR_Date >= '2007-03-15') and
> > (dbo.AM_Contract_TnM_vw.Checkin_RBR_Date < '2007-03-16')
> > and dbo.Location.Owning_Company_ID=7425 and
> > dbo.Contract.Pick_Up_On>='2007-01-08'
> > and len(dbo.Contract.FF_Member_Number )=11
> >
> > 2. Using temp table, it runs as fast as hardcoded date
> >
> > DECLARE @startDate datetime,
> > @endDate datetime
> >
> > SELECT @startDate = '2007-03-15',
> > @endDate = '2007-03-15'
> >
> > --Generate Air Miles EFT Files
> > drop table #AM_Contract_TnM
> >
> > Select * into #AM_Contract_TnM from dbo.AM_Contract_TnM_vw where
> > (dbo.AM_Contract_TnM_vw.Checkin_RBR_Date >= @startDate) and
> > (dbo.AM_Contract_TnM_vw.Checkin_RBR_Date < @endDate+1)
> >
> >
> > SELECT Contract_TnM.*
> > FROM dbo.Contract
> > INNER JOIN dbo.Frequent_Flyer_Plan
> > ON dbo.Contract.Frequent_Flyer_Plan_ID =
> > dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan_ID
> > INNER JOIN #AM_Contract_TnM Contract_TnM
> > ON dbo.Contract.Contract_Number = Contract_TnM .Contract_Number
> > INNER JOIN dbo.Location
> > ON dbo.Contract.Pick_Up_Location_ID = dbo.Location.Location_ID
> > LEFT OUTER JOIN
> > dbo.Contract_Payment_Item ON dbo.Contract.Contract_Number =

royHe

3/20/2007 9:08:00 PM

0

Thank you all for your help.

I have more questions.

Instead using store procedure, I use T-SQL script to run:

DECLARE @startDate datetime,
@endDate datetime


SELECT @startDate = '2007-03-15',
@endDate = '2007-03-15'




SELECT dbo.AM_Contract_TnM_vw.*
FROM dbo.Contract
INNER JOIN dbo.Frequent_Flyer_Plan
ON dbo.Contract.Frequent_Flyer_Plan_ID =
dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan_ID
INNER JOIN dbo.AM_Contract_TnM_vw
ON dbo.Contract.Contract_Number = dbo.AM_Contract_TnM_vw.Contract_Number
INNER JOIN dbo.Location
ON dbo.Contract.Pick_Up_Location_ID = dbo.Location.Location_ID
LEFT OUTER JOIN
dbo.Contract_Payment_Item ON dbo.Contract.Contract_Number =
dbo.Contract_Payment_Item.Contract_Number and Sequence=0

WHERE (dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan = 'Air Miles') AND
(dbo.AM_Contract_TnM_vw.Checkin_RBR_Date >= @startDate) and
(dbo.AM_Contract_TnM_vw.Checkin_RBR_Date < @endDate+1)
and dbo.Location.Owning_Company_ID=7425 and
dbo.Contract.Pick_Up_On>='2007-01-08'
and len(dbo.Contract.FF_Member_Number )=11



This one still take me more than 1 hour to return the result

If I replace the variable with the hardcoded value like this:




SELECT dbo.AM_Contract_TnM_vw.*
FROM dbo.Contract
INNER JOIN dbo.Frequent_Flyer_Plan
ON dbo.Contract.Frequent_Flyer_Plan_ID =
dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan_ID
INNER JOIN dbo.AM_Contract_TnM_vw
ON dbo.Contract.Contract_Number = dbo.AM_Contract_TnM_vw.Contract_Number
INNER JOIN dbo.Location
ON dbo.Contract.Pick_Up_Location_ID = dbo.Location.Location_ID
LEFT OUTER JOIN
dbo.Contract_Payment_Item ON dbo.Contract.Contract_Number =
dbo.Contract_Payment_Item.Contract_Number and Sequence=0

WHERE (dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan = 'Air Miles') AND
(dbo.AM_Contract_TnM_vw.Checkin_RBR_Date >= '2007-03-15') and
(dbo.AM_Contract_TnM_vw.Checkin_RBR_Date < '2007-03-16')
and dbo.Location.Owning_Company_ID=7425 and
dbo.Contract.Pick_Up_On>='2007-01-08'
and len(dbo.Contract.FF_Member_Number )=11


It tooks less than 1 minute.

Thanks

royHe

3/20/2007 9:11:00 PM

0

Yes. autocreate statistics and auto update
statistics were on

Thanks

"Gert-Jan Strik" wrote:

> In addition to the other replies: make sure the table statistics are up
> to date. You may want to check if autocreate statistics and auto update
> statistics are on (which they are by default).
>
> If the problem persists, you could always experiment with the WITH
> RECOMPILE clause of the CREATE PROCEDURE command, or periodically run
> "sp_recompile AM_Create_EFT_File".
>
> HTH,
> Gert-Jan
>
>
> royHe wrote:
> >
> > I have a store procedure running everyday (a Job excute this store
> > procedure). Everytime after I re-boot the server, it only take less than 1
> > minute to run. Day after day, it running slower by a few seconds or more,
> > eventually it took more than an hour to run. And it slow down the whole
> > system.
> >
> > Here is the store procedures:
> >
> > =====================================================
> > if exists (select * from dbo.sysobjects where id =
> > object_id(N'[dbo].[AM_Create_EFT_File]') and OBJECTPROPERTY(id,
> > N'IsProcedure') = 1)
> > drop procedure [dbo].[AM_Create_EFT_File]
> > GO
> >
> > SET QUOTED_IDENTIFIER ON
> > GO
> > SET ANSI_NULLS ON
> > GO
> >
> > /*
> > PURPOSE:
> > REQUIRES:
> > AUTHOR: Roy He
> > DATE CREATED: Sep 14, 2006
> > CALLED BY: Schedule Job
> > MOD HISTORY:
> > Name Date Comments
> > */
> >
> > CREATE PROCEDURE AM_Create_EFT_File --'2006-12-01', '2006-12-19'
> > @paramStartDate varchar(20) = '15 April 1999',
> > @paramEndDate varchar(20) = '15 April 1999'
> > AS
> >
> > DECLARE @startDate datetime,
> > @endDate datetime
> >
> > SELECT @startDate = CONVERT(datetime, NULLIF(@paramStartDate,'')),
> > @endDate = CONVERT(datetime, NULLIF(@paramEndDate,''))
> >
> > --Generate Air Miles EFT Files
> > Delete Air_Miles_EFT_Header Where Starting_RBR_Date= @startDate and
> > Ending_RBR_Date =@endDate
> >
> > Declare @dtToday datetime
> > Declare @File_Name varchar(13)
> > Declare @Transaction_Type_Header varchar(2)
> > Declare @Originator_ID varchar(10)
> > Declare @File_Creation_Number int
> >
> > --Transaciton Detail
> > Declare @Transaction_Type_Detail varchar(2)
> > Declare @SponsorNumber varchar(2)
> > Declare @CustomerNumber varchar(4)
> > Declare @CompanyID varchar(25)
> >
> > --Header
> > Select @dtToday=getdate()
> > Select @Transaction_Type_Header='00'
> >
> > SELECT @File_Name= dbo.SystemSettingValues.SettingValue
> > FROM dbo.SystemSetting INNER JOIN
> > dbo.SystemSettingValues ON
> > dbo.SystemSetting.SettingID = dbo.SystemSettingValues.SettingID
> > WHERE (dbo.SystemSetting.SettingName =
> > 'AirMilesEFT') AND (dbo.SystemSettingValues.ValueName = 'FileName')
> > SELECT @Originator_ID= dbo.SystemSettingValues.SettingValue
> > FROM dbo.SystemSetting INNER JOIN
> > dbo.SystemSettingValues ON
> > dbo.SystemSetting.SettingID = dbo.SystemSettingValues.SettingID
> > WHERE (dbo.SystemSetting.SettingName =
> > 'AirMilesEFT') AND (dbo.SystemSettingValues.ValueName = 'OriginatorID')
> >
> > Insert into Air_Miles_EFT_Header
> > (
> > Starting_RBR_Date ,
> > Ending_RBR_Date,
> > File_Name,
> > Transaction_Type,
> > Originator_ID,
> > File_CreatetionDate
> > )
> > SELECT @startDate,
> > @endDate,
> > @File_Name,
> > @Transaction_Type_Header,
> > @Originator_ID,
> > @dtToday
> > --convert(varchar(2),day(@dtToday))+convert(varchar(2),month(@dtToday))+
> > substring(convert(varchar(4),year(@dtToday)),3,2) as File_Creation_Date
> >
> >
> > Select @File_Creation_Number=@@IDENTITY
> >
> > --Detail
> > Select @Transaction_Type_Detail='65'
> >
> > SELECT @SponsorNumber= dbo.SystemSettingValues.SettingValue
> > FROM dbo.SystemSetting INNER JOIN
> > dbo.SystemSettingValues ON
> > dbo.SystemSetting.SettingID = dbo.SystemSettingValues.SettingID
> > WHERE (dbo.SystemSetting.SettingName =
> > 'AirMilesEFT') AND (dbo.SystemSettingValues.ValueName = 'SponsorNumber')
> > SELECT @CustomerNumber= dbo.SystemSettingValues.SettingValue
> > FROM dbo.SystemSetting INNER JOIN
> > dbo.SystemSettingValues ON
> > dbo.SystemSetting.SettingID = dbo.SystemSettingValues.SettingID
> > WHERE (dbo.SystemSetting.SettingName =
> > 'AirMilesEFT') AND (dbo.SystemSettingValues.ValueName = 'CustomerNumber')
> > SELECT @CompanyID=Code from dbo.Lookup_Table
> > WHERE (dbo.Lookup_Table.Category = 'BudgetBC Company ')
> >
> > Delete Air_Miles_EFT_Detail Where (RBR_Date > @startDate) and (RBR_Date <
> > @endDate+1)
> >
> > --Temporary solution for the slow response
> >
> > Select * into #AM_Contract_TnM from dbo.AM_Contract_TnM_vw where
> > (dbo.AM_Contract_TnM_vw.Checkin_RBR_Date >= @startDate) and
> > (dbo.AM_Contract_TnM_vw.Checkin_RBR_Date < @endDate+1)
> >
> > Insert into Air_Miles_EFT_Detail
> >
> > SELECT @File_Creation_Number as File_Creation_Number,
> > Right(convert(varchar(15),(dbo.Contract.Contract_Number +10000000)),7) AS
> > Invoice_Number,
> > Contract_TnM .Business_Transaction_ID,
> > Contract_TnM .RBR_Date,
> > @CustomerNumber as Customer_Number,
> > dbo.Location.StationNumber AS Store_Number,
> > '0001' as Terminla_Number,
> > @Transaction_Type_Detail as Transaction_Type,
> > dbo.Contract.FF_Member_Number as Card_Number,
> > (Case When Contract_TnM .TnM_Amount>=0 then '00'
> > Else '30'
> > End) AS AMTM_Tran_Type,
> > (Case when dbo.Contract.FF_Swiped=1 then 'S'
> > Else 'M'
> > End) AS Entry_Mode,
> >
> > Right(CONVERT(varchar(3), DATEPART(hh, Contract_TnM
> > .Transaction_Date)+100),2) + Right(CONVERT(varchar(3), DATEPART(mi,
> > Contract_TnM .Transaction_Date)+100),2) AS Transaction_Time,
> >
> > Right(CONVERT(varchar(3), DAY(Contract_TnM .Transaction_Date)+100),2) +
> > RIGHT(CONVERT(varchar(3), MONTH(Contract_TnM .Transaction_Date)+100),2) +
> > RIGHT(CONVERT(varchar(4), YEAR(Contract_TnM .Transaction_Date)), 2)
> > AS Transaction_Date,
> > (Case when Payment_Type='Credit Card' then '3'
> > when Payment_Type='Cash' then '1'
> > Else '1'
> > End) as Payment_Type,
> > @SponsorNumber as Sponsor_Number,
> > '01' AS Base_Offer_Code,
> > Contract_TnM .TnM_Amount AS Sales_Amount,
> > CONVERT(INT, Contract_TnM .TnM_Amount / 10) AS Mile_Points,
> > 1 AS Multiply_Factor,
> > 0 AS Multipler_Miles,
> > (Case
> > When dbo.Contract.Pick_Up_On<'2007-03-16' then '9813'
> > Else '0000'
> > End) AS BONUS_OFFER_CODE,
> >
> > (Case
> > When dbo.Contract.Pick_Up_On<'2007-03-16' then 1
> > Else 0
> > End) AS Offer_Quantity,
> >
> > (Case
> > When dbo.Contract.Pick_Up_On<'2007-03-16' then CONVERT(INT,
> > Contract_TnM .TnM_Amount / 10)*2
> > Else 0
> > End) AS Bonus_Miles,
> >
> > 2 AS Offer_Type
> >
> > FROM dbo.Contract
> > INNER JOIN dbo.Frequent_Flyer_Plan
> > ON dbo.Contract.Frequent_Flyer_Plan_ID =
> > dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan_ID
> > INNER JOIN #AM_Contract_TnM Contract_TnM
> > ON dbo.Contract.Contract_Number = Contract_TnM .Contract_Number
> > INNER JOIN dbo.Location
> > ON dbo.Contract.Pick_Up_Location_ID = dbo.Location.Location_ID
> > LEFT OUTER JOIN
> > dbo.Contract_Payment_Item ON dbo.Contract.Contract_Number =
> > dbo.Contract_Payment_Item.Contract_Number and Sequence=0
> >
> > WHERE (dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan = 'Air Miles') AND
> > (Contract_TnM .Checkin_RBR_Date >= @startDate) and (Contract_TnM
> > .Checkin_RBR_Date < @endDate+1)
> > and dbo.Location.Owning_Company_ID=@CompanyID and
> > dbo.Contract.Pick_Up_On>='2007-01-08'
> > and len(dbo.Contract.FF_Member_Number )=11
> >
> > GO
> > SET QUOTED_IDENTIFIER OFF
> > GO
> > SET ANSI_NULLS ON
> > GO
> >
> > ===================================================
> > in order to find out the problem
> > I simply the procedure to the following statement:
> >
> > DECLARE @startDate datetime,
> > @endDate datetime
> >
> > SELECT @startDate = '2007-03-15'
> > @endDate = '2007-03-15'
> >
> > SELECT dbo.AM_Contract_TnM_vw.*
> > FROM dbo.Contract
> > INNER JOIN dbo.Frequent_Flyer_Plan
> > ON dbo.Contract.Frequent_Flyer_Plan_ID =
> > dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan_ID
> > INNER JOIN dbo.AM_Contract_TnM_vw
> > ON dbo.Contract.Contract_Number = dbo.AM_Contract_TnM_vw.Contract_Number
> > INNER JOIN dbo.Location
> > ON dbo.Contract.Pick_Up_Location_ID = dbo.Location.Location_ID
> > LEFT OUTER JOIN
> > dbo.Contract_Payment_Item ON dbo.Contract.Contract_Number =
> > dbo.Contract_Payment_Item.Contract_Number and Sequence=0
> >
> > WHERE (dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan = 'Air Miles') AND
> > (dbo.AM_Contract_TnM_vw.Checkin_RBR_Date >= @startDate) and
> > (dbo.AM_Contract_TnM_vw.Checkin_RBR_Date < @endDate+1)
> > and dbo.Location.Owning_Company_ID=7425 and
> > dbo.Contract.Pick_Up_On>='2007-01-08'
> > and len(dbo.Contract.FF_Member_Number )=11
> >
> > I found the above statement runs as slow as the procedure. However, I have
> > two interesting finds here:
> >
> > 1. If I replace the date range creteria with a hard coded date value
> > instead of using variable, it took only a few seconds to run, even I run it
> > repeatedly.
> >
> > SELECT dbo.AM_Contract_TnM_vw.*
> > FROM dbo.Contract
> > INNER JOIN dbo.Frequent_Flyer_Plan
> > ON dbo.Contract.Frequent_Flyer_Plan_ID =
> > dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan_ID
> > INNER JOIN dbo.AM_Contract_TnM_vw
> > ON dbo.Contract.Contract_Number = dbo.AM_Contract_TnM_vw.Contract_Number
> > INNER JOIN dbo.Location
> > ON dbo.Contract.Pick_Up_Location_ID = dbo.Location.Location_ID
> > LEFT OUTER JOIN
> > dbo.Contract_Payment_Item ON dbo.Contract.Contract_Number =
> > dbo.Contract_Payment_Item.Contract_Number and Sequence=0
> >
> > WHERE (dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan = 'Air Miles') AND
> > (dbo.AM_Contract_TnM_vw.Checkin_RBR_Date >= '2007-03-15') and
> > (dbo.AM_Contract_TnM_vw.Checkin_RBR_Date < '2007-03-16')
> > and dbo.Location.Owning_Company_ID=7425 and
> > dbo.Contract.Pick_Up_On>='2007-01-08'
> > and len(dbo.Contract.FF_Member_Number )=11
> >
> > 2. Using temp table, it runs as fast as hardcoded date
> >
> > DECLARE @startDate datetime,
> > @endDate datetime
> >
> > SELECT @startDate = '2007-03-15',
> > @endDate = '2007-03-15'
> >
> > --Generate Air Miles EFT Files
> > drop table #AM_Contract_TnM
> >
> > Select * into #AM_Contract_TnM from dbo.AM_Contract_TnM_vw where
> > (dbo.AM_Contract_TnM_vw.Checkin_RBR_Date >= @startDate) and
> > (dbo.AM_Contract_TnM_vw.Checkin_RBR_Date < @endDate+1)
> >
> >
> > SELECT Contract_TnM.*
> > FROM dbo.Contract
> > INNER JOIN dbo.Frequent_Flyer_Plan
> > ON dbo.Contract.Frequent_Flyer_Plan_ID =
> > dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan_ID
> > INNER JOIN #AM_Contract_TnM Contract_TnM
> > ON dbo.Contract.Contract_Number = Contract_TnM .Contract_Number
> > INNER JOIN dbo.Location
> > ON dbo.Contract.Pick_Up_Location_ID = dbo.Location.Location_ID
> > LEFT OUTER JOIN
> > dbo.Contract_Payment_Item ON dbo.Contract.Contract_Number =

Tibor Karaszi

3/20/2007 9:28:00 PM

0

That is why I suggested you Google and read up on Parameter sniffing. A parameter is very different
from a constant which in turn is very different from a variable:

Constant: The optimizer know about the value and can estimate selectivity based on that.

Variable: The optimizer has no idea of the value and have to guess ("lets see, an open interval,
where I don't know the value, I'll assume... say 25 % of the rows")

Parameter: The optimizer sniffs the value from what you pass when the plan is created, and that plan
is used for subsequent executions of the proc (even though the value passed in for the first
execution might be way off for the subsequent executions, after say a day or two).

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/d...
http://sqlblog.com/blogs/tib...


"royHe" <royHe@discussions.microsoft.com> wrote in message
news:9914C4B7-C041-4DE4-8D02-6E354D22799F@microsoft.com...
> Thank you all for your help.
>
> I have more questions.
>
> Instead using store procedure, I use T-SQL script to run:
>
> DECLARE @startDate datetime,
> @endDate datetime
>
>
> SELECT @startDate = '2007-03-15',
> @endDate = '2007-03-15'
>
>
>
>
> SELECT dbo.AM_Contract_TnM_vw.*
> FROM dbo.Contract
> INNER JOIN dbo.Frequent_Flyer_Plan
> ON dbo.Contract.Frequent_Flyer_Plan_ID =
> dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan_ID
> INNER JOIN dbo.AM_Contract_TnM_vw
> ON dbo.Contract.Contract_Number = dbo.AM_Contract_TnM_vw.Contract_Number
> INNER JOIN dbo.Location
> ON dbo.Contract.Pick_Up_Location_ID = dbo.Location.Location_ID
> LEFT OUTER JOIN
> dbo.Contract_Payment_Item ON dbo.Contract.Contract_Number =
> dbo.Contract_Payment_Item.Contract_Number and Sequence=0
>
> WHERE (dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan = 'Air Miles') AND
> (dbo.AM_Contract_TnM_vw.Checkin_RBR_Date >= @startDate) and
> (dbo.AM_Contract_TnM_vw.Checkin_RBR_Date < @endDate+1)
> and dbo.Location.Owning_Company_ID=7425 and
> dbo.Contract.Pick_Up_On>='2007-01-08'
> and len(dbo.Contract.FF_Member_Number )=11
>
>
>
> This one still take me more than 1 hour to return the result
>
> If I replace the variable with the hardcoded value like this:
>
>
>
>
> SELECT dbo.AM_Contract_TnM_vw.*
> FROM dbo.Contract
> INNER JOIN dbo.Frequent_Flyer_Plan
> ON dbo.Contract.Frequent_Flyer_Plan_ID =
> dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan_ID
> INNER JOIN dbo.AM_Contract_TnM_vw
> ON dbo.Contract.Contract_Number = dbo.AM_Contract_TnM_vw.Contract_Number
> INNER JOIN dbo.Location
> ON dbo.Contract.Pick_Up_Location_ID = dbo.Location.Location_ID
> LEFT OUTER JOIN
> dbo.Contract_Payment_Item ON dbo.Contract.Contract_Number =
> dbo.Contract_Payment_Item.Contract_Number and Sequence=0
>
> WHERE (dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan = 'Air Miles') AND
> (dbo.AM_Contract_TnM_vw.Checkin_RBR_Date >= '2007-03-15') and
> (dbo.AM_Contract_TnM_vw.Checkin_RBR_Date < '2007-03-16')
> and dbo.Location.Owning_Company_ID=7425 and
> dbo.Contract.Pick_Up_On>='2007-01-08'
> and len(dbo.Contract.FF_Member_Number )=11
>
>
> It tooks less than 1 minute.
>
> Thanks
>
>

Alejandro Mesa

3/20/2007 9:31:00 PM

0

royHe,

You are mimicking what you were doing in the sp. Do not use variables in the
"where" clause expressions. If you want to simulate the what could happend if
you use parameters in the expression, use sp_executesql.


AMB


"royHe" wrote:

> Thank you all for your help.
>
> I have more questions.
>
> Instead using store procedure, I use T-SQL script to run:
>
> DECLARE @startDate datetime,
> @endDate datetime
>
>
> SELECT @startDate = '2007-03-15',
> @endDate = '2007-03-15'
>
>
>
>
> SELECT dbo.AM_Contract_TnM_vw.*
> FROM dbo.Contract
> INNER JOIN dbo.Frequent_Flyer_Plan
> ON dbo.Contract.Frequent_Flyer_Plan_ID =
> dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan_ID
> INNER JOIN dbo.AM_Contract_TnM_vw
> ON dbo.Contract.Contract_Number = dbo.AM_Contract_TnM_vw.Contract_Number
> INNER JOIN dbo.Location
> ON dbo.Contract.Pick_Up_Location_ID = dbo.Location.Location_ID
> LEFT OUTER JOIN
> dbo.Contract_Payment_Item ON dbo.Contract.Contract_Number =
> dbo.Contract_Payment_Item.Contract_Number and Sequence=0
>
> WHERE (dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan = 'Air Miles') AND
> (dbo.AM_Contract_TnM_vw.Checkin_RBR_Date >= @startDate) and
> (dbo.AM_Contract_TnM_vw.Checkin_RBR_Date < @endDate+1)
> and dbo.Location.Owning_Company_ID=7425 and
> dbo.Contract.Pick_Up_On>='2007-01-08'
> and len(dbo.Contract.FF_Member_Number )=11
>
>
>
> This one still take me more than 1 hour to return the result
>
> If I replace the variable with the hardcoded value like this:
>
>
>
>
> SELECT dbo.AM_Contract_TnM_vw.*
> FROM dbo.Contract
> INNER JOIN dbo.Frequent_Flyer_Plan
> ON dbo.Contract.Frequent_Flyer_Plan_ID =
> dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan_ID
> INNER JOIN dbo.AM_Contract_TnM_vw
> ON dbo.Contract.Contract_Number = dbo.AM_Contract_TnM_vw.Contract_Number
> INNER JOIN dbo.Location
> ON dbo.Contract.Pick_Up_Location_ID = dbo.Location.Location_ID
> LEFT OUTER JOIN
> dbo.Contract_Payment_Item ON dbo.Contract.Contract_Number =
> dbo.Contract_Payment_Item.Contract_Number and Sequence=0
>
> WHERE (dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan = 'Air Miles') AND
> (dbo.AM_Contract_TnM_vw.Checkin_RBR_Date >= '2007-03-15') and
> (dbo.AM_Contract_TnM_vw.Checkin_RBR_Date < '2007-03-16')
> and dbo.Location.Owning_Company_ID=7425 and
> dbo.Contract.Pick_Up_On>='2007-01-08'
> and len(dbo.Contract.FF_Member_Number )=11
>
>
> It tooks less than 1 minute.
>
> Thanks
>
>

royHe

3/20/2007 10:04:00 PM

0

Thank you very much for your help.

After I removed the variables in where clause, the store procedure runs as
fast as the hard code ones.



"Alejandro Mesa" wrote:

> royHe,
>
> You are mimicking what you were doing in the sp. Do not use variables in the
> "where" clause expressions. If you want to simulate the what could happend if
> you use parameters in the expression, use sp_executesql.
>
>
> AMB
>
>
> "royHe" wrote:
>
> > Thank you all for your help.
> >
> > I have more questions.
> >
> > Instead using store procedure, I use T-SQL script to run:
> >
> > DECLARE @startDate datetime,
> > @endDate datetime
> >
> >
> > SELECT @startDate = '2007-03-15',
> > @endDate = '2007-03-15'
> >
> >
> >
> >
> > SELECT dbo.AM_Contract_TnM_vw.*
> > FROM dbo.Contract
> > INNER JOIN dbo.Frequent_Flyer_Plan
> > ON dbo.Contract.Frequent_Flyer_Plan_ID =
> > dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan_ID
> > INNER JOIN dbo.AM_Contract_TnM_vw
> > ON dbo.Contract.Contract_Number = dbo.AM_Contract_TnM_vw.Contract_Number
> > INNER JOIN dbo.Location
> > ON dbo.Contract.Pick_Up_Location_ID = dbo.Location.Location_ID
> > LEFT OUTER JOIN
> > dbo.Contract_Payment_Item ON dbo.Contract.Contract_Number =
> > dbo.Contract_Payment_Item.Contract_Number and Sequence=0
> >
> > WHERE (dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan = 'Air Miles') AND
> > (dbo.AM_Contract_TnM_vw.Checkin_RBR_Date >= @startDate) and
> > (dbo.AM_Contract_TnM_vw.Checkin_RBR_Date < @endDate+1)
> > and dbo.Location.Owning_Company_ID=7425 and
> > dbo.Contract.Pick_Up_On>='2007-01-08'
> > and len(dbo.Contract.FF_Member_Number )=11
> >
> >
> >
> > This one still take me more than 1 hour to return the result
> >
> > If I replace the variable with the hardcoded value like this:
> >
> >
> >
> >
> > SELECT dbo.AM_Contract_TnM_vw.*
> > FROM dbo.Contract
> > INNER JOIN dbo.Frequent_Flyer_Plan
> > ON dbo.Contract.Frequent_Flyer_Plan_ID =
> > dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan_ID
> > INNER JOIN dbo.AM_Contract_TnM_vw
> > ON dbo.Contract.Contract_Number = dbo.AM_Contract_TnM_vw.Contract_Number
> > INNER JOIN dbo.Location
> > ON dbo.Contract.Pick_Up_Location_ID = dbo.Location.Location_ID
> > LEFT OUTER JOIN
> > dbo.Contract_Payment_Item ON dbo.Contract.Contract_Number =
> > dbo.Contract_Payment_Item.Contract_Number and Sequence=0
> >
> > WHERE (dbo.Frequent_Flyer_Plan.Frequent_Flyer_Plan = 'Air Miles') AND
> > (dbo.AM_Contract_TnM_vw.Checkin_RBR_Date >= '2007-03-15') and
> > (dbo.AM_Contract_TnM_vw.Checkin_RBR_Date < '2007-03-16')
> > and dbo.Location.Owning_Company_ID=7425 and
> > dbo.Contract.Pick_Up_On>='2007-01-08'
> > and len(dbo.Contract.FF_Member_Number )=11
> >
> >
> > It tooks less than 1 minute.
> >
> > Thanks
> >
> >