【正文】 is clustered by ID, and fields IntField and TextField have nonclustered indices. Because of the clustered index, the response time when sorting by ID is significantly smaller pared to the response time when sorting by IntField and TextField. This pattern repeats in Figure 7and Figure 8: the response time when sorting by a clustered index is shorter that the response time when sorting by a nonclustered index. Comparing Figure 6, Figure 7 and Figure 8 with Figure 5 it bees obvious that using either clustered or nonclustered indices provides significant improvements in the time required for data fetching. The presence of indices has no impact on the response time when the sorting and paging is done in the web page on the web server using the SQL stored procedure from Code 1. Results are identical to those shown in Figure 3 and Figure 4. A peculiar property of Figures 58 is the presence of two peaks. They appear when sorting is done on a nonindexed field (all curves in Figure 5), or a field with a nonclustered index (IntField and TextField in Figure 6, ID and IntField in Figure 7, ID and TextField in Figure 8). This means that there are two different groups of time responses for the SQL stored procedure in Code 2. The problem lies in the second select statement ―SELECT * FROM testTableWHERE ID @LastID ORDER BY ID ASC‖ in Code 2. We detected that the response time is much longer when the input parameter @pageNumber 18000. @LastID is smaller for smaller values of @pageNumber. Consequently, the SELECT statement sorts and returns a larger data set, and the time needed for its execution increases. The SQL server uses the index file to identify the ordering of records, and then joins the index file with the records from the table testTable, and finally returns every column in the record (note the use of the * sign which means that all table columns are returned). As the size of the recordset increases, the SQL server uses more memory to sort the recordset. If the recordset consumes more memory than what is available to the SQL server process, then the SQL server starts to use virtual memory which is much slower than the RAM. In our test environment, SQL server starts using the virtual memory when the number of records in the recordset is larger than 820,000 (first 18,000 pages with 10 records each are skipped). The above argument holds for both cases sorting is done on a nonindexed field or a field with a nonclustered index. However, when the sorting is done by the clustered index field (ID in Figure 6, TextField in Figure 7, IntField in Figure 8), then it can be noticed that there is a single peak. Records in the table are already physically ordered by the clustered index field. We repeated the measurements for a different number of records in the table: , , , and records. The aim was to test the dependency of the response time on the number of records. As expected, the response time is larger for larger number of database records. Response time grows faster with the number of records for server sorting and paging pared to SQL server sorting and paging. Figure 9 Figure 12 show the relation between response times averaged over 500 tests and the number of records in the table .testTable. The fastest response time and the slowest growth with the number of records in the table testTable is achieved when using an SQL stored procedure with a clustered index, followed by an SQL stored procedure with a nonclustered index,followed by an SQL stored procedure without index, followed by web server sorting and paging. Other relations between the response times as shown in Figure 3Figure 5 are valid irrespective of the number of records have been preserved. The only difference worth of mentioning is shown in Figure the number of records in the table is smaller (. 500,000) and the clustered index is on TextField, then there is only one group of response times when sorting by ID and IntField since the memory consumption is small enough not to trigger usage of virtual memory. Furthermore, the response time when sorting by ID and IntField and 500,000 records is significantly smaller pared to Figure 7 for 1,000,000 records. Above mentioned tests were repeated in a distributed deployment scenario: MS SQL server was installed on one physical server, and IIS web server was installed on another server. We have observed dramatic increases in the response time depending on the work speed for the test cases where the sorting and paging is done on the web server and consequently significant amounts of data travel over the work. SQL sorting and paging The problem with the second select statement from Code 2 mentioned before in Section 4 can be solved by this modification of the storeprocedure: CREATE TABLE t(x INT) SET ROWCOUNT @PageSize INSERT INTO t SELECT [int] FROM testTable WHERE [int] @LastID ORDER BY [int] ASC SELECT testTable.* FROM t LEFT JOIN testTable ON t.[x] = testTable.[int] Code 3. Modifications to the SQL Stored procedure from Code 2. “ SELECT *‖ statement from Code 2 is broken into two parts: First part orders the records by the indexed field and stores only the indexed field into a temporary table t. Only @PageSize records arestored. No join is done between the index and the records in the table testTable, and thus the execution time