SQL server joining before WHERE-clause is executed

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL server joining before WHERE-clause is executed

Author

Topic

jonasfh
Starting Member

9 Posts

Posted - 2007-09-10 : 06:18:30

Hi,

The code below takes approx 1.4 sec to execute, while the same result (further down) obtained using transact-sql to force the where clause to be executed first, takes approx 10 millisec. I run the query several times so it is not the query cache that makes the difference. There are indexes on all joined columns and on the date_time -column. I have also tried having the where-clause as a sub-query (also shown below). Writing out all the columns instead of doing "select * ..." does not change the result:


declare @d datetime;
set @d =(select getdate());
select * from data_values v
inner join sensors s on (v.sensor_id=s.sensor_id)
inner join data_loggers l on (l.data_logger_id=s.data_logger_id )
where date_time>(select dateadd(minute, -2, max(date_time)) from data_values);
select datediff(millisecond,@d, getdate());

As subquery:


declare @d datetime;
set @d =(select getdate());
select  * from (select * from data_values
where date_time>(select dateadd(minute, -2, max(date_time)) from data_values)) v
inner join sensors s on (v.sensor_id=s.sensor_id)
inner join data_loggers l on (l.data_logger_id=s.data_logger_id );
select datediff(millisecond,@d, getdate());

However, by forcing the join to be executed only on the result returned by the where-clause, by first executing this as a separate statement, the execution time drops from 1.4 seconds to approx 10 milliseconds on the first execution, and to less that 1 millisecond after that. That is maybe a 10^4 times speed increase by this modification. Seems SQL-server is doing something seriously wrong here?


declare @d datetime;
set @d =(select getdate());
declare @s as table(data_value_id int, data_type_id int, sensor_id int, quality_id int, date_time datetime, lat_wgs84 float, lon_wgs84 float, height int, parallell int, data_value float);

insert into @s select  * from data_values v where date_time>(select dateadd(minute, -2, max(date_time)) from data_values);

select  * from @s v inner join sensors s on (v.sensor_id=s.sensor_id) inner join data_loggers l on (l.data_logger_id=s.data_logger_id );

select datediff(millisecond,@d, getdate());

Any way to force this behaviour without using the no-standard transact-SQL syntax that I'm doing now?

Regards Jonas:))

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-10 : 06:22:58

Same question AGAIN!
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89052

E 12°55'05.25"
N 56°04'39.16"