Hi,
Maybe you can cache the count and just query it on application startup.
(Then just + and - it as you add and remove)
Probably not the issue though :)
I'm guessing the inserts and removes are slowing it down. The only way I
can think (apart from Noel's varchar idea) is to not use an index (Not
sure if that works if you don't create a primary key?). Hopefully then a
table scan would match the record on the first row (because it is the
first record), so may actually be pretty fast. So my idea is that you
rely on the natural order of the database and hope that a table scan is
really quick. It's a long shot...
On 6/12/2013 5:26 PM, Wickman wrote:
Hi
I have a database with just one table with a primary int and a CLOB
column:
CREATE TABLE logs(id INT PRIMARY KEY AUTO_INCREMENT, message CLOB
The database is embedded in my app
There will never be more than one connection open against it at any
one time.
The table is used as a FIFO queue, so there are only a few queries
involved:
add = conn.prepareStatement("INSERT INTO logs (id, message)
VALUES(null, ?)");
peek = conn.prepareStatement("SELECT id, message FROM logs ORDER BY id
ASC LIMIT 1");
size = conn.prepareStatement("SELECT COUNT(*) FROM logs");
remove = conn.prepareStatement("DELETE FROM logs WHERE id = ?");
Ie. lots of "add last" and "remove first".
I tried a few things, but nothing that really did any difference
except not using the journal (which is not really an option :)
Any thoughts?
--
You received this message because you are subscribed to the Google
Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.
