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.
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.