Solved

t sql between dates performance

Posted on 2015-02-24
3
89 Views
Last Modified: 2015-02-24
All,

I have a table (Table A) and I'm trying to select records between dates and below is my query

Select Account,TransactionAmount, TransactionTime
From Table A
Where TransactionTime between Dateadd(dd,-8,getdate()) and dateadd(ss,-1,getdate())

This is taking about 10 mins to complete, is there any way to improve the performance? below is the index that's created on the table and where column is part of the composite index

PRIMARY KEY CLUSTERED
(
      Account ASC,
      TransactionAmount ASC
      TransactionTime ASC,

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
0
Comment
Question by:Samoin
3 Comments
 
LVL 40

Accepted Solution

by:
Kyle Abrahams earned 500 total points
ID: 40628989
Create a non-clustered index based on TransactionTime.  That'll speed things up tremendously.

A lot of people don't recommend using between for date times, but as long as the query is returning what you want, fine by me.

The last thing which I'm not 100% sure on will help is if you declare two variables . . . @startDate and @endDate  set the variables and use that in the where clause.  I believe sql will do the calculation for each record versus doing the calculation once per variable.  

Again, not 100% sure on the last but couldn't hurt.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 40628997
Your original clustered index wont make use of that query, you need an index on TransactionTime  ;
0
 
LVL 1

Author Closing Comment

by:Samoin
ID: 40629065
Done. Created a non clustered index on the transactiontime column and it significantly improved the performance.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

863 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

28 Experts available now in Live!

Get 1:1 Help Now