SQL Server Search, how to return the total count of rows?

Question

Hello,

I have another post which resulted in this

SELECT DISTINCT
     a.ArticleID, 
     COUNT(*) AS KeywordMatch,
     a.Headline,
     a.ShortDescription,
     a.CategoryID,
     a.ArticleSectionImage,
     a.DatePublished
    FROM 
     Article a
    JOIN SearchWords sw ON a.ArticleID = sw.ArticleID
    WHERE
     EXISTS 
     (
      SELECT 
        1 
      FROM 
        iter_charlist_to_tbl(@temp, ' ') s 
      WHERE
        s.nstr = sw.SearchWord
     )
     AND
      a.ArticleState = 3 
    GROUP BY 
     a.ArticleID, a.Headline, a.ShortDescription, a.CategoryID, a.ArticleSectionImage, a.DatePublished
    ORDER BY (KeywordMatch) DESC, (a.DatePublished) DESC

I am using this along with Linq-to-SQL to create paging for the users. The problem is, that i need to know how many records my search returned (total rows), to display the correct arrows for the user.

This is my Linq-to-SQL Query:

  int iPageNum = pageNumber;
  int iPageSize = (int)pageSize;

  results = data.SearchArticles(searchString).Skip((iPageNum - 1) * iPageSize).Take(iPageSize).ToList();

Any ideas?

Is my Linq-to-SQL pulling all records from the database? or does it create a query that only selects the records that i needs? How can i peak at the query?

Answer 1

since you are fetching the entire set anyway with the data.SearchArticles part why not do this:

var results = data.SearchArticles(searchString);
var count = results.Count();

results = results.Skip((iPageNum - 1) * iPageSize).Take(iPageSize).ToList();

Depends a lot on the size of the return results as to whether this is OK.

Answer 2

One approach is to have two queries: one that returns a count, and another that returns the data.

You can put them both in one SP with multiple result sets to avoid an extra DB round-trip. I explain a data paging example (including providing a count) in detail in my book: Ultra-Fast ASP.NET. My approach doesn't use LINQ (since it doesn't support multiple result sets), but it's also faster.

Here's a query that should count the number of rows in the result (not tested, but it should be close):

;WITH SearchArticles (aid, cnt, headline, descr, cid, img, datepub) as (  
SELECT DISTINCT
            a.ArticleID, 
            COUNT(*) AS KeywordMatch,
            a.Headline,
            a.ShortDescription,
            a.CategoryID,
            a.ArticleSectionImage,
            a.DatePublished
        FROM 
            Article a
        JOIN SearchWords sw ON a.ArticleID = sw.ArticleID
        WHERE
            EXISTS 
            (
                    SELECT 
                      1 
                    FROM 
                      iter_charlist_to_tbl(@temp, ' ') s 
                    WHERE
                      s.nstr = sw.SearchWord
            )
            AND
                    a.ArticleState = 3      
        GROUP BY 
            a.ArticleID, a.Headline, a.ShortDescription, a.CategoryID,
            a.ArticleSectionImage, a.DatePublished
) SELECT COUNT(*) FROM SearchArticles

Answer 3

After reading your comments on the other answers:

SQL Server does not have built in paging. So, by default, a query will fetch all rows. If a query is not fetching all rows then your ResultSet object is doing the paging for you. If the resultset is not paging, there is no need to iterate through all of the results in order to check its Count() property, it should just be set correctly as SQL Server does return the row count along with the data.

If you are worried about fetching too much data in one go and your resultset doesn't page, then you need to select the count first and then build a query that can return the page of data that you want.

Aside: You can leave the distinct off your select. Since you are aggregating and grouping you are guaranteed to have distinct result already.