Optional parameters 1

[Joiner]

Hi.
Can anyone recommend the best way to handle this situation? I have a stored procedure that returns a list of properties. I also have optional parameters that can be passed, for example, if a state parameter is not passed, it is assumed that the user wants to see all properties, if a state parameter is passed, it is assumed the user only wants to see properties within that state. So far, I have come up with the following solutions. Can anyone tell me which one is better or recommend something different? Thanks!!

OPTION1
create procedure sp_GetProperties
@state char(2) = null,
@city_id int = null
as
select *
from property p
where (@state is null or @state = p.state) and
(@city_id is null or @city_id = p.city_id)

OPTION2
create procedure sp_GetProperties
@state char(2) = null,
@city_id int = null
as
select *
from property p
where p.state = case when @state is null then p.state else @state end and
p.city_id = case when @city_id is null then p.city_id else @city_id end

 

[tlbroadbent]

Which option works best will depend on a number of factors including size of the table and indexing. I did some quick testing and found that the first option was usually the fastest.

I know a lot of people don't want to hear this but the fastest option was to dynamically create and execute the query. The two options that you presented consistently executed a query plan that did not use the best indexes on the table but that is understandable given the Where clauses.

When I created and executed a dynamic statement, the query was consistently 10 times faster. This may not be the case with your data but it is worth testing.

OPTION3
create procedure sp_GetProperties
@state char(2) = null,
@city_id int = null
as

Declare @sql varchar(400)

Set @sql='select * from property'

If @state Is Not Null Or @city_id Is Not Null
Set @sql=@sql+' Where'

If @state Is Not Null
Set @sql=' State='''+@state+''''

If @city_id Is Not Null
Begin
If @state Is Not Null
Set @sql=@sql+' And'

Set @sql=' City='+@city_id
End

Exec(@sql) Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[Joiner]

Thanks Terry. I have actually noticed that myself but was convinced that it was a fluke. I have since; however, found other stored procedures of mine similar to the one above that run faster when converted to a dynamic statement. Any ideas on why? I'll need to be able to support this decision with an educated response and I'm not sure I have one of those on my own.
Thanks again!

 

[tlbroadbent]

SQL Optimizer creates a query execution plan based on columns selected, where criteria and joins. The plan is also based on indexes and statistics for tables involved. When you use one of the options that you proposed, the optimizer doesn't choose correct indexes or chose to scan the index rather than seek. At least it performed this way in my tests.

I suspect this occurs because the Where clause doesn't define a simple criteria for each column. The optimizer can't choose indexes due to the use of functions in the criteria or the complexity of the criteria. When the statement is dynamically created, the criteria is very simple and easy for the optimizer to handle. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.