Home » SQL & PL/SQL » SQL & PL/SQL » High Query Execution Time (Oracle 9i)
High Query Execution Time [message #410767] Tue, 30 June 2009 03:17 Go to next message
MANASH
Messages: 7
Registered: June 2009
Junior Member
Dear Gurus,
I have the folowing query which has a very high execution time
Can it be written in any other way that is faster in execution
please suggest
Regards

Manash.

SELECT acid, tran_id, tran_particular, tran_rmks, ref_num, tran_amt,
       SUBSTR (tran_particular, 19, 10) trandate
  FROM dctd_acli
 WHERE ref_num = &&3
   AND tran_rmks NOT LIKE '  %'
   AND tran_amt > 100
   AND entry_user_id = 'CDCI'
   AND part_tran_type = 'D'
   AND value_date BETWEEN &&1 AND &&2
MINUS
(SELECT acid, tran_id, tran_particular, tran_rmks, ref_num, tran_amt,
        SUBSTR (tran_particular, 19, 10) trandate
   FROM dctd_acli
  WHERE ref_num = &&3
    AND TRIM (tran_id) IN (
           SELECT TRIM (SUBSTR (tran_rmks, 1, 9))
             FROM dctd_acli
            WHERE ref_num = &&3
              AND tran_amt > 100
              AND entry_user_id = 'CDCI'
              AND part_tran_type = 'D'
              AND value_date BETWEEN &&1 AND &&2)
    AND tran_amt > 100
    AND entry_user_id = 'CDCI'
    AND part_tran_type = 'D'
    AND value_date BETWEEN &&1 AND &&2);

Re: High Query Execution Time [message #410779 is a reply to message #410767] Tue, 30 June 2009 03:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can first read:
- OraFAQ Forum Guide, Performance Tuning section
- Performances Tuning sticky
- OraFAQ Performance Tuning
- Rosco's SQL Tuning Guide

Then provide the required and usual information.

You can also have a look at Database Performance Tuning Guide.

Regards
Michel
Re: High Query Execution Time [message #410818 is a reply to message #410767] Tue, 30 June 2009 07:18 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
I would try to find a way to not go into the same table (at least) 3 different times, as you are doing now.
Re: High Query Execution Time [message #410823 is a reply to message #410767] Tue, 30 June 2009 08:05 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
MANASH wrote on Tue, 30 June 2009 04:17

AND value_date BETWEEN &&1 AND &&2
[/code]



The wreaks of trouble. You're going to have to use TO_DATE on those variables.
Re: High Query Execution Time [message #410826 is a reply to message #410823] Tue, 30 June 2009 08:36 Go to previous messageGo to next message
MANASH
Messages: 7
Registered: June 2009
Junior Member
thank u gurus
but i couldn't figure out a way to get the desired result without the 'minus' statement
is there any other way to do so??
please suggest

regards

Manash......
Re: High Query Execution Time [message #410827 is a reply to message #410767] Tue, 30 June 2009 08:41 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>i couldn't figure out a way to get the desired result
Repeat where you have told us EXACTLY what is "desired results".

If we do not know "desired results", how can we help you?
Re: High Query Execution Time [message #410828 is a reply to message #410767] Tue, 30 June 2009 08:41 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Isn't it logically equivalent to this:
SELECT acid, tran_id, tran_particular, tran_rmks, ref_num, tran_amt,
       SUBSTR (tran_particular, 19, 10) trandate
FROM dctd_acli
WHERE ref_num = &&3
AND tran_rmks NOT LIKE '  %'
AND tran_amt > 100
AND entry_user_id = 'CDCI'
AND part_tran_type = 'D'
AND value_date BETWEEN &&1 AND &&2
AND TRIM (tran_id) NOT IN (
           SELECT TRIM (SUBSTR (tran_rmks, 1, 9))
             FROM dctd_acli
            WHERE ref_num = &&3
              AND tran_amt > 100
              AND entry_user_id = 'CDCI'
              AND part_tran_type = 'D'
              AND value_date BETWEEN &&1 AND &&2);


I'm not saying that that will be any faster mind, don't have enought info to tell.
Re: High Query Execution Time [message #410834 is a reply to message #410767] Tue, 30 June 2009 09:28 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Try this:
with src as (SELECT *
             FROM   dctd_acli
             WHERE  ref_num = &&3
             AND    tran_amt > 100
             AND    entry_user_id = 'CDCI'
             AND    part_tran_type = 'D'
             AND    value_date BETWEEN &&1 AND &&2)
SELECT acid
      ,tran_id
      ,tran_particular
      ,tran_rmks
      ,ref_num
      ,tran_amt
      ,SUBSTR (tran_particular, 19, 10) trandate
FROM   src
WHERE  tran_id not in (SELECT TRIM (SUBSTR (tran_rmks, 1, 9))
                       FROM   src);


What indexes do you have on DCTD_ACLI?

Assuming that VALUE_DATE is of type DATE, then you should replace this:
AND    value_date BETWEEN &&1 AND &&2
with
AND    value_date BETWEEN to_date(&&1,'<format>') AND to_date(&&2,'<format>')
Re: High Query Execution Time [message #410856 is a reply to message #410834] Tue, 30 June 2009 12:39 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
JRowbottom wrote on Tue, 30 June 2009 10:28

Assuming that VALUE_DATE is of type DATE, then you should replace this:
AND    value_date BETWEEN &&1 AND &&2
with
AND    value_date BETWEEN to_date(&&1,'<format>') AND to_date(&&2,'<format>')



And...Put the variables in quotes.
Re: High Query Execution Time [message #410943 is a reply to message #410856] Wed, 01 July 2009 03:37 Go to previous message
MANASH
Messages: 7
Registered: June 2009
Junior Member
Thank u all for all your suggestions and help

Manash.
Previous Topic: Conditional SELECT
Next Topic: sql max
Goto Forum:
  


Current Time: Fri Dec 02 19:03:58 CST 2016

Total time taken to generate the page: 0.12197 seconds