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  * from data_values v
where date_time>(select dateadd(minute, -2, max(date_time)) from data_values);

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 );

quote:

---

Originally posted by saad.ahnad@gmail.com

select *
from
(
select dateadd(minute, -2, max(date_time)) max_date from data_values
) max_date,
inner join data_values v on (v.date_time > max_date.max_date)
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 )

And have an index on date_time

--
Saad Ahmad
saad.ahmad@gmail.com

---

quote:

---

Originally posted by Kristen

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);

1) Don't use SELECT *, list out the columns
2) Always prefix all table names with "dbo." (or the owner, if different)
3) Make sure you have indexes on all Joined columns:
data_values.date_time
data_values.sensor_id
sensors.sensor_id
sensors.data_logger_id
data_loggers.data_logger_id

Personally I would try selecting the "2 minute" limit into a working variable before you start:

DECLARE @StartTime datetime
SELECT @StartTime = dateadd(minute, -2, max(date_time))
FROM dbo.data_values WITH (NOLOCK) -- Data integrity not critical here
...
WHERE date_time > @StartTime

Note that you should time the initial run, but also the average of 5 or more subsequent runs as the likelihood is that the query plan will be cached.

Kristen

---

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());

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());

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());

    declare @lastdate datetime; 
    set @lastdate=(select  dateadd(minute , -5, max(date_time)) from 
    data_values);
    select ... where date_time > @lastdate; 

quote:

---

Originally posted by Kristen

"Writing out all the columns instead of doing select * does not change the result"

Well it will, but I would not expect it to be very significant.

Listing all the columns saves SQL Server having to:

1) Work out what they are
2) Check if any new ones have been added since the previous query was Cached.
3) Prevents you sending new columns to your application which it never needed and doesn't now

Consider: You have a Customer Record. You retrieve it using SELECT *. In the future you add an Image of the customer, and a complete history of all the File Notes in a TEXT datatype column. previously your "Get Customer" routine was getting around 200 bytes per customer. Now its getting around 100K. BIG performance difference!

What was the impact of the other suggestions I made?

Kristen

---

quote:

---

Originally posted by Kristen

"but in my case the difference was neglectible."

Yes, but that's not the only point as I tried to explain.

What was the impact of the other suggestions I made?

---

quote:

---

Originally posted by Kristen

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);

3) Make sure you have indexes on all Joined columns:
data_values.date_time
data_values.sensor_id
sensors.sensor_id
sensors.data_logger_id
data_loggers.data_logger_id

---

quote:

---

I actually proposed that you used WITH (NOLOCK) but that doesn't show in code sample you posted above, and I also would not have used the syntax:

---