Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Create a View with OPTION (FORCE ORDER) Expand / Collapse
Author
Message
Posted Tuesday, August 2, 2005 4:30 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, October 6, 2008 9:55 PM
Points: 49, Visits: 9

Query works fine, but when I make the query a view, I get

Server: Msg 156, Level 15, State 1, Procedure OSView84, Line 87
Incorrect syntax near the keyword 'OPTION'.

Argh!!!  It seems like a view cannot have this option.  Is this true?

Post #206657
Posted Tuesday, August 2, 2005 4:46 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:04 PM
Points: 2,555, Visits: 601
John - what is the query ?! It appears that you are getting a syntax error...if you post the query it would help !







**ASCII stupid question, get a stupid ANSI !!!**
Post #206659
Posted Tuesday, August 2, 2005 5:10 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, October 6, 2008 9:55 PM
Points: 49, Visits: 9

-- this works
SELECT c.Name + '.' + o.Name AS Foo
FROM SysColumns c
INNER JOIN SysObjects o ON c.id=o.id
OPTION (FORCE ORDER)
GO

-- This works
CREATE VIEW Test1 AS
  SELECT c.Name + '.' + o.Name AS Foo
  FROM SysColumns c
  INNER JOIN SysObjects o ON c.id=o.id
GO

-- This fails
CREATE VIEW Test2 AS
  SELECT c.Name + '.' + o.Name AS Foo
  FROM SysColumns c
  INNER JOIN SysObjects o ON c.id=o.id
  OPTION (FORCE ORDER)
GO

 

Post #206662
Posted Tuesday, August 2, 2005 10:25 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:04 PM
Points: 2,555, Visits: 601
Yes - it does appear that SQL Server doesn't like any interference with its optimizer plans (at least for views)...

however, everything I've read so far indicates that we should "trust" sql server to do its' job on running queries...so I am curious about your reasoning behind using this!!! Do you use the option clause often ?!









**ASCII stupid question, get a stupid ANSI !!!**
Post #206697
Posted Wednesday, August 3, 2005 7:02 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, October 21, 2013 10:48 AM
Points: 966, Visits: 933
I've got a feeling that it is the same reasoning where MS doesn't allow ORDER BY in the view definition. They say to use ORDER BY clauses in the queries that you run against the view, not the view itself (Unless there is a TOP clause...).


Post #206832
Posted Wednesday, August 3, 2005 8:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, December 5, 2013 2:59 AM
Points: 22, Visits: 11

This is interesting (I'll just put my anorak on)

I have recently been playing with a copy of Quests SQL Central (I don't work for them!!!)  - it rewrites any TSQL statement in different ways to produce the same results then compares the speed and I/O hit of the query to pick the best result.

The thing is it often uses these OPTION hints and invariably gets better results than my original (not bad efforts) SQL.

It also likes to use COALESCE to speed up queries as well - it must force the Query Optimiser down a particular route.  Anyway it's all good stuff.

So maybe SQL doesn't always make the right decisions - usually when the stored proc has too many steps in it.

Sorry all this isn't much help for the view prob.  SQL is picky on what it allows a view to use. 




Cheers,

Sim Lever

Post #206885
Posted Wednesday, August 3, 2005 1:20 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, October 6, 2008 9:55 PM
Points: 49, Visits: 9

Thanks for all the replies.  I guess there is no way to do it.

We have a database with millions of rows in many different related tables.  It is a workflow engine, so during the day, items move from one queue to the next.  In our application, the optimizer gets good results some of the time, but hardly the optimal result - and it can change because queries are constantly re-optimized.  If a queue is empty it picks one optimization.  If the queue is full it picks another.

Or in this case it doesnt believe that the function p2run is going to return a minimal set (100) of envelopes (out of a possible 20 million), so that is where you want to start:

SELECT
  -- blah blah
FROM
    dbo.P2Run() P2Run
    LEFT JOIN Envelope    (NOLOCK) ON P2Run.idEnvelope = Envelope.idEnvelope
    LEFT JOIN Batch       (NOLOCK) ON Batch.idBatch = Envelope.idBatch 
    LEFT JOIN Page        (NOLOCK) ON Envelope.idEnvelope  = Page.idEnvelope
      AND Page.Side=0
      AND ISNULL(Page.RawMicrLine, '') NOT LIKE '%GC%'
      AND Page.PageTypeCode NOT IN ('E', 'P')
    LEFT JOIN Credit      (NOLOCK) ON Page.idEnvelope = Credit.idEnvelope
      AND Page.iPage = Credit.iPage
    LEFT JOIN Remit       (NOLOCK) ON Remit.idEnvelope = Envelope.idEnvelope 
      AND Page.iPage = Remit.iPage
    LEFT JOIN Jobs        (NOLOCK) ON Batch.idJob = Jobs.idJob
    LEFT JOIN PageType    (NOLOCK) ON Page.idPagetype = PageType.idPagetype
    LEFT JOIN P2Batch P2  (NOLOCK) ON Credit.idP2Batch= P2.idP2Batch
  WHERE Batch.iBatch > 0
     AND Envelope.iEnvelope > 0
     AND Envelope.idBatch > 0

 

So basicly, there are times when we can make a SQL statement execute 100's of times faster than SQL can.  Since we have a real time system with 50 users executing queries like the one above, we cant have any queries that take too long.  So we have to optimize them.

Currently we do SELECT * FROM VIEWXXX ORDER BY YYY WHERE ZZZ.

Is it good enough to put the force order clause in the SELECT FROM the view?  Or, should we use stored procedures to wrap the select statement and the order by and the where into a single precompiled object?

- John

Post #207075
Posted Friday, December 13, 2013 11:14 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 7:12 AM
Points: 50, Visits: 92
use option (force order) when selecting from the view
Post #1522811
Posted Friday, December 13, 2013 11:38 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:42 PM
Points: 12,905, Visits: 32,157
h.tobisch (12/13/2013)
use option (force order) when selecting from the view


note: eight year old thread.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1522819
Posted Friday, December 13, 2013 6:24 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:45 PM
Points: 35,342, Visits: 31,875
Lowell (12/13/2013)
h.tobisch (12/13/2013)
use option (force order) when selecting from the view


note: eight year old thread.


If the suggestion works, who cares how old the thread is?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1522911
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse