Solved

t sql between dates performance

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

756 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