Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query with nested subqueries Expand / Collapse
Author
Message
Posted Tuesday, February 8, 2011 11:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 28, 2014 7:31 PM
Points: 4, Visits: 118
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
Post #1060462
Posted Tuesday, February 8, 2011 12:20 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
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
Post #1060556
Posted Tuesday, February 8, 2011 12:27 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 10:58 AM
Points: 5,333, Visits: 25,272
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
Post #1060561
Posted Tuesday, February 8, 2011 12:32 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:13 PM
Points: 6,842, Visits: 13,364
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
Post #1060568
Posted Tuesday, February 8, 2011 8:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 28, 2014 7:31 PM
Points: 4, Visits: 118
ok. thanks all for the speedy responses. will try to get the table structure and some data.

thanks,
Post #1060782
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse