In sync query calls, one query causing other query to run slower. Why?

Question

Sorry for the long question, but I think this is an interesting situation and I couldn't find any explanations for it:

I was involved in optimization of an application that performed a large number of sequential SELECT and INSERT statements on a single dedicated SQL Server database.

The process needs to INSERT a large number of records into a table, but for each of them there should be some value mappings, which performed using SELECT statements on another table in the same database. For a specific execution, it took 90 minutes to run.

I used a profiler (JProfiler - the application is Java-based) to determine how much time does each part of the application take. It yields that 60% of the time was spent on INSERT method calls, and almost 20% on SELECT calls (the rest distributed in other parts).

After some trials, I came to this situation: I commented out the INSERT query that took 60% of the time. I was expecting for the total run time to be around 35 minutes, as I have removed 60% of the 90 minutes. But the whole process took the same 90 minutes (doing only SELECTs and nothing else), but each SELECT took longer this time!

Everything was running sync, there were no async calls. And there was only one single thread of execution. SELECT and INSERT queries are very simple, and don't have anything special, and they are on different tables, but on the same DB.

I tested with both the DB on the application machine, and on a remote network machine.

I can't think of any explanation for this, as the Profiler (Application profiler, not SQL Profiler) reported the changes in the method call times, and by removing INSERT statements SELECT statements took longer to run.

Can anyone give me some kind of explanation of what could have happened?

(there can't be cache / query optimization stuff, because the queries were run in sync, and in a single thread, and it was far from affecting the cache this much)

I should note that the bottleneck of the speed was in SQL server, using most of the CPU time.

Answer 1

The usual suspect for high CPU is lack of indexes. In this case, running SELECT may take longer than before because you have more data.

However, without some SQL examples with schema, it will be hard to tell.

Answer 2

You need to take into account the number of indexes on the insert table(s) and the definition and state of those indexes.

One possibility is that your INSERTs are causing a large number of page splits. Another is that you don't have any indexes, causing the SELECT lookups to table scan.

Try rebuilding the indexes before performing the INSERTS, and investigate how much fillfactor your indexes have. For tables with high inserts, I often set this to 75% (rather than default of 90%), but you need to take into account row sizes as this will increase the total number of pages required.

Answer 3

Could be that the inserts either:

a) impacted table/index statistics such that a better execution plan for the selects was chosen.

b) kept data required by the selects in the buffer.

Might be interesting to capture perfmon logs for comparison, paying particular attention to 'Buffer Manager/Buffer Cache Hit Ratio' and 'Page Life Expectancy', along with usual suspects i.e. CPU, Physical Disk etc.

Take a SQL profiler trace for the duration of both tests and run the output through a trace scrubber (http://msdn.microsoft.com/en-us/library/aa175800%28sql.80%29.aspx).

Answer 4

Your applicaiton blocked on database calls. When you measured the first time, the blocking occured on INSERT because perhaps those happened to be the calls that blocked first. When you removed the INSERT, the client code plough ahead and blocked on the next calls, the SELECT. There's really nothing special here, you simply removed one call and so the application simply blocked on the next call.

What you really need to investigate is the database blocking. Forget about the client side JProfiler and anything alike. Focus instead on what's happening on the database and use database trobleshooting methodologies. Likely your problem is contention and you block on locks.

Obvious problems will be visible right away by simply looking at the list of processes on the server (sys.dm_exec_requests). More subtle problems can be investigated by using the Waits and Queues methodology.