In your profiling, do you determine whether the INSERT or the DELETE is the
significantly slower than the other?
On Friday, 6 December 2013 14:43:59 UTC+1, 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].
>> > 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.
