I have a standard update statement which runs for 5 hours in its stored
procedure. When I cut/paste the update statement out of the procedure and run
in Query Analyzer it runs in 2:16.  There is one input parameter, so when I
run the update statement standalone I delcare it and set its value first. 
Other than that, the code is identical.  I had a senior DBA verify that this
is indeed the case. I then put the update statement in a new procedure and
passed no parameters and it ran in 2:16. When I passed in the parameter and
referenced it in the statement it slowed down again. When I used an
intermediate variable in the statement and set it to the parameter value (Set
queryVariable = parameterVariable) then it ran in 2:16.

The hint was recently added and did greatly improve performance.

We are all very surprised at this but it starts to explain several
procedures in our 2000 database which may be behaving this way. If anyone has
anything to share on this I would appreciate it.

Here is the code, with the intermediate variables commented-out.

create procedure testproc (
        @iVendorID INT,
                            @today smalldatetime
    ) as
Begin
--declare  @iVendorID int
--declare @today smalldatetime
--set  @iVendorID = @iVendorIDx
--set @today = @todayx
BEGIN TRANSACTION trUpdate

        UPDATE
            C
        SET
            C.RecordID =
                CASE
                    WHEN ISNULL( I.iImportID, 0 ) = 0
                    THEN C.RecordID
                    ELSE I.iImportID
                END,
            C.Position =
                CASE
                    WHEN ISNULL( I.vcVendorRecordID, '' ) = '' THEN C.Position
                    ELSE I.vcVendorRecordID
                END,
            C.SITUS_HOUSE_NO =
                CASE
                    WHEN ISNULL( I.vcSitusHouseNumber, '' ) = '' THEN C.SITUS_HOUSE_NO
                    ELSE LEFT( I.vcSitusHouseNumber, 10 ) -- Increase to 50
                END,
            C.SITUS_DIRECTION =
                CASE
                    WHEN ISNULL( I.vcSitusDirection, '' ) = '' THEN C.SITUS_DIRECTION
                    ELSE I.vcSitusDirection
                END,
            -- Need to increase situs_street_name to varchar(75)
            C.SITUS_STREET_NAME =
                CASE
                    WHEN ISNULL( I.vcSitusStreetName, '' ) = '' THEN C.SITUS_STREET_NAME
                    ELSE LEFT( I.vcSitusStreetName, 30 )
                END,
            C.SITUS_SUFFIX =
                CASE
                    WHEN ISNULL( I.vcSitusSuffix, '' ) = '' THEN C.SITUS_SUFFIX
                    ELSE I.vcSitusSuffix
                END,
            C.SITUS_POST_DIRECTION =
                CASE
                    WHEN ISNULL( I.vcSitusPostDirection, '' ) = '' THEN
