I would like to reduce the access to these tables: fact_input_output, dim_org_v, rap_combo

Ndoba 2014-10-08 13:06:37

Aaron Bertrand 2014-10-08 16:05:27
There's a key lookup you could eliminate by adding demande_id and evenement_id to IX_personne_v_id on fact_input_output. Also cardinality estimates are all over the place – I would see what happens if you do a proper statistics update on all the involved tables, particularly fact_input_output. Also why is there no clustered index (or at least a covering index) on dim_personnes_v? (I assume that's a view but it is performing a full table scan under the covers). Would also be nice to see an actual execution plan executed from within Plan Explorer (this one was pulled in from Management Studio I guess, and is missing runtime metrics like duration, CPU, and table I/O). The query itself looks like it might be optimized (the column = (expensive correlated subquery) model doesn't tend to work out very well), but I'd rather start with the basics. Would also be interesting to see what happens if you force a new plan (OPTION (RECOMPILE)) and also whether parallelism hurts or helps (OPTION (MAXDOP 1)).
Ndoba 2014-10-10 14:54:01
Hey Aaron.

I got rid of one key lookup. I modified the query a little bit.
I still have one key lookup despite the fact i've added the columns org_v_id and and type_demande_id in the IX_ndobs_demande_id_soustache_id index.
But anyway, it's better now.
I attached the plan.

Thanks a lot.