Hi guys,

I'm (rather) new to Hibernate. I've got a medium-sized project going on at university where we're using Hibernate 3.0 (we had little choice here) and we also had to use the javax.persistence-annotations - so a very restricted environment. Our program requires a rather complex logic when it comes to deleting objects / entries on the underlying MySQL database, so we decided to write the logic in our java client (we have no server-side java program running, only the database is running on a server) using native MySQL delete and update queries. Which works. However, as our logic is complex, the number of individual DELETE-queries is rather large. Often we need 10 or more of them to delete a single object.

As far as I know, in regular MySQL query syntax, one may write several queries into one by separating them using ";". This would be a HUGE help to us in increasing performance as instead of crossing the network 10 times we would only have to cross it ONCE. So we would have queries like this:

DELETE FROM a WHERE b; DELETE FROM c WHERE d; DELETE ... etc. with a, b, c, d as placeholders.

But, it seems as if Hibernate would not support this. Every time I write a ";" it tells me that the queries MySQL syntax is wrong. Does Hibernate use a different separator than ";"? Or is there a way to "batch-process" multiple native queries so that the network delay would become active only once?


Thanks in advance,


Alan

I think, that hibernate do not execute all delete entries until transaction is complete. So, at the end of transaction it should execute in one batch.

If you want to delete entry by entry immediately you have to call flush() and clear() methods.

So, you could create and execute as many queries as you want, and when it's time, call flush().

Correct me if I am wrong.

@meduolis: Is this default behaviour or does that take some special setting? Because to me it looks like as if hibernate would execute the queries one after another, even inside the same transaction (I print every query to the console before it gets executed and there's a delay from one query to the next every time), but I'm not entirely sure - I'll check on that once more.

Thanks,


Alan

Hi,

sorry I've been very busy during the last days so I couldn't post this sooner.

I tried what you said and packed a lot of queris into a single transaction. That did, in fact, make things a LITTLE better. However, it still takes a lot of time to delete a complex object which appears as reference in many tables (e.g. a user) because it needs a lot of DELETE queries to do so.
And when I say "a lot of time" I'm talking about more than 10 seconds. And it's not because my internet connection would be so bad...

I think I now know where the problem is: When executing UPDATE and/or DELETE queries with native SQL, you have to call "executeUpdate()" on every single one of them to make them become effective (as far as I know, that is). The javax.persistence API is not very precise about this method but I printed the system time before and after executing such a method to console, which resulted in a delay of 70 milliseconds - which explains why using larger transactions did not entirely do the trick. However, I'm not entirely sure whether that delay is actually a *network* delay or something else hibernate maybe does in the background...

Any other proposals for executing multiple *native* DELETE-statements besides putting them in the same transaction?


Thanks,


Alan