Hello,

We use hibernate 2.0.2 on a j2ee project. For performance reasons, our DBA suggests that we append 'with ur' to some specific queries (count(*) that span on multiple tables).

For now, appending 'with ur' to HQL queries fails with hibernate trying to put it in some mapping.

For example, from the following HQL query :

select * from test.className as c where c.property1 = ? and c.property2 = ? with ur


Hibernate executes the following statement on ou DB:

select * from our_table as t where (t.column1 = ?) and (t.column2 = ? with ur)

...which fails...

We tried to add extra parenthesis but without any success :-(

Any idea ?

Thank you in advance.

Xavier.

<quote>When working with JDBC or SQLJ at run time: ... <quote>, <quote>Use the setTransactionIsolation method in the java.sql interface connection.<quote>


[url]http://publib.boulder.ibm.com/infocenter/db2help/index.jsp[/url]

Regards
Horia

Dirty reads are just evil. This recommendation seems to be common among DB2 DBAs who are used to working with batch-processing systems that use long-running transactions. It is advice that is simply inappropriate for the kind of online, record-oriented systems that Hibernate is usually used for, where transactions are always extremely short.

It seems however a good recommendation since the sql queries we run are 'select count(*)...' and if the returned number is 290 or 291 does not really matter.

Yet, performance matters a lot, and our DBA believes that these queries should be 'dirty' in order to avoid an averall performance decrease.

Finally, we still want to use hibernate as it already holds all our database mapping.

OK, if its just for one of two aggregate queries, thats fine. Simply change the JDBC isolation level to uncommitted read. note, however, that even in read committed isolation, read locks are always momentary, they do not even last the length of the transaction, so it really should make very, very little difference to performance.

Thanks a lot ! It worked very well and I was able to verify it with an event monitor.

Hi,
Do you mean "with ur" and Connection.TRANSACTION_READ_UNCOMMITTED both are same?
thanks
Rajesh