| View previous topic :: View next topic |
| Author |
Message |
manojvoona
Beginner
Joined: 05 Jan 2006
Posts: 43
Topics: 14
|
 Posted: Tue Nov 14, 2006 1:09 pm Post subject: Help in fine tunning a SQL query using Joins and Like |
 |
|
Hi,
we have query some thing like below
| Code: |
Select D.PERM_ADDR_STATE_CD
,CUS.BIRTH_DT
,CUS.TAX_ID
,D.PERM_ADDR_ZIP_CD
,D.PERM_ADDR_CITY_NM
,CUST_FULL_NM
FROM MUS.TUZ42 AS C INNER JOIN
MUS.TUZ49 AS D ON
C.CUST_ID = D.CUST_ID JOIN
MUS.TUZ43 AS CUS ON
C.CUST_ID = CUS.CUST_ID AND
CUS.RCPT_DT = C.RCPT_DT
WHERE C.RCPT_DT = :RCPT-DT
AND CUST_FULL_NM LIKE :CUST-FULL-NM
AND C.AMPF_STA_CD = '1'
|
We need to fetch all the record from the database where ever the customer name is like the required feild.
The customer name is 27 charecters in the databas and the CUST-FULL-NM is 10 charecter length.
we are adding the rest of the length in the CUST-FULL-NM as '%"
The data Present in this table is around 5 million recors. The query runs of around 20 hours due to the LIKE In the query
Please help me if you have any better solution than like in the query
Thanks
Manoj Voona |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
| Posted: Tue Nov 14, 2006 1:36 pm Post subject: |
|
|
manojvoona,
2 Joins and a Like parameter will dent your performance. You need to re-design your logic.
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
manojvoona
Beginner
Joined: 05 Jan 2006
Posts: 43
Topics: 14
|
| Posted: Tue Nov 14, 2006 2:02 pm Post subject: |
|
|
Thanks a lot Kolusu.
We really appreciate if you could let us know how we can redesign it.
-Manoj voona |
|
| Back to top |
|
|
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
| Posted: Tue Nov 14, 2006 2:55 pm Post subject: |
|
|
how about removing all that join logic and defining a simple cursor (with the like) and then do singleton selects for the other two tables based on data returned by the fetch.
_________________
Dick Brenholtz
American living in Varel, Germany |
|
| Back to top |
|
|
manojvoona
Beginner
Joined: 05 Jan 2006
Posts: 43
Topics: 14
|
| Posted: Tue Nov 14, 2006 4:39 pm Post subject: |
|
|
Thanks a lot.
Does the total execution time for breaked up query may take same time as of the above query? this is because the record count is same and the database tables are same.
Please do mind if the its foolish questions, I am new to DB2.
Thanks |
|
| Back to top |
|
|
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
| Posted: Tue Nov 14, 2006 4:51 pm Post subject: |
|
|
my experience with huge (million or more) joins is that the simple cursor and singleton selects are faster, mainly because not so much DB2 resource (space) is used up building interm tables, sorting and then createing a result table.
_________________
Dick Brenholtz
American living in Varel, Germany |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
