SQL Query - Possibly the most poorly written query in the history of mankind

Question

Having a major headache trying to tune this query and looking for help, a programmer who had no idea what they were doing messed with this query and it is currently being used in production and it taking forever. It might have the worst execution plan ever to be seen before (see attached).

 SELECT        CASE WHEN dbo.vw_avoice_candidate_NO_contacts.candidate_id IS NULL THEN 'Yes' ELSE 'No' END AS ever_contacted, 
                         dbo.vw_avoice_last_contacted.contact_dte, dbo.vw_avoice_last_contacted.lastupdateddate AS last_contact_date, 
                         CASE WHEN dbo.vw_avoice_last_signed_card.signed_card IS NULL THEN 'No' ELSE dbo.vw_avoice_last_signed_card.signed_card END AS signed_card, 
                         CASE WHEN dbo.vw_avoice_last_signed_card.sign_card_dte = '19000101' THEN NULL 
                         ELSE dbo.vw_avoice_last_signed_card.sign_card_dte END AS sign_card_dte, dbo.avoice_candidate.candidate_id, dbo.avoice_candidate.campaign_id, 
                         dbo.avoice_candidate.location_id, dbo.avoice_candidate.ssn, dbo.avoice_candidate.lst_nme, dbo.avoice_candidate.frst_nme, dbo.avoice_candidate.avc_init, 
                         dbo.avoice_candidate.name_suffix, CASE WHEN len(avc_init) > 0 AND NOT avc_init IS NULL THEN LTRIM(isnull(name_suffix, '') 
                         + ' ' + frst_nme + ' ' + avc_init + ' ' + lst_nme) ELSE LTRIM(isnull(name_suffix, '') + ' ' + frst_nme + ' ' + isnull(lst_nme, '')) END AS Full_Nme, 
                         dbo.avoice_candidate.addr1, dbo.avoice_candidate.addr2, CASE WHEN avoice_candidate.addr2 IS NULL OR
                         avoice_candidate.addr2 = ' ' OR
                         avoice_candidate.addr2 = '' THEN avoice_candidate.city + ', ' + avoice_candidate.state + ' ' + CASE WHEN avoice_candidate.zip_cde IS NULL 
                         THEN ' ' ELSE avoice_candidate.zip_cde END ELSE avoice_candidate.addr2 END AS disp_addr2, CASE WHEN avoice_candidate.addr2 IS NULL OR
                         avoice_candidate.addr2 = ' ' OR
                         avoice_candidate.addr2 = '' THEN ' ' ELSE avoice_candidate.city + ', ' + avoice_candidate.state + ' ' + CASE WHEN avoice_candidate.zip_cde IS NULL 
                         THEN ' ' ELSE avoice_candidate.zip_cde END END AS disp_csz, CASE WHEN dbo.avoice_candidate.city IS NULL 
                         THEN 'Unknown' WHEN LEN(RTRIM(dbo.avoice_candidate.city)) < 1 THEN 'Unknown' ELSE dbo.avoice_candidate.city END AS city, dbo.avoice_candidate.state, 
                         CASE WHEN avoice_candidate.zip_cde IS NULL THEN ' ' ELSE avoice_candidate.zip_cde END AS zip_cde, dbo.avoice_candidate.home_addr1, 
                         CASE WHEN home_addr2 IS NULL OR
                         home_addr2 = ' ' OR
                         home_addr2 = '' THEN avoice_candidate.city + ', ' + avoice_candidate.state + ' ' + CASE WHEN avoice_candidate.zip_cde IS NULL 
                         THEN ' ' ELSE avoice_candidate.zip_cde END ELSE home_addr2 END AS home_disp_addr2, CASE WHEN home_addr2 IS NULL OR
                         home_addr2 = ' ' OR
                         home_addr2 = '' THEN ' ' ELSE avoice_candidate.city + ', ' + avoice_candidate.state + ' ' + CASE WHEN avoice_candidate.zip_cde IS NULL 
                         THEN ' ' ELSE avoice_candidate.zip_cde END END AS home_disp_csz, dbo.avoice_candidate.home_city, dbo.avoice_candidate.home_state, 
                         dbo.avoice_candidate.home_zip_cde, dbo.avoice_candidate.misc_flag1, dbo.avoice_candidate.misc_flag2, dbo.avoice_candidate.misc_number1, 
                         dbo.avoice_candidate.misc_number2, CASE WHEN dbo.avoice_candidate.misc_date1 = '19000101' THEN NULL 
                         ELSE dbo.avoice_candidate.misc_date1 END AS misc_date1, CASE WHEN dbo.avoice_candidate.misc_date2 = '19000101' THEN NULL 
                         ELSE dbo.avoice_candidate.misc_date2 END AS misc_date2, dbo.avoice_candidate.note, dbo.avoice_candidate.txt_msg_flg, dbo.avoice_candidate.latitude, 
                         dbo.avoice_candidate.longitude, dbo.avoice_candidate.zoom, dbo.avoice_candidate.iconfile, dbo.avoice_candidate.county, dbo.avoice_candidate.ctry_cde, 
                         dbo.avoice_candidate.home_phone, 
                         CASE WHEN dbo.avoice_candidate.home_phone = '(___) ___-____' THEN 'No' WHEN dbo.avoice_candidate.home_phone = '' THEN 'No' WHEN dbo.avoice_candidate.home_phone
                          IS NULL THEN 'No' ELSE 'Yes' END AS phone, 
                         CASE WHEN dbo.avoice_candidate.cell_phone = '(___) ___-____' THEN 'No' WHEN dbo.avoice_candidate.cell_phone = '' THEN 'No' WHEN dbo.avoice_candidate.cell_phone
                          IS NULL THEN 'No' ELSE 'Yes' END AS cellphone, CASE WHEN dbo.avoice_candidate.work_phone > ' ' THEN 'Yes' ELSE 'No' END AS workphone, 
                         dbo.avoice_candidate.cell_phone, dbo.avoice_candidate.work_phone, dbo.avoice_candidate.email_addr, 
                         CASE WHEN dbo.avoice_candidate.email_addr > ' ' THEN 'Yes' ELSE 'No' END AS email, dbo.avoice_candidate.fax, 
                         CASE WHEN dbo.avoice_candidate.birth_dte = '19000101' THEN NULL ELSE dbo.avoice_candidate.birth_dte END AS birth_dte, 
                         CASE WHEN dbo.avoice_candidate.sex = 'M' THEN 'Male' WHEN dbo.avoice_candidate.sex = 'F' THEN 'Female' ELSE 'Unknown' END AS sex, 
                         CASE WHEN dbo.avoice_candidate.marital_sts = 'M' THEN 'Married' WHEN dbo.avoice_candidate.marital_sts = 'S' THEN 'Single' WHEN dbo.avoice_candidate.marital_sts
                          = 'D' THEN 'Divorced' WHEN dbo.avoice_candidate.marital_sts = 'W' THEN 'Widowed' ELSE 'Unknown' END AS marital_sts, dbo.avoice_candidate.chalenge_vote, 
                         dbo.avoice_candidate.chal_vte_reas, 
                         CASE WHEN dbo.avoice_candidate.bad_addr_flg = 0 THEN 'No' WHEN dbo.avoice_candidate.bad_addr_flg = 1 THEN 'Yes' ELSE 'No' END AS bad_addr_flg, 
                         CASE WHEN dbo.avoice_candidate.misc_data1 > ' ' THEN dbo.avoice_candidate.misc_data1 ELSE 'None' END AS misc_data1, 
                         CASE WHEN dbo.avoice_candidate.misc_data2 > ' ' THEN dbo.avoice_candidate.misc_data2 ELSE 'None' END AS misc_data2, 
                         dbo.avoice_candidate.lastupdateduser, dbo.avoice_candidate.lastupdateddate, dbo.avoice_candidate.moduleid, dbo.avoice_candidate.roleid, 
                         dbo.avoice_campaign.campaign_name, dbo.avoice_candidate.work_ext, 
                         CASE WHEN dbo.avoice_candidate.full_time = 'Y' THEN 'Yes' WHEN dbo.avoice_candidate.full_time = 'N' THEN 'No' ELSE 'Unknown' END AS full_time, 
                         dbo.avoice_candidate.hours_worked, dbo.avoice_candidate.supervisor, dbo.avoice_candidate.pay_rate, 
                         CASE WHEN dbo.avoice_candidate.hire_dte = '19000101' THEN NULL ELSE dbo.avoice_candidate.hire_dte END AS hire_dte, dbo.avoice_candidate.term_reason, 
                         CASE WHEN dbo.avoice_candidate.seasonal = 'N' THEN 'No' WHEN dbo.avoice_candidate.seasonal = 'Y' THEN 'Yes' ELSE '' END AS seasonal, 
                         dbo.avoice_candidate.work_email, dbo.avoice_location.location_name, CASE WHEN avoice_vw_TableDetail_1.Description IS NULL 
                         THEN 'Unknown' ELSE avoice_vw_TableDetail_1.Description END AS DescDept, CASE WHEN avoice_vw_TableDetail_2.Description IS NULL 
                         THEN 'Unknown' ELSE avoice_vw_TableDetail_2.Description END AS DescStatus, CASE WHEN avoice_vw_TableDetail_3.Description IS NULL 
                         THEN 'Unknown' ELSE avoice_vw_TableDetail_3.Description END AS DescShift, CASE WHEN avoice_vw_TableDetail_4.Description IS NULL 
                         THEN 'Unknown' ELSE avoice_vw_TableDetail_4.Description END AS DescJob, CASE WHEN avoice_vw_TableDetail_5.Description IS NULL 
                         THEN 'Unknown' ELSE avoice_vw_TableDetail_5.Description END AS DescRace, CASE WHEN dbo.avoice_vw_TableDetail.Description IS NULL 
                         THEN 'Unknown' ELSE dbo.avoice_vw_TableDetail.Description END AS DescLanguage, CASE WHEN avoice_vw_TableDetail_6.Description IS NULL 
                         THEN 'Not Contacted' ELSE avoice_vw_TableDetail_6.Description END AS DescContact, CASE WHEN avoice_vw_TableDetail_7.Description IS NULL 
                         THEN 'None' ELSE avoice_vw_TableDetail_7.Description END AS DescEval, dbo.avoice_candidate.native_lang, 
                         CASE WHEN dbo.avoice_candidate.english_speak = 'N' THEN 'No' WHEN dbo.avoice_candidate.english_speak = 'Y' THEN 'Yes' ELSE '' END AS english_speak, 
                         dbo.avoice_candidate.sex AS Expr2, dbo.avoice_candidate.marital_sts AS Expr3, dbo.avoice_candidate.seasonal AS Expr4, 
                         CASE WHEN dbo.avoice_candidate.term_dte = '19000101' THEN NULL ELSE dbo.avoice_candidate.term_dte END AS term_dte, dbo.avoice_candidate.user_added, 
                         dbo.avoice_candidate.date_added, dbo.avoice_candidate.home_addr2, dbo.avoice_candidate.misc_flag3, dbo.avoice_candidate.misc_flag4
