Using with(nolock) from Universe

[Bobbber]

Hi,

We are looking at making a basic BO universe to test it's performance and usefulness against our currect solution (SQL Server stored procedures).

We are keeping the SQL Server - and currently the SPs references tables and includes the "with(nolock)" hint. Is there a way to force queries executed from a universe to appeand this hint to all the tables?

Help appreciated!

Thanks!

Bob

 

[blom0344]

You have to look in the universe property settings. In case of Oracle I remember adding optimizer hint statements in one of the properties.

Ties Blom

 

[blom0344]

Sorry, that should be connection parameters , custom tab..

Ties Blom

 

[Bobbber]

Thanks for the tip - but it would appear only Oracle table hints are available there... not for SQL Server?!

Bob

 

[blom0344]

No sorry, the nolock hint for SQL server seems to be used in a very different place in the SQL (after the from clause). However, it is not an optimizer hint like Oracle's , but a way to change the isolation level. With nolock you are enforcing 'dirty reads'. Are you sure you want this? Did you test whether performance is improved in any case?

Ties Blom

 

[Bobbber]

Yes I do want dirty reads. It results in a marked improvement in processing (report) times.

We have a live replication feed of data from the user production server - and i don't want the reporting/universe queries to lock this out.

Thanks for your help so far!

Bob

 

[blom0344]

Technically you could set isolation level to read uncommitted, but that's not a solution with BO.
If your BO version supports derived tables you could work the hint into the SQL definition (a lot of work...)
AFAIK there is no option to change the isolation level that is created through the ODBC DSN..

Ties Blom

 

[Bobbber]

Thanks for the replies. I was thinking something for derived tables like this :

select * from tbl1 with(nolock)

But then the BO universe query would be something like :

SELECT * FROM (SELECT * FROM tbl1 with(nolock)) X

Once you do that with a hundred derived tables joined together it becomes terrible! (I would guess - I'm not about to try it!)

I can see quite a few users may want this function - I'm thinking about bothering BO with it.

Bob

 

[skrandel]

I've been doing this for 10 years. I've never heard of such a requirement. We report against an Oracle database that is a live-replication of our ERP.

Locking has never been an issue. Why would this be a problem for you?

Steve Krandel
Intuit