Results 1 to 13 of 13
  1. #1
    Join Date
    Sep 2002
    Location
    Pakistan
    Posts
    17

    Arrow Unanswered: SQL quiries(On Informix) are too slow

    Hello all,

    I have some quiries with joins of different tables and at the end order by clause. These quiries take very longer time from 7 Min to more.
    The Records in the datatbase are more than half million
    When ever I remove the order by clause then the quiries pull out with in seconds.

    Here is sample sql
    ---------------------

    select * from a,b where a.col1=b.col1 and a.col2='123'
    order by 1 desc

    The database I am using is Informix.

    Do I need to increase the buffer size etc or there is need to do some performance tuning etc.

    Any help/tip will be highly appriciated.

    Thanks in advance
    Istikhar Ahmad
    ------------------
    Tel: (92-42) 111000911
    iahmad@i2cinc.com
    http://www.i2cinc.com

  2. #2
    Join Date
    Aug 2002
    Location
    Belgium
    Posts
    534
    A few tuning tips...

    Don't use 'select * ...'
    Create one or more temporary dbspaces (preferably on the fastest disks)
    Create an index on the order by column.
    Use PDQ and add sort threads. and PDQ-mem

    What version of Informix are U using?
    Can you give the explain output?
    rws

  3. #3
    Join Date
    Sep 2002
    Location
    Pakistan
    Posts
    17

    SQL quiries(On Informix) are too slow

    Hi Roelwe,
    Thank very much for your for response

    Please see the sqexplain.out.

    ---------------------->sqexplain.out<-------------------------------------

    QUERY:
    ------
    SELECT
    device_requests.trace_audit_no,
    device_requests.trans_date,
    device_requests.trans_time,
    device_requests.device_type,
    dt.device_type_desc,
    device_requests.device_id,
    device_requests.request_no,
    device_requests.card_acceptor_code,
    m.merchant_name,
    device_requests.comp_code
    ,fcc.comp_desc,
    device_requests.trans_local_dtime,
    device_requests.trans_status,
    device_requests.amount,
    device_requests.currency_code,
    cc.currency_desc,
    device_requests.contra_account,
    device_requests.description,
    device_requests.consumer_entry,
    device_requests.business_date,
    device_requests.commission_amount,
    device_requests.retrieval_ref_no,
    device_requests.auth_code,
    fac.auth_desc,
    device_requests.discounted_price,
    products.product_name,
    device_requests.clerk_id,
    device_requests.description,
    device_requests.shift_no,
    device_requests.iso_reconcil_date
    FROM
    device_requests,
    merchants m,
    currency_codes cc,
    fc_auth_codes fac,
    device_types dt,
    fc_comp_codes fcc ,
    products ,
    trans_charges t
    WHERE
    device_requests.card_acceptor_code=m.merchant_id
    AND device_requests.currency_code=cc.currency_code
    AND device_requests.device_type=dt.device_type
    AND device_requests.auth_code=fac.fc_auth_code
    AND device_requests.comp_code=fcc.fc_comp_code
    AND t.trace_audit_no=device_requests.trace_audit_no
    And device_requests.consumer_entry=products.product_id
    AND t.receiver_code=='2090701001 '
    And t.level >0
    order by 1 ASC

    Estimated Cost: 412
    Estimated # of Rows Returned: 43
    Temporary Files Required For: Order By

    1) root.t: INDEX PATH

    Filters: root.t.level > 0

    (1) Index Keys: receiver_code
    Lower Index Filter: root.t.receiver_code = '2090701001 '

    2) root.device_requests: INDEX PATH

    (1) Index Keys: trace_audit_no
    Lower Index Filter: root.device_requests.trace_audit_no = root.t.trace_audit_no
    NESTED LOOP JOIN

    3) root.m: INDEX PATH

    (1) Index Keys: merchant_id
    Lower Index Filter: root.m.merchant_id = root.device_requests.card_acceptor_code
    NESTED LOOP JOIN

    4) root.products: INDEX PATH

    (1) Index Keys: product_id
    Lower Index Filter: root.products.product_id = root.device_requests.consumer_entry
    NESTED LOOP JOIN

    5) root.fac: INDEX PATH

    (1) Index Keys: fc_auth_code
    Lower Index Filter: root.fac.fc_auth_code = root.device_requests.auth_code
    NESTED LOOP JOIN

    6) root.fcc: INDEX PATH

    (1) Index Keys: fc_comp_code
    Lower Index Filter: root.fcc.fc_comp_code = root.device_requests.comp_code
    NESTED LOOP JOIN

    7) root.cc: INDEX PATH

    (1) Index Keys: currency_code
    Lower Index Filter: root.cc.currency_code = root.device_requests.currency_code
    NESTED LOOP JOIN

    8) root.dt: INDEX PATH

    (1) Index Keys: device_type
    Lower Index Filter: root.dt.device_type = root.device_requests.device_type
    NESTED LOOP JOIN


    QUERY:
    ------
    SELECT
    device_requests.trace_audit_no,
    device_requests.trans_date,
    device_requests.trans_time,
    device_requests.device_type,
    dt.device_type_desc,
    device_requests.device_id,
    device_requests.request_no,
    device_requests.card_acceptor_code,
    m.merchant_name,
    device_requests.comp_code
    ,fcc.comp_desc,
    device_requests.trans_local_dtime,
    device_requests.trans_status,
    device_requests.amount,
    device_requests.currency_code,
    cc.currency_desc,
    device_requests.contra_account,
    device_requests.description,
    device_requests.consumer_entry,
    device_requests.business_date,
    device_requests.commission_amount,
    device_requests.retrieval_ref_no,
    device_requests.auth_code,
    fac.auth_desc,
    device_requests.discounted_price,
    products.product_name,
    device_requests.clerk_id,
    device_requests.description,
    device_requests.shift_no,
    device_requests.iso_reconcil_date
    FROM
    device_requests,
    merchants m,
    currency_codes cc,
    fc_auth_codes fac,
    device_types dt,
    fc_comp_codes fcc ,
    products ,
    trans_charges t
    WHERE
    device_requests.card_acceptor_code=m.merchant_id
    AND device_requests.currency_code=cc.currency_code
    AND device_requests.device_type=dt.device_type
    AND device_requests.auth_code=fac.fc_auth_code
    AND device_requests.comp_code=fcc.fc_comp_code
    AND t.trace_audit_no=device_requests.trace_audit_no
    And device_requests.consumer_entry=products.product_id
    AND t.receiver_code=='2090701001 '
    And t.level >0
    order by 1 ASC

    Estimated Cost: 412
    Estimated # of Rows Returned: 43
    Temporary Files Required For: Order By

    1) root.t: INDEX PATH

    Filters: root.t.level > 0

    (1) Index Keys: receiver_code
    Lower Index Filter: root.t.receiver_code = '2090701001 '

    2) root.device_requests: INDEX PATH

    (1) Index Keys: trace_audit_no
    Lower Index Filter: root.device_requests.trace_audit_no = root.t.trace_audit_no
    NESTED LOOP JOIN

    3) root.m: INDEX PATH

    (1) Index Keys: merchant_id
    Lower Index Filter: root.m.merchant_id = root.device_requests.card_acceptor_code
    NESTED LOOP JOIN

    4) root.products: INDEX PATH

    (1) Index Keys: product_id
    Lower Index Filter: root.products.product_id = root.device_requests.consumer_entry
    NESTED LOOP JOIN

    5) root.fac: INDEX PATH

    (1) Index Keys: fc_auth_code
    Lower Index Filter: root.fac.fc_auth_code = root.device_requests.auth_code
    NESTED LOOP JOIN

    6) root.fcc: INDEX PATH

    (1) Index Keys: fc_comp_code
    Lower Index Filter: root.fcc.fc_comp_code = root.device_requests.comp_code
    NESTED LOOP JOIN

    7) root.cc: INDEX PATH

    (1) Index Keys: currency_code
    Lower Index Filter: root.cc.currency_code = root.device_requests.currency_code
    NESTED LOOP JOIN

    8) root.dt: INDEX PATH

    (1) Index Keys: device_type
    Lower Index Filter: root.dt.device_type = root.device_requests.device_type
    NESTED LOOP JOIN

    ---------------------->end<-------------------------------------
    Istikhar Ahmad
    ------------------
    Tel: (92-42) 111000911
    iahmad@i2cinc.com
    http://www.i2cinc.com

  4. #4
    Join Date
    Sep 2002
    Location
    Pakistan
    Posts
    17

    Re: SQL quiries(On Informix) are too slow

    Following is the detail of Plateforms.

    OS-> UNIX sco 5.6
    DB-> Informix 7.3
    Istikhar Ahmad
    ------------------
    Tel: (92-42) 111000911
    iahmad@i2cinc.com
    http://www.i2cinc.com

  5. #5
    Join Date
    Aug 2002
    Location
    Belgium
    Posts
    534
    A weird thing is that the optimizer does not use the index for it's sort.
    Did you 'Update Statistics' correctly? If not, lots of scripts are available at http://www.iiug.org

    Another stange behaviour is that it uses temp files...
    What is the setting of your DBSPACETEMP in the $ONCONFIG file? Is it blank?

    What are the following settings in your $ONCONFIG?
    OPTCOMPIND
    OPT_GOAL

    For 7.3, it's better to set the OPT_GOAL = -1 and the OPTCOMPIND = 0.
    I know the optimizer has changed from 7.x to 9.x.

    You could always try to set Optimizer directives, to make it use the index on device_request for the sort.

    An easy way of speeding up the qry is putting in in a temp table:
    select blablabla
    from blablabla
    where blablabla
    into temp temp_table1 with no log;

    select .......
    from temp_table1
    order by 1 ASC;
    rws

  6. #6
    Join Date
    Sep 2002
    Location
    Pakistan
    Posts
    17

    SQL quiries(On Informix) are too slow

    Thank you very much for your response.

    Temp table is not solving the issue because it first enter more than half million of records in temp table then there is need to re-select order by which is again taking long time.

    More Over I need this fuctionality on each user basis seprately(Session wise) and I will have to create temp table for every user seprately.

    Here is the detail of settings which I am using in Informix.

    Command Value
    -------------- -----------
    DBSPACETEMP tempdbs
    OPTCOMPIND 2
    OPT_GOAL -1


    Please let me know if any other info is required.

    Thaks again for your support
    Istikhar Ahmad
    ------------------
    Tel: (92-42) 111000911
    iahmad@i2cinc.com
    http://www.i2cinc.com

  7. #7
    Join Date
    Aug 2002
    Location
    Belgium
    Posts
    534

    Re: SQL quiries(On Informix) are too slow

    Your explain output states the following:
    Estimated # of Rows Returned: 43

    That is why he will not choose the index to do the order by.
    If you say millions of rows are returned by the qry, you statistics are not correct.

    Use UPDATE STATISTICS scripts. You can download several scripts from iiug.org. If you cannot find one I could send you one.

    Temp tables are session based. No problem about that.

    Yuo might want to check onstat -d to see if tempdbs is one of the dbspaces. Either it is not large enough, or IDS cannot find it.

    You could also try to set the OPTCOMPIND to 0.
    That means he will try to use indexes as much as possible.
    rws

  8. #8
    Join Date
    Sep 2002
    Location
    Pakistan
    Posts
    17
    Hello Roelwe,


    Please send me the scripts at email address below,
    I will be highly obliged for this favour.

    Thank you,

    Istikhar
    Istikhar Ahmad
    ------------------
    Tel: (92-42) 111000911
    iahmad@i2cinc.com
    http://www.i2cinc.com

  9. #9
    Join Date
    Sep 2002
    Location
    Pakistan
    Posts
    17

    Arrow Re: SQL quiries(On Informix) are too slow

    I faced One strange thing during the investigation of the problem which is

    When the number of columns in select statement were more the quiry took longer time, But when the number of columns were too few the quiry surprisingly took less time.

    Can you please let me know why this is happening and what is the solution?


    Thanks in advance
    Istikhar Ahmad
    ------------------
    Tel: (92-42) 111000911
    iahmad@i2cinc.com
    http://www.i2cinc.com

  10. #10
    Join Date
    Aug 2002
    Location
    Belgium
    Posts
    534
    Don't you think that's normal?
    The more columns, the more data returned, the slower it is.
    rws

  11. #11
    Join Date
    Sep 2002
    Location
    Pakistan
    Posts
    17

    Use PDQ and SQL quiries(On Informix) are too slow

    Hello

    I have a problem with Parallel Database Query. By enabling PDQ and using the value PDQPRIORITY = 4 , The system serve the first few queries very fastly then with the passage of time it does not respond for the next queries. Then to make the system available again i need to restart the database.

    I am accessing the system through web in the form of crystal web reports and there are large number of concurrent users.
    In this case the enabling of PDQ is feasible?

    Here is the output by using the command onstat -g mgm which shows how the system goes to nonfunctional state. The free memory shown as 128 also goes to -128, -256 and so on reducing to handle new requests.
    ------------------------------------------------------------------------------------
    Informix Dynamic Server Version 7.31.UC5 -- On-Line -- Up 01:01:49 -- 167936 Kbytes

    Memory Grant Manager (MGM)
    --------------------------

    MAX_PDQPRIORITY: 100
    DS_MAX_QUERIES: 600
    DS_MAX_SCANS: 1000
    DS_TOTAL_MEMORY: 153600 KB

    Queries: Active Ready Maximum
    30 1 600

    Memory: Total Free Quantum
    (KB) 153600 128 256

    Scans: Total Free Quantum
    1000 987 1

    Load Control: (Memory) (Scans) (Priority) (Max Queries) (Reinit)
    Gate 1 Gate 2 Gate 3 Gate 4 Gate 5
    (Queue Length) 1 0 0 0 0

    Active Queries:
    ---------------
    Session Query Priority Thread Memory Scans Gate
    21 1775aec4 4 175a7e14 736/736 0/1 -
    8 17eca028 4 175f3cb0 0/0 0/1 -
    23 17f0b8b0 4 1760a320 0/736 0/0 -
    24 17e1910c 4 17635224 0/736 0/1 -
    24 1813c028 4 17635224 0/736 0/0 -
    8 17f3b804 4 175f3cb0 0/736 0/1 -
    8 17f8fa14 4 175f3cb0 0/736 0/1 -
    24 1801c028 4 17635224 0/736 0/0 -
    8 17ff8250 4 175f3cb0 0/736 0/1 -
    24 182ac028 4 17635224 0/736 0/0 -
    24 182b8c20 4 17635224 0/736 0/1 -
    23 17f17044 4 1760a320 0/736 0/0 -
    23 17f75b6c 4 1760a320 0/736 0/1 -
    24 18668028 4 17635224 0/736 0/0 -
    24 188b8444 4 17635224 0/736 0/1 -
    23 189c6028 4 1760a320 0/736 0/0 -
    8 18bead94 4 175f3cb0 0/736 0/1 -
    24 199a8028 4 17635224 0/736 0/0 -
    23 1888cfa8 4 1760a320 0/736 0/0 -
    23 181a8028 4 1760a320 0/736 0/0 -
    23 181d6028 4 1760a320 0/736 0/0 -
    23 18212028 4 1760a320 0/736 0/0 -
    23 1830c028 4 1760a320 0/736 0/0 -
    8 18318250 4 175f3cb0 0/736 0/1 -
    24 1838a028 4 17635224 0/736 0/0 -
    8 18354a8c 4 175f3cb0 0/736 0/1 -
    24 183e6028 4 17635224 0/736 0/0 -
    8 183c2d88 0 175f3cb0 0/16 0/1 -
    24 18438028 0 17635224 0/16 0/0 -
    23 1830e798 0 1760a320 0/16 0/0 -

    Ready Queries:
    --------------
    Session Query Priority Thread Memory Scans Gate
    47 183c910c 4 1997f148 0/736 0/1 1

    Free Resource Average # Minimum #
    -------------- --------------- ---------
    Memory 9230.0 +- 6856.1 16
    Scans 992.6 +- 4.5 987

    Queries Average # Maximum # Total #
    -------------- --------------- --------- -------
    Active 14.5 +- 9.7 30 57
    Ready 1.0 +- 0.0 1 1

    Resource/Lock Cycle Prevention count: 0
    ------------------------------------------------------------------------------------
    If more info to solve the issue is required then Please let me know
    Thanks in advance
    Istikhar Ahmad
    ------------------
    Tel: (92-42) 111000911
    iahmad@i2cinc.com
    http://www.i2cinc.com

  12. #12
    Join Date
    Mar 2002
    Posts
    112

    re

    You can use command onstat -g ses <you session> to analyze you session thread status , what status is ?
    In your onstat -g mgm you session is stucked by memory gate , I think your database doesn't process you query because you SET PDQPRIORITY and it(db server) proceses each other PDQ queries , in this case you can test by don't SET PDQPRIORITY in your query , it can help your query by unblock from eache other PDQ queries

  13. #13
    Join Date
    Aug 2002
    Location
    Belgium
    Posts
    534
    If you use PDQ with a lot of sessions, you need A LOT of memory.
    PDQ divides the memory into blocks: DS_TOTAL_MEMORY/DS_MAX_QUERIES= 256 (quantum)
    If the amount of free memory is below the quantum, the next query asking for memory, comes in a wait queue.

    SO, Either you allocate more memory, either you don't set your PDQ_PRIORITY to 4, but lower, either you increment the DS_MAX_QUERIES so the quamtum is lower.

    Analyse everything in your onstat -g mgm. Check the administration guide from informix for more tips. Chapter 15.
    and the Performance guide, chapter 9.

    http://www.ibm.com/software/data/informix/pubs

    then click documentation on Informix Products.
    rws

Posting Permissions

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