Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
314 views
in Technique[技术] by (71.8m points)

odbc - Need a row count after SELECT statement: what's the optimal SQL approach?

I'm trying to select a column from a single table (no joins) and I need the count of the number of rows, ideally before I begin retrieving the rows. I have come to two approaches that provide the information I need.

Approach 1:

SELECT COUNT( my_table.my_col ) AS row_count
  FROM my_table
 WHERE my_table.foo = 'bar'

Then

SELECT my_table.my_col
  FROM my_table
 WHERE my_table.foo = 'bar'

Or Approach 2

SELECT my_table.my_col, ( SELECT COUNT ( my_table.my_col )
                            FROM my_table
                           WHERE my_table.foo = 'bar' ) AS row_count
  FROM my_table
 WHERE my_table.foo = 'bar'

I am doing this because my SQL driver (SQL Native Client 9.0) does not allow me to use SQLRowCount on a SELECT statement but I need to know the number of rows in my result in order to allocate an array before assigning information to it. The use of a dynamically allocated container is, unfortunately, not an option in this area of my program.

I am concerned that the following scenario might occur:

  • SELECT for count occurs
  • Another instruction occurs, adding or removing a row
  • SELECT for data occurs and suddenly the array is the wrong size.
    -In the worse case, this will attempt to write data beyond the arrays limits and crash my program.

Does Approach 2 prohibit this issue?

Also, Will one of the two approaches be faster? If so, which?

Finally, is there a better approach that I should consider (perhaps a way to instruct the driver to return the number of rows in a SELECT result using SQLRowCount?)

For those that asked, I am using Native C++ with the aforementioned SQL driver (provided by Microsoft.)

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

If you're using SQL Server, after your query you can select the @@RowCount function (or if your result set might have more than 2 billion rows use the RowCount_Big() function). This will return the number of rows selected by the previous statement or number of rows affected by an insert/update/delete statement.

SELECT my_table.my_col
  FROM my_table
 WHERE my_table.foo = 'bar'

SELECT @@Rowcount

Or if you want to row count included in the result sent similar to Approach #2, you can use the the OVER clause.

SELECT my_table.my_col,
    count(*) OVER(PARTITION BY my_table.foo) AS 'Count'
  FROM my_table
 WHERE my_table.foo = 'bar'

Using the OVER clause will have much better performance than using a subquery to get the row count. Using the @@RowCount will have the best performance because the there won't be any query cost for the select @@RowCount statement

Update in response to comment: The example I gave would give the # of rows in partition - defined in this case by "PARTITION BY my_table.foo". The value of the column in each row is the # of rows with the same value of my_table.foo. Since your example query had the clause "WHERE my_table.foo = 'bar'", all rows in the resultset will have the same value of my_table.foo and therefore the value in the column will be the same for all rows and equal (in this case) this the # of rows in the query.

Here is a better/simpler example of how to include a column in each row that is the total # of rows in the resultset. Simply remove the optional Partition By clause.

SELECT my_table.my_col, count(*) OVER() AS 'Count'
  FROM my_table
 WHERE my_table.foo = 'bar'

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...