[dba-SQLServer] Recursive Queries in Microsoft SQL Server 2008

Gustav and Martin,

Since we're in tutorial mode, I feel that I should tell you a story.

<Tut>
I was called in to optimize an Access + SQL Server app, and there was one
lengthy procedure that acted as a wrapper for 9 separate SPs, each of which
did a query and then joined its result set to another table, retrieving
values from that table to include in the final result set, which was then
updated.

After analyzing the T-SQL for a while, I realized that each of the 9 SPs
called precisely the same record set from the main table, then each joined
to a table of immediate interest and did the relevant updates to the main
table. In other words, the SELECT from the main table was being performed 9
times.

Enter the fact that in SQL 2008 you can create a variable of type Table. I
took the 9 SPs and created one large SP (basically just by copying all
their code into a new SP. At the top I did the query from the main table (a
date-range and only some of the columns, not complicated at all. But the
thing is that I declared a variable of type Table and assigned its value as
the result set of the date-range query. Then I modified the code in the
copied T-SQL to refer to the variable, which is not at all complicated, all
you do is locate the FROM clause, and substitute the name of the Table
variable for the name of the actual table.

The point is, 8 re-queries of the rather large table were eliminated. The
query was called once, and its results stored in the Table variable, and
then each block of code that was formerly a SP already had the results from
the main table. The performance result was very gratifying: 40 minutes
became 8 minutes. Not bad for a morning's work on a job executed twice a
day.

Note:

This could have been optimized further in versions subsequent to SQL 2008,
since subsequent versions allow the passing of table variables as a
parameter. In that environment, I would return to the original layout,
updating it by passing the Table variable created in the SP that wraps all
the others. In other words...

Parent SP:
Declare myTable as (SELECT This, That FROM apothecary WHERE datesold ....
etc.)
Call SP1(myTable)
Call SP2(myTable)
etc.

This actually was my first choice, but only then did I discover that 2008
did not support the passing of tables to SPs. That happened later. Anyway,
the first version garnered a huge increase in performance, especially
valuable when the task is executed twice a day.
<Tut/>


On Tue, Oct 29, 2013 at 5:46 AM, Martin Reid <mwp.reid at qub.ac.uk> wrote:

> Spot on Arthur. Like Stewart I have immediate need of this as well.
>
> Martin
>
>
>