excessive recompiles | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

excessive recompiles

three questions: (1) why is this recompiling? (i assume DML + temp table reference)
(2) how can i get this to stop recompiling?(excessive recompiling)
(3) why does option (keepfixed plan) not stop it from recompiling?
CREATE PROCEDURE dbo.sptLogListingViewAdd
@Page varchar(50),
@ApplicationID int,
@ListingID int = null
SET NOCOUNT ON if (@page = ‘propertyResults.aspx’)
INSERT dbo.LOG_LISTING_VIEW(PropertyID,OrganizationID,ReferenceNumber,Page,[Date],ApplicationID)
SELECT L.ListingID,OrganizationID, ReferenceNumber,@Page,getdate(),@ApplicationID
FROM #List L JOIN dbo.Listing LS with (nolock) on L.listingID = LS.listingID –option (keepfixed plan)
INSERT dbo.LOG_LISTING_VIEW(PropertyID,OrganizationID,ReferenceNumber,Page,[Date],ApplicationID)
SELECT ListingId,OrganizationID, ReferenceNumber,@Page,getdate(),@ApplicationID
FROM dbo.Listing with (nolock)
WHERE ListingId= @ListingID –option (keepfixed plan)

http://www.sql-server-performance.com/stored_procedures.asp will have all questins answered
read it before. still would like someone to give me their thoughts to my questions if possible.

You will never get this particular query to stop recompiling. You will get recompiles in a couple places: 1. When you hit the temp table creation. This one is a given.
2. When you hit the IF statement. You can limit your recompiles, and possibly eliminate then by: 1. Using a table variable if you don’t have that many rows.
–BTW, where are you creating the temp table. I don’t see it in your post. 2. Using a function for each of the INSERT statements.
–Basically, just use sptLogListingViewAdd as a wrapper to test for @page.
–You would then run the rest of the procedure in a seperate function for each of the IF possibilities.
–This avoids recompiles completely a lot of times. Remember, SQL is optimized for set operations. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
1. Similar to Derrick’s idea, better create two separate procs to cover both inserts.
2. If it is possible use table function instead of temporary table.