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

Question

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

atroyan84

SSC Enthusiast

Points: 151
More actions
April 1, 2015 at 11:46 am

#296532

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)
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

This is an awful query, but it's not just the query. The problem comes from the database design. I see a special problem on dbo.avoice_vw_TableDetail as it is completely denormalized.

Another problem might come from using views instead of tables.

I'm just guessing here because I can't have the full picture of what's happening here.

I believe that this goes beyond a forum post.

I'll just leave a slightly more readable version of it. I hope that I didn't change the results.

SELECT CASE

WHEN acnc.candidate_id IS NULL

THEN 'Yes'

ELSE 'No'

END AS ever_contacted

,alt.contact_dte

,alt.lastupdateddate AS last_contact_date

,ISNULL(alsc.signed_card, 'No') AS signed_card

,NULLIF(alsc.sign_card_dte, '19000101') AS sign_card_dte

,ac.candidate_id

,ac.campaign_id

,ac.location_id

,ac.ssn

,ac.lst_nme

,ac.frst_nme

,ac.avc_init

,ac.name_suffix

,isnull(name_suffix + ' ', '') + frst_nme + ' ' + ISNULL(avc_init + ' ', '') + isnull(lst_nme, '') AS Full_Nme

,ac.addr1

,ac.addr2

,CASE

WHEN ac.addr2 IS NULL

OR ac.addr2 = ' '

OR ac.addr2 = ''

THEN ac.city + ', ' + ac.STATE + ' ' + ISNULL(ac.zip_cde, '')

ELSE ac.addr2

END AS disp_addr2

,CASE

WHEN ac.addr2 IS NULL

OR ac.addr2 = ' '

OR ac.addr2 = ''

THEN ' '

ELSE ac.city + ', ' + ac.STATE + ' ' + ISNULL(ac.zip_cde, '')

END AS disp_csz

,CASE

WHEN ac.city IS NULL

THEN 'Unknown'

WHEN LEN(RTRIM(ac.city)) < 1

THEN 'Unknown'

ELSE ac.city

END AS city

,ac.STATE

,ISNULL(ac.zip_cde, '') AS zip_cde

,ac.home_addr1

,CASE

WHEN home_addr2 IS NULL

OR home_addr2 = ' '

OR home_addr2 = ''

THEN ac.city + ', ' + ac.STATE + ' ' + ISNULL(ac.zip_cde, '')

ELSE home_addr2 END AS home_disp_addr2

,CASE

WHEN home_addr2 IS NULL

OR home_addr2 = ' '

OR home_addr2 = ''

THEN ' '

ELSE ac.city + ', ' + ac.STATE + ' ' + ISNULL(ac.zip_cde, '') END AS home_disp_csz

,ac.home_city

,ac.home_state

,ac.home_zip_cde

,ac.misc_flag1

,ac.misc_flag2

,ac.misc_number1

,ac.misc_number2

,NULLIF(ac.misc_date1, '19000101') AS misc_date1

,NULLIF(ac.misc_date2, '19000101') AS misc_date2

,ac.note

,ac.txt_msg_flg

,ac.latitude

,ac.longitude

,ac.zoom

,ac.iconfile

,ac.county

,ac.ctry_cde

,ac.home_phone

,CASE

WHEN ac.home_phone = '(_) _-____'

THEN 'No'

WHEN ac.home_phone = ''

THEN 'No'

WHEN ac.home_phone IS NULL

THEN 'No'

ELSE 'Yes'

END AS phone

,CASE

WHEN ac.cell_phone = '(_) _-____'

THEN 'No'

WHEN ac.cell_phone = ''

THEN 'No'

WHEN ac.cell_phone IS NULL

THEN 'No'

ELSE 'Yes'

END AS cellphone

,CASE

WHEN ac.work_phone > ' '

THEN 'Yes'

ELSE 'No'

END AS workphone

,ac.cell_phone

,ac.work_phone

,ac.email_addr

,CASE

WHEN ac.email_addr > ' '

THEN 'Yes'

ELSE 'No'

END AS email

,ac.fax

,NULLIF(ac.birth_dte, '19000101') AS birth_dte

,CASE

WHEN ac.sex = 'M'

THEN 'Male'

WHEN ac.sex = 'F'

THEN 'Female'

ELSE 'Unknown'

END AS sex

,CASE

WHEN ac.marital_sts = 'M'

THEN 'Married'

WHEN ac.marital_sts = 'S'

THEN 'Single'

WHEN ac.marital_sts = 'D'

THEN 'Divorced'

WHEN ac.marital_sts = 'W'

THEN 'Widowed'

ELSE 'Unknown'

END AS marital_sts

,ac.chalenge_vote

,ac.chal_vte_reas

,CASE

WHEN ac.bad_addr_flg = 0

THEN 'No'

WHEN ac.bad_addr_flg = 1

THEN 'Yes'

ELSE 'No'

END AS bad_addr_flg

,CASE

WHEN ac.misc_data1 > ' '

THEN ac.misc_data1

ELSE 'None'

END AS misc_data1

,CASE

WHEN ac.misc_data2 > ' '

THEN ac.misc_data2

ELSE 'None'

END AS misc_data2

,ac.lastupdateduser

,ac.lastupdateddate

,ac.moduleid

,ac.roleid

,acamp.campaign_name

,ac.work_ext

,CASE

WHEN ac.full_time = 'Y'

THEN 'Yes'

WHEN ac.full_time = 'N'

THEN 'No'

ELSE 'Unknown'

END AS full_time

,ac.hours_worked

,ac.supervisor

,ac.pay_rate

,NULLIF(ac.hire_dte, '19000101') AS hire_dte

,ac.term_reason

,CASE

WHEN ac.seasonal = 'N'

THEN 'No'

WHEN ac.seasonal = 'Y'

THEN 'Yes'

ELSE ''

END AS seasonal

,ac.work_email

,al.location_name

,ISNULL(atd1.Description, 'Unknown') AS DescDept

,ISNULL(atd2.Description, 'Unknown') AS DescStatus

,ISNULL(atd3.Description, 'Unknown') AS DescShift

,ISNULL(atd4.Description, 'Unknown') AS DescJob

,ISNULL(atd5.Description, 'Unknown') AS DescRace

,ISNULL(atd.Description, 'Unknown') AS DescLanguage

,ISNULL(atd6.Description, 'Not Contacted') AS DescContact

,ISNULL(atd7.Description, 'None') AS DescEval

,ac.native_lang

,CASE

WHEN ac.english_speak = 'N'

THEN 'No'

WHEN ac.english_speak = 'Y'

THEN 'Yes'

ELSE ''

END AS english_speak

,ac.sex AS Expr2

,ac.marital_sts AS Expr3

,ac.seasonal AS Expr4

,NULLIF(ac.term_dte, '19000101') AS term_dte

,ac.user_added

,ac.date_added

,ac.home_addr2

,ac.misc_flag3

,ac.misc_flag4

FROM dbo.avoice_vw_TableDetail AS atd7

RIGHT OUTER JOIN dbo.vw_avoice_last_contacted AS alt ON atd7.Id = alt.evaluation

RIGHT OUTER JOIN dbo.avoice_candidate AS ac ON alt.candidate_id = ac.candidate_id

RIGHT OUTER JOIN dbo.vw_avoice_last_signed_card AS alsc ON ac.candidate_id = alsc.candidate_id

INNER JOIN dbo.avoice_location AS al ON ac.location_id = al.location_id

INNER JOIN dbo.avoice_campaign AS acamp ON ac.campaign_id = acamp.campaign_id

LEFT OUTER JOIN dbo.vw_avoice_last_contact_type AS alct ON ac.candidate_id = alct.candidate_id

LEFT OUTER JOIN dbo.avoice_vw_TableDetail AS atd6 ON alct.contact_type = atd6.Id

LEFT OUTER JOIN dbo.vw_avoice_candidate_NO_contacts AS acnc ON ac.candidate_id = acnc.candidate_id

LEFT OUTER JOIN dbo.avoice_vw_TableDetail AS atd5 ON ac.race = atd5.Id

LEFT OUTER JOIN dbo.avoice_vw_TableDetail AS atd ON ac.native_lang = atd.Id

LEFT OUTER JOIN dbo.avoice_vw_TableDetail AS atd4 ON ac.tbl_dtl_job_id = atd4.Id

LEFT OUTER JOIN dbo.avoice_vw_TableDetail AS atd2 ON ac.status = atd2.Id

LEFT OUTER JOIN dbo.avoice_vw_TableDetail AS atd3 ON ac.work_shift = atd3.Id

LEFT OUTER JOIN dbo.avoice_vw_TableDetail AS atd1 ON ac.tbl_dtl_dept_id = atd1.Id

