Solved

SQL Timestamp offset

Posted on 2014-04-23
5
292 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 48

Accepted Solution

by:
PortletPaul earned 400 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 48

Expert Comment

by:PortletPaul
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 48

Expert Comment

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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

If you are thinking of adopting cloud services, or just curious as to what ‘the cloud’ can offer then the leader according to Gartner for Infrastructure as a Service (IaaS) is Amazon Web Services (AWS).  When I started using AWS I was completely new…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
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.

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now