October 13, 2004 at 7:48 am
I am after some opinions on the optimum way to write a particular type of query. I have a table of a hundred thousand rows, which holds for arguements sake an identity column (ID) with a clustered index on, a DateTime column (TimedAt) which has a non clustered index on it, and several other columns. I want to write a query to return a single row, which is the all the details in the newest row inserted into the table. I also want to append this single row with 3 extra columns of aggregated data from the last 10 minutes. I have achieved the result I require by doing the following:
Select * ,
AverageOne=(Select avg(One) From Table1 whereTimedAt>DateAdd(second,-600,GetDate())),
AverageTwo=(Select avg(Two) From Table1 where TimedAt>DateAdd(second,-600,GetDate())),
AverageThree=(Select avg(Three) From Table1 where TimedAt>DateAdd(second,-600,GetDate()))
From Table1 where ID= (select max(ID) from Table1)
I wondered in what other ways, (specifically regarding the subselects) I could achieve this. This piece of TSQL has been bugging me today, yet I havent been able to find a smarter way of doing it. Ideally I would like to avoid using temporary tables.
Any opions would be appreciated.
October 13, 2004 at 8:34 am
Instead of using subquery to get max(ID), try using "SELECT TOP 1 ... ORDER BY ID descending".
Instead of using 3 subqueries to get the avg(x), try using derived table that gets all 3 averages in one shot.
CREATE TABLE Table1 (ID int identity,
TimedAt datetime default getdate(),
One int, Two int, Three int)
INSERT Table1 (One, Two, Three) VALUES (1,2,3)
INSERT Table1 (One, Two, Three) VALUES (1,2,3)
INSERT Table1 (One, Two, Three) VALUES (1,2,3)
SELECT TOP 1
* ,
AverageOne,
AverageTwo,
AverageThree
FROM Table1
cross join (
SELECT
AverageOne = avg(One),
AverageTwo = avg(Two),
AverageThree = avg(Three)
FROM Table1
WHERE TimedAt>DateAdd(second,-600,GetDate() )
) Average
ORDER BY ID DESC
Don't know if it results in anything more efficient; but they're alternatives.
October 13, 2004 at 9:59 am
This might run slightly faster:
CREATE TABLE #Table1 (ID int identity,
TimedAt datetime default getdate(),
One int, Two int, Three int)
INSERT #Table1 (One, Two, Three) VALUES (1,2,3)
INSERT #Table1 (One, Two, Three) VALUES (1,2,3)
INSERT #Table1 (One, Two, Three) VALUES (1,2,3)
declare @Last datetime
select @Last = DateAdd(second,-600,GetDate() )
Select t1.*, average.*
from
(select top 1 * from #Table1 order by ID desc) t1
cross join
(
SELECT
AverageOne = avg(One),
AverageTwo = avg(Two),
AverageThree = avg(Three)
FROM #Table1
WHERE TimedAt>@last
) average
October 14, 2004 at 1:38 am
Hi all,
Since the ID column is IDENTITY, I would only suggest a replacement of the SELECT TOP 1 ... subquery with this one
(select * from Table1
where ID = convert(int, IDENT_CURRENT('Table1'))) t1
According to BOL: IDENT_CURRENT returns the last identity value generated for a specified table in any session and any scope.
So, the PK index scan is reduced to index seek.
This is really a minor enhancement, since it doesn't appear to be significant even on 1,000,000 rows, but anyway ...
Regards,
Goce Smilevski.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply