One statement for result & count(result)

[srudin]

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?

 

[bborissov]

SELECT A, B, C, COUNT(*) AS Cnt 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

Why you use ROW_NUMBER() in derived table when you didn't use it in Main select?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[srudin]

i do - it's part of the where statement. that's the way rowcount is supposed to be used in sql 2005.

 

[bborissov]

O, sorry.
I didn't read carefuly WHERE statement

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[srudin]

found a solution that works for me - don't think it will interest anybody but i'll post it here anyway in case anyone faces the same kind of problem i did it might give you at least some input.

the idea is still the same: to use a temporary table. but the trick is to only insert the rowid and the primary key in that table and to join only the 10 current result records with the rest of the data. the abstract syntax looks like this:

DECLARE @TempTable TABLE (
RowID INT,
OID UNIQUEIDENTIFIER
)

INSERT INTO @TempTable
SELECT ROW_NUMBER() OVER (ORDER BY @OrderClause) AS RowID, @MappingIdentityName AS OID
FROM [@MappingName]
WHERE @WhereClause

SELECT @FieldNames FROM
@TempTable INNER JOIN [@MappingName] ON [@TempTable].[OID] = [@MappingName].[@MappingIdentityName]
WHERE RowID BETWEEN @FromRow AND @ToRow

SELECT COUNT(OID) FROM @TempTable

works fine here and almost doubled the performance compared to the statements on top of this thread. just what i wanted...