Using FB 2.1.4 I try to tune a query that I know will take hours to
execute due to large table joins etc. I usually see the query plan in
FlameRobin and in Sinática Monitor, but in some cases I don't.
Why can't I see the plan in some cases, and is there a different way
that *would* allow me to see it?
To give you a grasp, the SQL is as follows, and I expect it to return
2-3 hundred thousand records:
select F."ECO_ID"
from "Företag" F /*~2 million records*/
inner join "Uppgiftshanterare" Uhant /*~2 million records*/
on Uhant."ECO_ID" = F."Uppgiftshanterare"
inner join "Uppgiftshållare" UH /*~150 million records*/
on UH."Hanterare" = UHant."ECO_ID"
inner join "Uppgift" Upg /*~170 million records*/
on Upg."Hållare" = UH."ECO_ID"
inner join "Uppgiftsdefinition" Def /*few records (~100 records)*/
on Def."ECO_ID" = UH."Definition"
where Upg."Borttagsuppdatering" + 0 is null
and Upg."Förrådsvärde" in (
2597, 2598, 2603, 2605, 2608, 2629, 2632, 2641, 2642, 2643,
2644, 2645, 2651, 2658, 2659, 2661, 2662, 2663, 2664, 2665,
2666, 2669, 2670, 2671, 2672, 2673, 2674, 2675, 2676, 2677,
2678, 2679, 2680, 2681, 2682, 2683, 2684, 2685, 2686, 2688,
2692, 2693, 2694, 2695, 2697, 2707, 2710, 2711, 2712, 2713,
2716, 2717, 2720, 2727, 2729, 2732, 2736, 2738, 2739, 2740,
2741, 2742, 2743, 2744, 2745, 2746, 2747, 2748, 2749, 2750,
2751, 2752, 2753, 2754, 2756, 2757, 2758, 2764, 2765, 2766,
2767, 2768, 2769, 2770, 2771, 2772, 2773, 2774, 2776, 2780,
2782, 2783, 2784, 2787, 2789, 2790, 2792, 2798, 2799, 2800,
2801, 2802, 2803, 2804, 2805, 2806, 2807, 2808, 2812, 2813,
2814, 2815, 2816, 2817, 2818, 2819, 2821, 2822, 2826, 2828,
2831, 2832, 2833, 2837, 2838, 2839, 2840, 2841, 2842, 2843,
2844, 2845, 2846, 2847, 2848, 2851, 2854, 2857, 2858, 2860,
2861, 2865, 2867, 2869, 2870, 2872, 2874, 2875, 2882, 2883,
2884, 2886, 2887, 2888, 2890, 2894, 2895, 2896, 2897, 2898,
2901, 2902, 2903, 2906, 2909, 2911, 2912, 2913, 2914, 2915,
2916, 2917, 2918, 2919, 2920, 2921, 2922, 2923, 2924, 2925,
2939, 2940, 2942, 2943, 2944, 2946, 2947, 2948, 2950, 2951,
2952, 2953, 2954, 2956, 2958, 2959, 2961, 2962, 2963, 2964,
2965, 2967, 2969, 2970, 2971, 2972, 2973, 2974, 2975, 2976,
2977, 2978, 2979, 2980, 2981, 2982, 2983, 2984, 2985, 2988,
2989, 2990, 2992, 2998, 2999, 3037, 3038, 3039, 3050, 3052,
3053, 3057, 3061, 3062, 3063, 3065, 3066, 3069, 3070, 3071,
3073, 3078, 3079, 3086, 3088, 3089, 3090, 3091, 3093, 3096,
3097, 3103, 3107, 3109, 3140, 3143, 3145, 3150, 3152, 3153,
3154, 3155, 3156, 3157, 3165, 3167, 3168, 3169, 3170, 3171,
3172, 3174, 3175, 3176, 3177, 3186, 3187, 3188, 3189, 3190,
3191, 3192, 3193, 3194, 3195, 3196, 3198, 3199, 3202, 3203,
3204, 3205, 3206, 3207, 3208, 3211, 3217, 3218, 3221, 3224,
3225, 3227, 3228, 3229, 3236, 3237, 3238, 3239, 3240, 3241,
3242, 3245, 3248, 3251, 3254, 3256, 3257, 3258, 3259, 3260,
3261, 3263, 3264, 3267, 3270, 3271, 3272, 3274, 3275
)
and (Def."Namn" = 'Branschkod1'
and UH."Propertynamn" = 'Branschkod1'
and F."Branschkod1" <> Upg."Förrådsvärde"
or Def."Namn" = 'Branschkod2'
and UH."Propertynamn" = 'Branschkod2'
and F."Branschkod2" <> Upg."Förrådsvärde"
or Def."Namn" = 'Branschkod3'
and UH."Propertynamn" = 'Branschkod3'
and F."Branschkod3" <> Upg."Förrådsvärde"
or Def."Namn" = 'Branschkod4'
and UH."Propertynamn" = 'Branschkod4'
and F."Branschkod4" <> Upg."Förrådsvärde"
or Def."Namn" = 'Branschkod5'
and UH."Propertynamn" = 'Branschkod5'
and F."Branschkod5" <> Upg."Förrådsvärde")
Regards,
Kjell
--
------------------------------
Kjell Rilbe
DataDIA AB
E-post: kjell.ri...@datadia.se
Telefon: 08-761 06 55
Mobil: 0733-44 24 64
