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

avatar image By Ndoba 1 asked Oct 08, 2014 at 01:06 PM
more ▼
(comments are locked)
avatar image Aaron Bertrand ♦ Oct 08, 2014 at 04:05 PM

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)).

10|10000 characters needed characters left

1 answer: sort voted first

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.

avatar image By Ndoba 1 answered Oct 10, 2014 at 02:54 PM
more ▼
(comments are locked)
10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.