Why sleeping between each data request from database gives me better performance?

Question

I have an application that query set of records from sql server 2008 database, the performance is good when there is sleep between each data request (set of queries) than without sleeping. The following is the pesudo code for my scenario:

for i =1 to end
GetData
Process
//adding sleep here gives better performance

I have run the sql profiler and found that the same queries take much time if the application executes without sleep. I am using C# on .Net 3.5 and sql server 2008 Can you explain why?

P.S

The SQL sever is on another machine, no one work on this machine during my test, I request data over network from WCF service that makes local queries, process and return results, SQL profiler gives me the execution time of each query and it is high when there is no sleep in my application

Answer 1

Did you check the load on the SQL server? At a guess, you will find that with the sleep, the DB isn't very loaded, but without the sleep it maxes out.

Answer 2

It sounds like potential parameter sniffing issues, but the delay between each submission can not be that great when you are 'sleeping' it, so you need to check the query plan being submitted and whether the statement is being parameterised, or whether you are compiling a new plan for every execution of the statement.

As with any performance tuning and optimisation, you have to ask yourself a few basic starting questions: What other load is on the SQL? What is the IO Subsystem behind the SQL, have you profiled it to check for problems? What is the query plan being generated, is it appropriate, is it parameterised, or are you flooding the plan cache?

It would be very difficult on such limited information to answer categorically as to 'why', we can only provide ideas as to what you should look at next.

Answer 3

Find out where the performance is changing. Is it at the database side? At the client side? Turning profiling on for SQL Server should show you how long each of the requests is taking. Using profiling on the client should show you where its time is going.

It does sound very odd, admittedly.

Is SQL Server on the same machine, or is it going across the network?

How much sleep do you need to add for it to have an effect?

Answer 4

Going out on a limb here, but is GetData Asynchronous? I'm thinking something like that it can't match and reuse the Execution Plan for some reason.

I.e. does performance stay good even if you only sleep between first request and second request but not the rest??

Answer 5

Hi everyone! Sorry for writing in this section, but it's my first question here and I don't have the permission to create a new topic... Well, my question for you is if have any of you heard about Jentla software? I've heard that it can be used to manage more than 10.000 sites from within Joomla.. it's perfect for my company, but can somebody tell me how can I install it? I'm looking forward for your reply! Thx