?
Solved

Tsql Top (10) with  Datepart(second, getdate()) - x

Posted on 2010-01-07
15
Medium Priority
?
1,280 Views
Last Modified: 2012-05-08
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
0
Comment
Question by:JoeUS
  • 7
  • 4
  • 3
  • +1
15 Comments
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 400 total points
ID: 26204628
SELECT  TOP (10) LogID
FROM Log_exp
WHERE DateTime  BETWEEN DATEADD(second,10,  GETDATE()) and GETDATE()  AND Name = 'buba'
ORDER BY LogID DESC
0
 
LVL 11

Assisted Solution

by:rajvja
rajvja earned 200 total points
ID: 26204683
Hi

  Try with DateDiff instead of datepart bla bla
0
 
LVL 10

Accepted Solution

by:
lof earned 1400 total points
ID: 26204784
I am assuming in the table you have a lot of records when you want to select no more than 10 for last 10 second for one name.

Be very careful which solution you will use as it may have huge effect on performance. The less function calls the better.

For that reason I would prefer using DateDiff.

You are using ORDER BY which means that firs all rows have to be processed and then ordered.
You say you call it on every new entry, so you should be able to get the ID of latest record and you could use that to initially limit the data-set
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26204936
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
0
 
LVL 10

Expert Comment

by:lof
ID: 26205124
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
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26205377
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    
0
 
LVL 10

Expert Comment

by:lof
ID: 26205411
;)

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?
0
 

Author Comment

by:JoeUS
ID: 26205440
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?
0
 
LVL 10

Expert Comment

by:lof
ID: 26205462
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.

0
 

Author Comment

by:JoeUS
ID: 26205573
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)
0
 

Author Comment

by:JoeUS
ID: 26205602
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()
0
 
LVL 10

Expert Comment

by:lof
ID: 26205654
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.
0
 
LVL 10

Expert Comment

by:lof
ID: 26205682
@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.
0
 
LVL 10

Expert Comment

by:lof
ID: 26205768
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
0
 

Author Closing Comment

by:JoeUS
ID: 31674268
Excellent follow up and went for the extra mile!
Thank you!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Suggested Courses

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question