Joined: 18 May 2005 Posts: 108 Topics: 31 Location: USA
Posted: Tue May 31, 2005 8:07 am Post subject: Fine tuning a query with Join on 4 tables
Hi, I need to tune the below query. Right now this query was giving timeout when I run production volume. I was running on the 32millions of data.
Code:
SELECT CASE RACE
WHEN 'M' THEN 'Missing'
WHEN 'W' THEN 'White/Caucasian'
WHEN 'B' THEN 'Black/Aferican American'
WHEN 'O' THEN 'Other'
END CASE,
g.outgrpid, outcmplo, outgrplbl, outgrpval, count(*)
FROM IDPOUT01.ITOUOUTC c,
IDPOUT01.ITOUGROP g,
IDPOUT01.ITOUADMN a,
IDPOUT01.ITOUFACT f
WHERE c.INSTTYPE = '10'
AND c.OUTTYPE ='EMPOWER'
AND g.OUTGRPID = c.OUTGRPID
AND c.OUTID = f.OUTID
AND f.ADMINID = a.ADMINID
AND f.OUTVAL BETWEEN g.OUTCMPLO AND g.OUTCMPHI
GROUP BY Race, g.outgrpid, outcmplo, outgrplbl, outgrpval
OUTID
ADMINID
OUTGRPID are defined as indexes. Is there any otherway to write/tune this query. Please help me out.
Joined: 26 Nov 2002 Posts: 12378 Topics: 75 Location: San Jose
Posted: Tue May 31, 2005 8:44 am Post subject:
schintala,
Please post with meaningful topic titles. You already had a topic with "Query Optimization " and if you open another topic with the same title , it adds confusion. A better title would be "Fine tuning a query with Join on 4 tables." I am editing the topic title.
Also please try enclose your code in BBCODEs to enable the proper format of the code and also better readability.
put your code enclosed in between the bbcode and check it.
ex:
{code}
This is my query
{/code}
Now replace the { brackets with [ for {CODE} & {/CODE} and your code looks like this
Code:
This is my query
Try this query.
Code:
SELECT CASE RACE
WHEN 'M' THEN 'Missing'
WHEN 'W' THEN 'White/Caucasian'
WHEN 'B' THEN 'Black/Aferican American'
WHEN 'O' THEN 'Other'
END CASE
,g.outgrpid
,outcmplo
,outgrplbl
,outgrpval
,count(*)
FROM IDPOUT01.ITOUGROP g
,IDPOUT01.ITOUADMN a
,IDPOUT01.ITOUFACT f
WHERE (g.OUTGRPID,F.OUTID) in (SELECT OUTGRPID
,OUTID
FROM IDPOUT01.ITOUOUTC
WHERE INSTTYPE = '10'
AND OUTTYPE = 'EMPOWER')
AND f.ADMINID = a.ADMINID
AND f.OUTVAL BETWEEN g.OUTCMPLO AND g.OUTCMPHI
GROUP BY Race
,g.outgrpid
,outcmplo
,outgrplbl
,outgrpval
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
