UNION ALL View Running terribly slow

Question

I am prepping data for vizualiaztion in Tableau. Currently, we have about a dozen tables, with some shared values, that I am combining into one large view via a huge UNION ALL script (attached showing 2 tables only)

My performance in Tableau is terribly slow, and I'm sure there is a more efficient way to structure the data. Currently, I have to refresh a tableau extract every 15 minutes, so the data is not truely "live"

Any help would be appreciated! Thanks

DROP VIEW XXXXX_alerts;
CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = root@% 
    SQL SECURITY DEFINER
VIEW XXXXX_alerts AS
    SELECT 
        _data435329_XXXXX_12_pre_tested_pipe_inspection_form.ID AS ID,
        _data435329_XXXXX_12_pre_tested_pipe_inspection_form.inspector AS Inspector,
        _data435329_XXXXX_12_pre_tested_pipe_inspection_form.CREATED_DATE AS Created_Date,
        _data435329_XXXXX_12_pre_tested_pipe_inspection_form.CREATED_LOCATION AS Created_Location,
        _data435329_XXXXX_12_pre_tested_pipe_inspection_form.MODIFIED_DATE AS Modified_Date,
        _data435329_XXXXX_12_pre_tested_pipe_inspection_form.MODIFIED_LOCATION AS Modified_Location,
        _data435329_XXXXX_12_pre_tested_pipe_inspection_form.date1 AS date1,
        NULL AS test_date_8200B,
        _data435329_XXXXX_12_pre_tested_pipe_inspection_form.division AS division,
        '8900' AS Form,
        NULL AS Device_Type,
        _data435329_XXXXX_12_pre_tested_pipe_inspection_form.coating_condition AS coating_condition,
        _data435329_XXXXX_12_pre_tested_pipe_inspection_form.next_calibration_date AS next_calibration_date,
        _data435329_XXXXX_12_pre_tested_pipe_inspection_form.pipe_barcode AS pipe_barcode,
        _data435329_XXXXX_12_pre_tested_pipe_inspection_form.nitrogen_pressure_when_left_psig AS nitrogen_pressure_when_left_psig,
        _data435329_XXXXX_12_pre_tested_pipe_inspection_form.is_nitrogen_left_value_correct AS is_nitrogen_left_value_correct,
        NULL AS Location,
        NULL AS Location_Conditional_Value,
        NULL AS Town,
        NULL AS readily_detectable_reading,
        NULL AS threshold_level_gas,
        NULL AS hefpa_shutoff,
        NULL AS customers_signature,
        NULL AS street,
        NULL AS serial_number,
        NULL AS tubing_filter_replaced_date,
        NULL AS calibration_date,
        NULL AS holiday_equipment_type,
        NULL AS date_tag_issued,
        NULL AS class,
        NULL AS condition1,
        NULL AS action_taken,
        NULL AS reinspect_complete,
        NULL AS person_designation,
        NULL AS apartment_complex,
        NULL AS result_of_contact_1,
        NULL AS result_of_contact_2,
        NULL AS date_letter_sent,
        NULL AS date_clerk_sent_letter_to_customer_advocate,
        NULL AS excessive_co_reinspect,
        NULL AS tag_cleared,
        NULL AS condition,
        NULL AS date_of_contact_to_customer_service_for_locked_off_meter,
        NULL AS date_tag_cleared,
        NULL AS type_of_fitting,
        NULL AS re_inspected_by_company_employee,
        NULL AS inspection_Result,
        NULL AS wo_sap_number,
        NULL AS material_studied,
        NULL AS time_studied,
        NULL AS program,
        NULL AS cgi_or_fi,
        NULL AS component_picture,
        NULL AS Test_Pressure,
        NULL AS Test_Duration,
        NULL AS Pipe_Footage,
        NULL AS Pipe_Size,
        NULL AS Barcode,
        NULL AS Material
    FROM
        _data435329_XXXXX_12_pre_tested_pipe_inspection_form 
    UNION ALL SELECT 
        _data435329_XXXXX_10_2_quantitative_odorant.ID AS ID,
        _data435329_XXXXX_10_2_quantitative_odorant.inspector AS Inspector,
        _data435329_XXXXX_10_2_quantitative_odorant.CREATED_DATE AS Created_Date,
        _data435329_XXXXX_10_2_quantitative_odorant.CREATED_LOCATION AS Created_Location,
        _data435329_XXXXX_10_2_quantitative_odorant.MODIFIED_DATE AS Modified_Date,
        _data435329_XXXXX_10_2_quantitative_odorant.MODIFIED_LOCATION AS Modified_Location,
        NULL AS date1,
        _data435329_XXXXX_10_2_quantitative_odorant.date1 AS test_date_8200B,
        _data435329_XXXXX_10_2_quantitative_odorant.division AS division,
        '8200B' AS Form,
        NULL AS Device_Type,
        NULL AS coating_condition,
        NULL AS next_calibration_date,
        NULL AS pipe_barcode,
        NULL AS nitrogen_pressure_when_left_psig,
        NULL AS is_nitrogen_left_value_correct,
        _data435329_XXXXX_10_2_quantitative_odorant.location1 AS Location,
        zcoptions.CONDITION_VALUE AS Location_Conditional_Value,
        _data435329_XXXXX_10_2_quantitative_odorant.town AS Town,
        _data435329_XXXXX_10_2_quantitative_odorant.odorometer_read_quantitative_readily_detectable___ AS readily_detectable_reading,
        _data435329_XXXXX_10_2_quantitative_odorant.odorometer_read_quantitative_threshold_level___gas AS threshold_level_gas,
        NULL AS hefpa_shutoff,
        NULL AS customers_signature,
        NULL AS street,
        NULL AS serial_number,
        NULL AS tubing_filter_replaced_date,
        NULL AS calibration_date,
        NULL AS holiday_equipment_type,
        NULL AS date_tag_issued,
        NULL AS class,
        NULL AS condition1,
        NULL AS action_taken,
        NULL AS reinspect_complete,
        NULL AS person_designation,
        NULL AS apartment_complex,
        NULL AS result_of_contact_1,
        NULL AS result_of_contact_2,
        NULL AS date_letter_sent,
        NULL AS date_clerk_sent_letter_to_customer_advocate,
        NULL AS excessive_co_reinspect,
        NULL AS tag_cleared,
        NULL AS condition,
        NULL AS date_of_contact_to_customer_service_for_locked_off_meter,
        NULL AS date_tag_cleared,
        NULL AS type_of_fitting,
        NULL AS re_inspected_by_company_employee,
        NULL AS inspection_Result,
        NULL AS wo_sap_number,
        NULL AS material_studied,
        NULL AS time_studied,
        NULL AS program,
        NULL AS cgi_or_fi,
        NULL AS component_picture,
        NULL AS Test_Pressure,
        NULL AS Test_Duration,
        NULL AS Pipe_Footage,
        NULL AS Pipe_Size,
        NULL AS Barcode,
        NULL AS Material
    FROM
        (_data435329_XXXXX_10_2_quantitative_odorant
        LEFT JOIN zcoptions ON ((zcoptions.KEY_VALUE = _data435329_XXXXX_10_2_quantitative_odorant.location1)))
    WHERE
        (zcoptions.OPTION_LIST_ID = 4553158)
    GROUP BY _data435329_XXXXX_10_2_quantitative_odorant.ID 
    UNION ALL SELECT

