March 9, 2009 at 11:33 am
Hi Guys,
When I am passing a value like following it is taking 1 min and 10 sec to execute the query ....
Declare @startdate datetime
Declare @enddate datetime
--converted to varchar by Rob because the time was being passed
--to the procedure causing errors
select @Startdate = convert(varchar(15),getdate()-1,101)
select @enddate = convert(varchar(15),getdate(),101)
Declare @IPCC_SkillGroupCount int
Declare @WRAP_SkillGroupCount int
select @WRAP_SkillGroupCount = Count(*)
from Table1
WHERE DateTime >= @StartDate and DateTime < @EndDate
select @IPCC_SkillGroupCount = Count(*)
from Table2
WHERE DateTime >= @StartDate and DateTime < @EndDate
Print '@IPCC_SkillGroupCount = ' + Cast(@IPCC_SkillGroupCount as varchar) +
'@WRAP_SkillGroupCount = ' + Cast(@WRAP_SkillGroupCount as varchar)
---------
But when I am passing following way it is very fast.. it take only 1 sec. to exeute ...
select @WRAP_SkillGroupCount = Count(*)
from Table1
where DateTime >= '03/08/2009' and DateTime < '03/09/2009'
select @IPCC_SkillGroupCount = Count(*)
from Table2
where DateTime >= '03/08/2009' and DateTime < '03/09/2009'
Print '@IPCC_SkillGroupCount = ' + Cast(@IPCC_SkillGroupCount as varchar) +
'@WRAP_SkillGroupCount = ' + Cast(@WRAP_SkillGroupCount as varchar)
Can anybody please help me why this is happening ?
March 9, 2009 at 11:36 am
Is the actual code being run inside a stored procedure? If so you are probably suffering from parameter sniffing. Please post the code for the stored procedure.
March 9, 2009 at 11:38 am
It's a common thing. It's because, with the hard-coded dates, the execution plan can take into account statistics, etc., that exist on that column. With variable dates, it has to plan on a potentially bigger range and thus can end up with a less optimum execution plan.
Is the end date always one full day after the start date? If so, using @StartDate + 1 instead of @EndDate might help.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 9, 2009 at 11:39 am
Here is my store Procedure
CREATE PROCEDURE sp_TransferData_Compare
(
@StartDate varchar(10),
@EndDate varchar(10)
)
AS
--
--Declare @startdate datetime
--Declare @enddate datetime
--
----converted to varchar by Rob because the time was being passed
----to the procedure causing errors
--select @Startdate = convert(varchar(15),getdate()-1,101)
--select @enddate = convert(varchar(15),getdate(),101)
Declare @IPCC_SkillGroupCount int
Declare @WRAP_SkillGroupCount int
select @WRAP_SkillGroupCount = Count(*)
from Table1
--where DateTime >= '03/08/2009' and DateTime < '03/09/2009'
WHERE DateTime >= @StartDate and DateTime < @EndDate
select @IPCC_SkillGroupCount = Count(*)
from Table2
--where DateTime >= '03/08/2009' and DateTime < '03/09/2009'
WHERE DateTime >= @StartDate and DateTime < @EndDate
Print '@IPCC_SkillGroupCount = ' + Cast(@IPCC_SkillGroupCount as varchar) +
'@WRAP_SkillGroupCount = ' + Cast(@WRAP_SkillGroupCount as varchar)
-----
Declare @IPCC_CallTypeCount int
Declare @WRAP_CallTypeCount int
select @WRAP_CallTypeCount = Count(distinct CAllTypeID) --distinct CAllTypeID
from Table3
--where DateTime >= '03/08/2009' and DateTime < '03/09/2009'
WHERE DateTime >= @StartDate and DateTime < @EndDate
select @IPCC_CallTypeCount = Count(distinct CAllTypeID) --distinct CAllTypeID -- *
from Table4
--where DateTime >= '03/08/2009' and DateTime < '03/09/2009'
WHERE DateTime >= @StartDate and DateTime < @EndDate
and DateTime>=DateAdd(hh,1,convert(varchar(20),DateTime,101))
and DateTime<DateAdd(hh,24,convert(varchar(20),DateTime,101))
Print '@IPCC_CallTypeCount = ' + Cast(@IPCC_CallTypeCount as varchar) +
'@WRAP_CallTypeCount = ' + Cast(@WRAP_CallTypeCount as varchar)
-----
go
March 9, 2009 at 11:52 am
After a couple of minor changes to your code:
CREATE PROCEDURE sp_TransferData_Compare
(
@StartDate varchar(10),
@EndDate varchar(10)
)
AS
BEGIN
declare @pStartDate datetime,
@pEndDate datetime
set @pStartDate = cast(@StartDate as datetime)
set @pEndDate = cast(@EndDate as datetime)
Declare @IPCC_SkillGroupCount int
Declare @WRAP_SkillGroupCount int
select @WRAP_SkillGroupCount = Count(*)
from Table1
--where DateTime >= '03/08/2009' and DateTime < '03/09/2009'
WHERE [DateTime] >= @pStartDate and [DateTime] < @pEndDate
select @IPCC_SkillGroupCount = Count(*)
from Table2
--where DateTime >= '03/08/2009' and DateTime < '03/09/2009'
WHERE [DateTime] >= @pStartDate and [DateTime] < @pEndDate
Print '@IPCC_SkillGroupCount = ' + Cast(@IPCC_SkillGroupCount as varchar) +
' @WRAP_SkillGroupCount = ' + Cast(@WRAP_SkillGroupCount as varchar)
-----
Declare @IPCC_CallTypeCount int
Declare @WRAP_CallTypeCount int
select @WRAP_CallTypeCount = Count(distinct CAllTypeID) --distinct CAllTypeID
from Table3
--where DateTime >= '03/08/2009' and DateTime < '03/09/2009'
WHERE [DateTime] >= @pStartDate and [DateTime] < @pEndDate
select @IPCC_CallTypeCount = Count(distinct CAllTypeID) --distinct CAllTypeID -- *
from Table4
--where DateTime >= '03/08/2009' and DateTime < '03/09/2009'
WHERE
[DateTime] >= @pStartDate and [DateTime] < @pEndDate
and [DateTime] >= DateAdd(hh,1,convert(varchar(20),[DateTime],101))
and [DateTime] < DateAdd(hh,24,convert(varchar(20),[DateTime],101))
Print '@IPCC_CallTypeCount = ' + Cast(@IPCC_CallTypeCount as varchar) +
' @WRAP_CallTypeCount = ' + Cast(@WRAP_CallTypeCount as varchar)
-----
END
go
Also, can you explain the WHERE clause in your last SELECT statement?
March 9, 2009 at 12:00 pm
Hi
Thank you for sending me the store procedure ... But the result is same .. no luck ...
March 9, 2009 at 12:06 pm
Can you capture the execution plan, zip it, and post to this thread? there may be more going on than just changing to a hard coded set of values.
Also, It may help if you post the table DDL (including currently defined indexes), sample data (in the form of insert statements that can be cut and past to SSMS and run without modification), and the expected results based on the sample data provided.
If you need help with some of the above, please read the first article that is referenced below in my signature block regarding asking for help.
March 9, 2009 at 4:59 pm
Try always set a clustered index on the column(s) used in range selections.
In your case its [DateTime].
Clustered index on that column will resolve the problem.
_____________
Code for TallyGenerator
March 10, 2009 at 12:15 am
As sergiy told, create clustered index on DATE column. It may help and reduce the execution time.
if you post the table structure with some sample data, you will get some good replies.
karthik
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply