Microsoft SQL Server 2000
Microsoft SQL Server 2000 While testing Microsoft SQL Server 2000, the issue became the JDBC (Java Database Connectivity) driver. We investigated this issue with the company that created the driver, DataDirect Technologies Inc., and with Microsoft itself, which did further testing in the months following our test. (The Microsoft SQL Server JDBC driver that Microsoft distributes is licensed from DataDirect.)"This statement causes problems for the driver because the server will not give the driver a server-side scrollable cursor if the statement is a batch," said Royce Willmschen, director of research and development at DataDirect, in Morrisville, N.C. "Therefore, we must emulate the scrollable cursor on the client side and process through the result set to find the last row." Willmschen suggested using JDBC to programmatically set a limit on the number of rows returned rather than doing this in SQL directly. Microsoft tested our application in its own lab and determined that replacing the "set rowcount" commands with an alternate syntax, "select top 500," resulted in much-improved performance (
see Microsoft SQL Server performance chart). This change folded the query limit constraint right into the select statement, eliminating the batch SQL statement.
As in the IBM DB2 case, this Microsoft test should not be directly compared with our numbers, but its clear the change did have a substantial positive impact on performance. Microsoft tested on a four-way server using slightly slower Intel Corp. Xeon CPUs than we used (550MHz versus 700MHz) and recorded a peak throughput of about 370 pages per second versus our peak throughput of 220 pages per second.
Microsoft didnt have exact performance figures for the application on its setup with our original "set rowcount" version, so we dont know precisely the performance delta this change introduced. However, the difference was substantial, according to Microsoft officials: "As I recall, it was over a doubling of performance with that changeit was a huge difference," said Greg Leake, director of Microsoft .Net Competitive Labs, Developer Division, in Redmond, Wash.
One other important subtlety that Microsoft noticed and wed like to pass along was a difference between the JSP (JavaServer Pages) version and the ASP (Active Server Pages) .Net version of the test application we used. In the JSP version, we did catalog queries using an "=" exact-match operator. With the ASP.Net version, a "like" and "%" wild-card operator was used that places an extra CPU burden on Microsoft SQL Server.
We did not compare the JSP and ASP.Net numbers against each other, an inappropriate comparison in any case because of other necessary changes between the JSP and ASP.Net testbeds. However, the applications were intended to be as close to identical as possible, and both should have used the same kind of comparison operators. Updated code can be downloaded from PC Magazines own database benchmark report.
Timothy Dyck can be reached at firstname.lastname@example.org.
The key issue, according to DataDirect, was the use of a batch SQL statement (one containing more than one command) combined with a bidirectional cursor. In particular, one commonly used page issues a SQL command to retrieve a set of books in a catalog query. The application limits the number of books returned to 500 by placing "set rowcount 500" and "set rowcount 0" commands around the SQL select statement.