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..!!

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.

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

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 ... )

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.

It might seem that does not carry more info thansince :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).

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.

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...

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 )

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."