IF( @value IS NULL ) BEGIN
		INSERT INTO #getEffectiveInitiatedValueTEMP
		SELECT	TOP 1
			ae.id, aer.newValue , aer.oldValue, ae.initiatedDate
		FROM	#tempApprovalEventRow aer,
			#tempApprovalEvent ae
		WHERE	ae.id = aer.eventId
		AND	DATEDIFF(dd, @significantEffectiveDate, ae.effectiveDate ) > 0
		AND 	( (@repriceApprovalEventId IS NULL ) OR (@repriceApprovalEventId <= 0 ) OR ( @repriceApprovalEventId > ae.id ) )
		AND 	DATEDIFF( dd, ae.effectiveDate, ae.initiatedDate ) >= 0
		AND 	aer.tableName = @tableName
		AND	aer.columnName = @columnName
		AND 	aer.idValue = @idValue
		AND	ae.status <> 'D'
		ORDER BY ae.initiatedDate ASC, ae.id ASC

		INSERT INTO #getEffectiveInitiatedValueTEMP
		SELECT	TOP 1
			ae.id, aer.newValue, aer.oldValue, ae.effectiveDate
		FROM	#tempApprovalEventRow aer,
			#tempApprovalEvent ae
		WHERE	ae.id = aer.eventId
		AND	DATEDIFF(dd, @significantEffectiveDate, ae.effectiveDate ) > 0
		AND 	( (@repriceApprovalEventId IS NULL ) OR (@repriceApprovalEventId <= 0 ) OR ( @repriceApprovalEventId > ae.id ) )
		AND 	DATEDIFF( dd, ae.initiatedDate, ae.effectiveDate ) >= 0
		AND 	aer.tableName = @tableName
		AND	aer.columnName = @columnName
		AND 	aer.idValue = @idValue
		AND	ae.status <> 'D'
		ORDER BY ae.effectiveDate ASC, ae.id ASC

		SELECT TOP 1 @value = oldValue
		FROM #getEffectiveInitiatedValueTEMP
		ORDER BY significantDate ASC, aeId ASC
	END

IF( @value IS NULL ) BEGIN
    EXEC new_StoredProc @value OUT
END

quote:

---

Originally posted by Transact Charlie

Do table variables require recompiles?

---

quote:

---

Originally posted by yosiasz

I don't know about table variable but have you looked into common tables...I have definitely seen performance increase when using common tables instead of rable variables. I do not know how common tables will perform with such a huge load..

2 cents

---

quote:

---

Originally posted by Transact Charlie

Do table variables require recompiles?

---

quote:

---

Originally posted by Transact Charlie

Thanks for all the tips:

Gail: "Why not reference the base table? Have you identified that doing slow is a performance problems and that querying a temp table is faster?"
The base table is an audit table for 2d time. It keeps track of *everything* for all employees. The way that the stored proc is written is that only a few rows in the base table are pertinent. The stored proc can make 15 separate selects on the base table each time it's called and it gets called a lot.
Because the table is constantly getting written to / updated and (occasionally) deleted from then the indices seem to become out of date really quickly. This can mean that sometimes the original stored proc takes *minutes* to finish when it should under a second.
I suggested that we should reindex the table more often but it was decided that we shouldn't do reindex except in the 1 hour downtime window available each day. I came up with the cache table idea to cut down the number of physical reads from the audit table (now only one read from the base table is required). That seems to solve the original problem well. But obviously has introduced this recompilation issue.


Peso : "The Query Optimizer always assumes there is one and only one record in a table variable because table variables does not have statistics.
That is why Query Optimizer sometimes choosed an incorrect plan.
My rule of thumb is that when the chance is high the table variable will hold more than one page of data, I change to temp table instead.
One page is the minimum amount of data read at any given time by SQL Server."
I think for this stored proc I can narrow down the data needed in the cache table some more. I think that for the vast majority of the time an employee will have under 10 rows that are pertinent to the task in hand (working out holiday entitlements at a point in time). I really only need 4 pieces of data for each line 2 DATETIME values and 2 FLOATS. If I manage to get down to this do you think a table variable is a good idea?

Sodeep : "I would definitely use KEEP PLAN to reduce recompiles in this scenerio? Also take advantages of index in Temp table for faster retrieval."
Added (KEEP PLAN) hint to the sp call -- will see what impact that has. Is it really worth adding index to temp table for around 20 rows (now that I'm narrowing down data even more). At the moment the temp table is a HEAP -- will it improve matters if I add a surrogate KEY to get a clustered index? for that many rows will the time taken making the index hurt more than help?

It will force to use same cache plan .Then you don't need to index at all. Why not try with Table variables if you are dealing with 20 rows? Compare performance with Temp tables.

Also are your TEMPDB Data files spread accross number of cores in your server? Is it on RAID 10 disk? This makes difference as well.

update :: I've added a check to find out if employee was in the worst case scenario (no relevant info) and if so not perform any more that the initial select. (and not bother making the temp tables) I think that will speed things up drastically for the load testing. I'd still like to get recommendations for further improvements though. A lot of our code is written like this.

NB : Also -- I managed to eliminate on of the temp tables completely and instead of INSERTING twice and SELECTING once now perform a SELECT from a derived table using UNIONS to join the different queries)

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

---