> 2008/9/29 Milan Babuskov <milanb@...>:
>> Daniel Albuschat wrote:
>>> Every now and then, but in what seems a specific interval, the
>>> execution of the update-statement takes exactly 60 seconds.
>>> I suggest there's some timeout occuring here, but the application has
>>> exclusive access to the database, so there are no concurrent
>>> transactions besides the two this application creates.
>>>
>>> Do you have any idea what is happening here?
>> Garbage collection? To test you can, for example, try disabling GC
>> completely and see if the problem goes away.
>
> I still don't know what this timeout of 60 seconds caused. But I made
> numerous optimizations to the application and database access and,
> after finishing all these optimizations and this 60-seconds-issue
> going away, I found the main reason for the slowdown.
> I did something like "insert into table(id, name, sortid) values
> (gen_id(table_gen, 1), :name, (select max(sortid) from table) + 1)".
> The subselect grew ever bigger and slower as more entries have been
> inserted. However, those entries were supposed to be in the 100 to
> 1.000 datasets-scale, but a subtile bug in the application caused the
> table to be filled with 100.000+ datasets instead.
>
> Stupid, I know, but that part of the source was a bit hidden and it
> looked so innocent when scrolling through it. ;-)

Yeah, isn't that always the case? ;-)

Btw, MAX can be speed up with a DESCENDING index on the field in question.




--
Best Regards,
Thomas Steinmaurer
LogManager Series - Logging/Auditing Suites supporting
InterBase, Firebird, Advantage Database, MS SQL Server and
NexusDB V2
Upscene Productions
http://www.upscene.com
My blog:
http://blog.upscene.com/thomas/