Yeah I'm not sure it would work. The table would need to be created
without an index, is that what you did first?
On 6/12/2013 9:43 PM, Wickman wrote:
Hi
Caching the head is an idea, thanks. But it's adds and removes that
takes time as you say.
I tried your approach without any index and it made no difference at
all, using these guys:
SELECT message FROM logs LIMIT 1
DELETE TOP 1 FROM logs
Den fredagen den 6:e december 2013 kl. 13:36:46 UTC+1 skrev Kartweel:
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] <javascript:>.
> To post to this group, send email to [email protected]
<javascript:>.
> Visit this group at http://groups.google.com/group/h2-database
<http://groups.google.com/group/h2-database>.
> For more options, visit https://groups.google.com/groups/opt_out
<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.
--
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.
