Improve company productivity with a Business Account.Sign Up

x
?
Solved

SQL Timestamp offset

Posted on 2014-04-23
5
Medium Priority
?
337 Views
Last Modified: 2014-11-12
I have recently migrated a database to AWS.  There is a timestamp on the transfer record for the system update.  This used to be Australian time, but is now UTC.
I have amended the stored procedure to select on :
dateadd(hour,10,T.PayoutCheckTmStamp) between @datelow and @datehigh

This reads through all 250,000 transfers and so slow that it often times out.

I have set up an index with the PayoutCheckTmStamp, but the execution does not use this.  I suspect that this is because it needs to add 10 hours to the field.

Any suggestions?
0
Comment
Question by:Nigel Keith-Walker
  • 3
  • 2
5 Comments
 
LVL 50

Accepted Solution

by:
Paul earned 1600 total points
ID: 40019094
a more sargable alternative would be to move the parameters 10 hours

T.PayoutCheckTmStamp between dateadd(hour,-10,@datelow) and dateadd(hour,-10,@datehigh)

Then I would also worry about if using between is actually the best method for you. If your data is time sensitive (which it appears to be) then the following construct is likely to be more accurate:

( T.PayoutCheckTmStamp >= dateadd(hour,-10,@datelow)
AND  T.PayoutCheckTmStamp < dateadd(hour,-10,@datehigh) )

for more on this topic please see: "Beware of Between"



---
You could of course update the old records so that this 10 hour offset is not an ngoing issue, or maybe add a computed column.
0
 

Author Comment

by:Nigel Keith-Walker
ID: 40019173
Thanks for your prompt reply.  

Your suggestion made a great deal of sense.  I have re-run the stored procedure with the dateadd on the parameters.  It has changed the costing on the tblTransfers, but still uses up 90% of the execution time.  This is the first time that I have had to seriously use the Execution plan to hone the select query.

I have discussed the possibility of adding the adjusted time to the table as another field.  Certainly it would be more efficient, but requires us to co-ordinate our changes. he looks after the web-based app, and I look after the back-office ap.

Do you think that a view would help?
0
 
LVL 50

Expert Comment

by:Paul
ID: 40019381
can you share the execution plan? (attach as .sqlplan file)
0
 

Author Closing Comment

by:Nigel Keith-Walker
ID: 40026442
Hi Apologies for the lack of response.  I thought that I had posted the sqlplan, but obviously failed to save it.

I have just returned from a meeting with the users.  The reporting using the PayoutCheckTmStamp and comparing to a time adjusted parameter is working satisfactorily.
0
 
LVL 50

Expert Comment

by:Paul
ID: 40026446
good news!
thank you,
Paul
0

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
During the weekend, I was asked to investigate into a deadlock in SQL Server 2014. SQL being something I don’t really fancy myself being an expert at, I had to do some refreshing. This article is a collection of my notes.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

606 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