Geek Answers Handbook

Select the top 1000, but know how many lines there are?

SQL Server 2005

I have 10 million rows in the database and run select (with lots of "where" and joints .. pretty complicated). The results are presented in a grid (I think goolge results), and because of this, the user cannot use more than 1000 results.

So, I am limiting my SQL TOP 1000.

Problem . The user still wants to know that 5432 results were found for his search.

Can I get this information without paying the price (or, in other words, still getting the speed advantages that the "top 1000" gives?)

Assumptions - Suppose that TOP 1000 contains 1000 rows of 100K . Thus, even a network travel cost of 100K can be a problem.

Conclusions No free lunch! you can get an elegant way (accepted answer), but it still takes longer than a more expensive operation (i.e. counting all the results). In real life, I will go using two SQL approaches, one of which will return 1000 rows for display, and the other - ASYNC and update some AJAX panel with the results of count (*), which will take much more time on the computer.

+3
source share
6 answers

It is very simple in SQL 2005. Why are there so many long answers?

SELECT TOP 1000 x,y,z, COUNT(*) OVER () AS TotalCount
FROM dbo.table
+9
source

Personally, I would choose two statements that fall into the database. One for retrieving an account, one for retrieving the first 1000 entries.

, , .

-- Get the count
select count(*) from table where [criteria]

-- Get the data
select [cols] from table where [criteria]
+5

"-1000", 5000 .

( ), , , THEN, , 1000 .

:

1) Count (*), , 1000, ( ).
2) , , 1000 .

, , ( 100 000 !)

0

SQL Server 2005, CTE . :

;WITH Search_Results AS
(
     SELECT TOP(@system_max_rows)
          my_column1,
          my_column2,
          ROW_NUMBER() OVER
          (
               ORDER BY
                    -- Your order criteria here
          ) AS row_num,
          COUNT(my_column1) OVER (PARTITION BY '') As total_count
     FROM
          My_Table
     -- Put any joins here
     WHERE
          -- Put WHERE criteria here
)
SELECT
     my_column1,
     my_column2,
     row_num,
     total_count
FROM
     Search_Results
WHERE
     ((row_num - 1)/@rows_per_page) + 1 = CASE
                WHEN ((total_count - 1)/@rows_per_page) + 1 < @page_number THEN ((total_count - 1)/@rows_per_page) + 1
                ELSE @page_number
           END
OPTION (RECOMPILE)

, , . , / . . system max rows , , , . 1000, @page_number = 1 @rows_per_page = 1000.

0

, , , :

.

, ( , *) ( !). , ?

, , .

, , , .

, , - , . , , , .

-2

More articles:


All Articles