#31 Sybase replacement for "select count(*) from"

[K.-M. Hansche]

This method counts rows in Sybase' ASE in a lot less time
and with less i/o than a standard count(*) from.

protected int fetchNumRowsSybase(Database db) throws SQLException {
PreparedStatement stmt = null;
ResultSet rs = null;
StringBuffer sql = new StringBuffer("select rowcnt from systabstats where id = object_id('");
sql.append(getName());
sql.append("') and indid = 0");

try {
stmt = db.getConnection().prepareStatement(sql.toString());
rs = stmt.executeQuery();
while (rs.next()) {
return rs.getInt(1);
}
return -1;
} finally {
if (rs != null)
rs.close();
if (stmt != null)
stmt.close();
}
}

[K.-M. Hansche]

Logged In: YES
user_id=11258
Originator: YES

Sorry, that should be:
and indid in (0,1)

[John Currier]

Logged In: YES
user_id=1264584
Originator: NO

Does it *significantly* reduce the overall amount of time that it takes SchemaSpy to run? This is database vendor specific...something that I try to avoid if at all possible and usually put into the .properties file.

John

[K.-M. Hansche]

Logged In: YES
user_id=11258
Originator: YES

Yes, it does. Sybase ASE performs a table scan for every select count(*). In our production environment (including a datawarehouse with approximately a third TB of netto data) this patch reduces the working time from several hours (about 8 iirc) to about half an hour.

Of course I would like to see a functionality to put the above statement into a .property, but didn't have the time to patch that myself.

Regards

Niki

[John Currier]

Logged In: YES
user_id=1264584
Originator: NO

Okay, that's a significant improvement. I'll get it in there when I can.

Thanks,
John

[John Currier]

Logged In: YES
user_id=1264584
Originator: NO

Klaus-Martin, can you create a feature request for this and reference this patch? That'd make it much easier to make sure it gets into the next release.

Thanks,
John

[John Currier]

Logged In: YES
user_id=1264584
Originator: NO

Ah, I just realized that I can directly change this into a feature request.

John

[John Currier]

Logged In: YES
user_id=1264584
Originator: NO

Can you try Subversion revision 420 and let me know if that resolves the problem? I don't have a Sybase database to try it out on.

Thanks,
John

[John Currier]

Implemented in release 4.0.0.

[SourceForge Robot]

This Tracker item was closed automatically by the system. It was
previously set to a Pending status, and the original submitter
did not respond within 14 days (the time period specified by
the administrator of this Tracker).