Re: query optimize

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? > On 12/9/06, Obnoxio The Clown <obnoxio@serendipita.com> wrote: >> >> >> devaraj.takhellambam@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.

without being able to make any guesses about the data and likely size of the tables, indexes etc (due to generic table/column names), I suspect this fellow's suggestions might help: http://www.onlamp.com/pub/a/onlamp/2004/09/30/from_clauses.html Obnoxio The Clown wrote: > 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.. > >> > 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;