Comparing Single-Processor and Dual-Processor Performance

We're converting a Visual FoxPro application to a Visual Basic .NET application. I wrote a simple, single-threaded Microsoft .NET application that moves data from the FoxPro database to the SQL Server 2000 database. The conversion process on my single, nonhyperthreaded Pentium 4 processor notebook computer takes 40 minutes. The same process on a dual-processor Pentium III 933MHz processor server takes more than 2 hours. Why did the dual-processor conversion take longer?

This question highlights a misconception about the nature of SQL Server parallel queries that many SQL Server novices share. The question also forces us to remember that all servers have bottlenecks.

You seem to assume that because the server has two processors, it will run faster than the single-processor notebook computer. Let's look at a typical workload to see whether we should expect a multiprocessor server to be faster than a single-CPU notebook computer with a faster processor.

SQL Server can't automatically make every query run in parallel. SQL Server doesn't break some queries—especially short, single-row INSERT statements—into individual steps for parallel execution because the cost of doing so is greater than running a serial query. Your application is performing a large number of single-row inserts into SQL Server, which won't run any of the inserts in parallel because single-row inserts are serial operations in SQL Server 2000.

So we can generalize the performance question to ask which server can more efficiently handle a serial stream of inserts: a dual-processor server or a single-processor server, in which the single processor has a faster clock speed than the dual processors. Because your application is single-threaded, most of the application's workload consists of a single server process ID (SPID) trying to perform INSERT statements as fast as it can.

Based on the information you provided in our subsequent email correspondence, I assume that the notebook has sufficient memory and that its disk throughput is more than sufficient to keep up with a single SPID submitting a stream of INSERT statements. If you don't have a memory or I/O bottleneck, the most likely cause of your performace problem is a CPU bottleneck. (A network bottleneck is unlikely because all the activity takes place on a single machine). This assumption makes sense because the workload is the result of a single-threaded application performing single-row inserts.

We'd often expect a 1-CPU machine with a faster processor to be more efficient than a dual-processor machine with slower processors. This is especially true when only one user is active on the system and the application is single-threaded.

In what situation could the server be faster? A lot of factors can alter the performance balance. The server might finish the benchmark faster, for instance, if the laptop disk is slow and caused the bottleneck when switching from CPU to I/O, if the laptop doesn't have adequate memory, if paging or other activity creates memory pressure that shifts the bottleneck from the CPU, or if you wrote the application in a multithreaded manner so that two SPIDs (or connections to SQL Server) simultaneously issue INSERT statements.

It can be dangerous to make assumptions about a database application's performance without deconstructing the application workload and considering how the machine responds.