JoeUS
asked on
Tsql Top (10) with Datepart(second, getdate()) - x
Hi,
I want to select the last, maximum 10 records within the last 10 seconds for buba. I call this on every new entry
SELECT TOP (10) LogID
FROM Log_exp
WHERE DateTime BETWEEN GETDATE() - (DATEPART(second, GETDATE()) - 10) AND GETDATE() AND Name = 'buba'
ORDER BY LogID DESC
Thanks
I want to select the last, maximum 10 records within the last 10 seconds for buba. I call this on every new entry
SELECT TOP (10) LogID
FROM Log_exp
WHERE DateTime BETWEEN GETDATE() - (DATEPART(second, GETDATE()) - 10) AND GETDATE() AND Name = 'buba'
ORDER BY LogID DESC
Thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Aneesh
assuming that there is an index on the datetime column, the one i provides work better that using the 'datediff' as that uses the indexes
Aneeshattingal,
With all due respect to such highly decorated expert, but forgetting the performance, your query is looking 10 seconds into future and for that reason will never return anything.
And back to performance. Even if you have index on DateTime you will need to compute
DATEADD(second,10, GETDATE()) and GETDATE() for each row - GetDate() is nondeterministic
My suggestion with really big table is to use (possibly) known primary key to get last 10 few records and do date comparison on them only.
Regards,
Lof
With all due respect to such highly decorated expert, but forgetting the performance, your query is looking 10 seconds into future and for that reason will never return anything.
And back to performance. Even if you have index on DateTime you will need to compute
DATEADD(second,10, GETDATE()) and GETDATE() for each row - GetDate() is nondeterministic
My suggestion with really big table is to use (possibly) known primary key to get last 10 few records and do date comparison on them only.
Regards,
Lof
oops ,,, My mistake it should be ' -10 '
In case of bigger tables , we can use aother variable to store the value of GETDATE() and perform the comparison
BETWEEN DATEADD(second,-10, @Now ) and @Now
In case of bigger tables , we can use aother variable to store the value of GETDATE() and perform the comparison
BETWEEN DATEADD(second,-10, @Now ) and @Now
;)
I've just checked that one on SQL 2008 and optimiser seems to do it automatically so GetDate() in WHERE has always the same value. That's worth investigating closer.
But still DateAdd() is performed for each row, isn't it?
I've just checked that one on SQL 2008 and optimiser seems to do it automatically so GetDate() in WHERE has always the same value. That's worth investigating closer.
But still DateAdd() is performed for each row, isn't it?
ASKER
Thank you for the great replies!
As "lof" pointed out ORDER BY may cause some issues. I don't expect more than 10000 records in this table at a time but performance is absolutely the most important.
"datediff" is totally the better way to go about but how would I get the last 10 recs without Order By?
As "lof" pointed out ORDER BY may cause some issues. I don't expect more than 10000 records in this table at a time but performance is absolutely the most important.
"datediff" is totally the better way to go about but how would I get the last 10 recs without Order By?
When do you run a query? is it in a trigger? You said you do it upon every entry.
Is it after entering a row to this very table? Tell us more about it and there may be ways of doing it with ordering much smaller set.
Is it after entering a row to this very table? Tell us more about it and there may be ways of doing it with ordering much smaller set.
ASKER
Correction: how would I get the last 10 recs without Order By only for 'buba'?
I changed the DATEPART to DATEADD with @Now parameter. (looks neater)
I changed the DATEPART to DATEADD with @Now parameter. (looks neater)
ASKER
When do you run a query?
Is it after entering a row to this very table?
Yes I run it right after the insert and I already capture the scope_identity()
Is it after entering a row to this very table?
Yes I run it right after the insert and I already capture the scope_identity()
So with this correction you cannot I'm afraid.
I was thinking that if you have a @@IDENTITY value of the new row inserted into the same table and you are interested only in no more than last 10 seconds, and you know that you insert no more than 10 records per second in average you could do something like that:
select * from (
select * from Log_exp where LogID>=@@IDENTITY-200
) MostRecentOnly
where .......some conditions
order by LogId
I was thinking you may have massive tables with millions of rows with this rate of inserts.
In such case this solution would allow you to sort only 200 rows rather than hundreds of thousands. But with 10000 records, just make sure you have index on dateTime field and you will be fine.
Ordering on indexed columns is quick as long as index is not fragmented too much.
I was thinking that if you have a @@IDENTITY value of the new row inserted into the same table and you are interested only in no more than last 10 seconds, and you know that you insert no more than 10 records per second in average you could do something like that:
select * from (
select * from Log_exp where LogID>=@@IDENTITY-200
) MostRecentOnly
where .......some conditions
order by LogId
I was thinking you may have massive tables with millions of rows with this rate of inserts.
In such case this solution would allow you to sort only 200 rows rather than hundreds of thousands. But with 10000 records, just make sure you have index on dateTime field and you will be fine.
Ordering on indexed columns is quick as long as index is not fragmented too much.
@aneeshattingal:
Always I learn something new... the discussion we had here made me run some performance experiments on date functions. I'm not done yet but it is already interesting.
Always I learn something new... the discussion we had here made me run some performance experiments on date functions. I'm not done yet but it is already interesting.
Ok, so here are the result.
If we consider DatePart time as 100% then approximately:
Datediff = 50%
number substraction 40%
in processor time.
the number substraction is something we weren't talking about it here.
The date is stored as a number so you write your WHERE clause as
where value > GetDate()-0.000115741
0.000115741 = 10 second
If we consider DatePart time as 100% then approximately:
Datediff = 50%
number substraction 40%
in processor time.
the number substraction is something we weren't talking about it here.
The date is stored as a number so you write your WHERE clause as
where value > GetDate()-0.000115741
0.000115741 = 10 second
ASKER
Excellent follow up and went for the extra mile!
Thank you!
Thank you!