March 2, 2010 at 10:44 am
Create Table #Daterange
(
start_d datetime,
end_d datetime,
)
begin
Insert into #Daterange
Values
('02/12/2003','12/03/2009')
end
select distinct a.empnumber, filldate,empcode
into #tmp_calc
from Vwrevenue as a inner join #revdates as b on a.empnumber = b.empnumber, #daterange
where filldate between start_d and end_d
Above query takes 15min to run but when i replace the query with this
from Vwrevenue as a inner join #revdates as b on a.empnumber = b.empnumber, #daterange
where filldate between '02/12/2003' and '12/03/2009'
it executes in 5 sec. any reason why ?
Also i found that if i dont use hard coded dates in the query , the table #revdates scans huge number of records.
March 2, 2010 at 10:55 am
Tara-1044200 (3/2/2010)
Create Table #Daterange
(
start_d datetime,
end_d datetime,
)
begin
Insert into #Daterange
Values
('02/12/2003','12/03/2009')
end
select distinct a.empnumber, filldate,empcode
into #tmp_calc
from Vwrevenue as a inner join #revdates as b on a.empnumber = b.empnumber, #daterange
where filldate between start_d and end_d
Above query takes 15min to run but when i replace the query with this
from Vwrevenue as a inner join #revdates as b on a.empnumber = b.empnumber, #daterange
where filldate between '02/12/2003' and '12/03/2009'
it executes in 5 sec. any reason why ?
Also i found that if i dont use hard coded dates in the query , the table #revdates scans huge number of records.
Care to provide us with table defs? Also, which table do the non-aliased columns come from? Really alias all columns in multi-table joins, you never know when a column with the same name may be added to a table and break a query.
March 2, 2010 at 11:43 am
...from Vwrevenue as a inner join #revdates as b on a.empnumber = b.empnumber, #daterange
i think because you inner join one table, and then use a comma to join the old ansi style, #daterange becomes a cross join agaisnt the two tables;
you need to actually join #daterange instead.
select distinct a.empnumber, filldate,empcode
into #tmp_calc
from Vwrevenue as a
inner join #revdates as b on a.empnumber = b.empnumber
inner join #daterange
on a.filldate >= #revdates.start_d
AND a.filldate <= #revdates.end_d
where filldate between start_d and end_d
Lowell
March 5, 2010 at 7:38 am
from the execution plan is see that #revdates has the following
actual number of rows = 3426054035
estimated rows = 643743
when i run the same qry on other similar databases i find both actual and estimated numbers as same but why only these database. where should i look into the database?
March 5, 2010 at 8:18 am
Here is my finding can some shedsome light on it. If i replace the below qry with code in * it run only in 2 sec comapred to 16min. any reason
select distinct a.empnumber, filldate,empcode
into #tmp_calc
from Vwrevenue as a
inner join #revdates as b on a.empnumber = b.empnumber
inner join #daterange
on a.filldate >= #revdates.start_d
AND a.filldate <= #revdates.end_d
/*********
on a.filldate >= '10/1/2004'
AND a.filldate <= ''10/1/2005''
*********/
where filldate between start_d and end_d
My question is why only putting exact dates gives better performance than pulling date values from temp table.
March 5, 2010 at 8:34 am
is there a way to increase performance withour using temp tables ?
March 9, 2010 at 12:49 pm
Would you mind posting actual execution plans for both cases? There is a chance that query optimizer doesn't have enough information (statistics) about the values of dates in the temp table and takes overcautious approach. Try to create index on the start_d and end_d columns or at least manual statistics.
Let us know the results.
Regards
Piotr
...and your only reply is slàinte mhath
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply