none
Time taken for sp execution from ado.net in some time

    Question

  • Hi,

    I have a sql server 2012.I have one SP. If I execute that SP alone in query analyser, the execution happened with in 2 sec.

    But I have used the same SP from ado.net, then it will take too much slow to complete execution. This is also happening in in

    some time only, not on every time.

    However, after made SET ARITHABORT ON in database properties, then it was executing very fast like lightening from ado.net.After made as ON, was executing very fast up to 5 days. After 5 days, again it back to too much slow, then again I changed back as    SET ARITHABORT OFF, then again executing very fast like rocket...Why this behaving like inconsistently. Please help to resolve this issue?                                                                                                                                Thanks & Regards, Dileep

    Thursday, February 8, 2018 10:36 AM

Answers

  • Hi Dileep,

    Set the arithabort setting that better works for your case. But this seems a clear problem with the table statistics getting out of date. So check the tables used by that SP, and create a maintenance job that runs daily on off hours, and do this as one of the steps:

    UPDATE STATISTICS schema_name.table_name1;

    UPDATE STATISTICS schema_name.table_name2;

    ...

    UPDATE STATISTICS schema_name.table_nameN;

    Other option, it is change slightly the SP code, and include OPTION RECOMPILE. See example on https://blogs.msdn.microsoft.com/robinlester/2016/08/10/improving-query-performance-with-option-recompile-constant-folding-and-avoiding-parameter-sniffing-issues/

    Hope that helps.

    Saturday, February 10, 2018 11:23 AM

All replies