|
View previous topic :: View next topic
|
| Author |
Message |
harishch_ch
New User
Joined: 07 Jan 2008
Posts: 27
Location: bangalore
|
|
|
|
Hi,
Can anyone try to optimize the below query:
| Code: |
DELETE FROM T1
WHERE T1_KEY LIKE 'ABC%'
AND SUBSTR(T1_KEY,7,7) IN
(SELECT SUBSTR(T2_KEY,7,7) FROM T2
WHERE T2 LIKE 'XYZ%'); COMMIT; |
Also can we use Joins in Delete query?
Let me know for more details.
Rgds,
Harish.
Edited: Please use BBcode when You post some code, that's rather readable...Anuj |
|
| Back to top |
|
 |
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India
|
|
|
|
Hi,
For first - let's start from you - what did you try so far ?
For this
| Quote: |
| Also can we use Joins in Delete query? |
answer is yes - but your query should be structured well. |
|
| Back to top |
|
|
Suresh Ponnusamy
Active User
Joined: 22 Feb 2008
Posts: 107
Location: New York
|
|
|
|
Hi Anuj
I think we cannot use join in Delete query. I tried the same in my shop, I got error message.
Could you please post an example. |
|
| Back to top |
|
|
harishch_ch
New User
Joined: 07 Jan 2008
Posts: 27
Location: bangalore
|
|
|
|
Hi,
Posted query is the optimized one which i had done.Still it is taking huge CPU time due to more number of records.I am not able to proceed after this.
Functionality of the query(may be helpful):
Inner query returns 80k recs. Each record fetched by outer query as to search all the 80k recs, this is leading to more cpu time.
Regards,
Harish. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| I am not able to proceed after this |
As long as you continue to use a single, very expensive query, probably not.
If the appropriate data was unloaded into qsam files, the process might be made to run very acceptable.
Of course, it would mean that some programming work might need to be done. . . |
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India
|
|
|
|
Hi Suresh,
Let's say you wanted to delete the rows in Table-1 (T1) that also exist in Table-2 (T2). A common mistake is to assume that this will work:
| Code: |
DELETE T1
INNER JOIN T2
ON T1.SE_NUMBER = T2.SE_NUMBER |
will yield error some thing like : error near Line 2, Incorrect syntax near the keyword 'INNER'.
What you need to do is delete from the table, and perform the join in the subquery, e.g.:
| Code: |
DELETE T1
FROM T1
INNER JOIN T2
ON T1.SE_NUMBER = T2.SE_NUMBER |
However, and while it may not be completely ANSI-friendly, I would prefer to do the following simply for readability:
| Code: |
DELETE T1
WHERE SE_NUMBER IN
(
SELECT SE_NUMBER FROM T2
) |
This makes it much clearer that your intention is to delete rows *only* from the table T1.
PS. My system is not up & 'am not keeping well - so din't test it (By the way this works with MySQL, didn't check with IBM DB2 though), please give a try or I'll have a look on this after some time, Thanks. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hi Anuj,
The issue isn't getting the query to work, but to get the query to work acceptably performance-wise. If there are thousands of rows in the outter query and each returns 80k rows it does not take long to get to hundreds of millions of returned rows.
Moving the process outside of sql may reduce the resources required dramatically. One example i can mention is a process that was terminated after running 3 days. The people responsible for that project asked me to look into it. Took their developers and me most of a weekend to re-do the process and the eventual soluton ran in under 3 hours. Their problem was a 4-deep cartesian product. . . . Once it was all explained, one of their people calculated the job would probably have run more than 11 days. . .
Happy Thanksgiving! |
|
| Back to top |
|
|
harishch_ch
New User
Joined: 07 Jan 2008
Posts: 27
Location: bangalore
|
|
|
|
Hi,
Proceeding with writing a new cobol-db2 program i am closing this topic.
Regards,
Harish. |
|
| Back to top |
|
|
|
|
