0

I have notification table (very large table). I need your help with the below scenario:

  1. select all notifications for user id;
  2. insert these notifications in notification_log table;
  3. delete all these notifications from the notification table.

My thoughts :

  1. create flag column in notification table and create on update trigger on it to do steps 2 and 3 in the above scenario. Draw Backs: trigger complicity o(n) and I am always select bulk of notification so it will be not nice for performance;
  2. create simple SQL procedure to do the above scenario. Draw Backs: what if step of the scenario failed to commit the whole procedure will roll-back.

Can you help me to optimize this?

  • Triggers aren't a good practice when implementing business rules. If you need to implement this in the DB side put it in a separate SP and call it from the first SP – jean Sep 16 '16 at 14:41
2

How about only SQL statements! no triggers, and no SPs; within a transaction for integrity:

START TRANSACTION;
INSERT INTO notification_log SELECT field1, field2, ... FROM notification_table WHERE user_id=123;
DELETE FROM notification_table WHERE user_id=123;
COMMIT;

Make sure you have an index on user_id.

HTH

|improve this answer|||||
0

Divide and conquer: write a simple stored procedure to perform step 2 (step2proc), then a simple stored procedure to perform step 3 (step3proc).

I would then write a master stored procedure to loop over all different users on the notification table and invoke step2proc and step3proc one user at a time. Be careful and use error handling. This should be a one-time really long execution (in terms of performance issue. You can later run the master stored procedure periodically with a lesser impact on performance.

|improve this answer|||||
0

A delete trigger for notification:

CREATE TRIGGER ... AFTER DELETE ON notification
FOR EACH ROW
BEGIN
    INSERT INTO notification_log (user_id, ...)
    VALUES (OLD.user_id, ...);
END;

Then delete from that table:

DELETE FROM notification WHERE user_id = ...;

No need to select any rows

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.