Any single selected should return only top 80000 records

Pughazendhi Chandrakasan 1 Reputation point
2023-07-19T16:47:23.4633333+00:00

Hi ,

Currently, I am preparing dynamic select queries and I also have a screen where users can give any select query or select UDF functions and generate reports or exports to Excel

I want to limit the number of rows in C# code BLL or data layer to 80000 when any select query is passed to SQL.

For example , Select * from tablename , which will return 900000 records. I have to optimize the query before passing it to the database, something like select top 80000 from tablename..

I tried SET ROWCOUNT, but it does not support UDF functions and TOP keyword fails when there is a top keyword in my dynamic query.

please need assistance.

Thanks in advance

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,438 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Viorel 119.7K Reputation points
    2023-07-19T17:05:37.9433333+00:00

    Maybe this will work:

    select top(80000) * from ( ... your current query ... ) t
    

    Show examples if this does not work.

    2 people found this answer helpful.

  2. Erland Sommarskog 116.5K Reputation points MVP
    2023-07-20T12:07:22.0666667+00:00

    Thanks for your clarifying comment. I will have to say that I think you are barking up the wrong tree.

    Consider this query:

    SELECT MAX(somecol) FROM LargeTable
    

    Say that there is no index on LargeTable. Say furthermore that it has two milliard rows. The query will return exactly one row, but it will have to scan the full table and then sort those two milliard values to find the max. That's quite a resource-intensive operation.

    Can you avoid this? Yes, you can intercept the query and change it to:

    SELECT MAX(somecol) FROM LargeTable WITH TABLESAMPLE (80000 ROWS)
    

    But in that case it is very likely that the query will return a different result. Is that acceptable for your users? I would not expect so.

    And overall, the very idea that you would limit or alter the queries and change the result, is something that your users are not likely to be keen on.

    Setting a cap of the number of rows returned may make some sense if you want to reduce the traffic on the network, and the load on the client side. However, if you are looking into improve the performance on database level, it is not a good idea at all, for reasons I have discussed. Rather, if you want to avoid wild queries brining the system down, you should look into using Resource Governor. With Resource Governor you can set a cap on how much resources that processes in a certainly workload group can take, and if a query in a low-priority group needs a lot, it will be throttled and take its time. I should add that Resource Governor is only available in Enterprise Edition.

    Then again:

    Because we have millions of records in db .

    If you only have "millions of rows", there is little reason to restrict anything, because that is still a very moderate amount of data.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.