• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 345
  • Last Modified:

SQL Timestamp offset

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
Nigel Keith-Walker
Asked:
Nigel Keith-Walker
  • 3
  • 2
1 Solution
 
PortletPaulfreelancerCommented:
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
 
Nigel Keith-WalkerContractorAuthor Commented:
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
 
PortletPaulfreelancerCommented:
can you share the execution plan? (attach as .sqlplan file)
0
 
Nigel Keith-WalkerContractorAuthor Commented:
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
 
PortletPaulfreelancerCommented:
good news!
thank you,
Paul
0
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

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now