Splitting stored procedures to improve costing

The optimizer cannot use statistics the final select in the following procedure,because it cannot know the value of @city until execution time:

create procedure au_city_names @pub_name varchar(30) as declare @city varchar(25) select @city = city from publishers where pub_name = @pub_name select au_lname from authors where city = @city

The following example shows the procedure split into two procedures. The first procedure calls the second one:

create procedure au_names_proc @pub_name varchar(30) as declare @city varchar(25) select @city = city from publishers where pub_name = @pub_name exec select_proc @city

create procedure select_proc @city varchar(25) as select au_lname from authors where city = @city

When the second procedure executes, Adaptive Server knows the value of @city and can optimize the select statement. Of course, if you modify the value of @city in the second procedure before it is used in the select statement, the optimizer may choose the wrong plan because it optimizes the query based on the value of @city at the start of the procedure. If @city has different values each time the second procedure is executed, leading to very different query plans, you may want to use with recompile.

Tags : , , , ,

If you enjoyed this post, please consider to leave a comment or subscribe to the feed and get future articles delivered to your feed reader.

Leave Comment