Create a View with OPTION (FORCE ORDER)
-
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?
-
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 !!!** -
-- 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
-
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 !!!** -
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...).
-
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.
-
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
-
use option (force order) when selecting from the view
-
h.tobisch (12/13/2013)
use option (force order) when selecting from the viewnote: 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! -
Lowell (12/13/2013)
h.tobisch (12/13/2013)
use option (force order) when selecting from the viewnote: 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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Well, the thread is really old but I'll post my suggestion here anyway. Use local join hint, it makes the optimizer to force join order. And can be used in a view.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply