?
Solved

SQL Timestamp offset

Posted on 2014-04-23
5
Medium Priority
?
317 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 49

Accepted Solution

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

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 49

Expert Comment

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

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

752 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