Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2011
    Posts
    12

    Unanswered: Optimizing query performance when joining multiple tables

    Hi all,

    I am experiencing performance problems on my queries. When i query from one table queries are fast but when i query on more tables by joining tables my queries take long. i tried using indexes on columns that are queried most oftenly but it is not helping. Do you know of any way how i can solve this issue?

    please help, thanks.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    index the join columns
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2011
    Posts
    12
    Quote Originally Posted by r937 View Post
    index the join columns
    I used index on those columns but still the queries are very slow.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    which database system is this? you posted in the "Other" forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2008
    Posts
    147
    Could you post a)the type of database server b)the query c)the index types \ structures
    ---------------------------------
    www.sqlserver-dba.com
    www.dba-db2.com

  6. #6
    Join Date
    Mar 2011
    Posts
    12
    Quote Originally Posted by r937 View Post
    which database system is this? you posted in the "Other" forum
    Its Oracle sql developer

  7. #7
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by sellyh19 View Post
    Its Oracle sql developer
    That's a SQL (GUI) client which can connect to different DBMS.

  8. #8
    Join Date
    Mar 2011
    Posts
    12
    Quote Originally Posted by JackVamvas View Post
    Could you post a)the type of database server b)the query c)the index types \ structures
    here is my query and i am running it on Oracle application express (APEX)


    select /*+ INDEX (HUAWEIDATA_TB MSISDN_IDX )*/
    *
    from cdruser.huaweidata_tb@MTCCDRT a,
    cdruser.huaweidata_cocconditions_tb@MTCCDRT b
    where open_date >= to_date(:P405_start_date,'dd-mon-rrrr hh24miss') and
    open_date <= to_date(:P405_end_date,'dd-mon-rrrr hh24miss')
    and (record_type = '12' or record_type = '13' or record_type = '14')
    and a.open_date = b.hd_cocc_date (+)
    and a.local_seq_nr = b.hd_cocc_local_seq_nr (+)
    and a.msisdn_nr = :P405_msisdn_number
    order by open_date

    when i query on more than one day it takes very long and does not return anything you will have to stop it because its taking long.

  9. #9
    Join Date
    Aug 2008
    Posts
    147
    Look at rewriting and placing some of the WHERE prediactes as part of a INNER JOIN.
    ---------------------------------
    www.sqlserver-dba.com
    www.dba-db2.com

Posting Permissions

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