Have you run an explain plan on your query? This doesn't sound like a Tableau performance problem, it's on the database side. A 12 table UNION ALL that is this complicated is going to naturally take up db resources. The explain plan will help you identify inefficient join paths and where indexing might help. — Sam M, Dec 13 '17 at 06:53

score 2 · Answer 1 · answered Dec 12 '17 at 19:08

With what you provided, we can address only

    FROM  (_data435329_XXXXX_10_2_quantitative_odorant
    LEFT JOIN  zcoptions
          ON ((zcoptions.KEY_VALUE = 
              _data435329_XXXXX_10_2_quantitative_odorant.location1))
          )
    WHERE  (zcoptions.OPTION_LIST_ID = 4553158)
    GROUP BY  _data435329_XXXXX_10_2_quantitative_odorant.ID

The WHERE (WHERE (zcoptions.OPTION_LIST_ID = 4553158) = 4553158) effectively turns the LEFT JOIN into JOIN. So, I wonder why you did not say JOIN?

Index:

zcoptions:  INDEX(OPTION_LIST_ID)

or, to make it "covering", hence somewhat faster:

zcoptions:  INDEX(OPTION_LIST_ID, KEY_VALUE, CONDITION_VALUE)

This smells like an EAV schema. If so, please follow the advice here for setting up a better PRIMARY KEY, etc. Ditto for any other key-value tables.

The next thing that smells is this pattern:

FROM a JOIN b ...
GROUP BY a.id

I call it "explode-implode" because the JOIN first multiplies the number of rows, then the GROUP BY brings it back to one row per row in one of the tables.

If the JOIN does not actually have multiple rows in b for each in a, then the GROUP BY in unnecessary. (Without knowing the data, I cannot predict this.) In this case, another way to express the query is

SELECT
    ...,
    ( SELECT CONDITION_VALUE
        FROM zcoptions
        WHERE KEY_VALUE = _data435329_XXXXX_10_2_quantitative_odorant.location1
          AND OPTION_LIST_ID = 4553158
    ) AS Location_Conditional_Value,
    ...

and get rid of the JOIN and GROUP BY.

If there are multiple rows in zcoptions, then the GROUP BY is improperly formed. The query is free to mix up the non-grouped-by columns. A possible workaround is to pick, say, the smallest:

    ( SELECT min(CONDITION_VALUE) ...

or pick the 'first'

    ( SELECT CONDITION_VALUE ... LIMIT 1 ) as ...,

UNION ALL View Running terribly slow

1 Answers1