Minimizing number of queries and performance of certain queries
Hi,
I have the following problem to be solved.
There are two tables
1) applications
2) docs
each application has a set of docs.
mark all applications with status ='skipped' to status = null and in the corresponding docs associated with this application mark a column attr to null.
I initially did it like this -
1) fetch all the applications with status = skipped
for each application
update application set status = null
update docs set attr = null
looping over each application id. so for example if there were 100 applications i will be making 200 queries
normally i am expecting 300-400 applications but it could go up to 10K
I am not sure but on the surface this does seem like a bad strategy if i had to make 10K* 2 db calls
2)
Since we have list of all application ids
update docs set attr= null where application_id in ();
update applications set status = null where id in ();
So in this case no matter how many applications are there it will need only 2 queries
Also the initial one query to fetch all the applications so a total of 3 queries exactly no matter how many applications are there.
Here i am not sure about the performance implications of in clause. but it does seem faster on the surface due to minimal number of queries.
Please comment on this approach if this will be better.
Also is there any way to achieve the same in less than 3 queries or a faster way ?
Thanks
I have the following problem to be solved.
There are two tables
1) applications
2) docs
each application has a set of docs.
mark all applications with status ='skipped' to status = null and in the corresponding docs associated with this application mark a column attr to null.
I initially did it like this -
1) fetch all the applications with status = skipped
for each application
update application set status = null
update docs set attr = null
looping over each application id. so for example if there were 100 applications i will be making 200 queries
normally i am expecting 300-400 applications but it could go up to 10K
I am not sure but on the surface this does seem like a bad strategy if i had to make 10K* 2 db calls
2)
Since we have list of all application ids
update docs set attr= null where application_id in ();
update applications set status = null where id in ();
So in this case no matter how many applications are there it will need only 2 queries
Also the initial one query to fetch all the applications so a total of 3 queries exactly no matter how many applications are there.
Here i am not sure about the performance implications of in clause. but it does seem faster on the surface due to minimal number of queries.
Please comment on this approach if this will be better.
Also is there any way to achieve the same in less than 3 queries or a faster way ?
Thanks
update docs set attr = null where application_id in (select id from applications where status = 'skipped')
update applications set status = null where status = 'skipped';
update applications set status = null where status = 'skipped';
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
http://www.postgresqltutorial.com/postgresql-inner-join/
Select a.id,a.status,b.applicatio
From applications a
Inner join docs b on b.application_id=a.id
Where a.status='skipped'
The join might be outer/left/right to pull all rows from docs.
Select a.id,a.status,b.applicatio
From applications a
Inner join docs b on b.application_id=a.id
Where a.status='skipped'
The join might be outer/left/right to pull all rows from docs.
update applications set status = null where id in (select application_id from docs where status ='skipped');
update docs set attr = null where status ='skipped';
commit;
You shouldn't need any loops.