Query Plan Problem: Parellelism/Repartioning Streams

[rsai]

Hi
I have a query which runs in about 7 minutes, when I add another column from a simple (small) lookup table joined to one of the other tables already in the query the query time drops to 2 minutes. When I examine the query plans for the two queries the major difference is that the faster (but more complex) query invokes Paralleism/Repartitioning Streams or Parallelism/ Broadcast at virtually every stage in the plan whilst none is invoked for the slower/simpler query.

Can anyone explain what is affecting the query plan and how I can ensure that the most efficient plan is being used in all cases.

I have been through each of the tables involved in the slower query and updated the statistics on the indexes and the columns, but it makes no difference.

 

[Sammy145]

well have you got indexes on the lookup table

What you need to look out for is index scan and table scan

index scan = FAST
table scan = Slow


also when you join to the look up tablle your are narrowing the serach criteria ( i am assuming)

You should hear more advice hopefully.

Sam