> Maybe it simply isn't apparent to you. You have changed the information and options available to the optimizer in a very

> complex construct. It does what it can with that information.

True, but I have changed only one thing: a unique index to a nonunique index. Now call me old fashioned, but I believe that there should be either no difference in how these are implemented for a read-only scenario, or the unique index should be more efficient, hence this shouldn't cause a significant change in the plan.

This is why I have the audacity to call it a bug.

The way I see it, the plan is big, but that's not necessarily the same as complex. Big things that can be broken down in to small simple chunks are not necessarily that complex, and the indexing and constraints are the fuel to drive the problem solving on a micro level. This should scale.

I'm kinda wanting a little more consistency and control than I'm getting here.

But I do acknowledge that I'm very much out on my own here, and I concede that you guys, being some of the most experienced around here, are clearly right. Thank you.

> In my experience, hints are directives that the optimiser has to obey. That said, with tonnes of view and extra tables, the

> hints will probably have to be applied in the views, not in the outer query, plus it will be very hard to work out an optimal

> set of hints, especially when working with just the ones available in SQL 2000.

Totally agree - low likelihood a hint which is good for one query will be good for the next. I tried applying a hint to an inner view just as an experiment and it didn't work. I felt relieved once I'd got it out of there.

> have you tried unwinding all of the views in your query and maing them into a single statement a

This is a solution, but we are wandering OT a bit - lets get back to the point of the OP.

I badly need my views here. Due to the size of the data chunks I'm dealing with I have to wrap them in views to stay sane. eg. Produce details are needed frequently, so there's a view wrapping the produce table and the associated brands, grades, classes, product type, varieties, pack types, pack weights, colours, etc (about 10 tables). If I had to deal with these individually in every query, my head would explode, and I would have to upgrade to that 26" monitor.

I'm one programmmer and this is a large database (and we haven't even touched on the actual app ...).

Similarly, with temporary tables for partial results, I generally need to make them part of a view 'upstream' to avoid horribly complicated queries.

Can't do this with temp tables (they can't be incorporated into views).

Now, I HAVE re-worked some critical reports with partial result tables etc, and it took nearly a day for about six reports, and it turned the code into a maintenance nightmare.

Then I noticed that I could dramatically alter query speeds with a few little modifications, and as I mentioned in the OP, this was found to be due to the query plan not the indexing.

But results were highly inconsistent (adding and removing seemingly inconsequential tables has an almost random effect), and I can never get back to my original query without the speed hit.

So what I'm asking in this thread is can I nudge this query plan specifically WITHOUT significant changes to the structure of any of my current SQL, views, or application.

Seems the answer is 'not reliably'.

Anyway, I'm very happy with SQL 2005's performance in this regard (so far ...), and believe this thread is pretty much wrapped up.

If you still want to compare the query plans and see if you can work out what has caused the shift, I'd be intrigued.

I would love it if I could have more control, but I suspect that it would require some sort of grammar/language for describing join behaviour far more sophisticated that what is there at present.

It is interesting though that as we inevitably move towards RAM-based database servers and work on the 'object/relational impedance mismatch', I think a well written API-interactive query parser/optimiser is going to be a major part of the solution in the long term.

Regarding the original reasons for the session-related views/tables, it took me a while to remember the reasoning - this project was coded mainly in 2001-2003.

I believe that I originally used the sessionIDs because I had SPs to do the very complex charges calculations, and these used dynamic SQL. Global cursors are needed to be visible to the dynamic SQL, which means one SP per session. I also wanted to use the temp views to feed selected data into the charge calculation SPs, and because temp tables/views aren't visible from dynamic SQL, again these need to be declared as non-temp and hence need the SessionID.

(Hey, please, I know this site is prettly much dedicated to the hunting down and elimination of dynamic SQL. I know what rbar is and who Jeff Moden is. I respect and agree with all of that. The dynamic SQL is actually quite necessary 🙂 )

Add this to the fact you can't add temp tables to views and that pretty much clinched it.

Thanks for the reminder though - true temp tables could be a useful tool to throw in the mix.