SQL Hints

["Ferrer, Eric" <er...@transcore.com>]

Is it possible to hint to OJB on persisted calls to use the WITH (NO
LOCK) attributes on tables?

 

When ever we are in a multiple transaction and selecting data from large
tables we sometimes slow things down and for performance reasons
sometimes have to create views.

 

Thanks

-Eric

Re: SQL Hints

[Armin Waibel <ar...@apache.org>]

Hi Eric,

Ferrer, Eric wrote:
> Is it possible to hint to OJB on persisted calls to use the WITH (NO
> LOCK) attributes on tables?

The "WITH (NOLOCK)" clause is a proprietary sql extension for mssql, so 
same problem as with "native limit" support. If using "WITH (NOLOCK)" 
dirty reads can occur - what about data consistency?

Isn't it possible to change the transaction level of the current used 
connection
http://msdn2.microsoft.com/en-gb/library/ms378149.aspx
AFAIK is TRANSACTION_READ_UNCOMMITTED the same as "WITH (NOLOCK)".
Something like this:

PersistenceBroker broker = lookup PB instance (no tx running!!)
// get the current connection
Connection con = broker.serviceConnectionManager().getConnection();
int iso = con.getTransactionIsolation();
try
{
    con.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
    ...
    // perform the query or other work
    broker.getCollectionByQuery(...);
    ...
}
finally
{
    // rollback to default tx-isolation
    con.setTransactionIsolation(iso);
    broker.close();
}

regards,
Armin

> 
>  
> 
> When ever we are in a multiple transaction and selecting data from large
> tables we sometimes slow things down and for performance reasons
> sometimes have to create views.
> 
>  
> 
> Thanks
> 
> -Eric
> 
> 

---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org