Results 1 to 7 of 7

Thread: Query very slow

  1. #1
    Join Date
    Jan 2006
    Posts
    1

    Unanswered: Query very slow

    Hi,

    I pasted below my query, whisch is taking 25 min to get the result, please help me in correcting the query.
    Thanks in Advance

    SELECT
    AF.NAME AS CUSTOMER_SITE_NAME
    ,AF.ADDR1 || AF.ADDR2 || AF.ADDR3 || AF.CITY || AF.STATE AS CUSTOMER_SITE_ADDRESS
    ,AF.ESCCSRCODE AS SITE_CSR
    ,EF.SERIALNUM AS SERIAL_NUMBER
    ,EF.PCSR AS SN_CSR
    ,EF.MODELNUM AS MODEL_NUMBER
    ,PF.DESCR AS MODEL_NUMBER_DESCRIPTION
    ,CC.CONTRREF AS CONTRACT_REFERENCE
    ,CC.DESCR AS CONTRACT_DESCRIPTION
    FROM
    ADDR_FILE AF,
    EQUIP_FILE EF,
    PART_FILE PF,
    CUST_CONTR CC,
    CONI_FILE CF,
    CUST_FILE CU_FI,
    ADDR_SETS ADS
    WHERE
    ADS.U##SBCODE = ef.u##SBCODE
    AND ads.U##ADDRCODE = af.U##ADDRCODE
    AND ads.U##ATYPE = af.U##ATYPE
    AND ads.U##CODE = af.U##CODE
    AND ef.MODELNUM = pf.PARTNO
    AND cf.SERIAL = ef.SERIALNUM
    AND cf.U##CONTRREF = cc.U##CONTRREF
    AND cu_fi.u##code = ef.U##CODE
    AND cf.SITE = af.U##ADDRCODE
    AND ef.SERIALNUM like '%004000%'


    regards
    sundar

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Why do you think that this should take less time than 25 minutes? How large are tables involved in query? Did you check whether relevant columns are indexed? Did you properly join all tables?

    If I'm not wrong, the only thing that might be time consuming is the last line (serialnum LIKE ...), but I don't know how could you improve this.

  3. #3
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    First of, I am no Guru or anything. Possibly a wanabe Guru.... But no where near.... But because I have been helped so many times I am just trying to contribute.... So...Here goes nothing.

    There are 3 things I think that can be done better...

    1)
    Move the like (AND ef.SERIALNUM like '%004000%') to a subquery instead of this big query... This would result in your EF table beeing restricted (possibly) greatly in the # of rows returned. Seeing as this EF table is the core of the query this could seriously help.

    This would make your query look like this:
    SELECT
    AF.NAME AS CUSTOMER_SITE_NAME
    ,AF.ADDR1 || AF.ADDR2 || AF.ADDR3 || AF.CITY || AF.STATE AS CUSTOMER_SITE_ADDRESS
    ,AF.ESCCSRCODE AS SITE_CSR
    ,EF.SERIALNUM AS SERIAL_NUMBER
    ,EF.PCSR AS SN_CSR
    ,EF.MODELNUM AS MODEL_NUMBER
    ,PF.DESCR AS MODEL_NUMBER_DESCRIPTION
    ,CC.CONTRREF AS CONTRACT_REFERENCE
    ,CC.DESCR AS CONTRACT_DESCRIPTION
    FROM
    ADDR_FILE AF,
    (select * from EQUIP_FILE where SERIALNUM like '%004000%') EF,
    PART_FILE PF,
    CUST_CONTR CC,
    CONI_FILE CF,
    CUST_FILE CU_FI,
    ADDR_SETS ADS
    WHERE
    ADS.U##SBCODE = ef.u##SBCODE
    AND ads.U##ADDRCODE = af.U##ADDRCODE
    AND ads.U##ATYPE = af.U##ATYPE
    AND ads.U##CODE = af.U##CODE
    AND ef.MODELNUM = pf.PARTNO
    AND cf.SERIAL = ef.SERIALNUM
    AND cf.U##CONTRREF = cc.U##CONTRREF
    AND cu_fi.u##code = ef.U##CODE
    AND cf.SITE = af.U##ADDRCODE

    2)
    AND cf.SERIAL = ef.SERIALNUM
    AND cf.SITE = af.U##ADDRCODE

    Linking one table (CF) via 2 others is usually not the best way.
    You may try, if this indeed is correct, to create a sub query instead to help.
    Making a subquery to make the top of your tree (including tables ADS, AF and EF) then linking the rest to that subquery.

    Or reverse that and make the EF "tree" as one subquery and link that to your ADS and AF table

    3)
    Drop unused tables, in your select statement you are selecting columns from AF, EF, PF and CC
    This means that atleast Cu_Fi appears to be one table that you do not need.
    Possibly you do not need ADS either, but as it is at the top of your tree... It is probably needed to tie AF and EF together.

    I hope this helps

  4. #4
    Join Date
    Sep 2004
    Posts
    60

    Lightbulb Consider these basic points

    In addition to point's mentioned by namliam , consider following basic points for query optimization, if you negelected them by any chance:
    1. See which "and" condition is eliminating maximum rows. Put such condition first.
    2. Look for indexes on the columns coming in where clause. Prefer using indexed column first in place of unindexed column.

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    and make sure you have PK's in place, and indexes on your foreign keys.

    I just purchased the O'Reilly book 'SQL Tuning' by Dan Tow, and it is a very worthwhile purchase. You might see if your organization will buy it for you.

    -Chuck

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    And use EXPLAIN PLAN or SET AUTOTRACE ON in SQL Plus to see the execution plan for your query - this will tell you what indexes are being used etc.

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    try this code and let's see if anything improves.
    I basically incorporated most of the ideas in this thread.
    PHP Code:
    select
       af
    .name as customer_site_name,
       
    af.addr1 || af.addr2 || af.addr3 || af.city || af.state as customer_site_address,
       
    af.esccsrcode as site_csr,
       
    ef.serialnum as serial_number,
       
    ef.pcsr as sn_csr,
       
    ef.modelnum as model_number,
       (
    select pf.descr
        from part_file pf
        where pf
    .partno ef.modelnum ) as model_number_description,
       
    cc.contrref as contract_reference,
       
    cc.descr as contract_description
    from
       addr_file af
    ,
       (
    select serialnumpcsrmodelnumu##sbcode
        
    from equip_file efcust_file cu_fi
        where ef
    .serialnum like '%004000%'
        
    and ef.u##code = cu_fi.u##code) ef,
       
    cust_contr cc,
       
    coni_file cf,
       
    addr_sets ads
    where
       ads
    .u##sbcode        = ef.u##sbcode
       
    and ads.u##addrcode  = af.u##addrcode
       
    and ads.u##atype     = af.u##atype
       
    and ads.u##code      = af.u##code
       
    and cf.serial        ef.serialnum
       
    and cf.u##contrref   = cc.u##contrref
       
    and cf.site          af.u##addrcode; 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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