Force SQL Server to recompile stored procedures every time they run (SQL Server 7/2000)

This is a solution for a very specific problem, and it's one that you'll hardly ever use, but it's important to know about that one scenario where it can save your neck. Ordinarily, stored procedures are only recompiled if they're no longer in the procedure cache. But if a stored procedure's execution plan is still in the cache, then SQL Server reuses the compiled stored
procedure and its existing execution plan. This is almost always the best course of action. Almost always, but not always.
Sometimes, however, reusing an existing plan doesn't offer the most efficient performance. Imagine, for example, that your stored procedure accepts a parameter that determines the nature
of a JOIN operation. The results can vary in a big way, so you wouldn't want your procedure to be locked into an execution plan that might be completely inappropriate for that JOIN. In a highly
specialized case like this, you might want to force SQL Server to recompile the procedure every time the procedure runs. Doing so comes at a performance cost, but this might be offset by the
savings you gain in not executing the procedure with an awful compiled execution plan. Consider carefully whether to use this approach (or whether to re-engineer the over-design of your
application to avoid this situation in the first place). Should you need to instruct SQL Server to recompile each time, add the WITH RECOMPILE directive to the procedure, like this:
CREATE PROCEDURE ProcName
@.Param int /* ... other parameters */
WITH RECOMPILE
AS /* ... procedure code follows */

If we omit "WITH RECOMPILE", what will be the consequence? Thanks

WITH RECOMPILE can kill an Asp.net application because HTTP is stateless. The better solution is to force SQL Server to put all your stored procs in the procedure cache on start up. There is a stored proc in the Master called SP(system stored proc) Procoption you can use it to auto start all your stored procs. Recompile is modified in SQL Server 2005 you can recompile only the line you need then your solution will be ok for now there are alternatives. See code below the only value for option is Startup and value is true for ON and false for OFF. Hope this helps.

sp_procoption[@.ProcName =]'procedure'
,[@.OptionName =]'option'
,[@.OptionValue =]'value'

|||

Please kindly elaborate more on: "WITH RECOMPILE can kill an Asp.net application because HTTP is stateless." What do "kill" and "stateless" mean here? Thanks again.

|||

You get the best performance if all your stored procs are in the procedure cache all the time WITH RECOMPILE will not allow that, so everytime your stored proc is accessed your user will wait for SQL Server to recompile the stored proc before executing it. In Asp.net some processes will time out before your stored proc will execute. Kill means your code will timeout and stateless means a protocol without state HTTP is one of them. Your users will wait for SQL Server which is session to finish before objects on your pages can be accessed. The first thing to know about stored procs is avoid Recompile even in Windows appilcation. Hope this helps.

For deleting duplicate row?

Hi,

I want to delete duplicate row from a very big table. Actually this table is used by a SP, and it's a very important. Due to duplicate record entry it's falling. I use bellow method for discarding the dulicate record.

PLz tel me it's the most efficent way to this job or u have some other way

1. I drop the primary key

2. Then I let all the duplicate record came into the table

3. then I removed them by using Group by clause and setting rowcount(1 - group by count).

4. Put primary key back and update the statistics.

Code is

If Exists (select * from SYSINDEXES where name='PrimaryKey' and id=Object_id('AdjustmentTransactions'))
DROP INDEX AdjustmentTransactions.PrimaryKey

Insert into AdjustmentTransactions
(UrnABS, UrnBar, .................Description)
Select a.UrnAbs,
a.UrnBar,
a.TxnUrn.....................
a.Description
from TxnProcess a
where a.InsUpFlag = 'I'
and a.Processed = 'N'
and ASCII(b.TxnType) = 65

Set @.row_count=0
Declare dup_cursor cursor for
Select UrnBAR,TxnUrnBarPat,count(*) counts from AdjustmentTransactions
group by UrnBAR,TxnUrnBarPat having count(*) > 1
Open dup_cursor
Fetch next from dup_cursor into @.VUrnBAR,@.VTxnUrnBarPat,@.count

While (@.@.Fetch_Status = 0)
Begin
Select @.row_count=@.count-1
Set rowcount @.row_count
Delete from AdjustmentTransactions where UrnBAR=@.VUrnBAR and TxnUrnBarPat=@.VTxnUrnBarPat
Fetch next from dup_cursor into @.VUrnBAR,@.VTxnUrnBarPat,@.count
End
Set rowcount 0
Close dup_cursor
Deallocate dup_cursor
If not Exists (select * from SYSINDEXES where name='PrimaryKey' and id=Object_id('AdjustmentTransactions'))
CREATE UNIQUE INDEX [PrimaryKey] ON [dbo].[AdjustmentTransactions]([UrnBAR], [TxnUrnBarPat]) ON [PRIMARY]
Update Statistics AdjustmentTransactions

Thanks

Sandipan

Is it SQL Server 2000 or 2k5 ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

it's sql server 2000

Thanks

sandipan