Which way of inserting large amounts of data into a table is more efficient and/or considered best practice - using INSERT INTO statement in the stored procedure, or using DTS/SSIS data transformation taks? The source data lives on the same SQL server as destination table.
将大量数据插入表中的哪种方式更有效和/或被认为是最佳实践 - 在存储过程中使用INSERT INTO语句,或使用DTS / SSIS数据转换?源数据与目标表位于同一SQL服务器上。
3 个解决方案
#1
1
I would suggest that if your transforms are straightforward; i.e., if they can be easily coded in a couple of SELECT statements, then a simple stored procedure with some SELECT INTO statements is your best option. Very efficient, and fewer moving parts to maintain.
我建议如果你的变换是直截了当的;即,如果它们可以在几个SELECT语句中轻松编码,那么带有一些SELECT INTO语句的简单存储过程是您的最佳选择。非常高效,维护的运动部件更少。
On the other hand, if your transformation logic is very complex and/or changeable, consider SSIS. It exists to allow you to model and maintain complex, multi-step transformations.
另一方面,如果您的转换逻辑非常复杂和/或可更改,请考虑SSIS。它的存在允许您建模和维护复杂的多步骤转换。
Good luck!
#2
1
I think you have to ask yourself a bunch of questions before you can answer this one. Here is what I go through...
我想你必须先问自己一堆问题才能回答这个问题。这是我经历的......
- One time or repeated? This is mostly to determine whether i need to answer the support and performance questions. If it is a one time transfer, just do it whereever you feel most comfortable and archive the source code for WHEN you have to do it again.
- What are the other developers in my shop using/most comfortable with? Here, I do a lot of the SSIS stuff and they do mostly just SQL (and VB). Since I want them to support it when I am away, I will probably build it into an SP if the task isn't too complex.
- Will this run at night when the server load is low or does performance tuning matter? For daytime stuff, I will almost always build the stored procedure, since that is easier for me, or someone else, to dig into and tweak for performance issues. If performance is less of an issue, I am more likely to use SSIS. This is not because I think the INSERT is faster, just that it is easier to tune after the fact.
一次还是重复?这主要是为了确定我是否需要回答支持和性能问题。如果是一次性转移,只需在您感觉最舒适的时候进行,并将源代码归档为何时必须再次进行。
我店内其他开发人员使用/最舒适的是什么?在这里,我做了很多SSIS的东西,他们主要只做SQL(和VB)。因为我希望他们在我离开时支持它,如果任务不是太复杂,我可能会把它建成一个SP。
当服务器负载较低或性能调整是否重要时,这会在晚上运行吗?对于白天的东西,我几乎总是构建存储过程,因为对于我或其他人来说,更容易深入挖掘和调整性能问题。如果性能不是问题,我更有可能使用SSIS。这不是因为我认为INSERT更快,只是事后更容易调整。
#3
0
I always use Bulk Insert. You will need to drop the indexes and at the end rebuild them.
我总是使用批量插入。您将需要删除索引,并在最后重建它们。
http://msdn.microsoft.com/en-us/library/ms188365.aspx
-
ALTER INDEX [indexName] ON Table DISABLE
ALTER INDEX [indexName] ON表DISABLE
Bulk Insert
-
ALTER INDEX [indexName] ON Table REBUILD
ALTER INDEX [indexName] ON表REBUILD
#1
1
I would suggest that if your transforms are straightforward; i.e., if they can be easily coded in a couple of SELECT statements, then a simple stored procedure with some SELECT INTO statements is your best option. Very efficient, and fewer moving parts to maintain.
我建议如果你的变换是直截了当的;即,如果它们可以在几个SELECT语句中轻松编码,那么带有一些SELECT INTO语句的简单存储过程是您的最佳选择。非常高效,维护的运动部件更少。
On the other hand, if your transformation logic is very complex and/or changeable, consider SSIS. It exists to allow you to model and maintain complex, multi-step transformations.
另一方面,如果您的转换逻辑非常复杂和/或可更改,请考虑SSIS。它的存在允许您建模和维护复杂的多步骤转换。
Good luck!
#2
1
I think you have to ask yourself a bunch of questions before you can answer this one. Here is what I go through...
我想你必须先问自己一堆问题才能回答这个问题。这是我经历的......
- One time or repeated? This is mostly to determine whether i need to answer the support and performance questions. If it is a one time transfer, just do it whereever you feel most comfortable and archive the source code for WHEN you have to do it again.
- What are the other developers in my shop using/most comfortable with? Here, I do a lot of the SSIS stuff and they do mostly just SQL (and VB). Since I want them to support it when I am away, I will probably build it into an SP if the task isn't too complex.
- Will this run at night when the server load is low or does performance tuning matter? For daytime stuff, I will almost always build the stored procedure, since that is easier for me, or someone else, to dig into and tweak for performance issues. If performance is less of an issue, I am more likely to use SSIS. This is not because I think the INSERT is faster, just that it is easier to tune after the fact.
一次还是重复?这主要是为了确定我是否需要回答支持和性能问题。如果是一次性转移,只需在您感觉最舒适的时候进行,并将源代码归档为何时必须再次进行。
我店内其他开发人员使用/最舒适的是什么?在这里,我做了很多SSIS的东西,他们主要只做SQL(和VB)。因为我希望他们在我离开时支持它,如果任务不是太复杂,我可能会把它建成一个SP。
当服务器负载较低或性能调整是否重要时,这会在晚上运行吗?对于白天的东西,我几乎总是构建存储过程,因为对于我或其他人来说,更容易深入挖掘和调整性能问题。如果性能不是问题,我更有可能使用SSIS。这不是因为我认为INSERT更快,只是事后更容易调整。
#3
0
I always use Bulk Insert. You will need to drop the indexes and at the end rebuild them.
我总是使用批量插入。您将需要删除索引,并在最后重建它们。
http://msdn.microsoft.com/en-us/library/ms188365.aspx
-
ALTER INDEX [indexName] ON Table DISABLE
ALTER INDEX [indexName] ON表DISABLE
Bulk Insert
-
ALTER INDEX [indexName] ON Table REBUILD
ALTER INDEX [indexName] ON表REBUILD