Possible to get resultset rowcount with query results?

Question

Hi, Currently I need to run two queries to get both the total amount of items in my resultset as well as the resultset. Is it possible to just get the resultset count as well as the resultset in one pass to the database. I am trying to optimize my code so I don't have to make 2 passes to the database as these individual select statements already take minutes to run. I am looking for a solution in both oracle sql and ms sql because I use both. I am also using Microsoft's Oracle access libraries to get to the Oracle database and not the Oracle access libraries.

Thank you.

Answer 1

If you are iterating through the result set, wouldn't it be possible to increment a counter in each iteration?

One tactic might be to add a column count(*) as RESULTSET_SIZE?

Answer 2

There is no property of OracleCommand or oracleDataReader that shows the fetched rows. The number of rows is known after fetchin all rows.

But if you fetch all data into an resultset, then you have the row count in resultset.Tables[0].Rows.Count as stated above. This has no extra costs on the database, because the Count is a property of the row collection. The index number depends on the amount of tables in your resultset.

Answer 3

Depending on your Oracle version you can use the analytic function count( ) to return a column containing the number of total rows.

http://download.oracle.com/docs/cd/B28359%5F01/server.111/b28286/functions032.htm#i82697

select a.*,
       count(*) over() totalRows
  from table1 a;

Answer 4

OK this might be what you are after: @@ROWCOUNT

It returns the number of rows affected by the users last database operation. So you dont need to add a column or do another pass, just do the query then ask for @@ROWCOUNT:

SELECT @@ROWCOUNT as 'Rows_Returned'

EDIT:

This only works for SQL server. For Oracle use SQL%Rowcount

Hope this helps