Solved

t sql between dates performance

Posted on 2015-02-24
3
103 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 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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

732 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