timeout issue

[ersatz]

Hi,

I have a stored procedure that was implemented last year.

This procedure is used once every month from aspnet page, to import data from csv file to sql server table and it takes less than 1 minute.

It was ok last month but yesterday, when the client executed the same storproc he had a timeout (after 10 minutes).

I recompiled the procedure which brought back the time to less than 1 minute.

In the future my client wants me to modify the storproc by adding “with recompile” option.
I would like to know your opinion on this matter. Is it a good idea adding this option?

I forgot to tell you that I have restored this db in another environment and the exec time was ok. The developer told me he thinks it is normal because after a restore, all stor proc will be recompiled.
Can someone explain me if really all storproc will be recompiled after a restore?


Does anyone have an idea what could be the cause for the timeout?

There is the error message:

Server Error in '/' Application.
--------------------------------------------------------------------------------

Error for Main Step: Object reference not set to an instance of an object.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Exception: Error for Main Step: Object reference not set to an instance of an object.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[Exception: Error for Main Step: Object reference not set to an instance of an object.]



Thanks in advance for your help

 

[gmmastros]

The "with recompile" suggestion is a method for solving the "Parameter Sniffing" problem. I encourage you to do a little research so that you can appreciate what causes this.

Personally, I see absolutely no problem with adding "With Recompile" to a stored procedure that takes a minute to execute and is run once a month. Usually, compiling a stored procedure is pretty fast, but when you have a stored procedure that is run VERY often and needs to perform very quickly, the recompile time can add up. So, the default is to not recompile the SP when it is run.

As an aside, I would encourage you to regularly rebuild your indexes and update your statistics. Parameter sniffing problems occur more often when statistics get out of date.

I don't know if stored procedures are recompiled after you restore a database, but I don't think they are. More research would be needed here.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ersatz]

Hi George,

Thanks very much for your fast answer.

You are a gold mine for people like me! I suppose you are connected to this forum 24 h....

Thank you for the details. Today I have changed the storproc by adding with recompile option and tomorrow I’ll verify the maintenance plan and I will make research about Parameter Sniffing...

Tanks again!