Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query with nested subqueries


Query with nested subqueries

Author
Message
barb0822
barb0822
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 127
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
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14375 Visits: 9729
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
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5689 Visits: 25280
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.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
LutzM
LutzM
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7001 Visits: 13559
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.



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
barb0822
barb0822
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 127
ok. thanks all for the speedy responses. will try to get the table structure and some data.

thanks,
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search