Does this take More CPU..??

vini_srcna · Posted: Thu Dec 20, 2007 9:47 pm

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

expat · Posted: Thu Dec 20, 2007 10:19 pm

Why not try it for yourself and let us know your findings ?

Santoshdorge · New User Joined: 27 Jun 2006 Posts: 48 Location: Pune

Hi,
Basically this depends on the index of the table.In the above case if the index is created on column A definately performance of the first query would be better than that of second.

thanks,
Santosh

dbzTHEdinosauer · Posted: Fri Dec 21, 2007 3:35 pm

????????????? the only difference between the two queries is that the first returns two columns, the second only one column.

so, what do you think would take more time, dealing with two columns or only dealing with one?

Santoshdorge · New User Joined: 27 Jun 2006 Posts: 48 Location: Pune

Hi Dick,
I do agree with you index if there any would affect both the queries.

As per my understanding that should not be preformance difference if you are pointing to the number of columns being selected in query.Because records from the db2 internal data sets are bring into buffer as a whole not a perticular column and then filtered out for particular column.

Plz do correct me if i am going in wrong direction.

thanks,
Santosh.

vini_srcna · Posted: Fri Dec 21, 2007 7:23 pm

I still think there should be some difference. Nothing comes for free.
It may not be measurable. Will let you know when i get the right answer.

dick scherrer · Posted: Fri Dec 21, 2007 11:42 pm

Hello,

vini_srcna · Posted: Thu Dec 27, 2007 10:00 pm

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.

stodolas · Posted: Thu Dec 27, 2007 11:59 pm

Looks like you showed yourself that the time is not different at all.

vini_srcna · Posted: Fri Dec 28, 2007 6:26 pm

Sorry stodolas,

I didnt understand what you mean.

stodolas · Posted: Fri Dec 28, 2007 7:17 pm

Your results show no difference in the PROCSU (processor service units column?). Which means that you answered your own question. There is no measurable difference in DB2 processor usage between your statements.

vini_srcna · Posted: Fri Dec 28, 2007 9:34 pm

Hi Stodolas,

If am right, the Explain gives the estimated CPU cost however it wont give the I/O Cost.

Most of the things we are trying to measure might come under I/O.

stodolas · Posted: Fri Dec 28, 2007 10:00 pm

The difference in I/O is negligible, probably immeasurable. DB2 stores rows in a special type of VSAM. I don't know for sure, but it probably reads a whole row to get a single column or to get multiple columns.

If you are looking at this to improve performance, I suggest looking elsewhere.

enrico-sorichetti · Posted: Fri Dec 28, 2007 10:01 pm