Mike Yearwood
Toronto, Ontario, Canada
General information
Forum:
Microsoft SQL Server
Environment versions
SQL Server:
SQL Server 2012
>>>>I would recommend dynamic SQL in any case. You'd only optimize the parts of the where clause that need it.
>>>
>>>What do you mean by "You'd only optimize the parts of the where clause that need it"?
>>
>>If you create a where like
>>
>> (@MinDate is null OR @MinDate < = Date) and
>> (@MaxDate is null OR @MaxDate > = Date) and
>> (@MinAmount is null OR @MinAmount > = Amount) and
>>
>>SQL is doing a lot of extra work internally. If you construct the query dynamically so that in the case where @MinAmount is not passed you execute
>>
>> (@MinDate is null OR @MinDate < = Date) and
>> (@MaxDate is null OR @MaxDate > = Date)
>>
>>The optimizer will not waste time even thinking about MinAmount. :)
>
>Correct me if I'm wrong, but that is (I believe) basically what the RECOMPILE will do. I'm not saying one is better than the other - just curious if there are situations where dynamic SQL is going to yield a better execution plan than a RECOMPILE.
I'd say so. RECOMPILE takes a lot of overhead. As each dynamic SQL is executed, the plan will be cached. RECOMPILE would recreate the plan per execution.
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only