Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
annqueue
Starting Member
12 Posts |
 Posted - 2006-01-11 : 18:31:45
|
| hello all,I'm building a great big nasty view and having trouble optimizing it. The view lives on top of Axapta tables, which means the underlying tables are not indexed (hey, I didn't write it).What I've discovered is: the view happens to run quickly if the execution plan is done with pure hash matches, with no nested loops or parallelism. We're talking 5 seconds as opposed to 1 minute 30 seconds.How do I force a query to use pure hash matches and no parallelism or nested loops?thanks,Ann |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2006-01-12 : 05:30:25
|
| You may get a better response on a product related site....here 99%+ of issues are MS SQL Server.fyi...I never heard of this product/technology until today...and i've seen no references here before to it.Have a go at dbforums.com to see if anybody knows the product....or maybe even ask the builders...M$ |
 |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2006-01-12 : 05:41:58
|
| You can use the MAXDOP option to set the parallelism for the query. Look up index hints in books online to read about setting the query to use hash joins.-------Moo. :) |
|
|
|
annqueue
Starting Member
12 Posts |
Posted - 2006-01-12 : 13:23:44
|
| I found the hash join hint, and that brings it down to 15 seconds. But the MAXDOP setting is server level, and I certainly don't want to set that to 1 for the entire server.I'm trying to figure out how to save the query plan created in our dev server to use on our live server now now (the dev environment plan runs with hash joins & no parallelism, in 5 seconds). Anyone have hints on how to do that?thanks,Ann |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2006-01-12 : 13:40:39
|
| MAXDOP is a query hint (see Books Online). The server setting you mention is "max degree of parallelism" and is set using sp_configure. |
|
|
|
annqueue
Starting Member
12 Posts |
Posted - 2006-01-12 : 14:13:43
|
quote:
Originally posted by robvolkMAXDOP is a query hint (see Books Online). The server setting you mention is "max degree of parallelism" and is set using sp_configure.
Ah. Found it. Thanks! |
|
|
|
|
|
|
how to force hash instead of nested loop?