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:
-- blah blah
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 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?