SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Create a View with OPTION (FORCE ORDER)


Create a View with OPTION (FORCE ORDER)

Author
Message
John F
John F
SSC Veteran
SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)

Group: General Forum Members
Points: 287 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?


sushila
sushila
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8325 Visits: 639
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 !!!**
John F
John F
SSC Veteran
SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)

Group: General Forum Members
Points: 287 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


sushila
sushila
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8325 Visits: 639
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 !!!**
ChrisMoix-87856
ChrisMoix-87856
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2124 Visits: 941
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...).



Sim Lever
Sim Lever
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 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

John F
John F
SSC Veteran
SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)

Group: General Forum Members
Points: 287 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


h.tobisch
h.tobisch
SSC-Addicted
SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)

Group: General Forum Members
Points: 438 Visits: 255
use option (force order) when selecting from the view
Lowell
Lowell
SSC Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70556 Visits: 40924
h.tobisch (12/13/2013)
use option (force order) when selecting from the view


note: eight year old thread.

Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211965 Visits: 41977
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search