WHERE ac.term_dte = '19000101'

OR ac.term_dte IS NULL

EDIT: Correct code

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 2

Quick thought, this is NOT a tuning task, this demands a complete rewrite! Anything with the cardinality in excess of 10^12 going through both HASH MATCH 8 times, apart from everything else, is enough to muffle even the beefiest of servers.

😎

I would not only reject the code, I would break this programmers keyboard....

mister.magoo SSC-Forever Points: 47068 More actions · Answer 3

I see two choices.

1. It is April 1st.

2. You win the prize for worst query ever.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Eric M Russell SSC Guru Points: 125521 More actions · Answer 4

LOL! Every SQL Server related job I've ever had, at some point during the first week, I get handed some pile of nonsense like this and told it's now one of my responsibilities to fix it. I'm still hoping there is a green field opportunity out there somewhere for me, where I can start with a set of well documented requirements and then write code the right way from the start.

Glancing through the SQL, it seems there are a lot of transformations going on in this query, and also a lot of conditional logic to compensate for missing data. It helps if you can see a copy of a past report, so you can visualize the final result. If you're luckty, you can get some functional documentation from the business that describes what they use it for. Talk to whomever uses this thing. You'd be surprised how many BI reports get thrown together, scheduled to kick off every night, but then no one looks at them. Half of the stuff a typical BI or accounting team has running can typically be discarded.

If they seriously want you to spend your time on this thing, then block off a day or two on your calendar and deconstruct all these joins and conditions. Copy a small subset of the data to your local database, so you can tweak it and re-run it a hundred times without having to wait half an hour in between for it to return a result.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 5

It's only 238 lines. That's what, two printed pages, maybe three. We don't start talking about how evil a query is until it breaks at least 10 printed pages.

So, we have a view. That we join over and over. Indications are that view is also somewhat problematic. I love the hash match join on, did I count the zeros correctly, 130 billion rows. I'm sure no one is shocked to find that the optimizer timed out.

First question, what's this for? No one looks at 300 billion rows. And, as I'm sure you're figuring out, you can't really process 300 billion rows in a single step.

This is not a tuning project. This is a redesign project. Eirikur has it right (which is not abnormal).

Second question, is it 300 billion (or however many zeroes there are) rows or, on top of the very problematic T-SQL do you also have statistics issues?

I'm with Erik. Break this thing down to the smallest possible pieces and start rebuilding one join at a time, directly against the tables. Don't use that view ever again.

If everything else wasn't enough, pretty sure this will also lead to scans:

(CASE WHEN dbo.avoice_candidate.term_dte = '19000101' THEN NULL

ELSE dbo.avoice_candidate.term_dte

END IS NULL)

Could be wrong about that, but it hardly matters. You need to reconstruct this thing entirely AND, most importantly, understand what it's actually for, what it is intended to accomplish.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 6

Eirikur Eiriksson (4/1/2015)
Quick thought, this is NOT a tuning task, this demands a complete rewrite! Anything with the cardinality in excess of 10^12 going through both HASH MATCH 8 times, apart from everything else, is enough to muffle even the beefiest of servers.
😎
I would not only reject the code, I would break this programmers keyboard....

Over his/her head.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Grant Fritchey (4/1/2015)
We don't start talking about how evil a query is until it breaks at least 10 printed pages.

My worst ~~query~~ procedure so far is 95 printed pages using more nested table-valued functions than I've ever seen. Worst single query would be an EF-generated amusement which ran to ~750 lines, at least it did once formatted in a readable way.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

mister.magoo (4/1/2015)
I see two choices.
1. It is April 1st.
2. You win the prize for worst query ever.

My guess is that that is a cardinality estimation error, either from poor stats (though that more often causes low estimations than high) or SQL thinking the joins are many-to-many when they aren't.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 9

Looking at plan i see only 3 tables behind the views. Seems like kind of EAV database design.

Probably optimizer is totally lost on proper cardinality estimations when decoding views which reconstruct 'semantic' from EAV storage, as GilaMonster pointed out.

Can you share DDL at least for avoice_vw_TableDetail and dbo.avoice_candidate ?

webrunner SSC-Dedicated Points: 31715 More actions · Answer 10

Grant Fritchey (4/1/2015)
It's only 238 lines. That's what, two printed pages, maybe three. We don't start talking about how evil a query is until it breaks at least 10 printed pages. ...

+100 !!!

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html