Hi Michael,
Michael Glaesemann wrote:
On Jul 6, 2007, at 9:42 , Arnau wrote:
I have the following scenario, I have users and groups where a user
can belong to n groups, and a group can have n users. A user must
belogn at least to a group. So when I delete a group I must check that
there isn't any orphan. To do this I have something like that:
IF v_count = 1 THEN
DELETE FROM users WHERE user_id = result.user_id;
v_deleted = v_deleted + 1;
END IF;
Am I right in reading that you're deleting any users that would be
orphans? If so, you can just delete the orphans after rather than delete
them beforehand (untested):
-- delete user_groupDELETE FROM user_groups
WHERE user_group_id = p_group_id;
-- delete users that don't belong to any group
DELETE FROM users
WHERE user_id IN (
SELECT user_id
LEFT JOIN user_groups
WHERE group_id IS NULL);
This should execute pretty quickly. You don't need to loop over any
results. Remember, SQL is a set-based language, so if you can pose your
question in a set-based way, you can probably find a pretty good,
efficient solution.
I have tested your solution and it's much worse than mine.
My test database has about 254000 users and about 30 groups. The test
I have done is remove a group with 258 users, my solution has taken
about 3 seconds and your solution after 20seconds didn't finished. Of
course the test machine is an old celeron with few MB of RAM, but as
test machine does the job.
Thank you very much
--
Arnau
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
