Results 1 to 10 of 10
  1. #1
    Join Date
    May 2006
    Posts
    82

    Unanswered: Does this take More CPU..??

    Hi,

    I have come across some presentations saying there should be some difference between the following queries.(not much may be litle bit).

    SELECT A,B FROM TABLE WHERE A = :VAR
    and
    SELECT B FROM TABLE WHERE A = :VAR

    I have read articles saying, Select the columns only you need. The unused columns may be additional over head.

    Could there be any minor difference in CPU or IO for the above two queries..?.

    Please justify. Thanks..!!
    Vinay,

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    If you select more data than you need, you have allow for more resources being spent. In your example, the DBMS has to allocate memory buffers for column A in the select list and the data in that column also has to be sent over the wire you your client application. This will require more CPU and memory resources than the 2nd query. The question is if the difference could be measurable - I rather doubt that.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    In my opinion it depends what kind of size of data you have. It could impact on millions of rows. It is also important what does where condition returns. Is column A primary key? If is I don't think there can be measurable. But if it is not it can... It would be nice to see such a benchmark. If you have time, try this out and be so kind to let as know.
    Hope this helps,
    Grofaty
    Last edited by grofaty; 12-21-07 at 05:30.

  4. #4
    Join Date
    Dec 2005
    Posts
    273
    consider some overhead in the hosting program, too:

    /***$$$
    EXEC SQL
    SELECT NAME, CREATOR
    INTO :A , :B
    FROM SYSIBM.SYSIBM.SYSTABLES
    WHERE NAME = :A
    $$$***/
    DO;
    SQLPLIST1.SQLPVADDR(1) = ADDR(A);
    SQLPLIST1.SQLPVTYPE(1) = 452;
    SQLPLIST1.SQLPVLEN(1) = 120;
    SQLPLIST1.SQLPVIND(1) = SYSNULL();
    SQLPLIST1.SQLPVNAME(1) = '';
    SQLPLIST1.SQLPVARS.SQLPVDAID = 'E2C3F6C4C1404008'X;
    SQLPLIST1.SQLPVARS.SQLPVDABC = 60;
    SQLPLIST1.SQLPVARS.SQLPVN = 1;
    SQLPLIST1.SQLPVARS.SQLPVD = 1;
    SQLPLIST1_SQLVPARM = ADDR(SQLPLIST1.SQLPVARS.SQLPVDAID);
    SQLPLIST1.SQLAVADDR(1) = ADDR(A);
    SQLPLIST1.SQLAVTYPE(1) = 452;
    SQLPLIST1.SQLAVLEN(1) = 120;
    SQLPLIST1.SQLAVIND(1) = NULL();
    SQLPLIST1.SQLAVNAME(1) = '';

    SQLPLIST1.SQLAVADDR(2) = ADDR(B);
    SQLPLIST1.SQLAVTYPE(2) = 452;
    SQLPLIST1.SQLAVLEN(2) = 120;
    SQLPLIST1.SQLAVIND(2) = NULL();
    SQLPLIST1.SQLAVNAME(2) = '';
    SQLPLIST1.SQLAVARS.SQLAVDAID = 'E2E8C344C1414508'X;
    SQLPLIST1.SQLAVARS.SQLAVDABC = 104;
    SQLPLIST1.SQLAVARS.SQLAVN = 2;
    SQLPLIST1.SQLAVARS.SQLAVD = 2;
    SQLPLIST1_SQLAPARM = ADDR(SQLPLIST1.SQLAVARS.SQLAVDAID);
    SQLPLIST1.SQLCODEP=ADDR(SQLCA);
    CALL DSNHLI(SQLPLIST1);



    for each column you need 5 host-variables and several instructions ( and the ADDR() function is "expensive" in PL1 ).

    Of course, most performance is wasted, if such columns are sorted by ORDER BY, DISTINCT or GROUP BY.
    ( greater record length, more pages needed, more getpage-requests ... )

  5. #5
    Join Date
    May 2006
    Posts
    82

    Great

    AWESOME...!!. Great idea flows in. I see the importance of Knowledge sharing. It always keep our knowledge up.!

    Thanks for the info guys. I will do RND and will get back to you if am success in seeing some thing.
    Vinay,

  6. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    It might seem that
    Code:
    SELECT A,B FROM TABLE WHERE A = :VAR
    does not carry more info than
    Code:
    SELECT B FROM TABLE WHERE A = :VAR
    since :VAR is known at the client side.
    But still the returned A may carry a tiny bit of extra information!

    Assume column A is of type VARCHAR(100).
    Assume the content of variable VAR is ' ' (one blank).
    Assume table TABLE contains 100 rows, where field A of the i-th row consists of i blanks.

    In that case, all rows satisfy the condition, but still 100 different values for A are returned (namely all of different lengths).
    --_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
    May 2006
    Posts
    82

    DSN_STATEMENT_TABLE doenst give any clue

    Hi,

    I tried running an Explain on this queries. Let me tell how and what i did this.

    I ran an EXPLAIN in Production on one of the tables where it had the latest statistics. I have my own Plan table and DSN tables in PROD.

    Table name: ICB_data

    column names:
    Tms --> TIMESTAMP NOT NULL WITH DEFAULT
    REFNR --> CHAR(10) NOT NULL
    and other columns which we dont need.

    There is an Unique index built on TMS and REFNR in the order,
    COLNAME ORDER
    TMS A
    REFNR A

    I ran DB2 Explain on the below four queries for this table.

    EXPLAIN PLAN SET QUERYNO = 96
    SELECT REFNR, TMS FROM ICB_DATA WHERE REFNR = ?

    EXPLAIN PLAN SET QUERYNO = 97
    SELECT TMS FROM ICB_DATA WHERE REFNR = ?

    EXPLAIN PLAN SET QUERYNO = 98
    SELECT REFNR, TMS FROM ICB_DATA WHERE TMS = ?

    EXPLAIN PLAN SET QUERYNO = 99
    SELECT REFNR FROM ICB_DATA WHERE TMS = ?

    Once I ran explain, I queried the DSN_STATEMENT table to check the COST_CATEGARY, Estimated milliseconds and Estimated Serivce units. I just had hope that the columns milliseconds and service units will be different in each case however it was not the case.

    I dont understand why the queires 96 & 97 are going with MATCHCOLS = 0 though there is an index built on REFNR. I see it is the last column on the index however the ACCESSTYPE is still I. All the queries says it has INDEX ONLY ACCESS.

    Here is the result from Dsn statement table.

    EXPLAIN COST
    QUERYNO TIME CATEGORY PROCMS PROCSU
    -------- -------------------------- -------- ----------- -----------
    96 2007-12-27-13.40.07.930000 A 173 4206
    97 2007-12-27-13.41.25.100000 A 173 4206
    98 2007-12-27-13.43.02.940000 A 1 1
    99 2007-12-27-13.43.18.690000 A 1 1

    So Is there any difference to measure..?. Is there any other place we can see more information..? I just did what all i could do as am not expertise.

    Please let me know your comments on this. Thanks a lot for your time.
    Vinay,

  8. #8
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by vini_srcna
    Code:
     QUERYNO   EXPLAIN TIME               COST CATEGORY  PROCMS       PROCSU  
    --------  --------------------------  -------------  ------  -----------  
          96  2007-12-27-13.40.07.930000  A                 173         4206  
          97  2007-12-27-13.41.25.100000  A                 173         4206  
          98  2007-12-27-13.43.02.940000  A                   1            1  
          99  2007-12-27-13.43.18.690000  A                   1            1
    So Is there any difference to measure..?
    DSN_STATEMNT_TABLE only carries the (estimated) CPU cost (inside the DB2 address space), no I/O cost.
    Since the only difference between 96 and 97 is the I/O, it's to be expected that the two PROCSU values are identical.
    And no, there is no other (obvious) place to find the I/O cost difference...
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  9. #9
    Join Date
    Dec 2005
    Posts
    273
    Quote Originally Posted by vini_srcna
    I dont understand why the queires 96 & 97 are going with MATCHCOLS = 0 though there is an index built on REFNR. I see it is the last column on the index however the ACCESSTYPE is still I. All the queries says it has INDEX ONLY ACCESS.

    Db2 has two choices.
    1) Use the index on REFNR ( with one matching column, but access to the data )
    2) Use the index on TMS,REFNR ( which is a non-matching index-scan, but index-only as all required data can be found in the index )

    DB2 prefers the index-only access.
    ( An index on REFNR,TMS would take advantage of both, matching-column and index-only )

  10. #10
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Here is the extract from the SQL reference guide (topic "EXPLAIN") on PROCSU:

    "The estimated processor cost, in service units, for the SQL statement. The estimate is rounded up to the next integer value. The maximum value for this cost is 2147483647 service units. If the estimated value exceeds this maximum, the maximum value is reported."
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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