Performance with Wildcards Dynamic SQL vs.Variables

Performance with Wildcards Dynamic SQL vs.Variables

Post by Matt » Wed, 03 Mar 2004 03:41:30



When faced with reasonable fixed SQL query alternatives to dynamic
SQL, I'll generally take them to avoid performance slowdowns due to
recompiles, etc. I'm currently observing a basic situation in which
the Dynamic SQL alternative performs far better than fixed SQL query
alternative. This involves a basic search for a string with a trailing
wildcard against a varchar column of Widget names.
I can't seem to find any fixed alternative that performs better. After
viewing this sample, keep in mind that the reason the dynamic SQL
seems to do better is that it utilizes an Index seek to quickly pull
those records whose widget names match the string plus trailing
wildcard. The fixed code with goes with a much slower index scan.
Given the table sizes and indexes, this changes the performance from a
fraction of a second to better parts of a minute which is
unacceptible. I'm sure this one has come up before. Do I need to stay
with the Dynamic SQL alternative in this case?

Here is the sample (with names changed to protect whatever)
-- DDL Code

CREATE TABLE dbo.ToyWidgetIndex (iWidgetID INT PRIMARY KEY, iToyID
INT PRIMARY KEY)
-- About 50,000,000 rows
GO

CREATE TABLE dbo.WidgetIndex (iWidgetID INT IDENTITY (1, 1) PRIMARY
KEY  ,vchWidget VARCHAR(30))
-- about 2,800,000 rows
GO

 CREATE  INDEX ix_ToyWidgetIndex_ToyID ON dbo.ToyWidgetIndex(iToyID,
iWidgetID)
GO
 CREATE  UNIQUE  INDEX ixUN_WidgetWidget ON dbo.WidgetIndex(vchWidget)
GO

-- Query alternatives


-- The first 3 variables define external crteria within a union query


-- Example (1) -  Dynamic SQL w/trailing wildcard within string
variable
-- Performs very fast



II.iWidgetid'


CHAR(39)



-- Example (2) Fixed SQL, w/trailing wildcard within string variable
-- Performs very slow

SELECT DISTINCT iki1.iToyId
                FROM ToyWidgetIndex iki1
                JOIN WidgetIndex II ON IKI1.iWidgetID = II.iWidgetid




-- Example (3) Fixed SQL, Utilizing PATINDEX insteead of LIKE
-- Performs very slow, bit faster than (2)

SELECT DISTINCT iki1.iToyId
                FROM ToyWidgetIndex iki1
                JOIN WidgetIndex II ON IKI1.iWidgetID = II.iWidgetid




 
 
 

Performance with Wildcards Dynamic SQL vs.Variables

Post by Stephen Hendrick » Wed, 03 Mar 2004 05:50:47


The speed difference between Example 1 and 2 makes sense.  In Example 1
the string has been converted to a literal which the optimizer can
recognize as being quaranteed to begin with literal text followed by a
wildcard.  Example 2, on the other hand compares to a variable.  The
optimizer won't make the same assupmtions.

A couple of things that you could try are:

  1) Use the RECOMPILE option on your stored procedure.  Now the
optimizer can build an execution plan based on the specific value you
passed in.

  2) Use the INDEX = <indexname> query hint.

As far as Example 3 goes, you're no longer comparing apples to apples.
The comparison can only use the index when the beginning of the data
column is being compared.  If the string to match can be anywhere in the
data column, the optimizer will always perform a table scan.

HTH

=======================================
Everyone here speaks SQL; some are more fluent, others less.  When
describing your SQL object (table, etc.), do so in the language that we
all understand - SQL, not English.  It makes it easier to understand
your issue and makes it more likely that you will get the assistance
that you are asking for.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

Performance with Wildcards Dynamic SQL vs.Variables

Post by Erland Sommarsko » Wed, 03 Mar 2004 08:26:25



> When faced with reasonable fixed SQL query alternatives to dynamic
> SQL, I'll generally take them to avoid performance slowdowns due to
> recompiles, etc. I'm currently observing a basic situation in which
> the Dynamic SQL alternative performs far better than fixed SQL query
> alternative. This involves a basic search for a string with a trailing
> wildcard against a varchar column of Widget names.

The problem with the fixed code in your example is that the optimizer

that is, there is a leading percent sign. Whence the scan.


parameter value when building the first plan, so here you can get a
seek.

The fact that dynamic SQL sometimes gives best performance is actually
not surprising at all. In this case you give SQL Server only constants
to play with, and the estimates will be more accurate. There is a cost
for the compilation, but if the alternative is a plan with an execution
time of 30 seconds it pays back.

--

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

 
 
 

1. performance of dynamic vs. static SQL

Currently, our SQL statements are strings (generated by Tcl code) that
are "prepared" by the Oracle server before being executed. This is
called dynamic SQL, right?

1. Will the performance of our application improve if we skip one step
by having our SQL statements prepared in advance (like the precompile
step when using embedded SQL in a compiled host language)? How?
2. Is this "static" SQL?
3. Is that the same as PL-SQL?
4. How do we supply variables to the pre-compiled SQL statements?
5. Do the precompiled statements reside on the client or on the server?

Regards, Michiel

--
Michiel Perdeck
------------------------------------------------------------------------



------------------------------------------------------------------------

2. web comp: raw HTML code in a field?

3. SQL 2000 vs SQL 7 Using LIKE Wildcards

4. updating disconnected recordsets

5. Dynamic Execute Statement vs. Passing Dynamic SQL

6. How To enlarge a Database

7. SQL 6.5 Performance vs SQL 7.0 Performance

8. VB 6 Database and Access 2K Database Problem

9. Dynamic Files vs Static Hashed - Performance Issues

10. Performance of output variables vs row data

11. SQL Select statement vs sp AND literals vs variables

12. XPath Variable Query Performance using URL vs ADO