Can I get the rowcount before executing a stored procedure?

Question

I have some complex stored procedures that may return many thousands of rows, and take a long time to complete.

Is there any way to find out how many rows are going to be returned before the query executes and fetches the data?

This is with Visual Studio 2005, a Winforms application and SQL Server 2005.

Answer 1

make a stored proc to count the rows first.

SELECT COUNT(*) FROM table

Answer 2

You mentioned your stored procedures take a long time to complete. Is the majority of the time taken up during the process of selecting the rows from the database or returning the rows to the caller?

If it is the latter, maybe you can create a mirror version of your SP that just gets the count instead of the actual rows. If it is the former, well, there isn't really that much you can do since it is the act of finding the eligible rows which is slow.

Answer 3

Unless there's some aspect of the business logic of you app that allows calculating this, no. The database it going to have to do all the where & join logic to figure out how line rows, and that's the vast majority of the time spend in the SP.

Answer 4

You can't get the rowcount of a procedure without executing the procedure.

You could make a different procedure that accepts the same parameters, the purpose of which is to tell you how many rows the other procedure should return. However, the steps required by this procedure would normally be so similar to those of the main procedure that it should take just about as long as just executing the main procedure.

Answer 5

You would have to write a different version of the stored procedure to get a row count. This one would probably be much faster because you could eliminate joining tables which you aren't filtered against, remove ordering, etc. For example if your stored proc executed the sql such as:

select firstname, lastname, email, orderdate  from 
customer inner join productorder on customer.customerid=productorder.productorderid
where orderdate>@orderdate order by lastname, firstname;

your counting version would be something like:

select count(*) from productorder where orderdate>@orderdate;

Answer 6

Not in general.

Through knowledge about the operation of the stored procedure, you may be able to get either an estimate or an accurate count (for instance, if the "core" or "base" table of the query is able to be quickly calculated, but it is complex joins and/or summaries which drive the time upwards).

But you would have to call the counting SP first and then the data SP or you could look at using a multiple result set SP.

Answer 7

It could take as long to get a row count as to get the actual data, so I wouldn't advodate performing a count in most cases.

Some possibilities:

1) Does SQL Server expose its query optimiser findings in some way? i.e. can you parse the query and then obtain an estimate of the rowcount? (I don't know SQL Server).

2) Perhaps based on the criteria the user gives you can perform some estimations of your own. For example, if the user enters 'S%' in the customer surname field to query orders you could determine that that matches 7% (say) of the customer records, and extrapolate that the query may return about 7% of the order records.

Answer 8

A solution to your problem might be to re-write the stored procedure so that it limits the result set to some number, like:

SELECT TOP 1000 * FROM tblWHATEVER

in SQL Server, or

SELECT * FROM tblWHATEVER WHERE ROWNUM <= 1000

in Oracle. Or implement a paging solution so that the result set of each call is acceptably small.

Answer 9

Going on what Tony Andrews said in his answer, you can get an estimated query plan of the call to your query with:

SET showplan_text OFF
GO
SET showplan_all on
GO
--Replace with call you your stored procedure
select * from MyTable
GO 
SET showplan_all ofF
GO

This should return a table, or many tables which will let you get the estimated row count of your query.

Answer 10

You need to analyze the returned data set, to determine what is a logical, (meaningful) primary key for the result set that is being returned. In general this WILL be much faster than the complete procedure, because the server is not constructing a result set from data in all the columns of each row of each table, it is simply counting the rows... In general, it may not even need to read the actual table rows off disk to do this, it may simply need to count index nodes...

Then write another SQL statement that only includes the tables necessary to generate those key columns (Hopefully this is a subset of the tables in the main sql query), and the same where clause with the same filtering predicate values...

Then add another Optional parameter to the Stored Proc called, say, @CountsOnly, with a default of false (0) as so...

Alter Procedure <storedProcName>
@param1 Type, 
-- Other current params
@CountsOnly TinyInt = 0
As
Set NoCount On

   If @CountsOnly = 1
       Select Count(*) 
       From TableA A 
          Join TableB B On   etc. etc...
       Where < here put all Filtering predicates >

   Else
      <Here put old SQL That returns complete resultset with all data>

  Return 0

You can then just call the same stored proc with @CountsOnly set equal to 1 to just get the count of records. Old code that calls the proc would still function as it used to, since the parameter value is set to default to false (0), if it is not included

Answer 11

It's at least technically possible to run a procedure that puts the result set in a temporary table. Then you can find the number of rows before you move the data from server to application and would save having to create the result set twice.

But I doubt it's worth the trouble unless creating the result set takes a very long time, and in that case it may be big enough that the temp table would be a problem. Almost certainly the time to move the big table over the network will be many times what is needed to create it.