Order by is to slow in a query

Question

Order by is to slow in a query

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

BRP Default port Points: 1433 More actions · Answer 1

Same result 7:26 for 32640 records. I have tried most of those (same thought) but not that one, thought you were onto something. Nice try.

Thanks,

Bruce

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 2

eric larocca (11/9/2010)
this way i believe it won't rescan the original table but order only the records try this.
It should process the order by after it gathers the records
whats inside the parenthesis is done first always....

Incorrect, Eric. SQL is a declarative, not a procedural, language. Subqueries are folded into the execution plan by the optimizer, and are merely logical organizations (for aggregations, as an example).

The only time the subquery is forced to run independently before being merged is when you use OPTION (FORCE ORDER), which could actually make your run times worse, depending on the scenario.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

fanningm Newbie Points: 6 More actions · Answer 3

I'd use a temp table like they mentioned earlier. Then you can add any indexes to the temp table that you want which will help speed things up too. If you can filter down the amount of initial data that will go into the temp table, it will help speed things up too. Otherwise you must get buy-off from the vendor to add the indexes to their tables.

And to answer your question on the view indexes, the view must be created with the SCHEMABINDING option (if you are new to this, then you can either read about this in MSDN/BooksOnLine, or you can also read a good article on this here: http://www.mssqltips.com/tip.asp?tip=1610).

BRP Default port Points: 1433 More actions · Answer 4

The temp table is fast enough but I can't see a way to implement it at user level at this stage, the vendors are looking at it. The indexed view looks like overkill to me but will gladly do some reading on it.

Thanks for the input,

Bruce