?
Solved

t sql between dates performance

Posted on 2015-02-24
3
Medium Priority
?
116 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
[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 Comments
 
LVL 40

Accepted Solution

by:
Kyle Abrahams earned 2000 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
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…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

765 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