Empty Stored Proc takes 8ms to complete?

Question

Empty Stored Proc takes 8ms to complete?

rmudway

SSC-Addicted

Points: 481
More actions
February 11, 2010 at 5:53 am

#215557

Hi,
We have a custom DLL written in C++ which calls an empty Stored Proc (through a thread) via the ADO
library and have found that it is taking 8ms to complete! When measuring the execution time from
within the thread we found that starting the call to the Stored Proc took approx 5ms, and then the
"empty" Stored Proc took 3ms to execute on the SQL Server (2008 Enterprise), giving us a total of 8ms
and no data has been transferred yet.
So i am describing two problems:
1) Starting the Stored Proc via ADO from within the DLL takes too long (5ms).
2) An "empty" Stored Proc takes 3ms to execute on the SQL Server and return.
We have also tried pinging the SQL Server from another computer (where the DLL is running) and this
returns within 1ms. So network is not the issue, but the ping is similar to what we have done with
the empty stored proc, as this too is doing no processing, it has no code to execute, so it only
returns.
My thoughts are that computers can process much more information in this amount of time, so this is
taking way too long and need to get both steps down to at least 2 or 3ms in total, can anyone help or
shead any light on this please?
Thanks in advance!!
Rich.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

You have to take into account the fact that you are either instantiating a connection or pulling one out of the connection pool. This will also add some overhead to your call. Since the procedure doesn't do anything, there's going to be a cache hit and a moment or two in the compiler while it tries to find the execution plan for the procedure and then it tries to, and decides not, compile the procedure. I'm sure I'm missing a few steps on my initial thought through this, but you can see how there's a heck of a lot more going on here than simply pinging the server. Then, you also have to take into account that there's probably a 2-3ms error in the measurement time... It all adds up. I pretty much assuming anything less than about 10ms is effectively zero.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Craig@Work SSCrazy Points: 2346 More actions · Answer 2

Hi,

I created this on my local desktop instance ...

CREATE PROCEDURE dbo.Test

AS

RETURN

GO

I set STATISTICS TIME ON

Executed the procedure, and sure enough the first time it took 16ms to compile and generate a plan doing nothing. It took about half that time next execution, presumambly fetching the plan from cache and loading into memory.

With subsequent executions, the execution time was reported as 0ms as the plan will have been kept in memory.

Is there a specific response time you are required to have (and why)?

rmudway SSC-Addicted Points: 481 More actions · Answer 3

Hi,

Thanks for the Stored Proc code with the Statistics option.

While i was running some tests i eventually needed to time how long the Stored Proc was taking inside SQL, so i ran the SQL Profiler. When i did, i found that there was many additional messages drowning my SQL call to execute the Stored Proc. These messages were FMTONLY and BROWSETABLE.

After more investigation i found that this was a side effect of ADO, which is a wrapper for C++ to make SQL calls. From what i have learned, these commands return Meta data on the parameters, even when the code explicity defines their type?

I have found that i could use an option "adEXECUTENORECORDS" and this tells ADO that there will no records returned.

This cut down the execution time from 8ms to 4ms, so there was a vast improvement.

But i am still trying to get this down further.

I also created SQL script to call the Stored Proc and found that SQL could execute it in approx 1.2ms, which to me sounds correct. The call to execute the Stored Proc from an external program (e.g VBScript) is taking 4ms, so with SQL only taking 1.2ms, there is still 2.8ms execute the Stored Proc.

Does anyone have any more ideas?

Thanks for everyone's suggestions so far!!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Cut down further to what? 4 ms to call a sp is pretty darn fast and you'll never get any complaints about that.

From then on assuming you may need to load a couple millions rows using that sp, then you need to look into bcp or ssis to import the data... which will beat the crap out of any sp you can every write.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 5

What's the final goal here? You can't get a completely free call to the database (TANSTAAFL always applies). Even if it shows up on Profiler as 0ms, it's probably not really 0ms and it won't always be 0ms because of possible contention with other processes.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning