question

Rich 1 avatar image
Rich 1 asked

Empty Stored Proc takes 8ms to complete?

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.

sql-server-2008stored-procedures
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

2 Answers

·
Scot Hauder avatar image
Scot Hauder answered

If 3ms is unacceptable you are in for a surprise when the stored proc actually contains code to read or write data. Multiple executions of the procedure could vary by 20ms or more. If your app is this time sensitive you may want to load all data into memory and work with it there

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

graz avatar image
graz answered

How many times did you run the test? Just a single time or in a loop multiple times?

What are you using to time it? What resolution does it have?

Do you actually have a requirement to process faster or do you just think it should be faster?

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.