FROM            dbo.avoice_vw_TableDetail AS avoice_vw_TableDetail_7 RIGHT OUTER JOIN
                         dbo.vw_avoice_last_contacted ON avoice_vw_TableDetail_7.Id = dbo.vw_avoice_last_contacted.evaluation RIGHT OUTER JOIN
                         dbo.vw_avoice_last_signed_card RIGHT OUTER JOIN
                         dbo.avoice_candidate INNER JOIN
                         dbo.avoice_location ON dbo.avoice_candidate.location_id = dbo.avoice_location.location_id INNER JOIN
                         dbo.avoice_campaign ON dbo.avoice_candidate.campaign_id = dbo.avoice_campaign.campaign_id ON 
                         dbo.vw_avoice_last_signed_card.candidate_id = dbo.avoice_candidate.candidate_id LEFT OUTER JOIN
                         dbo.vw_avoice_last_contact_type LEFT OUTER JOIN
                         dbo.avoice_vw_TableDetail AS avoice_vw_TableDetail_6 ON dbo.vw_avoice_last_contact_type.contact_type = avoice_vw_TableDetail_6.Id ON 
                         dbo.avoice_candidate.candidate_id = dbo.vw_avoice_last_contact_type.candidate_id ON 
                         dbo.vw_avoice_last_contacted.candidate_id = dbo.avoice_candidate.candidate_id LEFT OUTER JOIN
                         dbo.vw_avoice_candidate_NO_contacts ON dbo.avoice_candidate.candidate_id = dbo.vw_avoice_candidate_NO_contacts.candidate_id LEFT OUTER JOIN
                         dbo.avoice_vw_TableDetail AS avoice_vw_TableDetail_5 ON dbo.avoice_candidate.race = avoice_vw_TableDetail_5.Id LEFT OUTER JOIN
                         dbo.avoice_vw_TableDetail ON dbo.avoice_candidate.native_lang = dbo.avoice_vw_TableDetail.Id LEFT OUTER JOIN
                         dbo.avoice_vw_TableDetail AS avoice_vw_TableDetail_4 ON dbo.avoice_candidate.tbl_dtl_job_id = avoice_vw_TableDetail_4.Id LEFT OUTER JOIN
                         dbo.avoice_vw_TableDetail AS avoice_vw_TableDetail_2 ON dbo.avoice_candidate.status = avoice_vw_TableDetail_2.Id LEFT OUTER JOIN
                         dbo.avoice_vw_TableDetail AS avoice_vw_TableDetail_3 ON dbo.avoice_candidate.work_shift = avoice_vw_TableDetail_3.Id LEFT OUTER JOIN
                         dbo.avoice_vw_TableDetail AS avoice_vw_TableDetail_1 ON dbo.avoice_candidate.tbl_dtl_dept_id = avoice_vw_TableDetail_1.Id
WHERE        (CASE WHEN dbo.avoice_candidate.term_dte = '19000101' THEN NULL ELSE dbo.avoice_candidate.term_dte END IS NULL)

Well that's certainly a lot of business logic in the code but I'm deeply sorry to state that there's far worse you're going to encounter in the wild. Can you please paste the execution plan? Do you use SQL Sentry Plan Explorer? If so, send that plan info too. It lets you anonymize the data as well. — Ali Razeghi - AWS, Apr 1, 2015 at 17:49
also how long ago did the query change? You might be able to pull up the old one from cache or if you have DML logging triggers — Ali Razeghi - AWS, Apr 1, 2015 at 17:59
The plan is too large to copy the xml on this site, is there a way to upload a file on the forums here?... I have the plan posted over on EE if you can access it - experts-exchange.com/questions/28648116/… — Alan Troyan, Apr 1, 2015 at 18:17
Are the stats updated especially on the devdnn.dbo.[avoice_candidate] ? The actual vs estimated rows are way off. — Kin Shah, Apr 1, 2015 at 19:34
Please use a decent paste site, like pastebin.com/ next time. — ypercubeᵀᴹ, Apr 1, 2015 at 19:42

Stephan · Accepted Answer · 2015-09-03 23:04:10Z

First poorsql.com is your best friend for formattting issues. You have some funky joins with on clauses all over the place. I'm not really even sure how it's working in SQL-Server. IF I were you, I'd rewrite the whole thing or at least the joins. I can't do it without sample data.

