SQL Server 2008 INSERT优化 [英] SQL Server 2008 INSERT Optimization

问题描述

我必须向SQL Server数据库插入很多行(超过1.000.000.000).该表具有一个AI ID，两个varchar(80)cols和一个较小的datetime(默认值为GETDATE).最后一个仅用于听觉，但必要.

I've to INSERT a lot of rows (more than 1.000.000.000) to a SQL Server data base. The table has an AI Id, two varchar(80) cols and a smalldatetime with GETDATE as default value. The last one is just for auditory, but necesary.

我想知道最好(最快)的方法来插入行.我一直在阅读有关BULK INSERT的信息.但是，如果可能的话，我想避免这种情况，因为该应用程序不在托管数据库的同一服务器上运行，并且我希望将它们保持与可能的隔离.

I'd like to know the best (fastest) way to INSERT the rows. I've been reading about BULK INSERT. But if posible I'd like to avoid it because the app does not run on the same server where database is hosted and I'd like to keep them as isolated as posible.

谢谢！

迭戈

推荐答案

另一个选项是或者，如果您使用的是.NET，则可以使用我写过关于的性能，您可能会感兴趣，因为我将SqlBulkCopy与通过.NET(使用SqlDataAdapter)将数据批量加载到SQL Server的另一种方式进行了比较.使用SqlBulkCopy加载100,000行的基本示例花费了0.8229s，而使用SqlDataAdapter方法花费了25.0729s.

Alternatively, if you're using .NET you can use the SqlBulkCopy class to bulk insert data. This is something I've blogged about on the performance of, which you may be interested in as I compared SqlBulkCopy vs another way of bulk loading data to SQL Server from .NET (using SqlDataAdapter). Basic example loading 100,000 rows took 0.8229s using SqlBulkCopy vs. 25.0729s using the SqlDataAdapter approach.

这篇关于SQL Server 2008 INSERT优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！