Paging record sets in SQL Server 2005 - Part 2
In my previous post, I outlined my twist on the use of WITH, ROW_COUNT() and OVER to generate paged recordsets in SQL Server 2005. After creating the solution, I decided to run some performance tests to see how my method performed.
Test Bed
Client machine (custom-built desktop)
- Intel E6400 Core2 Duo
- 2 GB RAM
- 10K RPM Raptor system disk
- SQL Management Studio Express
Server (HP TC4400 laptop)
- Intel T7400 Core2 Duo
- 2 GB RAM
- 80 GB 7200 RPM system disk
- SQL Server 2005
I added 130,000 records to the content repository cmObjects table and queried to pull an arbitrary set of 20 records from within the full dataset, ordered by the objectname field, which is an indexed nvarchar field. Below is the query, followed by the client statistics captured from SQL Management Studio.
WITH cmObjects
(
SELECT ROW_NUMBER() OVER (ORDER BY objectname) AS Row, o.*, t.TypeName, null as total
FROM cmObject o
JOIN cmType t on o.TypeID = t.TypeID
)
SELECT *
FROM cmObjects
WHERE Row BETWEEN 10000 AND 10020
UNION
SELECT null,null,null,null,null,null,null,null,null,null,null,null,null, count(objectid) as total
FROM cmObject o
JOIN cmType t on o.TypeID = t.TypeID
First query
- Total Execution Time: 12812 ms
Second query
- Total Execution Time: 109 ms
Third query (new page range)
- Total Execution Time: 125 ms
Granting that my laptop is not an ideal test bed for a server application, I am nonetheless satisfied with the results of the test.
I decided to up the ante and see how the technique would work on millions of rows instead of a hundred thousand rows. I created a new query to pull against the content properties repository table cmProperty. The talbe has roughly two million rows of data in it. Below are the query and the client statistics:
AS
(
SELECT ROW_NUMBER() OVER (ORDER BY objectid) AS Row, p.propertyid, p.propertyname, p.objectid, ft.fieldType, null as total
FROM cmProperty p
JOIN cmPropertyType PT ON p.PropertyTypeID = PT.PropertyTypeID
JOIN cmFieldType ft ON pt.FieldTypeID = ft.FieldTypeID
)
SELECT *
FROM cmProperties
WHERE Row BETWEEN 100000 AND 100020
UNION
SELECT null,null,null,null,null, count(propertyid) as total
FROM cmProperty
First query
- Total Execution Time: 3828 ms
Second query
- Total Execution Time: 3843 ms
Third query (new page range)
- Total Execution Time: 7562 ms
Not great numbers, but perfectly acceptable given the server test bed. This query isn't a realistic use case, though, as there is typically no reason to pull an arbitrary set of records from the cmProperty table.
My current plan calls for migration of 500,000 records into this content system, which comes out to 500,000 rows in the cmObject table and 8 million rows in the cmProperty table. I will run further performance tests with the full dataset and post my findings.
There are no comments for this entry.
[Add Comment]