October 9, 2014 at 8:27 pm
Hi Team,
I have very funny problem, I have the data batch date wise, now my current batch date is 06/10/2014 if I rum this batch date query it will take 6 sec time, and the same time I have previous batch date 22/09/2014 if I run this batch date data it will take > 1 sec.
Now current batch date is 06/10/2014 hear less performance in this batch date.
But last month current batch date is 22/09/2014 , that time it was given less performance this previous batch date 08/09/2014 will give good performance.
The below query is taken too long time:
select (select count(Identity_Key) from dbo.SPICE_tblPremiumoverirde tblp
inner join (Select ts1.Orgcode,ts1.Effectivedate from SPICE_tblOrgheriarchystructure ts1
inner join (SELECT Orgcode , max(Effectivedate)as Effectivedate FROM SPICE_tblOrgheriarchystructure
where Effectivedate<= convert(date,'06/10/2014',103) group by Orgcode) as ts2
on ts1.Orgcode=ts2.Orgcode and ts1.Effectivedate=ts2.Effectivedate
where (ts1.ReporttoOrgcode in (72551)
)) AS stl ON tblp.StaffUID = stl.Orgcode
where tblp.Batchdate = Convert(date,'06/10/2014',103) )-
(SELECT COUNT(als.Identity_Key) FROM SPICE_tblallocationstatus AS als
INNER JOIN SPICE_tblallocation AS al ON als.allocation_key = al.allocation_key
INNER JOIN dbo.SPICE_tblPremiumoverirde tblp ON als.Identity_Key = tblp.Identity_Key
inner join (Select ts1.Orgcode,ts1.Effectivedate from SPICE_tblOrgheriarchystructure ts1
inner join
(SELECT Orgcode , max(Effectivedate)as Effectivedate FROM SPICE_tblOrgheriarchystructure
where Effectivedate<= convert(date,'06/10/2014',103) group by Orgcode) as ts2
on ts1.Orgcode=ts2.Orgcode and ts1.Effectivedate=ts2.Effectivedate
where (ts1.ReporttoOrgcode in (72551)
)) AS stl
ON tblp.StaffUID = stl.Orgcode WHERE (al.Report_ID = 2)
AND (tblp.Batchdate = Convert(date,'06/10/2014',103)) ) Counttrans
-- but this below query was taken less time:
select (select count(Identity_Key) from dbo.SPICE_tblPremiumoverirde tblp
inner join (Select ts1.Orgcode,ts1.Effectivedate from SPICE_tblOrgheriarchystructure ts1
inner join (SELECT Orgcode , max(Effectivedate)as Effectivedate FROM SPICE_tblOrgheriarchystructure
where Effectivedate<= convert(date,'22/09/2014',103) group by Orgcode) as ts2
on ts1.Orgcode=ts2.Orgcode and ts1.Effectivedate=ts2.Effectivedate
where (ts1.ReporttoOrgcode in (72551)
)) AS stl ON tblp.StaffUID = stl.Orgcode
where tblp.Batchdate = Convert(date,'22/09/2014',103) )-
(SELECT COUNT(als.Identity_Key) FROM SPICE_tblallocationstatus AS als
INNER JOIN SPICE_tblallocation AS al ON als.allocation_key = al.allocation_key
INNER JOIN dbo.SPICE_tblPremiumoverirde tblp ON als.Identity_Key = tblp.Identity_Key
inner join (Select ts1.Orgcode,ts1.Effectivedate from SPICE_tblOrgheriarchystructure ts1
inner join
(SELECT Orgcode , max(Effectivedate)as Effectivedate FROM SPICE_tblOrgheriarchystructure
where Effectivedate<= convert(date,'22/09/2014',103) group by Orgcode) as ts2
on ts1.Orgcode=ts2.Orgcode and ts1.Effectivedate=ts2.Effectivedate
where (ts1.ReporttoOrgcode in (72551)
)) AS stl
ON tblp.StaffUID = stl.Orgcode WHERE (al.Report_ID = 2)
AND (tblp.Batchdate = Convert(date,'22/09/2014',103)) ) Counttrans
January 20, 2015 at 11:01 pm
Have you looked at the execution plans? Are they identical?
January 20, 2015 at 11:29 pm
have you verified the amount of data for each date your are comparing these result?
Lets Say Date1 01-Feb-2014 have 120,000 rows
and Date1 01-Jan-2014 have 12,000 rows
something like this, furthermore, have you check the actual query plan for the queries you have shared?
January 21, 2015 at 2:11 am
The execution plans won't be identical because the queries aren't identical.
Use a variable for your date filter and you can use the exact same query (and hence exact same execution plan) for both queries:
DECLARE @Effectivedate DATE
SET @Effectivedate = convert(date,'06/10/2014',103)
select (
select count(Identity_Key)
from dbo.SPICE_tblPremiumoverirde tblp
inner join ( -- stl
Select ts1.Orgcode,ts1.Effectivedate
from SPICE_tblOrgheriarchystructure ts1
inner join ( -- ts2
SELECT Orgcode , max(Effectivedate) as Effectivedate
FROM SPICE_tblOrgheriarchystructure
where Effectivedate<= @Effectivedate
group by Orgcode
) as ts2
on ts1.Orgcode=ts2.Orgcode
and ts1.Effectivedate=ts2.Effectivedate
where (ts1.ReporttoOrgcode in (72551))
) AS stl
ON tblp.StaffUID = stl.Orgcode
where tblp.Batchdate = @Effectivedate
)-(
SELECT COUNT(als.Identity_Key)
FROM SPICE_tblallocationstatus AS als
INNER JOIN SPICE_tblallocation AS al
ON als.allocation_key = al.allocation_key
INNER JOIN dbo.SPICE_tblPremiumoverirde tblp
ON als.Identity_Key = tblp.Identity_Key
inner join ( -- stl
Select ts1.Orgcode,ts1.Effectivedate
from SPICE_tblOrgheriarchystructure ts1
inner join ( -- ts2
SELECT Orgcode , max(Effectivedate) as Effectivedate
FROM SPICE_tblOrgheriarchystructure
where Effectivedate <= @Effectivedate
group by Orgcode
) as ts2
on ts1.Orgcode=ts2.Orgcode
and ts1.Effectivedate=ts2.Effectivedate
where (ts1.ReporttoOrgcode in (72551) )
) AS stl
ON tblp.StaffUID = stl.Orgcode
WHERE (al.Report_ID = 2)
AND (tblp.Batchdate = @Effectivedate)
) Counttrans
Eight tables are read by the query (even if some of them are the same table read more than once) which will put you somewhere around the tipping point for an optimiser timeout. Check if this is the case as it could easily cause the two queries to run in substantially different times. If it is, then simplify the query - the repeated section would make this easy, simply resolve that section out into a temp table, like this:
SELECT ts1.Orgcode --, ts1.Effectivedate
INTO #stl
FROM SPICE_tblOrgheriarchystructure ts1
INNER JOIN ( -- ts2
SELECT Orgcode, max(Effectivedate) as Effectivedate
FROM SPICE_tblOrgheriarchystructure
WHERE Effectivedate <= @Effectivedate
GROUP BY Orgcode
) AS ts2
ON ts1.Orgcode = ts2.Orgcode
AND ts1.Effectivedate = ts2.Effectivedate
WHERE ts1.ReporttoOrgcode in (72551)
select (
select count(Identity_Key)
from dbo.SPICE_tblPremiumoverirde tblp
inner join #st1 AS stl
ON tblp.StaffUID = stl.Orgcode
where tblp.Batchdate = @Effectivedate
)-(
SELECT COUNT(als.Identity_Key)
FROM SPICE_tblallocationstatus AS als
INNER JOIN SPICE_tblallocation AS al
ON als.allocation_key = al.allocation_key
INNER JOIN dbo.SPICE_tblPremiumoverirde tblp
ON als.Identity_Key = tblp.Identity_Key
INNER JOIN #st1 AS stl
ON tblp.StaffUID = stl.Orgcode
WHERE tblp.Batchdate = @Effectivedate
AND al.Report_ID = 2
) Counttrans
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply