.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

With Recompile

Posted By:      Posted Date: September 27, 2010    Points: 0   Category :Sql Server


I have stored procedure which is based on tables that one of them has nearly 4M records and is being updated constantly. After a nearly a month my report which is using this stored procedure become very slow. Runs for ever. If I am recompiling the stored procedure then everything is fine and everyone is happy. 

Do I need to add "With Recompile" to the beginning of this stored procedure or I need to create a job which every week or so will recompile this stored procedure automatically. 

P.S. This report is being run mostly and maybe average is being called 15 times per hour on regular days. In closing day maybe more. 

Any suggestion would be nice and helpful.

Thanks in advance.

Best Wishes, Arbi --- Please vote if you find this posting was helpful or Mark it as answered.

View Complete Post

More Related Resource Links

With Recompile and Execute as clause in SQL 2005 Stored Procedure



I am working on an asp.net application which uses quite a few sql 2005 procedures. Due to some change in requirement I had to modify a couple of the procedures.

During this change, I found that most of the procedures contain clause With Recompile, Execute As Caller.

I am planning to refactor the code in procedure but not sure if I can remove these clauses. Can any one please help me with these?

Thanks in advance.

Script Transformation Does Not Recompile


I have a package that was originally developed using BIDS 2005 for SSIS 2005. The package uses a script transformation as an output to write specially formatted flat files. The original script transformation was written using VB (as was the only option at the time). 

I was running 32-bit Windows XP Professional at the time.

I recently upgraded to SSIS 2008 and BIDS 2008. After the package was upgraded, the script transformation was dropped and recreated so it could be rewritten in C#.

Up until this point, everything had worked fine.

I just upgraded my workstation to Windows 7 64-bit and I am still using BIDS 2008. Today, I modified the code in the script transformation to add some additional output to the flat file. 

After saving the package (without error), it was run in the BIDS debugger.

The changes I made did not result in the output file changing at all. The script editor in the package showed my changes, but every time the package was run, the output file reflected the state of the file before I made my code changes.

I'm fairly certain this is because the code is not pre-compiliing when I save the package.

To test this, I deleted the component and recreated it with the new code and it worked.

Has anyone else encountered this in SSIS 2008? I know this was a problem with 64-bit environments and SS

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend