CODEWITHSUNDEEP
c#sqllimit
fishmong3r
fishmong3r

Reputation: 1434

How to measure the result of an sql query?

I have a simple tool for searching in a given db. The user can provide numerous conditions and my tool puts the sql query together based on that. However I want to prevent the query to be executed in case it returns too many records. For e.g. in case the user leaves all the filters blank then the query would pull all the records from the db which would take tens of minutes. Of course it's not necessary for any of my users. So I want some limitation.

I was thinking about running a count() sql query with the same conditions before each 'real' query, but that takes too much time.

Is there any option to measure the records 'during' the query and stop it if a certain amount is being reached? Throwing some exception asking the user to refine the search.

Upvotes: 0

Views: 298

Answers (4)

JPK
JPK

Reputation: 1364

This depends on your application and how you want it to work.

If your only wanting to displaying data on a table and setting a maximum size to your query is enough. You can use TOP in your select statement.

SELECT TOP N [ColumnName]

But considering you said a count takes too much time then I think your concerned about handling a very large data set and maybe manipulating it not necessarily just getting a limited set of data from the query.

Then one method is to break apart the job into chunks and run across the job size so grab the first N rows then the next N rows and repeat until there is no more values to be returned. You can also have record keeping for rollbacks and checkpoints to ensure data integrity.

Similar questions maybe: query to limit records returned by sql query based on size of data

How to iterate through a large SQL result set with multiple related tables

Upvotes: 0

user1948635
user1948635

Reputation: 1409

Following on from the answer above, if you are working with large amounts of data, select top N, with the fast query option.

E.g.

SELECT TOP 101 [ColumnName]
FROM [Table]
OPTION (FAST 101)

Upvotes: 0

Michael Ord
Michael Ord

Reputation: 9

You could run a test query to search the database with the user defined options and only return the id field of the returned results, this would be very quick and also allow you to test the count(). Then if all is ok then you can run the full query to return all of their results.

Upvotes: 1

Patrick Hofman
Patrick Hofman

Reputation: 157048

I use this approach:

State that you want to fetch AT MOST 100 rows. Construct your query so it returns at most 101 rows (with TOP N or the more generic ANSI way by filtering on row_number). Then you can easily detect whether there is more. You can act accordingly, in my case, show a 'read more'.

Upvotes: 3

Related Questions