db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Daniel Tripp" <dtr...@mdisolutions.com>
Subject A fast row count with DatabaseMetaData.getIndexInfo() / tableIndexStatistic / CARDINALITY?
Date Fri, 02 May 2008 21:51:05 GMT
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.



Mime
View raw message