Results 1 to 15 of 15

Thread: Select

  1. #1
    Join Date
    Nov 2007
    Posts
    27

    Unanswered: Select

    Hi,
    I have a table with 10-12 columns which adds around 10-15k rows daily. I have a procedure which has simple select statement (has order by one column) with a date filter. Now the proc takes around 1.5- 2 min to give the complete result set.(10-15k rows) .

    Is there any way by which i can improve the performance of this query.

    Thanks

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Create an index on the date column.

    Andy

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    After that, do not forget to do runstats and rebind the SP.

    Andy

  4. #4
    Join Date
    Nov 2007
    Posts
    27
    Its already there.....and statistics are updated and even rebind the sp after that...

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Please supply the following:

    DB2 version and fixpack level
    OS
    the SP definition
    the ddl for all the tables in the select.
    The index definition for the above tables
    the access plan for the SP.

    Andy

  6. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by rajinbits
    I have a table with 10-12 columns which adds around 10-15k rows daily. I have a procedure which has simple select statement (has order by one column) with a date filter.
    Is partitioning by date an option? And make sure that other column's index is the clustering index.
    That way you turn your query essentially into a single partition scan.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  7. #7
    Join Date
    Nov 2007
    Posts
    27
    Below are the deatils:
    DB2 Version: DB2 ESE V 9.1.5
    OS: Linux
    SP:
    CREATE PROCEDURE MSPA_COMMON.TEST(IN IN_REPORT_DATE DATE,
    IN IN_USER_ID VARCHAR(9))
    SPECIFIC MSPA_COMMON.TEST
    RESULT SETS 1
    MODIFIES SQL DATA
    NOT DETERMINISTIC
    NULL CALL
    LANGUAGE SQL
    BEGIN

    --------------------------------------------------------------------------------------------------------------------
    -- DECLARE VARIABLES
    --------------------------------------------------------------------------------------------------------------------

    DECLARE SQLCODE INTEGER DEFAULT 0;
    DECLARE SQLSTATE CHAR(5) DEFAULT '00000';

    DECLARE V_SQLCODE INTEGER DEFAULT 0;
    DECLARE V_SQLSTATE CHAR(5) DEFAULT '00000';
    DECLARE V_TRACKER CHAR(50);
    DECLARE V_EXIT_MESSAGE CHAR(70);

    -------------------------------------------------------------------------------------------------
    -- DECLARE THE EXIT HANDLER FOR THE SQLEXCEPTION
    -------------------------------------------------------------------------------------------------


    DECLARE EXIT HANDLER FOR SQLEXCEPTION

    BEGIN
    VALUES (SQLCODE,SQLSTATE) INTO V_SQLCODE,V_SQLSTATE;
    SET V_EXIT_MESSAGE = 'ERROR:'|| TRIM(V_SQLSTATE) || ',' || TRIM(CHAR(V_SQLCODE)) || ' AT ' || TRIM(V_TRACKER);
    SIGNAL SQLSTATE VALUE V_SQLSTATE SET MESSAGE_TEXT = V_EXIT_MESSAGE;
    END ;




    RS:BEGIN

    DECLARE CUR_POS_PROD CURSOR WITH RETURN FOR
    SELECT REPORT_DATE,
    NAME_ID,
    NAME,
    TRIM(ACCOUNT_ID),
    TOTAL_VAL,
    TOTAL_COUNT,
    A_TOTAL_VAL,
    A_COUNT,
    B_VAL,
    B_COUNT,
    C_VAL,
    c_COUNT,
    D_VAL,
    D_COUNT,
    E_VAL,
    E_COUNT,
    F_VAL,
    F_COUNT,
    G_VAL,
    G_COUNT,
    H_VAL,
    H_COUNT,
    I_VAL,
    I_COUNT,
    J_VAL,
    J_COUNT,
    K_VAL,
    K_COUNT,
    L_VAL,
    L_COUNT,
    M_VAL,
    M_COUNT,
    N_VAL,
    N_COUNT
    FROM MSPA_COMMON.TEST
    WHERE REPORT_DATE = IN_REPORT_DATE
    ORDER BY NAME_ID
    FOR FETCH ONLY ;

    OPEN CUR_POS_PROD;

    END RS;


    END
    ;

    Table DDL:
    CREATE TABLE MSPA_COMMON.TEST
    (
    REPORT_DATE DATE,
    NAME_ID VARCHAR(50),
    NAME VARCHAR(120),
    ACCOUNT_ID CHARACTER(50),
    TOTAL_VAL DECIMAL(26,4),
    TOTAL_COUNT BIGINT,
    A_TOTAL_VAL DECIMAL(26,4),
    A_COUNT BIGINT,
    B_VAL DECIMAL(26,4),
    B_COUNT BIGINT,
    C_VAL DECIMAL(26,4),
    c_COUNT BIGINT,
    D_VAL DECIMAL(26,4),
    D_COUNT BIGINT,
    E_VAL DECIMAL(26,4),
    E_COUNT BIGINT,
    F_VAL DECIMAL(26,4),
    F_COUNT BIGINT,
    G_VAL DECIMAL(26,4),
    G_COUNT BIGINT,
    H_VAL DECIMAL(26,4),
    H_COUNT BIGINT,
    I_VAL DECIMAL(26,4),
    I_COUNT BIGINT,
    J_VAL DECIMAL(26,4),
    J_COUNT BIGINT,
    K_VAL DECIMAL(26,4),
    K_COUNT BIGINT,
    L_VAL DECIMAL(26,4),
    L_COUNT BIGINT,
    M_VAL DECIMAL(26,4),
    M_COUNT BIGINT,
    N_VAL DECIMAL(26,4),
    N_COUNT BIGINT
    );

    CREATE INDEX MSPA_COMMON.IDX_C_TEST
    ON MSPA_COMMON.TEST(REPORT_DATE,NAME_ID)
    CLUSTER
    ALLOW REVERSE SCANS PAGE SPLIT SYMMETRIC
    ;

    Access Plan:
    DB2 Universal Database Version 9.1, 5622-044 (c) Copyright IBM Corp. 1991, 2006
    Licensed Material - Program Property of IBM
    IBM DB2 Universal Database SQL and XQUERY Explain Tool

    ******************** PACKAGE ***************************************

    Package Name = "MSPA_COMMON"."P3291938" Version = ""

    Prep Date = 2009/02/04
    Prep Time = 03:29:19

    Bind Timestamp = 2009-02-04-03.29.19.390826

    Isolation Level = Cursor Stability
    Blocking = Block Unambiguous Cursors
    Query Optimization Class = 5

    Partition Parallel = No
    Intra-Partition Parallel = No

    SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM",
    "RAJESHAG"

    -------------------- SECTION ---------------------------------------
    Section = 1


    Statement:
    DECLARE CUR_POS_PROD CURSOR
    WITH RETURN
    FOR
    SELECT REPORT_DATE,
    NAME_ID,
    NAME,
    TRIM(ACCOUNT_ID),

    TOTAL_VAL,
    TOTAL_COUNT,
    A_TOTAL_VAL,
    A_COUNT,
    B_VAL,

    B_COUNT,
    C_VAL,
    C_COUNT,
    D_VAL,
    D_COUNT,
    E_VAL,

    E_COUNT,
    F_VAL,
    F_COUNT,
    G_VAL,
    G_COUNT,
    H_VAL,

    H_COUNT,
    I_VAL,
    I_COUNT,
    J_VAL,
    J_COUNT,
    K_VAL,

    K_COUNT,
    L_VAL,
    L_COUNT,
    M_VAL,
    M_COUNT,
    N_VAL,
    N_COUNT
    FROM MSPA_COMMON.TEST
    WHERE REPORT_DATE =:HV00008
    :HI00008
    ORDER BY NAME_ID
    FOR
    FETCH ONLY


    Section Code Page = 1208

    Estimated Cost = 686.985962
    Estimated Cardinality = 2425.800049

    Access Table Name = MSPA_COMMON.TEST ID = 5,8202
    | Index Scan: Name = MSPA_COMMON.IDX_C_TEST ID = 1
    | | Regular Index (Clustered)
    | | Index Columns:
    | | | 1: REPORT_DATE (Ascending)
    | | | 2: NAME_ID (Ascending)
    | #Columns = 0
    | #Key Columns = 1
    | | Start Key: Inclusive Value
    | | | | 1: ?
    | | Stop Key: Inclusive Value
    | | | | 1: ?
    | Index-Only Access
    | Index Prefetch: Eligible 65
    Isolation Level: Uncommitted Read
    | Lock Intents
    | | Table: Intent None
    | | Row : None
    | Sargable Index Predicate(s)
    | | Insert Into Sorted Temp Table ID = t1
    | | | #Columns = 1
    | | | #Sort Key Columns = 1
    | | | | Key 1: (Ascending)
    | | | Sortheap Allocation Parameters:
    | | | | #Rows = 2426.000000
    | | | | Row Width = 16
    | | | Piped
    | | | Duplicate Elimination
    Sorted Temp Table Completion ID = t1
    List Prefetch Preparation
    | Access Table Name = MSPA_COMMON.TEST ID = 5,8202
    | | #Columns = 33
    | | Fetch Using Prefetched List
    | | | Prefetch: 730 Pages
    | | Lock Intents
    | | | Table: Intent Share
    | | | Row : Next Key Share
    | | Sargable Predicate(s)
    | | | #Predicates = 1
    | | | Insert Into Sorted Temp Table ID = t2
    | | | | #Columns = 33
    | | | | #Sort Key Columns = 1
    | | | | | Key 1: NAME_ID (Ascending)
    | | | | Sortheap Allocation Parameters:
    | | | | | #Rows = 2426.000000
    | | | | | Row Width = 540
    | | | | Piped
    Sorted Temp Table Completion ID = t2
    Access Temp Table ID = t2
    | #Columns = 33
    | Relation Scan
    | | Prefetch: Eligible
    Return Data to Application
    | #Columns = 34

    End of section


    Optimizer Plan:

    RETURN
    ( 1)
    |
    TBSCAN
    ( 2)
    |
    SORT
    ( 3)
    |
    FETCH
    (----)
    / \
    RIDSCN Table:
    ( 5) MSPA_COMMON
    | TEST
    SORT
    ( 6)
    |
    IXSCAN
    ( 7)
    / \
    Index: Table:
    MSPA_COMMON MSPA_COMMON
    IDX_C_TEST TEST


    -------------------- SECTION ---------------------------------------
    Section = 2


    Statement:
    VALUES (:HV00000 :HI00000 , :HV00001 :HI00001 )INTO :HV00010
    :HI00010 , :HV00011 :HI00011


    Section Code Page = 1208

    Estimated Cost = 0.000028
    Estimated Cardinality = 1.000000

    Table Constructor
    | 1-Row(s)
    Return Data to Application
    | #Columns = 2

    End of section


    Optimizer Plan:

    RETURN
    ( 1)
    |
    TBSCAN
    ( 2)
    |
    TFunc:
    SYSIBM
    GENROW


    -------------------- SECTION ---------------------------------------
    Section = 3


    Statement:
    -------------------- SECTION ---------------------------------------
    Section = 2


    Statement:
    VALUES (:HV00000 :HI00000 , :HV00001 :HI00001 )INTO :HV00010
    :HI00010 , :HV00011 :HI00011


    Section Code Page = 1208

    Estimated Cost = 0.000028
    Estimated Cardinality = 1.000000

    Table Constructor
    | 1-Row(s)
    Return Data to Application
    | #Columns = 2

    End of section


    Optimizer Plan:

    RETURN
    ( 1)
    |
    TBSCAN
    ( 2)
    |
    TFunc:
    SYSIBM
    GENROW


    -------------------- SECTION ---------------------------------------
    Section = 3


    Statement:
    VALUES('ERROR:' || TRIM(:HV00011 :HI00011 )|| ', ' || TRIM(CHAR(
    :HV00010 :HI00010 ))|| ' AT ' || TRIM(:HV00012 :HI00012 ))
    INTO :HV00013 :HI00013


    Section Code Page = 1208

    Estimated Cost = 0.000239
    Estimated Cardinality = 1.000000

    Table Constructor
    | 1-Row(s)
    Return Data to Application
    | #Columns = 1

    End of section


    Optimizer Plan:

    RETURN
    ( 1)
    |
    TBSCAN
    ( 2)
    |
    TFunc:
    SYSIBM
    GENROW

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You access plan look very good. How many rows are in the table? When was the last reorg done?

    Andy

  9. #9
    Join Date
    Nov 2007
    Posts
    27
    Around 70k as of now...recently created the table...

  10. #10
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Try doing a reorg and see if that helps.

    Andy

  11. #11
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Also reorg the indexes.
    After the reorg, do not forget to do runstats and rebind.

  12. #12
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Something does not look right here. DB2 has its quirks, but two minutes for 70K records...

    How do you measure the elapsed time? Does the procedure run in isolation?

    I would also verify that there are no lock waits involved.
    ---
    "It does not work" is not a valid problem statement.

  13. #13
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I agree. 70K rows is hardly worth talking about. Each row is about 500-600 bytes long (I didn't count exactly). With a 4K tablespace, you will get 6-7 rows per page, which means you have a mere 12K pages and just 48MB memory. All this should be in the buffer pool anyway (or will be after prefetching). You should get subsecond response time. Also, even if there is a table scan, that should be much faster than 2min.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  14. #14
    Join Date
    Nov 2007
    Posts
    27
    There are no lock waits...

    I tested one more thing....I just redirected the result to a file and it completed within a second..So that means most of the time is spent in result io....

    How can I improve that?

    Thanks,
    Rajesh

  15. #15
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Don't write the output to the console.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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