Edit Question - KnowledgeHub

Title Body

I'm about to have to rewrite some rather old code using SQL Server's BULK INSERT command because the schema has changed, and it occurred to me that maybe I should think about switching to a stored procedure with a TVP instead, but I'm wondering what effect it might have on performance. Some background information that might help explain why I'm asking this question: The data actually comes in via a web service. The web service writes a text file to a shared folder on the database server which in turn performs a BULK INSERT . This process was originally implemented on SQL Server 2000, and at the time there was really no alternative other than chucking a few hundred INSERT statements at the server, which actually was the original process and was a performance disaster. The data is bulk inserted into a permanent staging table and then merged into a much larger table (after which it is deleted from the staging table). The amount of data to insert is "large", but not "huge" - usually a few hundred rows, maybe 5-10k rows tops in rare instances. Therefore my gut feeling is that BULK INSERT being a non-logged operation won't make that big a difference (but of course I'm not sure, hence the question). The insertion is actually part of a much larger pipelined batch process and needs to happen many times in succession; therefore performance is critical. The reasons I would like to replace the BULK INSERT with a TVP are: Writing the text file over NetBIOS is probably already costing some time, and it's pretty gruesome from an architectural perspective. I believe that the staging table can (and should) be eliminated. The main reason it's there is that the inserted data needs to be used for a couple of other updates at the same time of insertion, and it's far co

Tags (comma-separated) Cancel