OraFAQ Forum: Performance Tuning » Query taking much time

Home » RDBMS Server » Performance Tuning » Query taking much time (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Query taking much time [message #331416]

Thu, 03 July 2008 04:49

virmahi
Messages: 266
Registered: April 2008
Location: India

Senior Member

Hi,
I am running a query which is taking long time when I use a query with a sub query using IN operator.

select distinct s.user_code, u.user_company_name, 
                      u.region_id,s.so_id,s.order_date      
        from tbl_user_details u, tbl_sales_order s
       where u.user_code = s.user_code
         and so_id = (select max(so_id) 
                        from tbl_sales_order s
                       where u.user_code=s.user_code

The above query returns 5000 recods takes only 650ms to execute.

I am using the above query as subquery as in below query below:

Select field1, field2  
        from JDE_Table
       where use_code IN 
             (select distinct  s.user_code,u.user_company_name,  
                               u.region_id,s.so_id,s.order_date
                from tbl_user_details u, tbl_sales_order s
                where u.user_code = s.user_code
                  and so_id = (select max(so_id) 
                                from tbl_sales_order s
                               where u.user_code=s.user_code );
               )

Now this is taking around 8 seconds to execute.
Please give me some suggestions as what changes I can make to make this query run faster.

Thanks,
Mahi

[Updated on: Thu, 03 July 2008 04:53]

Report message to a moderator

Re: Query taking much time [message #331420 is a reply to message #331416]

Thu, 03 July 2008 04:55

Frank
Messages: 7901
Registered: March 2000

Senior Member

Do you have an index on jde_table.use_code?

Remove the distinct from the inner query

Report message to a moderator

Re: Query taking much time [message #331426 is a reply to message #331420]

Thu, 03 July 2008 05:19

virmahi
Messages: 266
Registered: April 2008
Location: India

Senior Member

JDE do not support indexes and the database is online. So can't even create index on the tbl_user_details u, tbl_sales_order s
for the columns u.user_code, s.user_code.

Please advice something. Will using Exist clause work here to eliminate DISTINCT.
If EXIST can help , please tell me how to implement it in this case, as in the examples I have come so far select columns from only one table as in the example below :

SELECT   D.deptno, D.dname
        FROM     dept D
        WHERE    NOT EXISTS
                 (
                 SELECT 1
                 FROM   emp E
                 WHERE  E.deptno = D.deptno
                 )
        ORDER BY D.deptno;

But in my case the columns need to be selected from both the tables in the inner query.

Please help me on this.

Thanks for looking into this,
Mahi

Report message to a moderator

Re: Query taking much time [message #331428 is a reply to message #331426]

Thu, 03 July 2008 05:25

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

Whether or not JDE supports indexes (and I've no idea what you mean by that) is irrelevant.

Oracle supports indexes, and will use them if they are available and it thinks they will help.

Get your DBA to create an index on Jde_Table.Use_Code.

You can just get rid of the DISTINCT - you don't have to replace the IN with an EXISTS.

Select field1, field2  
      from   JDE_Table
      where  use_code IN 
             (select s.user_code
                    ,u.user_company_name
                    ,u.region_id
                    ,s.so_id
                    ,s.order_date
              from   tbl_user_details u, tbl_sales_order s
              where  u.user_code = s.user_code
              and    so_id = (select max(so_id) 
                              from   tbl_sales_order s
                              where  u.user_code=s.user_code );
               )

Report message to a moderator

Re: Query taking much time [message #331433 is a reply to message #331428]

Thu, 03 July 2008 05:34

virmahi
Messages: 266
Registered: April 2008
Location: India

Senior Member

Sorry for the confusion Frank. But my Oracle database is connected to JDE. The subquery brings data from oracle and the table in JDE uses the select clause to get matching values of column Jde_Table.Use_Code from the inner clause.
In no way can I create indexes.

Are there no other way.....can using EXIST help anyway. We have very limited access to database and have to work anyway.

PLease advice on EXIST if at all it can help as I can't create Indexes here as JDE doesn't support indexes. This is the main problem that is why I am looking for some alternatives.

Thanks,
Mahi

Report message to a moderator

Re: Query taking much time [message #331435 is a reply to message #331433]

Thu, 03 July 2008 05:39

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

The reason why an index would make things quicker is that it would allow you to avoid looking at every row in JDE_Table.

Without an index on Jde_Table.Use_code, there is nothing you can do to avoid this.

I'm assuming that some of the tables in your query are actually synonym pointing across a Db link, as there's no other sense I can make of the phrase:

Quote:

But my Oracle database is connected to JDE. The subquery brings data from oracle and the table in JDE uses the select clause to get matching values of column Jde_Table.Use_Code from the inner clause.

Report message to a moderator

Re: Query taking much time [message #331443 is a reply to message #331435]

Thu, 03 July 2008 05:54

virmahi
Messages: 266
Registered: April 2008
Location: India

Senior Member

Hi Jrowbottom,
Thanks for your reply. I was referring an example, I came through ... ! You can see the example below which uses EXIST to remove the DISTINCT clause and got good performance. But it is selecting the columns from one table only while in my query I have to select from two tables.
Please look in the example below

SELECT   DISTINCT C.short_name, C.customer_id
        FROM     customers C, orders O
        WHERE    C.customer_type = 'Gold'
        AND      O.customer_id = C.customer_id
        AND      O.order_date > SYSDATE - 3
        ORDER BY C.short_name;

The execution plan (from a TKPROF report) for this query is: 

        Rows     Row Source Operation
        -------  ---------------------------------------------------
              2  SORT UNIQUE (cr=33608 r=30076 w=0 time=6704029 us)
             20   HASH JOIN  (cr=33608 r=30076 w=0 time=6703101 us)
             10    TABLE ACCESS FULL CUSTOMERS (cr=38 r=36 w=0 time=31718 us)
           2990    TABLE ACCESS FULL ORDERS (cr=33570 r=30040 w=0 time=6646420 us)

The DISTINCT has been removed using EXIST and there is good performance with the new query.
Is there a way my inner query can be used with EXIST to eliminate the DISTINCT keyword to remove duplicate checking.
I do not know how to use EXIST when selecting columns from two tables.
Please advice something if there is something we can do with EXIST as I am left with no other choice.

SELECT   C.short_name, C.customer_id
        FROM     customers C
        WHERE    C.customer_type = 'Gold'
        AND      EXISTS
                 (
                 SELECT /*+ NL_SJ */ 1
                 FROM   orders O
                 WHERE  O.customer_id = C.customer_id
                 AND    O.order_date > SYSDATE - 3
                 )
        ORDER BY C.short_name;
        
        Rows     Row Source Operation
        -------  ---------------------------------------------------
              2  SORT ORDER BY (cr=833 r=725 w=0 time=358431 us)
              2   NESTED LOOPS SEMI (cr=833 r=725 w=0 time=358232 us)
             10    TABLE ACCESS FULL CUSTOMERS (cr=38 r=0 w=0 time=2210 us)
              2    TABLE ACCESS BY INDEX ROWID ORDERS (cr=795 r=725 w=0 time=355822 us)
            780     INDEX RANGE SCAN ORDERS_N1 (cr=15 r=13 w=0 time=5601 us)(object id 34176)

Thanks again for looking into this,
Mahi

[Updated on: Thu, 03 July 2008 05:56]

Report message to a moderator

Re: Query taking much time [message #331460 is a reply to message #331443]

Thu, 03 July 2008 07:21

S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom

Senior Member

Quote:

Select field1, field2
from JDE_Table
where use_code IN
(select distinct s.user_code,u.user_company_name,
u.region_id,s.so_id,s.order_date
from tbl_user_details u, tbl_sales_order s
where u.user_code = s.user_code
and so_id = (select max(so_id)
from tbl_sales_order s
where u.user_code=s.user_code );
)

I am wondering how this query worked for you or is it a typo. Also can you tell us how many rows are there in JDE_TABLE and is it an oracle table or an object in JDE. BTW, what is JDE ?

Regards

Raj

Report message to a moderator

Re: Query taking much time [message #331553 is a reply to message #331460]

Thu, 03 July 2008 23:32

virmahi
Messages: 266
Registered: April 2008
Location: India

Senior Member

Hi Raja,
JDE is JD Edwards EnterpriseOne is an integrated applications suite of comprehensive ERP software.

The query having columns Feild1, field2 just for assumption. I have not written the actual name of fields.

There are around 2000 records in the JDE tables.

Hope it helps,
Mahi

Report message to a moderator

Re: Query taking much time [message #331587 is a reply to message #331553]

Fri, 04 July 2008 02:42

virmahi
Messages: 266
Registered: April 2008
Location: India

Senior Member

Hi,
Can I create an index when the table has large number of data in the tables tbl_user_details and tbl_sales_order ?

Report message to a moderator

Re: Query taking much time [message #331588 is a reply to message #331587]

Fri, 04 July 2008 02:45

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

You can, but from what you said about the performance of the inner query in your opening post, I'd guess it was already indexed - it certainly doesn't look like that's where the performance problem lies.

Out of curiosity, how did you do the timing for that inner query?

Report message to a moderator

Re: Query taking much time [message #331590 is a reply to message #331588]

Fri, 04 July 2008 03:02

virmahi
Messages: 266
Registered: April 2008
Location: India

Senior Member

I ran only the inner query and found the time taken on status bar of Toad.

I thought of putting Unique index and removing DISTINCT from the inner clause as eliminating duplicates might be effecting performance.

What do you say about it.

Thanks,
Mahi

Report message to a moderator

Re: Query taking much time [message #331594 is a reply to message #331590]

Fri, 04 July 2008 03:12

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

If you can add a unique index without removing any data then you don't need the distinct in the first place.

If you are using an IN query, there is ne need to have the distinct.

SELECT * FROM table WHERE column IN (SELECT DISTINCT...)

will return exactly the same set of rows as

SELECT * FROM table WHERE column IN (SELECT ...)

, but the distinct will slow things down.

Try running

SELECT count(*)
FROM (select distinct s.user_code
                     ,u.user_company_name
                     ,u.region_id
                     ,s.so_id
                     ,s.order_date      
      from   tbl_user_details u
            ,tbl_sales_order s
      where  u.user_code = s.user_code
      and    so_id = (select max(so_id) 
                      from tbl_sales_order s
                      where u.user_code=s.user_code)
     )

and see how long that takes.
TOAD only tells you how long it took to retrieve the first set of records from the query, not how long the whole query took

Report message to a moderator

Re: Query taking much time [message #331598 is a reply to message #331594]

Fri, 04 July 2008 03:21

Frank
Messages: 7901
Registered: March 2000

Senior Member

JRowbottom wrote on Fri, 04 July 2008 10:12

Try running

SELECT count(*)
FROM (select distinct s.user_code
                     ,u.user_company_name
                     ,u.region_id
                     ,s.so_id
                     ,s.order_date      
      from   tbl_user_details u
            ,tbl_sales_order s
      where  u.user_code = s.user_code
      and    so_id = (select max(so_id) 
                      from tbl_sales_order s
                      where u.user_code=s.user_code)
     )

and see how long that takes.
TOAD only tells you how long it took to retrieve the first set of records from the query, not how long the whole query took

Very true, and a pitfall in many cases.
However, because of the distinct, I am curious if it would make a big difference in this case.

Report message to a moderator

Re: Query taking much time [message #331759 is a reply to message #331598]

Sat, 05 July 2008 05:02

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Oh for pity's sake! How did we get to 13 posts without anyone asking for an Explain Plan?

1) Please post the plan for the query

2) Advise whether you have any of the following indexes:
--- JDE_Table(use_code) [I ask because you never answered @JR above]
--- tbl_sales_order(user_code, so_id)

3) How many rows in JDE_Table?

4) How many rows does the SQL return?

Ross Leishman

[Updated on: Sat, 05 July 2008 05:03]

Report message to a moderator

Re: Query taking much time [message #331815 is a reply to message #331416]

Sat, 05 July 2008 14:17

Kriptas
Messages: 118
Registered: June 2008

Senior Member

please try this query for your first question

Select field1, field2, t.user_company_name  
from JDE_Table d
INNER JOIN (select distinct  s.user_code,u.user_company_name,  
                             u.region_id,s.so_id,s.order_date
            from tbl_user_details u, tbl_sales_order s
            where u.user_code = s.user_code
                   and so_id = (select max(so_id) 
                                from tbl_sales_order s
                                where u.user_code=s.user_code)) t on t.use_code=d.use_code

Report message to a moderator

Previous Topic:	temp space estimation ?
Next Topic:	UNION causing slow performance

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jun 27 20:12:25 CDT 2024