Performance question and advice??

Question

Post reply

Performance question and advice??

sqlislife

Ten Centuries

Points: 1251
More actions
September 23, 2009 at 10:52 am

#215607

Hi experts, I have always wanted to know this,
What is the best way to write a tsql code for parametrized date ranges? For example
create procedure procname
(
@datestart datetime,
@dateend datetime
)
as
select column1
from myTable
where date between @datestart and @dateend
I have no ddl or anything, I have just always wanted to know an optimal way to achieve this to prevent problems like excessive reads or parameter sniffing when the date range increase. For the parameter sniffing problem, could declaring the @datestart and @dateend locally work?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

arun.sas SSChampion Points: 11831 More actions · Answer 1

The data range error, depends on the type of the date we have stored

1)Date only.

2)Date with time.

For all situations, I preferred the method like

Select column1

from myTable

where date >= isnull(@datestart,date)

and date = isnull(@datestart,date)

and date <= isnull(@dateend,date)

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

The only problem with using BETWEEN is that it evaluates to >= and = and = '9/1/2009' AND

theDate < '10/1/2009'

[/code]

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

sqlislife Ten Centuries Points: 1251 More actions · Answer 3

Thanks for the introspective view guys, what about performance? How do you approach that if you were to select all records for 3 months and then 9 months and then a year. Asides from indexing, what ways would you suggest to approach that??

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

After I submitted my last post I noticed you were really asking for performance. Really the only thing you can do is index and update stats. If your data distribution is skewed you may want to look at using OPTIMIZE FOR or PLAN GUIDES to try to get the plan that works best for the majority of queries.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

sqlislife Ten Centuries Points: 1251 More actions · Answer 5

Thank you for your help Jack, both replies helped a lot !!