Most Efficient Strategy to Insert Many Records

Question

Thanks for reading this. I'm trying to work out the best strategy to keep my customer table up to date.

I have a customer table plus 6 daily transaction tables that get new data every day. If there is a new customer, they might come in the transaction table so I need to find the customers in the transaction table that aren't in the customer table and add them. This seems right to me but I wanted to get input from others because it seems really inefficient with "select the customer that is not already in the primary table" when the customer table is so big. The data is loaded every day.

Here is my command:

insert into customer (customer, reptdate)  
(select distinct customer, getdate() as reptdate 
 from T_TX_CUST 
 where not customer in (select customer from customer ))

The thing is, as part of the transaction table processing, I go through the tables row wise in C#. I could do a select there to see if the customer existed and then add them if they don't. The programmer in me really wants to do it this way but using SQL would be much simpler but I'm afraid of the cost. Is using a set of SQL commands in a stored procedure faster than doing a select and then an insert on a record by record basis?

I would really appreciate any thoughts or advice.

Answer 1

As a programmer, your time is valuable too. For a daily load process I would start with something as simple as possible and only make it more complicated if you know that you need to improve performance. I don't know your exact requirements, but for a load that happens once per day you probably don't need your code to finish in seconds.

You have six transaction tables, so you can do a small proof of concept for just one table. I'm going to use T_TX_CUST as an example.

1. Load new data from your customer data source into the customer table
2. Load new data for T_TX_CUST into a temporary staging table.
3. Use the staging table to find and insert any missing customers into the customer table.
4. Load new data from staging into T_TX_CUST.

The advantage of this process is that it's simple and it doesn't matter how much data you already have in the T_TX_CUST table. All that matters is how much data you load per day. Try it out, and if it meets your needs then implement it for all of your tables. One trick to speed things up is to run multiple queries concurrently. For example, you may be able to load multiple staging tables at once or to load data into multiple transaction tables at once.