Recompile of stored procedures - URGENT

[De Veirman Marino]

We are developing a production/management solution for the photo finishing sector. We need a performance of 1 order priced per second.
If we run the procedure once we dont have a dramatical performance loss due to recompilation of the stored procs.
If we have about 3 consecutive sessions we find the performance loss to be at a rate of about 200 - 500 %.
We can't afford this. On the site of msdn we found some reasons why sql server needs to recompile, but since the structure of our db can't be changed in such a manner that this would resolve the problem we need an alternative.
All help is greatly appriciated.

[ec]

A few things to check, none of which are related to the structure of your database: are you using a lot of temp tables in this proc? if so, you'll want to investigate the KEEP PLAN hint, as temp tables whose values change often will cause a recompile. There is a Knowlege Base article on this. The other thing to check is whether the proc that keeps being recompiled is called from within another proc. This also often causes a recompile. if that's the case, try merging the called proc into the calling proc, and see if that alleviates the recompile.


------------
De Veirman Marino at 4/11/01 1:45:45 AM

We are developing a production/management solution for the photo finishing sector. We need a performance of 1 order priced per second.
If we run the procedure once we dont have a dramatical performance loss due to recompilation of the stored procs.
If we have about 3 consecutive sessions we find the performance loss to be at a rate of about 200 - 500 %.
We can't afford this. On the site of msdn we found some reasons why sql server needs to recompile, but since the structure of our db can't be changed in such a manner that this would resolve the problem we need an alternative.
All help is greatly appriciated.

[De Veirman Marino]

Hi,

Thanks for the reply but we have recompile problems on several stored proc's and the biggest problem is the stored proc that calculates the price
There is a complex structure for pricing (promo, customercard advantages, ...) and the stored proc is launched 3 times per order for the different kinds of prices (customer, consumer, branch). This proc isn't called from another sp and doesn't use temp tables.
It does use a table to store the temp data in, this table has a high change rate because every time the proc runs it creates, updates and deletes the temp records.
The option "Auto update statistics" is disabled.

We do use Microsoft Transaction Server for the multitier enviremont the standard isolation level used is "Serialisable" so we use optimizer hints to limit locking level.

Can this be a reason ?


------------
ec at 4/11/01 9:29:53 AM

A few things to check, none of which are related to the structure of your database: are you using a lot of temp tables in this proc? if so, you'll want to investigate the KEEP PLAN hint, as temp tables whose values change often will cause a recompile. There is a Knowlege Base article on this. The other thing to check is whether the proc that keeps being recompiled is called from within another proc. This also often causes a recompile. if that's the case, try merging the called proc into the calling proc, and see if that alleviates the recompile.


------------
De Veirman Marino at 4/11/01 1:45:45 AM

We are developing a production/management solution for the photo finishing sector. We need a performance of 1 order priced per second.
If we run the procedure once we dont have a dramatical performance loss due to recompilation of the stored procs.
If we have about 3 consecutive sessions we find the performance loss to be at a rate of about 200 - 500 %.
We can't afford this. On the site of msdn we found some reasons why sql server needs to recompile, but since the structure of our db can't be changed in such a manner that this would resolve the problem we need an alternative.
All help is greatly appriciated.

[Me]

See Q243586, if you haven't already.


------------
De Veirman Marino at 4/11/01 1:45:45 AM

[De Veirman Marino]

We just installed a SQL Server 7.0 Enterprise edition and most of the recompiles disappeared !!!
We need to test it further, but still the difference between both is phenomenal.

Why isn't this in the paper?

Thanks to everyone who replied.


------------
De Veirman Marino at 4/11/01 1:45:45 AM

We are developing a production/management solution for the photo finishing sector. We need a performance of 1 order priced per second.
If we run the procedure once we dont have a dramatical performance loss due to recompilation of the stored procs.
If we have about 3 consecutive sessions we find the performance loss to be at a rate of about 200 - 500 %.
We can't afford this. On the site of msdn we found some reasons why sql server needs to recompile, but since the structure of our db can't be changed in such a manner that this would resolve the problem we need an alternative.
All help is greatly appriciated.