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