As far as indexes and stats and all that try using the database engine tuning advisor. It should let you know what indexes to build and what stats to update, etc...

But I think I did find some quick easy wins. One easy one is your where clause. It should use any indexes you have now. I went through a couple of the columns and cleaned them up a bit. But I think the biggest performance boost should come from how I load a view that is joined like 5 times into a temp table and use the temp table. Try it out and let me know.

IF OBJECT_ID('tempdb..#avoice+vw_TableDetail') IS NOT NULL
    DROP TABLE #avoice_vw_TableDetail;

--This view is in multiple joins so load it into a temp table
SELECT * INTO #avoice_vw_TableDetail
FROM dbo.avoice_vw_TableDetail


SELECT CASE 
        WHEN dbo.vw_avoice_candidate_NO_contacts.candidate_id IS NULL
            THEN 'Yes'
        ELSE 'No'
        END AS ever_contacted
    ,dbo.vw_avoice_last_contacted.contact_dte
    ,dbo.vw_avoice_last_contacted.lastupdateddate AS last_contact_date
    ,CASE 
        WHEN dbo.vw_avoice_last_signed_card.signed_card IS NULL
            THEN 'No'
        ELSE dbo.vw_avoice_last_signed_card.signed_card
        END AS signed_card
    ,CASE 
        WHEN dbo.vw_avoice_last_signed_card.sign_card_dte = '19000101'
            THEN NULL
        ELSE dbo.vw_avoice_last_signed_card.sign_card_dte
        END AS sign_card_dte
    ,dbo.avoice_candidate.candidate_id
    ,dbo.avoice_candidate.campaign_id
    ,dbo.avoice_candidate.location_id
    ,dbo.avoice_candidate.ssn
    ,dbo.avoice_candidate.lst_nme
    ,dbo.avoice_candidate.frst_nme
    ,dbo.avoice_candidate.avc_init
    ,dbo.avoice_candidate.name_suffix
    ,CASE 
        WHEN len(avc_init) > 0
            AND NOT avc_init IS NULL
            THEN LTRIM(isnull(name_suffix, '') + ' ' + frst_nme + ' ' + avc_init + ' ' + lst_nme)
        ELSE LTRIM(isnull(name_suffix, '') + ' ' + frst_nme + ' ' + isnull(lst_nme, ''))
        END AS Full_Nme
    ,dbo.avoice_candidate.addr1
    ,dbo.avoice_candidate.addr2
    ,CASE 
        WHEN avoice_candidate.addr2 IS NULL
            OR avoice_candidate.addr2 = ' '
            OR avoice_candidate.addr2 = ''
            THEN avoice_candidate.city + ', ' + avoice_candidate.STATE + ' ' + CASE 
                    WHEN avoice_candidate.zip_cde IS NULL
                        THEN ' '
                    ELSE avoice_candidate.zip_cde
                    END
        ELSE avoice_candidate.addr2
        END AS disp_addr2
    ,CASE 
        WHEN avoice_candidate.addr2 IS NULL
            OR avoice_candidate.addr2 = ' '
            OR avoice_candidate.addr2 = ''
            THEN ' '
        ELSE avoice_candidate.city + ', ' + avoice_candidate.STATE + ' ' + CASE 
                WHEN avoice_candidate.zip_cde IS NULL
                    THEN ' '
                ELSE avoice_candidate.zip_cde
                END
        END AS disp_csz
    ,CASE 
        WHEN dbo.avoice_candidate.city IS NULL
            THEN 'Unknown'
        WHEN LEN(RTRIM(dbo.avoice_candidate.city)) < 1
            THEN 'Unknown'
        ELSE dbo.avoice_candidate.city
        END AS city
    ,dbo.avoice_candidate.STATE
    ,CASE 
        WHEN avoice_candidate.zip_cde IS NULL
            THEN ' '
        ELSE avoice_candidate.zip_cde
        END AS zip_cde
    ,dbo.avoice_candidate.home_addr1
    ,CASE 
        WHEN home_addr2 IS NULL
            OR home_addr2 = ' '
            OR home_addr2 = ''
            THEN avoice_candidate.city + ', ' + avoice_candidate.STATE + ' ' + CASE 
                    WHEN avoice_candidate.zip_cde IS NULL
                        THEN ' '
                    ELSE avoice_candidate.zip_cde
                    END
        ELSE home_addr2
        END AS home_disp_addr2
    ,CASE 
        WHEN home_addr2 IS NULL
            OR home_addr2 = ' '
            OR home_addr2 = ''
            THEN ' '
        ELSE avoice_candidate.city + ', ' + avoice_candidate.STATE + ' ' + CASE 
                WHEN avoice_candidate.zip_cde IS NULL
                    THEN ' '
                ELSE avoice_candidate.zip_cde
                END
        END AS home_disp_csz
    ,dbo.avoice_candidate.home_city
    ,dbo.avoice_candidate.home_state
    ,dbo.avoice_candidate.home_zip_cde
    ,dbo.avoice_candidate.misc_flag1
    ,dbo.avoice_candidate.misc_flag2
    ,dbo.avoice_candidate.misc_number1
    ,dbo.avoice_candidate.misc_number2
    ,CASE 
        WHEN dbo.avoice_candidate.misc_date1 = '19000101'
            THEN NULL
        ELSE dbo.avoice_candidate.misc_date1
        END AS misc_date1
    ,CASE 
        WHEN dbo.avoice_candidate.misc_date2 = '19000101'
            THEN NULL
        ELSE dbo.avoice_candidate.misc_date2
        END AS misc_date2
    ,dbo.avoice_candidate.note
    ,dbo.avoice_candidate.txt_msg_flg
    ,dbo.avoice_candidate.latitude
    ,dbo.avoice_candidate.longitude
    ,dbo.avoice_candidate.zoom
    ,dbo.avoice_candidate.iconfile
    ,dbo.avoice_candidate.county
    ,dbo.avoice_candidate.ctry_cde
    ,dbo.avoice_candidate.home_phone

    --Changed to OR instead of having so many when clauses
    ,CASE 
        WHEN dbo.avoice_candidate.home_phone = '(___) ___-____'
             OR dbo.avoice_candidate.home_phone = ''
             OR dbo.avoice_candidate.home_phone IS NULL
            THEN 'No'
        ELSE 'Yes'
        END AS phone
    ,CASE 
        WHEN dbo.avoice_candidate.cell_phone = '(___) ___-____'
             OR dbo.avoice_candidate.cell_phone = ''
             OR dbo.avoice_candidate.cell_phone IS NULL
            THEN 'No'
        ELSE 'Yes'
        END AS cellphone
    ,CASE 
        WHEN dbo.avoice_candidate.work_phone > ' '
            THEN 'Yes'
        ELSE 'No'
        END AS workphone
    ,dbo.avoice_candidate.cell_phone
    ,dbo.avoice_candidate.work_phone
    ,dbo.avoice_candidate.email_addr
    ,CASE 
        WHEN dbo.avoice_candidate.email_addr > ' '
            THEN 'Yes'
        ELSE 'No'
        END AS email
    ,dbo.avoice_candidate.fax
    ,CASE 
        WHEN dbo.avoice_candidate.birth_dte = '19000101'
            THEN NULL
        ELSE dbo.avoice_candidate.birth_dte
        END AS birth_dte
    ,CASE 
        WHEN dbo.avoice_candidate.sex = 'M'
            THEN 'Male'
        WHEN dbo.avoice_candidate.sex = 'F'
            THEN 'Female'
        ELSE 'Unknown'
        END AS sex
    ,CASE 
        WHEN dbo.avoice_candidate.marital_sts = 'M'
            THEN 'Married'
        WHEN dbo.avoice_candidate.marital_sts = 'S'
            THEN 'Single'
        WHEN dbo.avoice_candidate.marital_sts = 'D'
            THEN 'Divorced'
        WHEN dbo.avoice_candidate.marital_sts = 'W'
            THEN 'Widowed'
        ELSE 'Unknown'
        END AS marital_sts
    ,dbo.avoice_candidate.chalenge_vote
    ,dbo.avoice_candidate.chal_vte_reas
    ,CASE 
        WHEN dbo.avoice_candidate.bad_addr_flg = 0
            THEN 'No'
        WHEN dbo.avoice_candidate.bad_addr_flg = 1
            THEN 'Yes'
        ELSE 'No'
        END AS bad_addr_flg
    ,CASE 
        WHEN dbo.avoice_candidate.misc_data1 > ' '
            THEN dbo.avoice_candidate.misc_data1
        ELSE 'None'
        END AS misc_data1
    ,CASE 
        WHEN dbo.avoice_candidate.misc_data2 > ' '
            THEN dbo.avoice_candidate.misc_data2
        ELSE 'None'
        END AS misc_data2
    ,dbo.avoice_candidate.lastupdateduser
    ,dbo.avoice_candidate.lastupdateddate
    ,dbo.avoice_candidate.moduleid
    ,dbo.avoice_candidate.roleid
    ,dbo.avoice_campaign.campaign_name
    ,dbo.avoice_candidate.work_ext
    ,CASE 
        WHEN dbo.avoice_candidate.full_time = 'Y'
            THEN 'Yes'
        WHEN dbo.avoice_candidate.full_time = 'N'
            THEN 'No'
        ELSE 'Unknown'
        END AS full_time
    ,dbo.avoice_candidate.hours_worked
    ,dbo.avoice_candidate.supervisor
    ,dbo.avoice_candidate.pay_rate
    ,CASE 
        WHEN dbo.avoice_candidate.hire_dte = '19000101'
            THEN NULL
        ELSE dbo.avoice_candidate.hire_dte
        END AS hire_dte
    ,dbo.avoice_candidate.term_reason
    ,CASE 
        WHEN dbo.avoice_candidate.seasonal = 'N'
            THEN 'No'
        WHEN dbo.avoice_candidate.seasonal = 'Y'
            THEN 'Yes'
        ELSE ''
        END AS seasonal
    ,dbo.avoice_candidate.work_email
    ,dbo.avoice_location.location_name

    --Changed to ISNULL() instead of case when
    ,ISNULL(avoice_vw_TableDetail_1.Description,'Unknown')          AS DescDept
    ,ISNULL(avoice_vw_TableDetail_2.Description,'Unknown')          AS DescStatus
    ,ISNULL(avoice_vw_TableDetail_3.Description,'Unknown')          AS DescShift
    ,ISNULL(avoice_vw_TableDetail_4.Description,'Unknown')          AS DescJob
    ,ISNULL(avoice_vw_TableDetail_5.Description,'Unknown')          AS DescRace
    ,ISNULL(avoice_vw_TableDetail.Description,'Unknown')            AS DescLanguage
    ,ISNULL(avoice_vw_TableDetail_6.Description,'Not Contacted')    AS DescContact
    ,ISNULL(avoice_vw_TableDetail_7.Description,'None')             AS DescContact
    ,dbo.avoice_candidate.native_lang
    ,CASE 
        WHEN dbo.avoice_candidate.english_speak = 'N'
            THEN 'No'
        WHEN dbo.avoice_candidate.english_speak = 'Y'
            THEN 'Yes'
        ELSE ''
        END AS english_speak
    ,dbo.avoice_candidate.sex AS Expr2
    ,dbo.avoice_candidate.marital_sts AS Expr3
    ,dbo.avoice_candidate.seasonal AS Expr4
    ,CASE 
        WHEN dbo.avoice_candidate.term_dte = '19000101'
            THEN NULL
        ELSE dbo.avoice_candidate.term_dte
        END AS term_dte
    ,dbo.avoice_candidate.user_added
    ,dbo.avoice_candidate.date_added
    ,dbo.avoice_candidate.home_addr2
    ,dbo.avoice_candidate.misc_flag3
    ,dbo.avoice_candidate.misc_flag4

