Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

What is differnce bettween these quries.

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
BINDUIBMMF

New User


Joined: 21 Mar 2006
Posts: 6

PostPosted: Mon Jul 24, 2006 7:43 pm    Post subject: What is differnce bettween these quries.
Reply with quote

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

)
Back to top
View user's profile Send private message

DavidatK

Active Member


Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA

PostPosted: Tue Jul 25, 2006 12:23 am    Post subject: Re: What is differnce bettween these quries.
Reply with quote

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
Back to top
View user's profile Send private message
BINDUIBMMF

New User


Joined: 21 Mar 2006
Posts: 6

PostPosted: Tue Jul 25, 2006 7:56 pm    Post subject: Re: What is differnce bettween these quries.
Reply with quote

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.

icon_cool.gif
Back to top
View user's profile Send private message
chiranjeevi_mca

New User


Joined: 19 Feb 2006
Posts: 27

PostPosted: Mon Jul 31, 2006 10:58 am    Post subject: Re: What is differnce bettween these quries.
Reply with quote

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.
Back to top
View user's profile Send private message
BINDUIBMMF

New User


Joined: 21 Mar 2006
Posts: 6

PostPosted: Tue Aug 01, 2006 2:42 pm    Post subject: Re: What is differnce bettween these quries.
Reply with quote

Thanks Cheeranjeevi ,


Thanks for your reply and can you find some thing more differences on that other than that.
Back to top
View user's profile Send private message
chiranjeevi_mca

New User


Joined: 19 Feb 2006
Posts: 27

PostPosted: Tue Aug 01, 2006 5:25 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts can we acheive below quries with over... nkothako JCL & VSAM 1 Mon Feb 02, 2009 12:15 pm
No new posts Differnce in Function,Shared and appl... suchita_thorat TSO/ISPF 3 Tue Sep 16, 2008 2:59 pm
No new posts Differnce between move and set statem... ajayjindal21 CA Products 7 Fri May 16, 2008 10:35 am
No new posts DIFFERNCE BETWEEN DECLARING A CURSOR ... puppalso DB2 6 Wed Jan 02, 2008 2:57 pm
No new posts Differnce between ARG and PULL Gnanas N CLIST & REXX 4 Tue Dec 11, 2007 6:15 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us