| How to tune this sql? [message #437665] |
Tue, 05 January 2010 07:43  |
prachij593
Messages: 266
Registered: May 2009
|
Senior Member |
|
|
update table3 d set d.flag_status ='Y'
where
(select count(*)
from table1 a
where exists
(select 1
from table1 b
where b.ci_id = t.ci_id
and a.c_id=b.c_id))
=( select count(*) from table1 ci
where exists
(select 1
from table1 a,table2 b
where a.ci_id = b.ci1_id
and a.c_id=ci.c_id
and b.ci_id = t.ci_id
and b.status_cd = 'NA'));
Is there another way to write this sql for better performance.
Thanks!
|
|
|
|
| Re: How to tune this sql? [message #437673 is a reply to message #437665] |
Tue, 05 January 2010 08:08  |
cookiemonster
Messages: 13922
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Are you sure that update is correct? The where clause does not reference the table being updated at any point. Plus I doubt table1 should be listed 4 times - it'd have been simpler if you if hadn't renamed the tables in the select. In fact why don't you just copy and paste the original select.
Also next time you post code can you format it properly please, indentation is important.
|
|
|
|
| Re: How to tune this sql? [message #437674 is a reply to message #437673] |
Tue, 05 January 2010 08:16 |
prachij593
Messages: 266
Registered: May 2009
|
Senior Member |
|
|
Yes the replacement is correct for table1
I want to update table3 and set flag=y where count(*) from some table = count(*) from some table
update table3 d set d.flag_status ='Y'
where
(select count(*)
from table1 a
where exists
(select 1
from table1 b
where b.ci_id = d.ci_id
and a.c_id=b.c_id))
=( select count(*) from table1 ci
where exists
(select 1
from table1 a,table2 b
where a.ci_id = b.ci1_id
and a.c_id=ci.c_id
and b.ci_id = d.ci_id
and b.status_cd = 'NA'));
[Updated on: Tue, 05 January 2010 08:32] Report message to a moderator |
|
|
|
| Re: How to tune this sql? [message #437680 is a reply to message #437674] |
Tue, 05 January 2010 08:33 |
cookiemonster
Messages: 13922
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
prachij593 wrote on Tue, 05 January 2010 14:16Yes the replacement is correct for table1
Really?
In that case what is the point of this exists clause:
select count(*)
from table1 a
where exists (select 1
from table1 b
where b.ci_id = d.ci_id
and a.c_id=b.c_id)
You're joining a table to itself using the same columns. That'll always be true unless the table is empty.
And if the where clause really isn't supposed to reference the table being updated then I wouldn't do this as a single update. Write a seperate select to check the counts and if that gives the correct result do the update.
|
|
|
|
| Re: How to tune this sql? [message #437681 is a reply to message #437680] |
Tue, 05 January 2010 08:48 |
prachij593
Messages: 266
Registered: May 2009
|
Senior Member |
|
|
select 1
from table1 b
where b.ci_id = d.ci_id
gives the c_id for the join of tables table1,table3;
and then getting the count for the c_id from table1 that are in the above resulset.
|
|
|
|
| Re: How to tune this sql? [message #437688 is a reply to message #437665] |
Tue, 05 January 2010 08:57 |
cookiemonster
Messages: 13922
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Ok, I misread that.
I really think you need to explain in english and in detail what this update is supposed to do. Preferably with a test case (create table and insert statements) so we can play with it.
Because with understanding what it's supposed to do we really can't suggest alternatives and this is a complicated update.
|
|
|
|
| Re: How to tune this sql? [message #437690 is a reply to message #437674] |
Tue, 05 January 2010 09:14 |
narsap
Messages: 8
Registered: December 2009
|
Junior Member |
|
|
Prachi,
Few questions :
1. You said you want to tune this query but from the looks of it it appears to be kind-of one-time activity? Is it?
2) What is the relation between tables table1, table2 and table3?
From the sql, it appears that table3 has one-to-many relationship with table1 and table2. Is it the case? Also table2 appears to have two columns ci_id and ci1_id but both appear to contain the same value (which is same as the value of ci_id in table3 or table1). Is that the case? Also, what is the relationship between table1 and table2? Many-to-Many?
|
|
|
|
| Re: How to tune this sql? [message #437692 is a reply to message #437665] |
Tue, 05 January 2010 09:31 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
>Is there another way to write this sql for better performance.
Just curious, from where does alias "t" (as in t.ci_id) originate [depending upon which code morph you obfuscate]
It would be helpful if you provided DDL for tables involved.
It would be helpful if you provided DML for test data.
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
[Updated on: Tue, 05 January 2010 11:51] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
