BusinessObjects Board

Using Hints with SQL Server 2000

building semantic layer
#1

This topic is discussed for Oracle here:

https://bobj-board.org/t/15442

After reading through that disscusion I tried to implement hints on SQL2000. The problem is that the hint must come at the end of the SQL string and not within the SELECT statement. The hint I’m trying to use is:

 option (maxdop 1)

This causes the server to only use one processor to read the index. If I don’t use this hint I get the following error in WebI:

Database error: [Microsoft OLE DB Provider for SQL Server]:
Intra-query parallelism caused your server command (process ID #53) to deadlock. Rerun the query without intra-query parallelism by using the query hint option (maxdop 1)

The query with this option runs fine in Query Analyser but I can’t seem to get the hint to show up in the right place by creating an object like all the Oracle guys have.

Any thoughts on this???
Jeff

jmoore (BOB member since 2006-01-05)

#2

The problem is that you need this to show up after all of the clauses (FROM, WHERE, GROUP BY, etc.), right?

Then, a dimension object just isn’t going to cut it for you – a dimension object is going to be part of the SELECT columns – and come before all of your extra clauses.

Anita Craig :us: (BOB member since 2002-06-17)

#3

That is correct. Is there some way to get that hint at the end of my SQL???

jmoore (BOB member since 2006-01-05)

#4

I’m sorry, but I sure don’t have any suggestion. I think the Oracle trick works because someone just had an inspiration.

Anita Craig :us: (BOB member since 2002-06-17)

#5

See this post for a hint (pun intended) about one possibility. What version of Designer are you using?

Dave Rathbun :us: (BOB member since 2002-06-06)

#6

Another approach, give the BO generated SQL to a dba and explain you want to run this without having to use maxdop 1. It may be that a judicious re-jigging of the query can achieve this.

Nick Daniels :uk: (BOB member since 2002-08-15)

#7

The interface is WebI … Therefore all the SQL magic in the world won’t help me since WebI is generating the SQL.

I know I can set the maxdop hint in my connection but that would then be global for all users, this would be a bad thing…

jmoore (BOB member since 2006-01-05)

#8

Was a solution to this ever found with XIR2? We’re experiencing the same problem on a few of our larger queries.

If not… can XIR3 handle hints? (We’re about to migrate anyway…)

JPetlev (BOB member since 2006-11-01)

#9

You have to try create a query hint in the universe and then use it as the first/last object in your report. See also Hint topic

HenkK[/url]

HenkK :netherlands: (BOB member since 2004-03-02)