C.SITUS_POST_DIRECTION
                    ELSE I.vcSitusPostDirection
                END,
            C.SITUS_UNIT =
                CASE
                    WHEN ISNULL( I.vcSitusUnit, '' ) = '' THEN C.SITUS_UNIT
                    ELSE I.vcSitusUnit
                END,
            C.SITUS_CITY_STATE =
                CASE
                    WHEN LEN( ISNULL( I.vcSitusCityState, '' ) ) !> 6 THEN ISNULL(
C.SITUS_CITY_STATE, '' )
                    ELSE I.vcSitusCityState
                END,
            C.SITUS_CITY =
                CASE
                    WHEN LEN( ISNULL( I.vcSitusCity, '' ) ) !> 2 THEN ISNULL( C.SITUS_CITY,
'' )
                    ELSE I.vcSitusCity
                END,
            C.Unique_County_Code =
                CASE
                    WHEN I.cUniqueCountyCode IS  NULL THEN C.Unique_County_Code
                    ELSE I.cUniqueCountyCode END,
            C.SITUS_ZIP =
                CASE
                    WHEN I.vcSitusPostalCode IS NULL
                       OR LEN(Ltrim(Rtrim(I.vcSitusPostalCode))) < 5
                       OR NOT EXISTS (SELECT * FROM uLookup_ZipCode where vcZipcode =
I.vcSitusPostalCode)
                       THEN C.SITUS_ZIP
                    ELSE I.vcSitusPostalCode
                END,  
            C.PROPERTY_TYPE =
                CASE
                    WHEN  LEN( ISNULL( I.vcPropertyType, '' ) ) <> 4 THEN C.PROPERTY_TYPE
                    ELSE I.vcPropertyType
                END,
            C.TYPE_CODE =
                CASE
                    WHEN
                        ISNULL( I.cTypeCode, '' ) NOT LIKE '[C,R]'
                        AND ISNULL( C.TYPE_CODE, '' ) LIKE '[C,R]'
                    THEN C.TYPE_CODE
                    WHEN ISNULL( I.cTypeCode, '' ) LIKE '[C,R]'
                    THEN I.cTypeCode
                    ELSE 'R'
                END,
            C.Book_Block =
                CASE
                    WHEN ISNULL( I.vcBookBlock, '' ) = '' THEN ISNULL( C.Book_Block, '' )
                    ELSE I.vcBookBlock
                END,
            C.Page_Lot =
                CASE
                    WHEN ISNULL( I.vcPageLot, '' ) = '' THEN ISNULL( C.Page_Lot, '' )
                    ELSE I.vcPageLot
                END,
            C.YEAR_BUILT =
                CASE
                    WHEN LEN( ISNULL( I.vcYearBuilt, '' ) ) <> 4 THEN ISNULL( C.YEAR_BUILT,
'' )
                    ELSE I.vcYearBuilt
                END,
            C.LOT_SIZE =
                CASE
                    WHEN ISNULL( I.vcLotSize, 0 ) !> 0 THEN ISNULL( C.LOT_SIZE, 0 )
                    ELSE ISNULL( I.vcLotSize, 0 )
                END,
            C.SQUARE_FEET =
                CASE
                    WHEN ISNULL( I.vcSquareFeet, 0 ) !> 0 THEN ISNULL( C.SQUARE_FEET, 0 )
                    ELSE ISNULL( I.vcSquareFeet, 0 )
                END,
            C.NUMBER_OF_BEDROOMS =
                CASE
                    WHEN ISNULL( I.vcBeds, 0 ) !> 0 THEN ISNULL( C.NUMBER_OF_BEDROOMS, 0 )
                    ELSE ISNULL( I.vcBeds, 0 )
                END,
            C.NUMBER_OF_BATHROOMS =
                CASE
                    WHEN ISNULL( I.vcBathrooms, 0 ) !> 0 THEN ISNULL(
C.NUMBER_OF_BATHROOMS, 0 )
                    ELSE ISNULL( I.vcBathrooms, 0 )
                END,
            C.NUMBER_OF_GARAGES =
                CASE
                    WHEN ISNULL( I.vcGarages, 0 ) !> 0 THEN ISNULL( C.NUMBER_OF_GARAGES, 0 )
                    ELSE ISNULL( I.vcGarages, 0 )
                END,
            C.NUMBER_OF_UNITS =
                CASE
                    WHEN ISNULL( I.vcNumberOfUnits, 0 ) !> 0 THEN ISNULL(
C.NUMBER_OF_UNITS, 0 )
                    ELSE ISNULL( I.vcNumberOfUnits, 0 )
                END,
            C.ZONE_CODE =
                CASE
                    WHEN ISNULL( I.vcZoningCode, '' ) = '' THEN ISNULL( C.ZONE_CODE, '' )
                    ELSE ISNULL( I.vcZoningCode, '' )
                END,
            C.vcDescription =
                CASE
                    WHEN ISNULL( I.vcNotes, '' ) = '' THEN ISNULL( C.vcDescription, '' )
                    ELSE I.vcNotes
                END,
            C.sdUpdated = @today,

            C.searchDate = @today,

            C.searchPrice =
                CASE
                    WHEN ISNULL( I.vcSearchPrice, 0 ) = 0
                    THEN C.searchPrice
                    ELSE CONVERT( MONEY, I.vcSearchPrice )
                END,
            C.UNMATCHED = I.tiUNMATCHED,

            C.SortValue =
                CASE
                    WHEN ISNULL( I.iSortValue, 0 ) !> 0 THEN C.SortValue
                    ELSE I.iSortValue
                END,
            C.pictureURL =
                CASE
                    WHEN C.PictureExists <> 0
                    THEN C.PictureURL
                    ELSE I.vcPictureURL
                END,
            C.PictureExists =
                CASE
                    WHEN C.PictureExists <> 0
                    THEN C.PictureExists
                    ELSE I.tiPictureExists
                END,
            C.FAXMLReportType =
                CASE
                    WHEN ISNULL( I.siFAXMLReportType, 0 ) = 0
                    THEN C.FAXMLReportType
                    ELSE I.siFAXMLReportType
                END
        FROM   tblPropertyImport I                                  
        INNER JOIN   CombineTable    C     (index=ix_Position)          
           ON  C.Position        = I.vcVendorRecordID
           AND C.iVendorID_FK   = I.iVendorID
        WHERE I.iVendorID    = @iVendorID

rollback
End

Snake wrote:
This may be a case of "parameter sniffing". See this article:
http://tinyurl.com/f9r2

Bob Barrows

Bob,
You are a lifesaver!

Michael

Show quoteHide quote

Bob,
I read your link and reviewed Ken Henderson's book covering "parameter
sniffing" and am still a little confused.  A proc is optimized the first time
it is executed, and I always provide the parameter values when I execute my
proc. Any sniffing would always see good parametric values. Providing
parametric defaults, EXECUTE WITH RECOMPILE, and using intermediate variables
all seem to solve the problem.  But I just can't get my brain around why the
parameter values I pass in are not effective.

Michael

Show quoteHide quote

Snake wrote:
Are you running the latest SQL Server service pack? If not, what version
are you running?

