Microsoft SQL Server 2000

By Timothy Dyck  |  Posted 2003-07-07 Print this article Print

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.)

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.

"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 change—it 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

Timothy Dyck is a Senior Analyst with eWEEK Labs. He has been testing and reviewing application server, database and middleware products and technologies for eWEEK since 1996. Prior to joining eWEEK, he worked at the LAN and WAN network operations center for a large telecommunications firm, in operating systems and development tools technical marketing for a large software company and in the IT department at a government agency. He has an honors bachelors degree of mathematics in computer science from the University of Waterloo in Waterloo, Ontario, Canada, and a masters of arts degree in journalism from the University of Western Ontario in London, Ontario, Canada.

Submit a Comment

Loading Comments...
Manage your Newsletters: Login   Register My Newsletters

Rocket Fuel