Need a Row Count After Select Statement: What's the Optimal SQL Approach

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

There are only two ways to be 100% certain that the COUNT(*) and the actual query will give consistent results:

  • Combined the COUNT(*) with the query, as in your Approach 2. I recommend the form you show in your example, not the correlated subquery form shown in the comment from kogus.
  • Use two queries, as in your Approach 1, after starting a transaction in SNAPSHOT or SERIALIZABLE isolation level.

Using one of those isolation levels is important because any other isolation level allows new rows created by other clients to become visible in your current transaction. Read the MSDN documentation on SET TRANSACTION ISOLATION for more details.

how to get row count from Tibco Direct SQL or JDBC Query activity

I finally got what I need. I can just use XPATH "count" function directly from "SQL Direct" or "JDBC Query" activities output

"JDBC Query":
count($JDBC-Query/resultSet/Record) < 1000

"SQL Direct":
count($SQL-Direct/jdbcGeneralActivityOutput/unknownResultset/row) <1000

JDBC Query row count

Fastest way to count exact number of rows in a very large table?

Simple answer:

  • Database vendor independent solution = use the standard = COUNT(*)
  • There are approximate SQL Server solutions but don't use COUNT(*) = out of scope

Notes:

COUNT(1) = COUNT(*) = COUNT(PrimaryKey) just in case

Edit:

SQL Server example (1.4 billion rows, 12 columns)

SELECT COUNT(*) FROM MyBigtable WITH (NOLOCK)
-- NOLOCK here is for me only to let me test for this answer: no more, no less

1 runs, 5:46 minutes, count = 1,401,659,700

--Note, sp_spaceused uses this DMV
SELECT
Total_Rows= SUM(st.row_count)
FROM
sys.dm_db_partition_stats st
WHERE
object_name(object_id) = 'MyBigtable' AND (index_id < 2)

2 runs, both under 1 second, count = 1,401,659,670

The second one has less rows = wrong. Would be the same or more depending on writes (deletes are done out of hours here)

Total row count + select with limit

What about:

WITH  a AS (select *, count(*) over (range unbounded preceding)
FROM resgroups)
SELECT * from a order by foo limit 10 offset 10;

Now, I think you are actually better off to break this into two queries though because it looks like you are doing paging, effectively. If you select the count(*) first, and then decide how many pages you need (and maybe cache that result) then your subsequent partial queries can use an index, but in this case, every group of 10 will require a full sequential scan.

MySQL: Fastest way to count number of rows

When you COUNT(*) it takes in count column indexes, so it will be the best result. MySQL with MyISAM engine actually stores row count, it doesn't count all rows each time you try to count all rows. (based on primary key's column)

Using PHP to count rows is not very smart, because you have to send data from MySQL to PHP. Why do it when you can achieve the same on the MySQL side?

If the COUNT(*) is slow, you should run EXPLAIN on the query, and check if indexes are really used, and where they should be added.


The following is not the fastest way, but there is a case, where COUNT(*) doesn't really fit - when you start grouping results, you can run into a problem where COUNT doesn't really count all rows.

The solution is SQL_CALC_FOUND_ROWS. This is usually used when you are selecting rows but still need to know the total row count (for example, for paging).
When you select data rows, just append the SQL_CALC_FOUND_ROWS keyword after SELECT:

SELECT SQL_CALC_FOUND_ROWS [needed fields or *] FROM table LIMIT 20 OFFSET 0;

After you have selected needed rows, you can get the count with this single query:

SELECT FOUND_ROWS();

FOUND_ROWS() has to be called immediately after the data selecting query.


In conclusion, everything actually comes down to how many entries you have and what is in the WHERE statement. You should really pay attention on how indexes are being used, when there are lots of rows (tens of thousands, millions, and up).

Speeding up row counting in MySQL

So the question is

are there any techniques for speeding up these kinds of queries?

Well, not really. A column-based storage engine would probably be faster with those SELECT COUNT(*) queries but it would be less performant for pretty much any other query.

Your best bet is to maintain a summary table via triggers. It doesn't have much overhead and the SELECT part will be instantaneous no matter how big the table. Here's some boilerplate code:

DELIMITER //

CREATE TRIGGER ai_books AFTER INSERT ON books
FOR EACH ROW UPDATE books_cnt SET total = total + 1 WHERE status = NEW.status
//
CREATE TRIGGER ad_books AFTER DELETE ON books
FOR EACH ROW UPDATE books_cnt SET total = total - 1 WHERE status = OLD.status;
//
CREATE TRIGGER au_books AFTER UPDATE ON books
FOR EACH ROW
BEGIN
IF (OLD.status <> NEW.status)
THEN
UPDATE books_cnt SET total = total + IF(status = NEW.status, 1, -1) WHERE status IN (OLD.status, NEW.status);
END IF;
END
//

SQL Server: quickly retrieve row count for large table using where query

Try such a structure for your index

CREATE NONCLUSTERED INDEX [IX_HistoryData] ON [dbo].[HistoryData]
(
[Port] ASC,
[RecvTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Your Execution plan should look like this plan Mind the referenced IX_HistoryData not PK_HistoryData

PS. Your table is very simple, and I could easily create data for test, but I only have a local SQL Server (running on SSD) and results won't be relevant anyway.

Is it possible to specify condition in Count()?

If you can't just limit the query itself with a where clause, you can use the fact that the count aggregate only counts the non-null values:

select count(case Position when 'Manager' then 1 else null end)
from ...

You can also use the sum aggregate in a similar way:

select sum(case Position when 'Manager' then 1 else 0 end)
from ...


Related Topics



Leave a reply



Submit