Inserting new records can cause timeout if someone queries this table?

I'm using Microsoft SQL Database

I have a table with about 1000000 records. Each day I update it with new ones.

so I have two tables

Table1 where all records

and

Table2 where new records. The idea is to insert only the new ones. Because Table1 can contain already some of the records from Table2. Usually about 600K

Here is the query I'm using to update table with the new records.

    INSERT INTO Table1
       (
            Column1, Column2, Column3
       )           

    SELECT  Column1, Column2, Column3                         
    FROM Table2
    LEFT OUTER JOIN Table1
    ON Table1.ColumnID = Table2.ColumnID
    WHERE Table1.ColumnStockID IS NULL

The issue I have during the time executing this query. Most of the que开发者_高级运维ries to table 1, which select more than 1-10 records are giving time out. Even on the server I don't see high CPU.

Is the a problem in my query? Is there any way to make it more efficient? Or other more efficient way to insert new records?

Also the same happens when I'm trying to delete

    DELETE ITEMS
    FROM Table1 ITEMS WITH(NOLOCK)
    LEFT OUTER JOIN Table2 NEWITEMS     
    ON ITEMS.ID = NEWITEMS.ID
    WHERE NEWITEMS.ID IS NULL AND ITEMS.ID IS NOT NULL

---

Try to insert the records in local temporary table and then insert it from local temporary table select statement.

    SELECT  Column1, Column2, Column3                         
    into #temp FROM Table2
    LEFT OUTER JOIN Table1
    ON Table1.ColumnID = Table2.ColumnID
    WHERE Table1.ColumnStockID IS NULL


    INSERT INTO Table1
    (
        Column1, Column2, Column3
    )        
    Select * from #temp

    Drop table #temp  

---

First what are your indexes? FKs do not automatically get indexed.

NOT EXISTS is usually a faster form than the left join form to find records in one table but not the other.

INSERT INTO Table1 (Column1, Column2, Column3)                 
SELECT  Column1, Column2, Column3
FROM Table2   t2 
WHERE NOT EXISTS (SELECT * 
                    FROM Table1 t1 
                    WHERE  t1.ColumnId = t2.ColumnId) 

---

The join to table 1 is probably locking rows. In a table this big, with a large number of joins, SqlServer will quickly escalate the row locks to page locks to table locks.

Try adding "with (nolock) after the select/join from table1. This might keep the locks from blocking other queries.

You need to see if adding (nolock) is right for your situation. It should not be applied without understanding the downsides. It is not a cure all.

---

Why do you need to join Table1 and Table2? I don't think this is required. Joining takes a significant amount of time and hence the timeouts.

If you are using MySql, INSERT IGNORE would take care of unique inserts and you need not join Table1 and Table2.