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
AS
SET NOCOUNT ON if (@page = ‘propertyResults.aspx’)
BEGIN
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)
END
else
BEGIN
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)
END SET NOCOUNT OFF
GO

---

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.

---

]]>