Insert items from list of TVP via stored procedure only if record doesn’t exists - slow performance

Question

I need call a stored procedure from .NET 4.5 app. As ORM I use Dapper.

Stored procedure accepts TVP parameter and inserts car from list to the table Cars only when car with car_id doesn’t exists.

TABLE:

CREATE TABLE USERS
(
    CAR_ID int IDENTITY PRIMARY KEY,
    CAR_NAME varchar(255),
)

TVP:

CREATE TYPE dbo.CarType
AS TABLE
(
    CAR_ID int null,
    CAR_NAME varchar(800) not null,
);

Stored procedure:

CREATE PROCEDURE dbo.InsertCars
    @Cars AS CarType READONLY
AS
    BEGIN
        DECLARE @CountOfNew int

        INSERT INTO CARS (CAR_ID, CAR_NAME)
        SELECT C.CAR_ID, C.CAR_NAME
        FROM @CARS C
        WHERE NOT EXISTS(SELECT 1 FROM CARS WHERE CAR_ID = C.CAR_ID)

        SET @CountOfNew = @@ROWCOUNT
        RETURN @CountOfNew      
    END

Problem is performance of the stored procedure. If in the table exist more then 30 000 cars, stored procedure call take 20 seconds.

I debug my app, test via SQL Server Management Studio and in my opinion bottleneck is stored procedure.

Any advice how tuneup this stored procedure.

I use SQL Server 2012.

Thank you

Answer 1

Run the SQL Server Profiler and include the Showplan Statistics XML Profile event. Run your query for the large case (>= 30,000 cars) and see what it does.

Depending on your version of SQL Server, you might try a MERGE (if on SQL 2008 or higher), or a LEFT JOIN WHERE Car_ID IS NULL like this:

INSERT INTO CARS (CAR_ID, CARNAME)
SELECT C.CAR_ID, C.CARNAME
FROM @CARS C
LEFT JOIN Cars ON Cars.CAR_ID = C.CAR_ID
WHERE Cars.Car_ID IS NULL -- the car does not already exist

Also, you could try declaring the CarType to have a PRIMARY KEY of CAR_ID.

(I am assuming the TVP CarType key is CAR_ID and not CARID as you wrote. Also, you declared this table as USERS instead of CARS.)