Solved

t sql between dates performance

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

717 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