What is differnce bettween these quries.

BINDUIBMMF · New User Joined: 21 Mar 2006 Posts: 6

Hi,

Any one can you tell me i have 2 quries(T1 and T2) below.T1 using commas and T2 using Concatinations. I want to know

which one is to use better. I know functional wise both are same and i think performence wise also both are same.But i think

some difference is there .Plz can u help me to find out (miner diff) the difference.

1) (T1) fIRST QUERY

DELETE FROM MS_STUDENT A
WHERE A.MS_CODE = 101

AND ( A.std_fst_name , A.std_mid_name ,
A.std_lst_name , A.std_course_code ,
A.std_course_name
)

IN

( SELECT

B.std_fst_name , B.std_mid_name ,
B.std_lst_name , B.std_course_code ,
B.std_course_name

FROM STUEDNT_DEPT B

WHERE B.STUDENT_CODE = 101

)

2) (T2) SECOND QUERY

DELETE FROM MS_STUDENT A
WHERE A.MS_CODE = 101

AND ( A.std_fst_name || A.std_mid_name ||
A.std_lst_name || A.std_course_code ||
A.std_course_name
)

IN

( SELECT

B.std_fst_name || B.std_mid_name ||
B.std_lst_name || B.std_course_code ||
B.std_course_name

FROM STUEDNT_DEPT B

WHERE B.STUDENT_CODE = 101

)

DavidatK · Posted: Tue Jul 25, 2006 12:23 am

I think there is more than a minor difference between these two SQLs.

In T1 DB2 is comparing column for column, this is the normal way to do this.

In T2 DB2 is creating a concatenated string and comparing string for string. In the case that you have presented here, the result will be the same, as all of the columns are ?char? values.

However, to use the concatenation method, when all of the column values are not ?char? values, you must ?cast? the non ?char? value to a ?char? value to do the concatenation.

Also, when you use the concatenation method, you will not be able to utilize the indexes as efficiently as you might.

Dave

BINDUIBMMF · New User Joined: 21 Mar 2006 Posts: 6

Thanks for your reply David ,

But i checked with DBA he told index we can use and performance wise also i checked with him .he find T1 query is more efficiency than T2 query.
but i believe some diff is there . any one can you tell T2 is good than T1.

chiranjeevi_mca · New User Joined: 19 Feb 2006 Posts: 27

hi,

for the first query

it will compares the first column of the first table and first column of the second table, when it matches it will compare with the second column else
it skips that record.

for the second query

it will concatenate all the data of the columns retrieved from the first table like that for the second table it will compare.

as a performance issue the first one is better than second one.

BINDUIBMMF · New User Joined: 21 Mar 2006 Posts: 6

Thanks Cheeranjeevi ,

Thanks for your reply and can you find some thing more differences on that other than that.

chiranjeevi_mca · New User Joined: 19 Feb 2006 Posts: 27

ex:

table1
col1 col2 col3 col4
------ ------ ------ -------
1 a z i
2 b y j
3 c x k
4
5
6
7
8
9

table2
col1 col2 col3 col4
------ ------ ------ -------
10 a z i
2 b y j
30 c x k
4
50
60
7
8
9

in this case think col1 as primary key, uniquie index

for first query
-----------------

it will compare
first row of the first table column and all the rows of second table column

second row of the first table column and all the rows of second table column

1=10 if it does not matches
1=2
1=30
1=4
1=5
...
...
2=10
2=2(here it matches)
it skips for first column trying to match second column
if you have more than one equal matches it will check the second column of all the matched rows
b=b(here it matches)
it skips for second column trying to match third column
....
..
..

for second query
--------------------
1azi=10azi
1azi=2byj
1azi=3cxk
...
...
...
2byj=10azi
2byj=2byj(it matched) it directly retrives this row.

from here onwards it continues checking

2byj=3cxk
.....
.....
3cxk=10azi
3cxk=2byj
3cxk=3cxk(it matched) it directly retrives this row.

if you are using order by caluse then perfomance of index clause will come

if you are not using order by clause any where it will retrive the data by using index value.