SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 How to make this query sargable		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sql-lover
Yak Posting Veteran

57 Posts
Posted - 08/01/2012 :  16:21:23  Show Profile  Reply with Quote
Hi,

Got a query that is so slow, that just returns a timeout error to the user, via application.

I checked the store procedure and execution plan and I believe this is the reason:

WHERE TimeAdded between @StartDate and DATEADD(d, 1, @EndDate)

How can I change that statement, while keeping logic, to a sargable one? I though about using >= and < plus and AND operator, removing the BETWEEN, but I have not tested it yet.

Any suggestion is highly appreciated.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

India
47157 Posts
Posted - 08/01/2012 :  16:35:10  Show Profile  Reply with Quote
why do you think the above condition is NOn sargeable?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

robvolk
Most Valuable Yak

USA
15559 Posts
Posted - 08/01/2012 :  16:35:49  Show Profile  Visit robvolk's Homepage  Reply with Quote
That should already be sargable, if not:
SET @EndDate=DATEADD(d, 1, @EndDate)
...
WHERE TimeAdded between @StartDate and @EndDate
What do you mean by "sargable" anyway? If you're selecting all or most of the columns, and the date range is not very selective a table scan may likely be the most efficient. Make sure your statistics are up to date.
Go to Top of Page

sql-lover
Yak Posting Veteran

57 Posts
Posted - 08/01/2012 :  16:49:54  Show Profile  Reply with Quote
Ignore... no caffeine today ...

The issue is lack of a proper Index. It was working before (as per the developer) a few years ago, but we do have much more data now.

I'll deploy a proper Index and validate.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47157 Posts
Posted - 08/01/2012 :  16:57:33  Show Profile  Reply with Quote
quote:
Originally posted by sql-lover

Ignore... no caffeine today ...

The issue is lack of a proper Index. It was working before (as per the developer) a few years ago, but we do have much more data now.

I'll deploy a proper Index and validate.

No problem
let us know the outcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000