The following insert query is taking around 7 minutes to handle 4000 rows. The select runs in less than a second. At the end of the run it gets a foreign key constraint error.
I am assuming that the constraint error is what is taking so long. It created thousands of locks on the table. We will fix the constraint error, but is there anything we can do that would improve the time this takes? or a best practices to help with the locks?
Thanks.



INSERT INTO ServiceHistory(
ROID,
VehicleID,
ServiceAdvisorID,
CustomerID,
BatchHistoryID,
ServiceDate,
Mileage,
RONumber)
SELECT DISTINCT RO.ROID,RO.VehicleID,SA.ServiceAdvisorID,
RO.CustomerID,RO.BatchHistoryID,RO.CloseDate,RO.Vehicle_Mileage,
RO.RONumber
FROM ROs RO
JOIN ROLines RL ON RO.ROID = RL.ROID
JOIN ServiceAdvisors SA ON RO.DealerID = SA.DealerID
AND RL.AdvisorNbr = SA.ServiceAdvisorNumber
WHERE RO.Processed = 0

What kind of indexes dop you have on the target tables? Check the fill factor for the indexes.

Also, does the target table have any trigger firing for inserts? If so, check if there is any less-than-optimal processing in there. If the insert trigger updates certain fields, note that this in turn would fire up any update trigger on the same table.

See if it makes any difference if you do it in 4 batches of 1000 rows each.

3 indexes on target table, no triggers, breaking up the query did not help. I did notice the database(this is a development database) was set to grow at 1MB, I changed it to 10%. This did not help.
Also, got rid of the foreign key issue, and we still are very slow. 2 minutes to insert 999 records.

Probably the indexes - fill factor too small, or clustered on multiple columns?

Try clustered on identity column, then nonclustered for PK etc.

insert the select results into a temp table, how long does this take
how big is the table

then insert the temp contents into the final table, how long does this take

check your disk counters during this

make sure there is no database growth during the test, size your files correctly

Recreated the table, everything is running fast now