query optimize
All 3 messages in topic - view as tree
From: devaraj.takhellam...@gmail.com - view profile
Date: Sat, Dec 9 2006 9:45 am
Email: devaraj.takhellam...@gmail.com


Hi,

How can I optimize the informix query below- the query uses an OUTER
join...


select col1, col2, '' col3, col4, A.col5, A.col6,
count(*) cout
from table A, table B, table C, OUTER table D
where A.col9 = B.col9 and
A.col7 = D.col7 and
A.col8 = 'Y' and
B.col1 = c.d and
D.col8 = 'Y'
group by 1,2,3,4,5,6;


Thanks,
Jernd


Reply » Rate this post: Text for clearing space


From: Obnoxio The Clown - view profile
Date: Sat, Dec 9 2006 10:33 am
Email: "Obnoxio The Clown"


devaraj.takhellam...@gmail.com said:



> Hi,

> How can I optimize the informix query below- the query uses an OUTER
> join...


> select col1, col2, '' col3, col4, A.col5, A.col6,
> count(*) cout
> from table A, table B, table C, OUTER table D
> where A.col9 = B.col9 and
> A.col7 = D.col7 and
> A.col8 = 'Y' and
> B.col1 = c.d and
> D.col8 = 'Y'
> group by 1,2,3,4,5,6;



Why do you believe it needs optimising?

--
Bye now,
Obnoxio


"I don't read newspapers anymore except the local rag which I do weekly to
cheer myself trying to see if anyone I hate has been stabbed."
-- Horribilis XVI


--
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.


Reply » Rate this post: Text for clearing space


From: Obnoxio The Clown - view profile
Date: Sat, Dec 9 2006 10:52 am
Email: "Obnoxio The Clown"
Groups: comp.databases.informix
Not yet ratedRating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show original | Report Abuse | Find messages by this author


Devaraj Takhellambam said:



> Thanks for replying...The query has been working well and takes just about
> 20-30 minutes in the past but recently it has been degraded and took about
> 3-4 hours and sometimes 5 hour..so i thought it needs to be
> optimized....Thanks..


OK, so how about some platform information?

Hardware / OS / IDS / Versions / RAM / disk / # of rows / dbschema of the
tables and their indexes? How about an explain plan?




- Hide quoted text -
- Show quoted text -

> On 12/9/06, Obnoxio The Clown wrote:

>> devaraj.takhellam...@gmail.com said:
>> > Hi,


>> > How can I optimize the informix query below- the query uses an OUTER
>> > join...


>> > select col1, col2, '' col3, col4, A.col5, A.col6,
>> > count(*) cout
>> > from table A, table B, table C, OUTER table D
>> > where A.col9 = B.col9 and
>> > A.col7 = D.col7 and
>> > A.col8 = 'Y' and
>> > B.col1 = c.d and
>> > D.col8 = 'Y'
>> > group by 1,2,3,4,5,6;


>> Why do you believe it needs optimising?


>> --
>> Bye now,
>> Obnoxio


>> "I don't read newspapers anymore except the local rag which I do weekly
>> to
>> cheer myself trying to see if anyone I hate has been stabbed."
>> -- Horribilis XVI


>> --
>> This message has been scanned for viruses and
>> dangerous content by OpenProtect(http://www.openprotect.com), and is
>> believed to be clean.


> --
> This message has been scanned for viruses and
> dangerous content by OpenProtect(http://www.openprotect.com), and is
> believed to be clean.



--
Bye now,
Obnoxio

"I don't read newspapers anymore except the local rag which I do weekly to
cheer myself trying to see if anyone I hate has been stabbed."
-- Horribilis XVI


--
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.


Reply » Rate this post: Text for clearing space


End of messages