SQL Query Hints

[jeff_crisp]

Can someone tell me if it is possible (and how) to add SQL Query hints?  I am looking to try and add WITH (NOLOCK) to the generated queries.

I have looked through the forums and I have only found where it talks about adding Oracle hints.

Any assistance would be appreciated.

Thanks

[Former Member]

Hi Jeff,

Here's a way to do it with minimal changes.

Create a new database user called bo_views

Build a script to script all your tables as views along the lines of

CREATE VIEW bo_views.table_name AS select * from dbo.table_name WITH (NOLOCK)

Run that script and you'll have a full set of views owned by bo_views with the exact same structure as your tables

In BO, all you need to do now is change the owner of the tables from dbo to bo_views - Designer doesn't differentiate between views and tables so it won't throw an error.

Should take about 20-30 minutes to complete in total.

Regards,

Mark

[Former Member]

I think addition of HINT at object defination is working solution here.

You don't need to create a separate view for that .

lets say you don't have rights to create any views on database then how will you do it!!

I can say this solution can be done on universe only as mentioned by noel

Thanks,
Swapnil

[Former Member]

Absolute rubbish. I've done it this way before. Worked fine on BO6.5.1, XIr2 and XI3.

You've thrown in a hypothetical "no database accees".

I've used this solution over vendor-supplied databases because they'd rather I create a separate user or even database within the instance and obviously they were keen for me to steer clear of their provided solution.

It means that no changes to objects or classes were needed, just the connection to point at the different database with a different owner. It really is a simple and elegant solution compared to a solution involving derived tables that the DBA has no visibility of.

[Former Member]

I dont think so ... as you have to deal with DBA.

Not in every organisation you have rights to do same.

That may be true in your case as you may be DBA or BIG GUY then it can be possible.

Anyways lets decide Jeff what he wants....

I am not saying anything about your approach .. its right any ways..

But my point is completely different and that to practical.

[Former Member]

You make the DBA sound like the enemy!!! The DBA should be your friend, helping you improve performance, not working against you.

[Former Member]

I think you can add hints to SQL in WEBI as WEBI engine will put down that query back to Oracle so that Oracle can execute it & throw back the results.

Anyways what do you need exactly please let me know.

Are you looking for some performance enhancement?

Thanks,
Swapnil

[jeff_crisp]

Currently, the ad hoc query is being created as:

SELECT col1, col2, col3 FROM Table

What I am wanting it to do is create:

SELECT col1, col2, col3 FROM Table WITH (READUNCOMMITTED)

or

SELECT col1, col2, col3 FROM Table WITH (NOLOCK)

Bascially, we are having issues with one of our heavily used tables and having blocking issues.  Its causing reports to timeout because the query is being blocked by another unrelated task.

[nscheaffer]

Did you try my suggestion?

Noel

[jeff_crisp]

Its possible I did it wrong, but it got onto me for an unknown table name.

[nscheaffer]

Did you get something like this?

The issue is that you need to fully qualify your table name with the database and table owner. So for me that looks like this...

That yields a query like this...

Hope this helps,

Noel

[nscheaffer]

I am sure there are issues with what I am about to suggest. However, you are looking for ideas and I cannot think of any other way.

It would work to replace any table you want to have the "WITH (NOLOCK)" with a derived table that explicity includes that hint. So if you have TableA you would replace it with a derived table defined as follows...

SELECT Column1, Column2, ... FROM TableA WITH (NOLOCK)

One other possibility is that you could create a view on the database that includes the desired query hint and then add that view to your data foundation just like you would add the table. The view would be defined pretty much like the derived table above.

Like I said I am sure there are drawbacks to this, but that's all I can come up with.

Noel