Optimal SQL solution?

Question

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 rollback

can you help me to optimize this .

Thanks in advance

Answer 1

I think You should go with the simple procedure.

Below are the steps you should follow.

1) create index on user_id in notification table. 2) write procedure with cursor. 3) collect all the user_id in cursor and iterate. 4) select all the rows from notification with user_id from cursor. 5) insert those rows in notification_log table. 6) delete inserted column from notification column.

NOTE :-- Do not add any foreign key or index on notification_log table once you have executed your procedure successfully then add required foreign key and indexes.

Answer 2

There is not much that can fail on INSERT ... SELECT ...; DELETE ...;, so I would definitely go with the stored procedure.

Also, there are some options to make the whole process faster (temporarily remove indexes and constraints, etc), but that's not what you asked about :-)