Passing parameters to stored procedure mystery.

Question

Passing parameters to stored procedure mystery.

Mike Oloughnane

Grasshopper

Points: 17
More actions
September 26, 2005 at 9:47 am

#152559

I have a stored procedure which contains a normal Update statement which references 1 parameter (int) in its predicate. The proc takes 5 hours to run. I then modified the proc to pass the parameter to a variable and then used the variable in the predicate rather than the parameter. The proc now runs in under two minutes! I have had a lead developer verify this and he is amazed.
This is on SqlServer 2000. I have rebuilt the indexes and updated the statistics. I posted this on the MSDN forum and it somehow did not stick . . .
Can you give me a hint as to why this is happening? I don't think this behavior is limited to this situation as I have been perplexed by some stored procedure behavior in other procs around here. My boss thinks I am crazy (but she has no explanation herself.)
Here is a test proc containing the actual production Update. Note the VendorId parameter being washed through a variable which oddly enough greatly improves performance.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER procedure testproc (
  @iVendorIDx INT,
  @todayx smalldatetime
&nbsp 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
     ON C.Position  = I.vcVendorRecordID
     AND C.iVendorID_FK   = I.iVendorID
  WHERE I.iVendorID    = @iVendorID
rollback
End
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

It's called parameter sniffing. Basically the first time you execute the SP a plan is generated and saved. But that plan is generated based on the value of the parameter given. If that parameter is uncharacteristic of the application, a bad plan can be generated and therefore cause scans where seeks should be used (imagine a date driven report where 10 years of data where requested on the first call resulting in a clustered index scan but where the normal calls request one month at the time which would normally require a simple seek).

The way to go around that is to recompile the sp and use this little trick : Create a new variable for each parameter in the proc. Copy the values of the parameters to the variables and use THOSE variables in the statement. The optimizer won't be able to sniff the parameters because they are not used in the query (looking useless).

Mike Oloughnane Grasshopper Points: 17 More actions · Answer 2

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 ". . . parameter is uncharacteristic . ." 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 by 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. I had a senior DBA review some of this and he was shocked. Of course, this all changes in November . . .

AndrewMurphy SSCertifiable Points: 5586 More actions · Answer 3

"...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."

There's only been 1 explanation so far on this site....and going on the quality of the past (and vast) advice from Remi (aka RGR'us), I'd accept and follow it. Parameter sniffing sounds to the unitiated like myself when I first came acrosss the term, like a strange behaviour, but it's a well described occurrance and given it's effects and the simple solution to avoid it, it's not a pain to follow it. Hopefully this quoted beneficial improvement will challenge you (and me) to look closer at other areas of your (and mine) applications to see where other imporvements can be made.

Remember when performing performance testing to use the "dbcc dropcleanbuffers" and "dbcc freeproccache" statements between tests to ensure independent results.

saleem-200718 Valued Member Points: 69 More actions · Answer 4

Amazing!!!!!!!!!!!!!

I want to know more about this.

Can anybody really judge this topic well.

Ian Yates SSCoach Points: 19738 More actions · Answer 5

There must be 50 posts on this site about the topic - it is true, and makes good sense when you think that SQL Server has to make some assumption about the data you wish to retrieve when it compiles the SP - so it uses the parameter defaults.

Put "Parameter Sniffing" into Google - you get many reputable links, even from blogs hosted by MSDN ( see http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx&nbsp

Yes it may sound strange, but it is true