Home » RDBMS Server » Performance Tuning » Performance Tuning in Count(DISTINCT (Oracle 9i)
Performance Tuning in Count(DISTINCT [message #407416] Wed, 10 June 2009 02:38 Go to next message
sivakumar.rj
Messages: 17
Registered: November 2008
Location: Chennai
Junior Member
I have a query which contains COUNT(DISTINCT colname) clause. It takes very long time. Can it be replaced by some other statements or anything else.

SELECT COUNT(DISTINCT a.col1)
  FROM tbl1 a,tbl2 b,tbl3 c
 WHERE a.col2 = b.col1
   AND b.col2 = c.col2
   AND fn_mth(a.col3,a.col4,5) = 12
   AND a.col5 = :input_value 
   AND c.col5 = 0;
Re: Performance Tuning in Count(DISTINCT [message #407427 is a reply to message #407416] Wed, 10 June 2009 02:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is long is due to fn_mth function.

Regards
Michel
Re: Performance Tuning in Count(DISTINCT [message #407439 is a reply to message #407416] Wed, 10 June 2009 03:30 Go to previous messageGo to next message
sivakumar.rj
Messages: 17
Registered: November 2008
Location: Chennai
Junior Member
Its a function to validate some value..thats it..
Re: Performance Tuning in Count(DISTINCT [message #407442 is a reply to message #407439] Wed, 10 June 2009 03:48 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
May be!
But that function is being called for each row.
Can you incorporate that logic in this sql itself?

By
Vamsi
Re: Performance Tuning in Count(DISTINCT [message #407443 is a reply to message #407442] Wed, 10 June 2009 03:50 Go to previous messageGo to next message
sivakumar.rj
Messages: 17
Registered: November 2008
Location: Chennai
Junior Member
no we should nt...we should validate for each and every row...
Re: Performance Tuning in Count(DISTINCT [message #407446 is a reply to message #407443] Wed, 10 June 2009 03:55 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
sivakumar.rj wrote on Wed, 10 June 2009 14:20
no we should nt...we should validate for each and every row...
Then live with it.

Why can't you use the same logic in the sql itself?
What that function does? validate is a VERY vague answer.

By
Vamsi
Re: Performance Tuning in Count(DISTINCT [message #407473 is a reply to message #407446] Wed, 10 June 2009 05:26 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Prove it to yourself. Run the query WITHOUT that function and compare the run time. If it is much faster, then the function is the problem and there is no point tuning anything BUT the function.

However if you remove the function and it is not much faster (or worse, it is slower) then we can concentrate on tuning the rest of the query.

But the other guys are right, it is almost certainly the function - you just need to convince yourself.

Ross Leishman
Re: Performance Tuning in Count(DISTINCT [message #407628 is a reply to message #407416] Wed, 10 June 2009 17:23 Go to previous message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
SELECT Count(DISTINCT a.col1)
FROM   tbl1 a
WHERE  a.col5 = :input_value
 AND   a.col2 IN ( SELECT b.col1 
                   from tbl2 b
                   WHERE b.col2 IN (SELECT c.col2 
                                    from tbl3 c
                                    WHERE c.col5 = 0
                                   )
                 )
 AND   Fn_mth(a.col3,a.col4,5) = 12


How does SQL above perform?

[Updated on: Wed, 10 June 2009 17:31]

Report message to a moderator

Previous Topic: Latch:Library Cache
Next Topic: slow query with MERGE JOIN CARTESIAN and LATCH cache buffer chains
Goto Forum:
  


Current Time: Mon Dec 05 08:59:07 CST 2016

Total time taken to generate the page: 0.14032 seconds