Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Location
    Geramany
    Posts
    3

    Unanswered: Slow performance

    Hi,
    I am working with Oracle 9i, but the performance seem to be too slow..

    I have an hirarchical DB structure where whenever I read one row I need to read his childs (one to many connection).. so.. every time I read another component I open the connection and close it whenever I have finished (in x4 hirarchical structure).. does anybody know if the performance problem might be connected to the open/close connection ??

  2. #2
    Join Date
    Jan 2004
    Posts
    99
    do you have an explain plan or trace of what's going on?

  3. #3
    Join Date
    Apr 2004
    Location
    Geramany
    Posts
    3
    I will try to explain it with a small sketch

    |A|----1--------m---|S|---1--------m---|M|----1--------m---|P|----1--------m---|E|

    As u can see an A has many S, an S has Many M and so on...
    Now, when I am calling an the A i need to call his children as well (quite a big tree till it get to it's end).

    But every component like S or M or P is independent because of othe functions, he is opening it's connection and closing it whenever he has finished.
    Therefore in order to read the whole tree I need open and close many connections, can that be the reason for the poor performance ?

    I have to say that currently I have not more than 100 objects in my tree but it is still very poor (can last seconds till he reads or writes the whole tree).

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Yep opening and closing connections will have a drastic effect on performance. If possible do it with one connection. Also if it is a tree hierarchy you might want to see if you can use the sql connect by clause to build the hierarchy for you.

    Alan

  5. #5
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482
    Originally posted by yossitsafrir
    I will try to explain it with a small sketch

    |A|----1--------m---|S|---1--------m---|M|----1--------m---|P|----1--------m---|E|

    As u can see an A has many S, an S has Many M and so on...
    Now, when I am calling an the A i need to call his children as well (quite a big tree till it get to it's end).

    But every component like S or M or P is independent because of othe functions, he is opening it's connection and closing it whenever he has finished.
    Therefore in order to read the whole tree I need open and close many connections, can that be the reason for the poor performance ?

    I have to say that currently I have not more than 100 objects in my tree but it is still very poor (can last seconds till he reads or writes the whole tree).

    What kind of connection opening/closings you are talking about? If you do that between separate databases, then use DB LINKS, instead of your current logic.

    If you have different schemas you pull data from - then schema. tablename can be used in a single query to merge the data.

    HTH,

    clio_usa - OCP 8/8i/9i DBA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •