i need to show a datagrid in a webapp that allows paging & sorting. it's supposed to show only 10 records at a time and the total number of records in the footer. i'm using MS SQL Server 2005.
to accomplish that i made a complex select-statement that 1. finds all the records matching some criterias, 2. sorts the records acording to the users wishes and 3. returns only the current 10 records to display. then i make another select-statement that 1. finds all the records matching some criterias (as above) and 2. returns the count of these records.
i have to return only the 10 current records (instead of returning all the records and make the sorting/paging in the webapp) for performance reason.
now my question: isn't there a way to include the counting in the first statement? this would significantly boost performance as the time consuming aspect of my statements is the finding of the records - which needs to be done twice.
let me give you an abstract example of my current statements:
SELECT A, B, C FROM
(SELECT ROW_NUMBER() OVER(ORDER BY A) AS RowID, A, B, C
FROM Table1
WHERE B = 'x')
AS Table1
WHERE RowID BETWEEN 11 AND 20
SELECT COUNT(*)
FROM Table1
WHERE B = 'x'
can i include the count somehow in the first statement?
i tried using group by clauses but that doesn't work with text/xml fields (and i have some of them).
i also tried creating a temp table, inserting the 'matching' records and the selecting the 10 current records and the count from this temp table, but the performance gain executing the queries was more or less lost by the time it took to create and fill the temp table.
any other ideas?
to accomplish that i made a complex select-statement that 1. finds all the records matching some criterias, 2. sorts the records acording to the users wishes and 3. returns only the current 10 records to display. then i make another select-statement that 1. finds all the records matching some criterias (as above) and 2. returns the count of these records.
i have to return only the 10 current records (instead of returning all the records and make the sorting/paging in the webapp) for performance reason.
now my question: isn't there a way to include the counting in the first statement? this would significantly boost performance as the time consuming aspect of my statements is the finding of the records - which needs to be done twice.
let me give you an abstract example of my current statements:
SELECT A, B, C FROM
(SELECT ROW_NUMBER() OVER(ORDER BY A) AS RowID, A, B, C
FROM Table1
WHERE B = 'x')
AS Table1
WHERE RowID BETWEEN 11 AND 20
SELECT COUNT(*)
FROM Table1
WHERE B = 'x'
can i include the count somehow in the first statement?
i tried using group by clauses but that doesn't work with text/xml fields (and i have some of them).
i also tried creating a temp table, inserting the 'matching' records and the selecting the 10 current records and the count from this temp table, but the performance gain executing the queries was more or less lost by the time it took to create and fill the temp table.
any other ideas?