A couple more questions: corporate database design standards mean that I'm unable to not have a primary key for my table.
Corporate database design standards like that need to be changed because they are too clumsy and ignornat of the technical needs of the solution. It's about as logical to impose such a restriction as it is to say "No table shall have more than 100 records, or 10 columns and if your data storage requirements exceed this vertically, make another table and union it in or horizontally, make another table and join it in"
Hopefully you can see how stupid this is, and could only be dreamt up by someone who thinks "having hundreds of thousands of rows in a table is bad" but has no technical grasp on why their opinion is thus (or wrong!)
Will this significantly affect the performance of the index?
Er.. It's the index that affects the performance of the query.
Youre making a logging table, that will be inserted to thousands of times and queried seldom, probably always in aggregate. That kind of table does not need, and should not have a unique index, because it is a 100% pointless waste of server resources to be able to uniquely identify a particular row, and maintain an N-leaf index for it(where N is the number of rows in the table)
You need to use your coding judgment to make a call on this one; blindly accept the corporate rule, or create the table to service the demands placed on it. Don't take my word for it; go ask other database people whether they would create a unique index (that will probably never be used) on a table that will have an insert:select ratio of thousands to one.
Question 2 is - does your 1000-row-per-second bulk insert come from individual web transactions?
In this context, it makes no difference. It's from a file, read by a windows Service, of millions of customer details every night; as such it would be performing exactly the same as your context. A service (your service is IIS) opens a connection, inserts some data and closes the connection.
In classic insert mode, one record per insert, it gets around 1000 lines/sec. In a performance enhanced insert mode whereby it concatenates 25 rows into one string and passes it to a stored procedure, which then splits the string and performs 25 inserts locally, it does between 6000 and 9000 inserts per second.
Using Oracle's bulk transfer mode, passing an array of 500 records at a time it does 10,000 inserts per second. The slight gain using OBT didnt offset the cost of installing the necessary drivers so it wasnt done. It's a good example of how you can get a performance boost if you find your classic insert mode to be lacking, but I don't think you'll get a similar increase by using your datatable idea, because under the hood it still operates in a broadly similar fashion; you'd need to implement some form of bulk transfer of records but remember also that your data rates are some orders of magnitude lower than discussed here (The cost of implementing a bulk solution may never be repaid in real-world gains)
I'm still trying to get an idea of by how much the web server's performance will be affected by a database insert being part of the request servicing.
Might be an idea to ask Neal, the owner of this forum, roughly how many inserts/updates/deletes/selects it takes to make the forum work. If you want a busier example, ask forums.oracle.com - some forums actually put on the bottom of each page, how many queries and how long they took, usually 5 - 15 and 0.00xxx seconds. You do additionally have the option of implementing your chosen method than stress testing it
The cost of inserting into an indexless table is negligible. The cost of inserting into a table that is maintaining an index is far more significant
