1

I have the following query in SQL Server:

select * from sales

This returns about a billion rows, and I need to process the data. I would like to show the progress while the data is being processed using something like the following:

res=conn.execute('select * from sales s1')
total_rows = ?
processed_rows = 0
step_size = 10000
while True:
    data = res.fetchmany(step_size)
    processed_rows += step_size
    print "Progress: %s / %s" % (processed_rows, total_rows)

Is there a way to get the total number of rows in a SQL query without running another query (or doing an operation such as len(res.fetchall()), which will add a lot of overhead to the above)?

Note: I'm not interested in paginating here (which is what should be done). This is more a question to see if it's possible to get the TOTAL ROW COUNT in a query in SQL Server BEFORE paginating/processing the data.

Dale K
  • 25,246
  • 15
  • 42
  • 71
David542
  • 104,438
  • 178
  • 489
  • 842
  • Possible duplicate of [What is the best way to paginate results in SQL Server](https://stackoverflow.com/questions/109232/what-is-the-best-way-to-paginate-results-in-sql-server) – T Tse May 03 '19 at 02:24
  • 1
    What you need is to paginate the result. The exact answer to your question would be something like `select count(*) from sales`, but that's still going to be terribly slow – T Tse May 03 '19 at 02:25
  • @ShioT I'm wondering if it's possible with mssql -- I'm not interested in paginating here, just seeing if it's possible to get the above. It seems like the answer is a "no". – David542 May 03 '19 at 02:26
  • @ShioT would doing something like the following add a lot of overhead to the query? `results=conn.execute('SELECT i.*, COUNT(*) OVER () FROM (select * from sales s1) i ')` – David542 May 03 '19 at 02:32
  • 1
    Counting the number of rows, so long as the `where` clause is well matched with an index, is usually very fast. But as always with SQL, you have to try it to see. You could try `declare @count int; select @count = count(*) from Sales; select *, @Count from Sales;` which should perform OK. – Dale K May 03 '19 at 02:40

0 Answers0