February 8, 2011 at 11:10 am
I have to rewrite an inherited query that nested subqueries because it used to run but now fails after running for 1 1/2 hours - disk full, ran out of space.
In addition the nested subqueries in the from clause has a several where clauses and all values are stored in one field.
I took out the subqueries and created joins with the data but still runs out -so it is with the where clause.
here is the query:
SELECT TOP (100) PERCENT a.document_id, a.document_uid,
MAX(CASE WHEN y.prompt = 'Date Notes / Claim Received' THEN y.value ELSE NULL END)
AS [Date Notes / Claim Received],
MAX(CASE WHEN a.prompt = 'Date From' THEN a.value ELSE NULL END) AS [Date From],
MAX(CASE WHEN y.prompt = 'CASE STATUS' THEN y.value ELSE NULL END) as [Case Status],
MAX(CASE WHEN y.prompt = 'OUTCOME' THEN y.value ELSE NULL END) as [Case Outcome],
(CASE WHEN y.prompt = 'TAX ID' then y.value ELSE NULL END) as [TAX ID],
MAX(CASE WHEN y.prompt = 'Date Actiongram Sent To Humana' or y.prompt = 'DATE DECISION SENT 'THEN y.value ELSE NULL END) AS [Date Sent],
MAX(CASE WHEN y.prompt = 'Case Decision' THEN y.value ELSE NULL END) AS [Case Decision],
MAX(CASE WHEN y.prompt = 'Decision By' THEN y.value ELSE NULL END) AS [Decision By],
MAX(CASE WHEN a.prompt = 'Notes' THEN a.value ELSE NULL END) AS [Notes],
dbo.sf_documents.contact_name, dbo.sf_documents.contact_info AS [Phone/Email]
FROM dbo.v_custom_all_prompts_and_values_withLine AS a INNER JOIN
(SELECT document_uid, line FROM dbo.v_custom_all_prompts_and_values_withLine
WHERE (line <> 'Global') AND (value <> ': : ') AND (value <> ': : : : ') and (value <> ': : : : ') and (value <> ': : : :') and (value <>': : : : ')
GROUP BY document_uid, line) AS x ON a.document_uid = x.document_uid AND a.line = x.line INNER JOIN
(SELECT document_uid, prompt, prompt_name, value, type, sf_name, display_order, depth, sf_system_grid_uid, grid_name, page_number,
document_id, line FROM dbo.v_custom_all_prompts_and_values_withLine AS v_custom_all_prompts_and_values_withLine_1
WHERE (line = 'Global')) AS y ON a.document_uid = y.document_uid INNER JOIN
dbo.sf_documents ON a.document_uid = dbo.sf_documents.document_uid
GROUP BY a.document_uid, a.document_id, a.line, dbo.sf_documents.contact_name, dbo.sf_documents.contact_info
February 8, 2011 at 12:20 pm
I'd have to see the underlying tables and some data from them to be sure, but this looks like a usual "pivot" attempt on a "one true lookup" table.
There are better ways to query those.
Can you provide the table definitions and some sample data? (Anonymize the data as needed, but make it as realistic as possible.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 8, 2011 at 12:27 pm
barb0822
To receive tested answers please, please post Table definition(s), some sample data, (you have already posted your T-SQL statement, but not in an easy to use format), and the desired results.
To do all of the above quickly and easily, please click on the first link in my signature block and follow the instructions in the article.
That will assist people such as GSQuared, to assist you with a tested solution.
February 8, 2011 at 12:32 pm
It looks like a CrossTab query to get data in a pivoted format.
The query itself doesn't look that bad except for the following issues:
The subquery aliased as y selects more columns than used in the outer query. I recommend to remove all columns not being part of the final query.
The WHERE clause for table aliased x is weird: There are three checks for an identical constant (value <> ': : : : '). Should be reduced to one check (I'd expect the query optimizer being smart enough to eliminate the dupe internally anyway...)
One reason the query suddenly fails could be a removed/disabled index leading to table scans and a heavy usage of tempdb...
It would help a lot if you could attach the actual execution plan as a sqlplan file together with the row count for each table, so we'd have some figures to look at.
If proper indexing can't reduce the time to query the data, preaggregation might be the way to go... But I don't think that's needed.
February 8, 2011 at 8:29 pm
ok. thanks all for the speedy responses. will try to get the table structure and some data.
thanks,
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply