Solved

SQL Timestamp offset

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

688 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