New Challenges in Physical Database Design - Automated Physical Database Design and Tuning - page 219

DECLARE @a int = 1, @b int = 0

WHILE (@b < 1000) BEGIN
SELECT @b = @b + MIN(colB)
FROM R
WHERE colId < @a

IF(@b%2=0)
SELECT @a = @a + 1

ELSE

SELECT @a = @a + COUNT(*)
FROM S
WHERE colId < @b

END

FIGURE 12.2 Example of an SQL script that uses control-flow statements.

12.1.2 Beyond Single-Statement SQL
Although SQL queries are the most common way of interacting with a database
management system (DBMS), there are extensions that provide more flexi-
bility and expressive power. It is common for SQL -based languages to support
flow control constructs, local variables, and many other features that increase
the complexity of plain SQL . Figure 12.2 shows a fragment written in T-SQL ,
which is Microsoft and Sybase's extension to SQL . The code fragment shows
SQL statements that are executed inside WHILE loops and others that execute
(or not) depending on specific values of temporary values. Specifically, we first
initialize two local variables @a and @b . We then execute iteratively a sequence
of statements in a loop, until the value of @b exceeds the value 1,000. During
each loop iteration, we execute a query over table R , which returns the small-
est value of colB that satisfies colId < @a . This result is added to the local
variable @b and then, depending on whether @b is odd or even, updates the
value of @a by either incrementing it by one or adding the number of tuples
from S for which colId is smaller than the current value of @b . Although
the example is not necessarily realistic, it illustrates features in T-SQL that
lie outside pure SQL . Suppose that we want to tune the previous fragment.
Just by analyzing the script, it is not clear to understand (1) how many times
(if any) each SQL statement would be executed, and (2) which would be the
values of @a and @b at each execution. To tune this workload, we can follow
various approaches:

• We can consider each valid SQL statement in the fragment as a different

query that would be executed once and can tune the resulting workload.
We can also instantiate local variables by using default values.

• We can perform some form of static analysis to infer better multiplicity

values for each query in the script. We might be able to infer that the