Parameter sniffing is normally beneficial, but sometimes the parameter
values can fool the query processor into selecting an inefficient plan.
I assume when you are using the parameter, SQL Server is selecting
another index or opting for a scan operation instead of using the index
you see when a local variable is used.

What might be happening is the following:
- Parameter sniffing causes SQL Server to examine the parameter value
and use that particular value at compile time to better detemine the
query plan. On the the first execution, if the parameter has a value
that _should_ cause SQL Server to perform a table scan or similar
operation, the execution plan is saved using that operation.
- The next time the procedure is run, SQL Server sees it has a plan in
cache and reuses it. Even if the parameter value this time would warrant
an INDEX SEEK for best performance.
- Executing WITH RECOMPILE will prevent SQL Server from using the old
plan, at the expense of creating a new one each execution.
- Occasionally, SQL Server may decide on a table scan operation
prematurely based on the statistics available in the indexes. If the
statistics are out of date, this can cause problems as well.
- If the statistics are up-to-date and SQL Server still selects the
wrong index based on your performance testing, then you can opt to use
an index hint. SQL Server is normally pretty good at index optimization,
but not always.
- In general, though, if the procedure is not executed with high
frequency, then I would probably create the procedure using the WITH
RECOMPILE clause and make a note to revisit the procedure at a later
time.

David,
I have experimented quite a bit with this over the last few days and feel
uncomfortable with the explanations offered so far. Something is being left
out.

I drop and create my test procedure before each execution, ensuring uniform
test conditions and ensure that the optimizer is not influenced by previous
executions. When people say things like ". . . that particular value at
compile time . ." it is not clear if they are talking about the values
provided in the Execute statement or the parameter defaults.  I use the same
parameter values on every test and only change the contents of the procedure,
sometimes changing the parameter defaults. I believe the optimizer has some
subtlety not mentioned yet. 

My test results suggest the following:  

(1) If you provide a parameter default, then the optimizer uses that and
ignores the value provided on the Execute statement.  How do I know this? My
test results  change when the default is changed, holding the execute
parameter constant and "good."

(2) If intermediate variables are used, the optimizer ignores the null
parameter defaults  and makes an educated guess (per MSDN). This appears to
be true as the test results change.

(3) The EXECUTE WITH RECOMPILE option does not yield the same query plan as
the first time a procedure is compiled and executed without the clause. How
do I know? I drop and create the proc prior to a test and get one result,
followed with the EXECUTE WITH RECOMPILE option and get another.  The compile
as a resut of the first execution of the proc are clearly different than
after the EXECUTE WITH RECOMPILE.  The parameter has a NULL default, and I
suspect this is involved somehow.

I am sure that some readers are quite upset by this but I suspect that most
of them have not really tested the party-line quite this thoroughly. Of
course, this all changes in November . . .


Michael

Show quoteHide quote

Snake wrote:
Show quoteHide quoteWhat does the procedure look like if the parameter can accept NULL
values? For example:

Select
  col
From
dbo.MyTable
Where
  MyCol = @Param

would return no rows if a NULL parameter is passed in since nothing
equals NULL. Why allow a NULL default in this case?


I don't know what your data looks like or how many rows and what
percentage of rows are expected to be returned with the various
parameter values. Sometimes, and I stress sometimes, parameter sniffing
can cause a problem.

In some tests I just ran, I have a procedure which accepts a single
parameter with a NULL default. The query uses a ">" with the parameter
so I can vary the number of rows returned based on parameter value. If I
run that procedure with a value that returns many rows, I see a scan
operation on the table as expected (tests reveal its faster than the
seek). If I then run the same procedure without a parameter (even though
the query cannot return any rows) I get another scan, when clearly a
seek would be faster. I clear the cache and buffers. I then run the
query first without a parameter, I get an index seek as expected. If I
run again with the parameter value, I get a seek again, which performs
worse than the original scan (consumes twice as much duration and about
20% more reads). Clearly, the problems with parameter sniffing are
demonstrated with the example.

To overcome the problem I would have to create the procedure with the
recompile option or execute it each time with a recompile. The proc is
small enough that the recompile overhead may me less than selecting the
wrong execution plan.

I assume you are either running SQL 2000 SP3a or SP4.

I'm not seeing the same behaviour you are with repsect to SQL Server
using the default parameter value (which I tested as NULL and using a
few different values that each cause a different plan). Each time, I see
my parameter value used at compile time to generate the execution plan.

To best clear the cache and buffers between executions, use:
dbcc freeproccache
dbcc dropcleanbuffers

If you want to post your tables, indexes, queries, total number of rows
and number of expected rows returned for each parameter type, we may be
able to shed more light on what you're seeing.

Could you post the schema of the tables, with indexes? Also, could you
execute the following statement and post the execution plan output
after running your update statement and sproc:

SET SHOWPLAN_TEXT ON

GO



-Alan

SELECT different columns based on the value of another column
Question about referencing UDFs
Torn over a normalizing decision
Mulitple Create Views in Query Batch
Strange Timeout problem.