How to predict result set row count?

views:

answers:

+1 Q:

How to predict result set row count?

I have an application where I create a big SQL query dynamically for SQL server 2008. This query is based on various search criteria which the user might give such as search by lastname, firstname, ssn etc.

The requirement is that if the user gives a condition due to which the formed query might return a lot of rows(configurable for max N rows), then the application must send back a message instead to the user saying that he needs to refine his search query as the existing query will return too many rows.

I would not want to bring back say, 5000 rows to the client and then discard that data just to show the user an error. What is an efficient way to tackle this issue?

+4 A:

why not just show the first N rows, AND the message? limit the rows returned to N+1 and if the count of returned rows is > N then show the message :)

if you just want to check how many rows WOULD be returned by a query then select count(id) (or some column name) instead of select *

oedo 2010-05-25 18:08:33

We limit this way too.

gbn 2010-05-25 18:21:21

The requirement is not to show the user any result if the query would return too many rows.And problem with doing a count is that the query can become huge with multiple INTERSECTS, UNIONS etc.... and it is sent from .NET to DB. So doing a count would mean that I need to send the full query once with count, and if its within limits, then send the query all over again to SQL. Means double bandwidth requirement.Any other ideas?

Saurabh Kumar 2010-05-26 02:23:51

there are no other ideas. either you return the actual data and use it, or you return a count first and then query again for the data. you could do this in a stored procedure but it would still have to do 2 queries if the count is < N. however, if you LIMIT to N+1 then at least you're only returning that much data maximum, and will still be able to fulfil your requirements. i would definitely recommend showing N rows and a message though if possible.

oedo 2010-05-26 08:39:26

ansaurus

tags:

views:

answers:

How to predict result set row count?

related questions