Query works fine, but when I make the query a view, I get
Server: Msg 156, Level 15, State 1, Procedure OSView84, Line 87Incorrect syntax near the keyword 'OPTION'.
Argh!!! It seems like a view cannot have this option. Is this true?
-- this worksSELECT c.Name + '.' + o.Name AS FooFROM SysColumns cINNER JOIN SysObjects o ON c.id=o.idOPTION (FORCE ORDER)GO
-- This worksCREATE VIEW Test1 AS SELECT c.Name + '.' + o.Name AS Foo FROM SysColumns c INNER JOIN SysObjects o ON c.id=o.idGO
-- This failsCREATE 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
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 blahFROM 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?