I have to design a table where records will be inserted in two ways.

  1. Bulk insert from a batch process from a delimited text file between 2 - 3 million rows in a short burst. Later the same records will also need an bulk update for the status. e.g. from 'Pending' to 'Completed'.
  2. Single row insert from a LOB application, via stored procedure, say one thousand rows per day. Some of these records will not require the status update and some will.

Process 1 is already in place (The text file is used as an external table. insert/update via a stored procedure) but we are willing to re-design this as currently we have about 2 million rows but we are expecting major volume growth of say 30 million records per year from the batch plus say 3.5 million from the application. Data for 7 years needs to be stored. Data will be queried using a Id, type and date range (say current year) with pagination.

The preferred DB is Oracle 11G. But I am also open to SQL Server 2012 enterprise edition. In this case process 1 will perhaps be replaced by SSIS.

My questions are:

  1. What are the considerations required for designing a table like this? Should the bulk and single row insert be in different tables with identical structure? I wouldn't like the bulk insert to affect the performance of single row insert and query when happening at the same time.
  2. Is there any significant difference between using Oracle vs SQL Server for this scenario specially for performance?
  3. Is SSIS appropriate to use for process 1 if SQL Server is chosen?

1a) If the two insert methods create the same data then making them one table seems to make the most sense. After a bulk load I would recomend running maintenance scripts to make sure that all indexing is up to date.

1b) The only way to know if the bulk insert and the single insert would effect each other in this specific case would be to test it. With sufficient hardware this would not be an issue.

2) Both SQL Server and Oracle are mature products that can handle what you are describing very well. It sounds like you have a preference for Oracle which should be able to handle what you are attempting.

3) SSIS would be a great solution for what you are describing concerning loading millions of rows into SQL Server.

| improve this answer | |
  • 3
    SQLLDR --> only a few x10 seconds for few million rows. – Munchi Aug 5 '14 at 13:33
  • @Munchi: Agreed, Oracle SQLLoader might be an excellent choice. Got any links with hard numbers on how it compares to bulk inserts? – FrustratedWithFormsDesigner Aug 5 '14 at 15:31
  • @FrustratedWithFormsDesigner I can give you some numbers later today or tomorrow, we do daily loads of 30-100M rows. We don't use SQLLDR due to transform, but I tested it in the past to compare speed. – Munchi Aug 6 '14 at 7:49

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