Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2001
    Posts
    80

    Unanswered: Cursor Performance

    I have two versions of Select SQL - One is dynamic and the other is static.

    When I run the static SQL by using C program, it run very slow, use 11 seconds to run the statement, but for dynamic one, it use only 1 second. The table contains about 980,000 records.

    I have done reorg, runstats on the corresponding table and indexes, and also rebind on the static SQL. Then I run explain on both static and dynamic SQL and found that the plan are the same. (using the same index for data retrieving)

    Then I apply logging in the C program and found that almost all the time is spent on open cursor, fetch only consume very little of time.

    Does anyone have idea why open cursor consume a lots of time, and how to minimize the time on opening cursor, thx in advance.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You say that you have two versions of the SQL statement. Please post them exactly, with host variables or literals and they occur in the SQL statement.

    Sometimes DB2 will need to materialize the answer set in the temporary tablespace (into a table which is hidden to you) before the first row is fetched into your program. This can happen with an ORDER BY or other clause where DB2 needs to read all of the rows before the first one is returned. This "might be" the explanation, but it is difficult to understand why the static statement would do this, and not the dynamic version, without seeing the two SQL statements.

    Also, in accordance with posting rules, please provide DB2 version (and fixpak level) and OS.

  3. #3
    Join Date
    Dec 2001
    Posts
    80
    Originally posted by Marcus_A
    You say that you have two versions of the SQL statement. Please post them exactly, with host variables or literals and they occur in the SQL statement.

    Sometimes DB2 will need to materialize the answer set in the temporary tablespace (into a table which is hidden to you) before the first row is fetched into your program. This can happen with an ORDER BY or other clause where DB2 needs to read all of the rows before the first one is returned. This "might be" the explanation, but it is difficult to understand why the static statement would do this, and not the dynamic version, without seeing the two SQL statements.

    Also, in accordance with posting rules, please provide DB2 version (and fixpak level) and OS.
    Marcus_A,
    Thx for your help.
    We are using DB2 v8.1/AIX with FP2.

    Pls. find below the Static and Dynamic SQL:
    Static SQL
    ------------
    SELECT MCID, MID, ICPRF1, ICPRF2, ICNUM, CHAR(DTCLEAR, ISO),
    TMMRCLEAR, CHAR(DTTRAN, ISO), LOCID, TERMNO, UIDTX,
    TRANID, TRANSPID , POEDEST, ICMRST, CNTRTY, TARHITIND,
    SYNBATCHNO, CHAR(TMTRAN, ISO), PGRCD, CNLREA, CNLRMK,
    TMSTMPLSTUPD, SAERRIND
    FROM WICMRD
    WHERE (TMSTMPLSTUPD > :H00027 ) OR (TMSTMPLSTUPD = :H00027 AND
    MCID > :H00028 ) OR (TMSTMPLSTUPD = :H00027 AND MCID =
    :H00028 AND MID > :H00029 )
    ORDER BY TMSTMPLSTUPD, MCID, MID
    OPTIMIZE
    FOR 1 ROWS

    Explain Plan for Static SQL
    -------------------------------
    Estimated Cost = 162144.656250
    Estimated Cardinality = 100254.265625

    Access Table Name = DB2ICA.LICMRD ID = 7,4
    | #Columns = 24
    | Index Scan: Name = DB2ICA.XICMRD2 ID = 4
    | | Regular Index (Not Clustered)
    | | Index Columns:
    | | | 1: TMSTMPLSTUPD (Ascending)
    | | | 2: MCID (Ascending)
    | | | 3: MID (Ascending)
    | | #Key Columns = 0
    | | | Start Key: Beginning of Index
    | | | Stop Key: End of Index
    | | Data Prefetch: None
    | | Index Prefetch: None
    | | Sargable Index Predicate(s)
    | | | #Predicates = 6
    | Lock Intents
    | | Table: Intent Share
    | | Row : Next Key Share
    Return Data to Application
    | #Columns = 24

    End of section


    Dynamic SQL
    ----------------
    SELECT MCID, MID, ICPRF1, ICPRF2, ICNUM, CHAR(DTCLEAR, ISO),
    TMMRCLEAR, CHAR(DTTRAN, ISO), LOCID, TERMNO, UIDTX,
    TRANID, TRANSPID , POEDEST, ICMRST, CNTRTY, TARHITIND,
    SYNBATCHNO, CHAR(TMTRAN, ISO), PGRCD, CNLREA, CNLRMK,
    TMSTMPLSTUPD, SAERRIND
    FROM db2ica.WICMRD
    WHERE (TMSTMPLSTUPD > '2003-01-01-00.00.00.000000' ) OR (TMSTMPLSTUPD = '2003-01-01-00.00.00.000000' AND
    MCID > 'G1' ) OR (TMSTMPLSTUPD = '2003-01-01-00.00.00.000000' AND MCID =
    'G1' AND MID > 10000 )
    ORDER BY TMSTMPLSTUPD, MCID, MID
    OPTIMIZE
    FOR 1 ROWS;

    Explain Plan for Dynamic SQL
    ----------------------------------
    Estimated Cost = 366530.593750
    Estimated Cardinality = 980307.000000

    Access Table Name = DB2ICA.LICMRD ID = 7,4
    | #Columns = 24
    | Index Scan: Name = DB2ICA.XICMRD2 ID = 4
    | | Regular Index (Not Clustered)
    | | Index Columns:
    | | | 1: TMSTMPLSTUPD (Ascending)
    | | | 2: MCID (Ascending)
    | | | 3: MID (Ascending)
    | | #Key Columns = 0
    | | | Start Key: Beginning of Index
    | | | Stop Key: End of Index
    | | Data Prefetch: None
    | | Index Prefetch: None
    | | Sargable Index Predicate(s)
    | | | #Predicates = 6
    | Lock Intents
    | | Table: Intent Share
    | | Row : Next Key Share
    Return Data to Application
    | #Columns = 24

    End of section


    The table structure and index:
    Table
    ------
    CREATE TABLE "DB2ICA "."LICMRD" (
    "MCID" CHAR(2) NOT NULL ,
    "MID" INTEGER NOT NULL ,
    "ICPRF1" CHAR(1) NOT NULL ,
    "ICPRF2" CHAR(1) NOT NULL ,
    "ICNUM" INTEGER NOT NULL ,
    "DTCLEAR" DATE NOT NULL ,
    "DTTRAN" DATE NOT NULL ,
    "LOCID" CHAR(3) NOT NULL ,
    "TERMNO" SMALLINT NOT NULL ,
    "TRANSPID" CHAR(7) NOT NULL ,
    "POEDEST" CHAR(1) NOT NULL ,
    "ICMRST" CHAR(1) NOT NULL ,
    "CNTRTY" CHAR(1) NOT NULL ,
    "TARHITIND" CHAR(1) NOT NULL ,
    "SYNBATCHNO" INTEGER NOT NULL ,
    "TMTRAN" TIME NOT NULL WITH DEFAULT CURRENT TIME ,
    "SAERRIND" CHAR(1) NOT NULL WITH DEFAULT '0' ,
    "TMMRCLEAR" INTEGER NOT NULL ,
    "UIDTX" CHAR(8) NOT NULL ,
    "TRANID" CHAR(4) NOT NULL ,
    "PGRCD" CHAR(1) NOT NULL ,
    "CNLREA" CHAR(2) NOT NULL ,
    "CNLRMK" CHAR(150) NOT NULL ,
    "TMSTMPLSTUPD" TIMESTAMP NOT NULL WITH DEFAULT CURRENT TIMESTAMP )
    IN "TPMRC_TBL" INDEX IN "TPMRC_IDX" ;

    Index
    -------
    CREATE UNIQUE INDEX "DB2ICA "."XICMRD" ON "DB2ICA "."LICMRD"
    ("MCID" ASC,
    "MID" ASC);

    CREATE INDEX "DB2ICA "."XICMRD1" ON "DB2ICA "."LICMRD"
    ("TRANID" ASC,
    "ICNUM" ASC,
    "ICPRF2" ASC);

    CREATE UNIQUE INDEX "DB2ICA "."XICMRD2" ON "DB2ICA "."LICMRD"
    ("TMSTMPLSTUPD" ASC,
    "MCID" ASC,
    "MID" ASC);

    CREATE INDEX "DB2ICA "."XLICMRD2" ON "DB2ICA "."LICMRD"
    ("TRANID" ASC,
    "LOCID" ASC,
    "ICPRF1" ASC,
    "ICPRF2" ASC,
    "ICNUM" ASC,
    "TMSTMPLSTUPD" ASC);



    View
    ------
    CREATE VIEW DB2ICA.WICMRD AS SELECT MCID, MID, ICPRF1 , ICPRF2, ICNUM, DTCLEAR
    , DTTRAN, LOCID, TERMNO , TRANSPID, POEDEST, ICMRST , CNTRTY, TARHITIND,
    SYNBATCHNO , TMTRAN, SAERRIND, TMMRCLEAR , UIDTX, TRANID, PGRCD , CNLREA,
    CNLRMK, TMSTMPLSTUPD FROM DB2ICA.LICMRD;


    From the explain, we found that the cost of Dynamic SQL is much more than Static one, but actually the running time of Dynamic SQL is much less than Static one.....

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Matthewlau,

    This is a little perplexing and I donít know what the problem is. But have listed some things to look at. I realize that you may have already looked at these.

    1. Since everything looks the same in terms of the access path, I wonder if there is some mismatch of DB2 column types and host-variable definitions. I am not proficient in C so I am not sure how this needs to be handled. I "think" that C variables are varchar and DB2 timestamps are fixed length, and don't know if there is something relating to that whch could cause the problem. I am sure that others can comment on that.

    2. I would look at the Bind parameters. Specify QUERYOPT 7. You should probably always use this for static binds since the amount of time it takes to optimize a during the bind is not an issue (except if you use level 9). There may be other bind parameters that need to be looked at.

    3. Make sure you do runstats with full statistics.

    RUNSTATS ON TABLE table-name WITH DISTRIBUTION
    AND DETAILED INDEXES ALL

    4. Try getting rid of OPTIMIZE FOR n ROWS. It seems to me that the index should have prefetch enabled and I am not sure why it is not according to the explain. The entire index is read from start to finish (not using the b-tree) because of the predicates used in the where clause.

    5. Is anyone else on the system when running the tests? What size are the buffer pools? How many times did you run the tests?

    Sorry for not having the answer, and I realize that most of the suggestions above have probably already been considered by you.

  5. #5
    Join Date
    Dec 2001
    Posts
    80
    Originally posted by Marcus_A
    Matthewlau,

    This is a little perplexing and I donít know what the problem is. But have listed some things to look at. I realize that you may have already looked at these.

    1. Since everything looks the same in terms of the access path, I wonder if there is some mismatch of DB2 column types and host-variable definitions. I am not proficient in C so I am not sure how this needs to be handled. I "think" that C variables are varchar and DB2 timestamps are fixed length, and don't know if there is something relating to that whch could cause the problem. I am sure that others can comment on that.

    2. I would look at the Bind parameters. Specify QUERYOPT 7. You should probably always use this for static binds since the amount of time it takes to optimize a during the bind is not an issue (except if you use level 9). There may be other bind parameters that need to be looked at.

    3. Make sure you do runstats with full statistics.

    RUNSTATS ON TABLE table-name WITH DISTRIBUTION
    AND DETAILED INDEXES ALL

    4. Try getting rid of OPTIMIZE FOR n ROWS. It seems to me that the index should have prefetch enabled and I am not sure why it is not according to the explain. The entire index is read from start to finish (not using the b-tree) because of the predicates used in the where clause.

    5. Is anyone else on the system when running the tests? What size are the buffer pools? How many times did you run the tests?

    Sorry for not having the answer, and I realize that most of the suggestions above have probably already been considered by you.
    Marcus_A,
    Thx for your info first, you really help a lots.

    For 1, I am also not sure does the type of host variable affect the performance by doing data conversion, I will try to change other data type and test again.

    For 2, I have tried it before, using 1, 3, 5, 7, 9 optimization level also got the same result.

    For 3, I have done it before.

    For 4, if I don't use optimize for n rows, the plan will not use index scan, instead of that, it will use table scan, which degrade the performance, and the actual running time is also longer when using table scan. I don't know why it will use table scan if I don't use optimize for n rows, may be it is optimizer bug.

    For 5, the buffer pool size is 100M.

    The question is: For the same SQL statement, Why dynamic one is faster than static one by 11 times?

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Host variable definitions can make a big difference in certain situations. Don't really know if it makes a difference in your situation.

    One thing I would try is to take the "dynamic" SQL statement that you have and put it in a program that is statically bound with the WHERE clause predicates hard-coded instead of using host variables. That would help determine if there is something wrong with static binds vs. dynamic, or if it is likely the host variables that are the problem.

    But I donít understand how they can have the same access path in the explain and then perform so differently. Actually I am not familiar with the explain output you presented and I normally use the Visual Explain (except on the mainframe).

  7. #7
    Join Date
    Dec 2001
    Posts
    80
    Originally posted by Marcus_A
    Host variable definitions can make a big difference in certain situations. Don't really know if it makes a difference in your situation.

    One thing I would try is to take the "dynamic" SQL statement that you have and put it in a program that is statically bound with the WHERE clause predicates hard-coded instead of using host variables. That would help determine if there is something wrong with static binds vs. dynamic, or if it is likely the host variables that are the problem.

    But I donít understand how they can have the same access path in the explain and then perform so differently. Actually I am not familiar with the explain output you presented and I normally use the Visual Explain (except on the mainframe).
    Thx for your help, just change the C program to run the SQL in dynamic mode instead of static mode, work fine, it takes less than 1 second to complete, if using static, use 11 seconds. However, at this moment, still don't know why static much slower than dynamic?

Posting Permissions

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