Best way to do 10,000 inserts in a SQL db?

Question

What happens is whenever i upload media onto my site; everyone will get a notification. Each person can click a box to remove and it will be gone from their msg queue forever.

If i had 10,000 people on my site how would i add it to every person msg queue? I can imagine it takes a lot of time so would i opt for something like a filesystem journal? mark that i need to notify people, the data then my current position. Then update my position every 100 inserts or so? I would need a PK on my watcher list so if anyone registers in the middle of it my order will not be broken since i'll be sorting via PK?

Is this the best solution for a mass notification system?

-edit-

This site is a user created content site. Admins can send out global messages and popular people may have thousands of subscribers.

Answer 1

Maybe just record for each user which notifications they have seen- so the set of notifications to show to a user are ones created before their "earliest_notification" horizon (when they registered, or a week ago...) minus the ones they have acknowledged. That way you delay inserting anything until it's a single user at once- plus if you only show users messages less than a week old, you can purge the read-this-notification flags that are a week or more old.

(Peformance optimisation hint from my DBA at my old job: "business processes are the easiest things to change, look at them first")

Answer 2

IMHO inserting records may not be the most efficient solution to this problem. Can you use a client side cookie to store whether the user removed the notification or do you have to keep track of this even if they clear cookies? If you upload a new video the app can just compare the cookie with the new video record id and decide to show or hide the notification based on whats stored in the cookie. This will save you a ton of database inserts, and keeps most heavy lifting on the client.

Answer 3

If you're using Postgres, you can use the COPY command which is the fastest method of them all:

COPY tablename (col1, col2, col3) FROM '/path/to/tabfile';

where tabfile is a TAB-separated file with lots of entries. This will fail if there are some UNIQUE constraints, though, and there are duplicates in the file.

Answer 4

If 10000 inserts into a narrow many to many table linking the recipients to the messages (recipientid, messageid, status) is slow, I expect you've get bigger problems with your design.

This is the kind of operation I wouldn't typically even worry about batching or people subscribing in the middle of the post operation - basically:

Assuming @publisherid known, @msg known on SQL Server:

BEGIN TRANSACTION

INSERT INTO msgs (publisherid, msg)
VALUES(@publisherid, @msg)
SET @messageid = SCOPE_IDENTITY()

INSERT INTO msqqueue (recipientid, messageid, status)
SELECT subscriberid, @messageid, 0 -- unread
FROM subscribers
WHERE subscribers.publisherid = @publisherid

COMMIT TRANSACTION

Answer 5

I'd say just let the database take care of things it is well capable of and designed to do. Insert and manage data. Don't try and do it in code, just write the SQL to insert the data all in one go. 10000 rows is a diddle for all real databases.