IF statements in SPs.

[donutman]

This month (Jan. 2005 p.15) SQL Server Magazine gave a definitive answer to something that I suspected...IF statements within a SP can cause poor performance. Apparently a compiled version of a SP can hold only one query plan, therefore an IF statement that utilizes an input parameter can make it impossible for there to be only one query plan. The article isn't crystal clear to me and dodges the issue of determining which IF statements can cause a problem. It even looks as though the author was intentionally vague, "If SQL Server selects different query plans based on the different branches in the stored procedure..."
The article also hinted at another issue that I've wondered about regarding performance testing, "The downside of breaking the queries into separate stored procedures is that you'll require more memory for the procedure cache because you'll need to hold extra plans in memory..." So SQL Server does keep execution plans (for queries other than SPs???) in memory. That has to play havoc with repeated performance tests. They may get knocked out of the cache.
The same magazine issue (p.13) answers another question bantered about here. The explicit and not so explicit join syntax are both ANSI syntax. And neither ANSI nor Microsoft favors one over the other, however, ANSI discourages the *= syntax which was never adopted by ANSI. (They adopted outer joins in SQL-92 using the From clause to explicitly define the join.) "In fact, SQL Server will produce an incorrect answer if you use the old outer join syntax and include an IS NULL predicate in a column on the dependent side of the join."
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ClayG]

I would strongly contest that stuff about IF statements performance, at least judging by the way you report it (I have not seen the article). The query engine will Not CHOOSE a query plan Within a procedure -- a procedure has ONE "query plan"* which includes all structured statements. For example:

if @x = 1
	Select * from tableA
else
	Select * from tableB

*It is arguable semantically whether this is called a query plan, but if you call it a procedure execution plan, the point is that both arms of the IF have already been compiled before anything is executed. It does Not wait until the condition is evaluated to decide to compile just one alternative.

Quite the opposite, having an IF separating two alternative queries, is often GOOD for performance, because there is a common situation where the alternative is worse. You might be passing, say "customercode", as a selection parameter, and the user has the option to choose all values. A common solution is to use Like, and pass "%" to get all:

Select .. from tableA where customercode like @customercode

or it might be numeric and I think this is just as bad:

Select .. from tableA where (customercode = @customercode OR @customercode = 0)

The optimiser will almost certainly do tablescan every time for the "like" condition, because it has no idea how many values your pattern parameter will match. If you separate the two possibilities with an IF:

IF @customercode = '%'
	Select .. from tableA  -- no where, get all.
Else
	Select .. from tableA where customercode = @customercode

The select with an equals condition is potentially much more efficient than the Like condition. Although it depends on the selectivity of the column. The overhead is the execution plan is a bit bigger.

I am not real comfortable with the statements about cached plans either. When talking about caching its important to realise that there are two extremes: queries/procedures that are called rapidly and process little data, versus queries/procedures that are called occasionally (minutes between calls) and process a lot of data. Query caching is significant only to the rapid-call queries, and these will not get pushed out of memory. High-volume queries take a lot more time accessing data so compile time is not important. Data buffering is relevant to both by the way.

 

[donutman]

ClayG, as best as I can tell from reading the article, this is exactly what the author was talking about:

if @x = 1
    Select * from tableA
else
    Select * from tableB

I don't have a copy of the magazine to refer to...I threw it out after reading, but I do recall it spoke of the issue of a 2nd user calling the same SP right after the 1st user called it with an alternate path. Does that put a different slant on the issue?
My suspicion, however, has always been that the optimizer isn't like a language compiler, where it makes no difference which path a procedural statement takes. If I had not read the article and you expressed that strong opinion, I would have accepted it on good faith. But having read the article, I would ask that you support your contention that the above would in fact resolve into one query plan that a SP would pre-compile and save.
Maybe one way to resolve the question is through testing, but I think it would require a much more complicated query at each branch, so that a difference in performance would be significant.
-Karl


[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[vongrunt]

Btw. take a look at this (from books offline):

The algorithms to match new SQL statements to existing, unused execution plans in the cache require that all object references be fully qualified. For example, the first of these SELECT statements is not matched with an existing plan, and the second is matched:

SELECT * FROM Employees

SELECT * FROM Northwind.dbo.Employees

If that's true, I don't like it.

 

[donutman]

That may not be as dire as it seems. Maybe (hopefully) it's referring to ad-hoc SQL submitted to the engine. It wouldn't seem plausible that THE programmers didn't fully qualify the object names in a pre-compiled version of a SP.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ClayG]

Create a IF statement like the simple ones discussed, and execute it in QueryAnalyser with "Show Execution Plan" switched on. (Its in the Connection options, not "Estimated execution plan"). Put it in a SP and Call it to be thorough.

After execution you supposedly see the execution plan that was used. So - do both select statements appear in this plan ?

The only caveat is whether you really see the actual plan as it would be when cached for a procedure, or would it be somehow incomplete until it is actually called. If it shows two selects then I think that proves they both are in the plan.

Its not at all hard to construct a test that shows whether IF and two Selects can avoid a tablescan as I discussed.

 

[ProdAir]

So SQL Server does keep execution plans (for queries other than SPs???) in memory..."

It supposedly does for ad hoc queries although I believe it will only do it if you use sp_executesql.