CODEWITHSUNDEEP
c++sql-serverssmsc++builder
Mochu33
Mochu33

Reputation: 1

Problem executing stored procedure in my code

I had a stored procedure that returns a set of 8 integer values. At the beginning all seems to be executed in the right way but after some execution when I execute it in my C++ code using TADOQuery the execution time grows up but not in the SSMS in which the execution time still correct.

In a first step I try to evolve my stored procedure to make it more performance aware. I earned some execution time but the problem still present.

I try also to examine my code searching some absurd lines that may extend execution time. But I use the same code with other stored procedures and they don't show the same effect.

So I use the SQL Profiler to see what the hell is going on. Below you can see the differences between a good execution and a bad one.

Good execution

 CPU | Reads | Writes | Duration 
 93  | 13657 | 0      | 113

Bad execution

 CPU      | Reads    | Writes | Duration
 22090    | 31960629 | 0      | 25158

As you can see the values of reads and CPU access grows exponentially.

If I execute the same stored procedure in the SSMS query editor, the results are straight forward to the good execution.

I just want that my stored procedure were executed always with more or less the same duration regardless where I execute the query.

Upvotes: 0

Views: 120

Answers (2)

Mochu33
Mochu33

Reputation: 1

The problem was in the execution plan.

When the stored procedure is compiled, a new plan based on actual data is determined. After a few executions the plan get to old because the data change frequently.

So to achieve this problem I used the WITH RECOMPILE options for the stored procedure. I think it's not the best solution but it solves the problem and now I don't have the query timeout anymore.

In this way I try some other solutions to apply the recompile in the best moment but the only solution that works well is with this option.

Upvotes: 0

Matthew Baker
Matthew Baker

Reputation: 2729

Have seen this several times - it catches a lot of us. Check the settings against your call for things like ansi nulls on/off, other settings etc. Changes like that lead to different execution plans. See SQL faster in ssms than in application for details.

Upvotes: 1

Related Questions