SQL Performance Tuning

   

We said in Chapter 1, "Facilis Descensus Averni," that we assume you're already familiar with (among other things) stored procedures, but here's a quick summary of syntax to refresh your memory.

Informix calls it Stored Procedure Language (SPL); Sybase and Microsoft call it Transact-SQL; Oracle calls it Procedure Language extensions to SQL (PL/SQL); the SQL Standard refers to Persistent Stored Modules (PSM). All these names refer to the same thing. It's easy to see this if you write the same stored procedure in several dialects and put the statements in columns side by side, with each syntax element occupying one row. We've taken the stored procedure declaration shown in Listing 11-1 and done this; the result is shown in Table 11-1.

Although no two columns of Table 11-1 are exactly alike, the important thing the table shows is how similar the statements are to one another, and to the SQL Standard. For example, if your background is Microsoft/Sybase, you just have to adapt to a few differences: Parameter and variable names do not begin with @; blocked statements are terminated explicitly (for example, IF END IF) as in Ada; the parameter list must be inside parentheses; semicolons are statement separators. Those are just details. We're confident that you'll be able to read our standard SQL PSM syntax examples regardless of your prior experience.

Table 11-1. Listing 11-1's Stored Procedure in Four SQL Dialects

ANSI SQL PSM Informix SPL Microsoft/Sybase Transact-SQL Oracle PL/SQL
CREATE PROCEDURE CREATE PROCEDURE CREATE PROCEDURE CREATE PROCEDURE
Sp_proc1 Sp_proc1 Sp_proc1 Sp_proc1
(param1 INT) (param1 INT) @param1 INT (param1 IN OUT INT)
MODIFIES SQL DATA      
BEGIN      
DECLARE num1 INT; DEFINE num1 INT; AS DECLARE @num1 INT AS num1 INT;
      BEGIN

IF param1 <> 0

THEN SET param1 = 1;

END IF;

IF param1<> 0

THEN LET param1 = 1;

END IF;

IF @param1<> 0

SELECT @param1 = 1

IF param1 <> 0

THEN param1 := 1;

END IF;

UPDATE Table1 SET

column1 = param1;

END

UPDATE Table1 SET

column1 = param1;

END PROCEDURE

UPDATE Table1 SET

column1 = @param1

UPDATE Table1 SET

column1 = param1;

END;

Determinism

"But it sufficeth that the day will end, And then the end is known."

William Shakespeare, Julius Caesar

A function is deterministic if it always generates the same outputs, given the same inputs. For example, the SQL Standard built-in function UPPER is deterministic if UPPER('i') always returns I . Notice that there are two inputs here: the explicit argument 'i' and the constant environment setting 'code page = Western' . (If the code page were Turkish, the result of UPPER('i') would not be I .)

In contrast, a function is nondeterministic if it's possible that it might generate different outputs each time it is run, even if the inputs are always the same. This user -defined function is nondeterministic:

CREATE FUNCTION Sp_non_deterministic () RETURNS INTEGER BEGIN IF CURRENT_TIME = TIME '11:00:00' THEN RETURN 1; ELSE RETURN 2; END IF; END

Function Sp_non_deterministic will return a different result depending on an input value that is not determinable until execution, namely the time of day.

Nondeterministic functions are bad.

  • They are bad in CHECK clauses because you cannot be sure that you can rerun the same data-change statements and get the same results each time.

  • They are bad in select lists because DBMSs like to say "if the query has the same syntax then use the same plan."

  • They are bad in WHERE clauses because they cannot be indexed. There's no point in indexing a function that is subject to unpredictable change. The DBMS has to reevaluate the function every time.

Nondeterminism is a long name but a fairly simple idea. Clearly, you want to make sure your functions are deterministic, and you want to declare to the DBMS that they are deterministic. The problem area is external functions. They often depend on hidden factors (like the existence of a file) that the DBMS cannot detect.

   
Related

Категории