[Yanel-dev] Boost / DB Performance

[Cedric Staub]

Hi

On Mon, May 02, 2011 at 09:41:57AM +0200, Balz Schreier wrote:
> I have the following issue:
> in our boost DB, we have two large tables (the nodes table and the table
> where the number of hits are counted).
> 
> indexes are all there and the queries are fast (tested via postgres
> command).
> 
> BUT: before the "java.sql.Connection" object can be used, it tests the
> connection first to the DB, before the object is handed over to your code.

I'm not sure that's where it's coming from, are you sure it's not from
the repository implementation?

> The query looks like this: SELECT 1 FROM <tablename>;

That should be in order to check if <tablename> exists or not, as the
query fails if the table doesn't exist but always succeeds if it does.
It should be in the repository implementation somewhere if you'd like to
change it.

> the problem with that query is, that it takes 4 seconds (we have > 3 Mio.
> rows in the tables).

Whoa, I never anticipated it would take this long (after all, it should
only select a constant value). So I googled around a bit and found this
for Postgres: http://www.peterbe.com/plog/pg_class

Not sure if the first query there is any faster, but the second one
certainly has to be (altough it will break HSQLDB compatiblity).

So this could still be coming from java.sql.Connection though... In that
case, I wouldn't know how to change it.

Cheers
Cedric
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 836 bytes
Desc: not available
URL: <http://lists.wyona.org/pipermail/yanel-development/attachments/20110502/bae0d52c/attachment.pgp>