In my scenario, each user can rapidly generate search result hits, about 1 million records per user. Each record is made up of a SetID and a ResultID, setID being tinyint, ResultID being int.
The records are generated by calling a stored procedure, each call using a new setID.
The most cost is observed during insertion and deletion. I found in the end having no indexes at all gives the best performance for what I am doing. Each user activity generates about 100k records and the delete handles about the same amount.
Initially I had a userID on the row as well but found the insert/deletions were far more time consuming the larger the table became. Since there is no scope to ever need to query across Users on this table, one of the ways I found I got better performance was to remove the UserID and create the table dynamically with the UserID forming part of the table name.
However this has led to me using a lot of dynamic SQL to reference the correct tables.
It is not possible to drop the tables and recreate them to avoid deletions altogether since I only ever delete 1 set of data at a time.
Equally temporary tables have offered little advantage since a users search results need to be preserved between stored procedures.
Are there any subject areas worthy of my study to either a) allow me to avoid dynamic SQL but retain my seperate table per user concept or b) which may steer me towards something where performance is likely to be less affected by a heavily populated table with 100k inserts and deletes than some of these past experiences have suggested.
Thanks in advance!