--Also changed this to the temp table
FROM #avoice_vw_TableDetail AS avoice_vw_TableDetail_7

RIGHT JOIN dbo.vw_avoice_last_contacted 
ON avoice_vw_TableDetail_7.Id = dbo.vw_avoice_last_contacted.evaluation

RIGHT JOIN dbo.vw_avoice_last_signed_card
    RIGHT JOIN dbo.avoice_candidate
        INNER JOIN dbo.avoice_location 
        ON dbo.avoice_candidate.location_id = dbo.avoice_location.location_id

        INNER JOIN dbo.avoice_campaign 
        ON dbo.avoice_candidate.campaign_id = dbo.avoice_campaign.campaign_id 
    ON dbo.vw_avoice_last_signed_card.candidate_id = dbo.avoice_candidate.candidate_id 

    LEFT JOIN dbo.vw_avoice_last_contact_type
        LEFT JOIN dbo.avoice_vw_TableDetail AS avoice_vw_TableDetail_6 
        ON dbo.vw_avoice_last_contact_type.contact_type = avoice_vw_TableDetail_6.Id 
    ON dbo.avoice_candidate.candidate_id = dbo.vw_avoice_last_contact_type.candidate_id 

ON dbo.vw_avoice_last_contacted.candidate_id = dbo.avoice_candidate.candidate_id


