A fast row count with DatabaseMetaData.getIndexInfo() / tableIndexStatistic / CARDINALITY?

[Daniel Tripp <dt...@mdisolutions.com>]

Hello all.  I am looking for a fast way to find out the number of rows in a
table.  

I am hoping for something faster than "select count(*) from X", which can
sometimes take a bit too long for me.  On one 2-million-row table of mine,
it takes 17 seconds on my machine.  Maybe I'm greedy, but I had an idea that
I could get the row count with DatabaseMetaData.getIndexInfo() instead (and
hopefully this would be faster than "select count(*) from X").  But
unfortunately the data returned by getIndexInfo() are not filling the bill.

The javadocs for java.sql.DatabaseMetaData.getIndexInfo() state:
"CARDINALITY int => When TYPE is tableIndexStatistic, then this is the
number of rows in the table...."

(http://java.sun.com/j2se/1.5.0/docs/api/java/sql/DatabaseMetaData.html#getI
ndexInfo(java.lang.String,%20java.lang.String,%20java.lang.String,%20boolean
,%20boolean))

And that is the nugget that I am after.  But Derby's
DatabaseMetaData.getIndexInfo() seems never to return a row with TYPE ==
DatabaseMetaData.tableIndexStatistic.  The following program (which produced
the same results on Derby 10.4.1.3 and 10.3.1.4) shows what I mean:

import java.sql.*;
public class GetNumberOfRowsViaMetaData {
	static public void main(String args_[]) throws Exception {
		Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
		String url =
"jdbc:derby:testDatabase-"+System.currentTimeMillis();
		Connection conn =
DriverManager.getConnection(url+";create=true");
		
		conn.prepareStatement("create table THETABLE (I integer, S
varchar(100))").execute();
		conn.prepareStatement("create index THEINDEX_ON_I on
THETABLE (I)").execute();
		conn.prepareStatement("create index THEINDEX_ON_S on
THETABLE (S)").execute();
		for(int i=0; i<42; ++i) { 
			PreparedStatement stmt =
conn.prepareStatement("insert into THETABLE values (?, ?)");
			stmt.setInt(1, i);
			stmt.setString(2, ""+i);
			stmt.execute();
		}
		
		ResultSet rset = conn.getMetaData().getIndexInfo(null, null,
"THETABLE", false, false);
		while(rset.next()) {
			String[] columns = new String[]{"TABLE_NAME",
"INDEX_NAME", "TYPE", "CARDINALITY"};
			for(int i=0; i<columns.length; ++i) {
				System.out.println(columns[i]+" =
'"+rset.getString(columns[i])+"'");
			}
			
			if(rset.getShort("TYPE") ==
DatabaseMetaData.tableIndexStatistic) {
				System.out.println("--- Success ---");
			}
			System.out.println("---");
		}
	}
}

When I run this program, I get the following output:

TABLE_NAME = 'THETABLE'
INDEX_NAME = 'THEINDEX_ON_I'
TYPE = '3'
CARDINALITY = 'null'
---
TABLE_NAME = 'THETABLE'
INDEX_NAME = 'THEINDEX_ON_S'
TYPE = '3'
CARDINALITY = 'null'
---

Note that a TYPE of 3 is DatabaseMetaData.tableIndexOther, which is not
useful to me for my row-counting purposes.  (I need tableIndexStatistic
(0).)

Also note that "--- Success ---" is NOT printed.  When I run this same code
a Microsoft SQL Server database, though, it is printed.

I realize that the JDBC docs don't say that a row with TYPE equal to
tableIndexStatistic is /required/ to be returned by getIndexInfo().  But I
am wondering if anyone can help me out by commenting on any of this.  In
particular:

- why Derby's DatabaseMetaData.getIndexInfo() doesn't return a TYPE ==
tableIndexStatistic row 
- how Derby might be made to return one, if possible 
- any alternate way of achieving my goal of a fast row count 

Thanks in advance.

RE: A fast row count with DatabaseMetaData.getIndexInfo() / tableIndexStatistic / CARDINALITY?

[Daniel Tripp <dt...@mdisolutions.com>]

That's an idea but I don't think it would be appropriate for my project,
since the code that is doing this row-counting is executed rarely.  Thanks
though.
 

-----Original Message-----
From: Bryan Pendleton [mailto:bpendleton@amberpoint.com] 
Sent: May 3, 2008 11:43 AM
To: Derby Discussion
Subject: Re: A fast row count with DatabaseMetaData.getIndexInfo() /
tableIndexStatistic / CARDINALITY?

> - any alternate way of achieving my goal of a fast row count

How accurate does your count need to be? One idea would be to set up some
sort of periodic background task which computes the current sizes of your
tables and stores that information in another table in your database.

Then, when you needed the count, you could simply read it from your
stored-counts table.

You could adjust the frequency of the background task to improve the
accuracy of the count.

thanks,

bryan

Re: A fast row count with DatabaseMetaData.getIndexInfo() / tableIndexStatistic / CARDINALITY?

[Bryan Pendleton <bp...@amberpoint.com>]

> - any alternate way of achieving my goal of a fast row count 

How accurate does your count need to be? One idea would be to
set up some sort of periodic background task which computes
the current sizes of your tables and stores that information in
another table in your database.

Then, when you needed the count, you could simply read it
from your stored-counts table.

You could adjust the frequency of the background task to
improve the accuracy of the count.

thanks,

bryan