| View previous topic :: View next topic |
| Author |
Message |
dilip
Beginner
Joined: 08 Nov 2003
Posts: 7
Topics: 7
Location: india
|
 Posted: Mon Dec 01, 2003 7:46 am Post subject: Query to check the presence of a record : Efficient way |
 |
|
Hi,
I need to write a query to find whether a particular record exists or not in a table. Basically I want the query to return a single value if the record is found.
Below are the possible solutions
| Code: |
SELECT 1
FROM MSG_STATUS
WHERE MSST_MSG_CTRL = 'H50344'
FETCH FIRST 1 ROW ONLY
SELECT distinct 1
FROM MSG_STATUS
WHERE MSST_MSG_CTRL = 'H50344'
SELECT COUNT(*)
FROM MSG_STATUS
WHERE MSST_MSG_CTRL = 'H50344'
SELECT 1
FROM SYSIBM.SYSDUMMY1 A
WHERE EXISTS (
SELECT 1 FROM
FROM MSG_STATUS
WHERE MSST_MSG_CTRL= 'H50344'
AND A.IBMREQD = A.IBMREQD
)
|
Is there any other way I can know whether the record is there or not .. performance is really a issue has the table contains records in lakhs or more
Pls let me know
_________________
Dilip T. |
|
| Back to top |
|
 |
SureshKumar
Intermediate
Joined: 23 Jan 2003
Posts: 211
Topics: 21
|
| Posted: Mon Dec 01, 2003 8:41 am Post subject: |
|
|
| Dilip, FETCH FIRST is the best, assuming there is an Index on MSST_MSG_CTRL . |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12399
Topics: 75
Location: San Jose
|
| Posted: Mon Dec 01, 2003 9:04 am Post subject: |
|
|
Dilip,
If you have DB2 Version 7 , then you can use the FETCH FIRST 1 ROW only which in my opinion is the best option.
Option2: Since your table is huge , using a distinct would be costly
Option3: Count(*) is a column function and may result in tablespace scan. Not recommended
Option4: This is the known solution for checking the existence of a record and it is also a good solution.
Alternatively you can run an explain on all the 3 queries and choose the optimal solution. Search this forum for examples of running EXPLAIN.
Hope this helps...
cheers
kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
Anand_R
Intermediate
Joined: 24 Dec 2002
Posts: 189
Topics: 60
|
| Posted: Tue Feb 24, 2004 4:32 pm Post subject: |
|
|
Kolusu,
In option 4 , Will it use stage 2 processing or stage 1 processing? . As of I know It will take stage2 processing, because of following statement
A.IBMREQD = A.IBMREQD
which is not so effective.
And for option 1, it is stage 1 processing.
Plesae clarify..
Thanks
Anand |
|
| Back to top |
|
|
vkphani
Intermediate
Joined: 05 Sep 2003
Posts: 483
Topics: 48
|
| Posted: Thu Feb 26, 2004 12:20 am Post subject: |
|
|
| Can anybody pls explain me the disadvantage of using SELECT(*) in an SQL query. |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12399
Topics: 75
Location: San Jose
|
| Posted: Mon Mar 01, 2004 2:09 pm Post subject: |
|
|
Vk phani,
Using select * is has its disadvantages in case of large tables(more columns) as DB2 has a lot more data to deal with which requires large buffer areas. But if the tables are small then there is absolutely no difference using select * with a select. But in general it is a good idea to select only the columns you are interested in.
Ravi: SYSIBM.SYSDUMMY1 table just contains 1 row. It has just one column IBMREQD which is 1 byte not null character field. The table is used for SQL statements in which a table reference is required, but the contents of the table are not important.
In your 4th sql you are self joining the sysdummy1 table to itself. so you will have 1 row which satisfies the inner query.
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