LEFT JOIN dbo.vw_avoice_candidate_NO_contacts ON dbo.avoice_candidate.candidate_id = dbo.vw_avoice_candidate_NO_contacts.candidate_id 


--Note these joins all use the same vw so use a temp table instead
LEFT JOIN #avoice_vw_TableDetail AS avoice_vw_TableDetail_5 ON dbo.avoice_candidate.race = avoice_vw_TableDetail_5.Id 
LEFT JOIN #avoice_vw_TableDetail AS avoice_vw_TableDetail_4 ON dbo.avoice_candidate.tbl_dtl_job_id = avoice_vw_TableDetail_4.Id 
LEFT JOIN #avoice_vw_TableDetail AS avoice_vw_TableDetail_2 ON dbo.avoice_candidate.STATUS = avoice_vw_TableDetail_2.Id 
LEFT JOIN #avoice_vw_TableDetail AS avoice_vw_TableDetail_3 ON dbo.avoice_candidate.work_shift = avoice_vw_TableDetail_3.Id 
LEFT JOIN #avoice_vw_TableDetail AS avoice_vw_TableDetail_1 ON dbo.avoice_candidate.tbl_dtl_dept_id = avoice_vw_TableDetail_1.Id 
LEFT JOIN #avoice_vw_TableDetail ON dbo.avoice_candidate.native_lang = dbo.avoice_vw_TableDetail.Id 


--Fixed your where clause so it will use any indexes you have
WHERE dbo.avoice_candidate.term_dte = '19000101'

This is standard SQL (probably produced by the help of some visual tool). I mean the FROM a JOIN (b JOIN c ON c.x=b.x) ON b_or_c.y=a.y part. The parentheses are optional but help to understand how it is parsed. — ypercubeᵀᴹ, Apr 2, 2015 at 9:06
Thanks for the info. He tagged it SQL-Server andd I didn't know SQL Server allowed it without the parentheses. Yikes so now to figure out where the parentheses would go as well... — Stephan, Apr 2, 2015 at 14:04

Stack Exchange Network

SQL Query - Possibly the most poorly written query in the history of mankind

1 Answer 1

Your Answer

Not the answer you're looking for? Browse other questions tagged
sql-server
or ask your own question.

Hot Network Questions

SQL Query - Possibly the most poorly written query in the history of mankind

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged sql-server or ask your own question.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
sql-server
or ask your own question.