Insert into select 大量数据 时间,该如何处理

Insert into select 大量数据 时间
Insert into select 大量数据 时间。 这样的插入一次性大概插入60W数据，但时间上不能接受，有没有更快的方法？

SQL code

 INSERT INTO StatusData.dbo.DataMissingReport ( InvestmentId, InvestmentType, ReportTypeId, EffectiveDate, UserId, Status, Action, ExpectedTime, LastUpdate ,FrequencyPattern, MarketHolidayId )          SELECT pfi.InvestmentId        AS InvestmentId,                   pfi.InvestmentType      AS InvestmentType,                   dm.ReportTypeId,                   @l_EffectiveDate        AS EffectiveDate,                   -1                      AS UserId,                   1                       AS Status,                   0                       AS Action,                   CASE WHEN pfi.DelayInDays = 0 THEN @r_WorkingDay1                        WHEN pfi.DelayInDays = 1 THEN @r_WorkingDay2                        WHEN pfi.DelayInDays = 2 THEN @r_WorkingDay3                        WHEN pfi.DelayInDays = 3 THEN @r_WorkingDay4                        WHEN pfi.DelayInDays = 4 THEN @r_WorkingDay5                        WHEN pfi.DelayInDays = 5 THEN @r_WorkingDay6                        WHEN pfi.DelayInDays = 6 THEN @r_WorkingDay7                        WHEN pfi.DelayInDays = 7 THEN @r_WorkingDay8                        WHEN pfi.DelayInDays = 8 THEN @r_WorkingDay9                        WHEN pfi.DelayInDays = 9 THEN @r_WorkingDay10                        ELSE DATEADD( DAY ,pfi.DelayInDays -10 +((@l_WorkdayNoOfWorkingDay10 + pfi.DelayInDays -10)/5)*2, @r_WorkingDay10 )                    END AS ExpectedTime,                   @l_CurrentTime          AS LastUpdate,                   @l_FrequencyPattern     AS FrequencyPattern,                   phm.MarketHolidayId     AS MarketHolidayId              FROM SupportData.dbo.PerformanceFeedInfoHistory pfi WITH (NOLOCK)                   INNER JOIN BasicData.dbo.InvestmentStaticInfo idr WITH (NOLOCK) ON pfi.InvestmentId = idr.GeneralId                                                                                   --AND pfi.InvestmentType = idr.PerformanceType                    INNER JOIN StatusData.dbo.DataPointReportTypeIdMapping dm WITH (NOLOCK) ON dm.DataPointType = pfi.DataPointType                   INNER JOIN BasicData.dbo.DataUnitUniverseSetting dus WITH (NOLOCK) ON dus.DataUnitId = dm.DataUnitId AND idr.Universe = dus.Universe                   INNER JOIN BasicData.dbo.DataUnitDomicileSetting dds WITH (NOLOCK) ON dds.DataUnitId = dm.DataUnitId AND ( dds.Domicile = '*' OR dds.Domicile = idr.CountryId )                   INNER JOIN BasicData.dbo.PerformanceMarketHolidayMapping phm WITH (NOLOCK) ON phm.PerformanceId = idr.PerformanceId                                  INNER JOIN dbo.fn_SplitStringToTable ( @l_MarketHolidayIds,',' ) a ON a.Value = phm.MarketHolidayId             WHERE pfi.FrequencyPattern = @l_FrequencyPattern               --AND idr.DataReadiness = 1               --AND pfi.DataPointType<>23             AND pfi.MarkLatest = 1               AND dm.ReportCategory = 1               AND NOT EXISTS ( SELECT 1 FROM StatusData.dbo.DataMissingReport dt WITH (NOLOCK) WHERE dt.InvestmentId = idr.PerformanceId10Char AND dt.InvestmentType = idr.PerformanceType AND dt.ReportTypeId = dm.ReportTypeId AND dt.EffectiveDate = @p_EffectiveDate)            

[解决办法]
1、在查询的表上建相应的索引以提高查询效率
2、在插入的目标表上先禁用索引，插入完毕后再启用。
或者
将查询出的结果集（60W)数据导入到 MDB 文件中。然后再将MDB中的数据导入目标表中。
利用导入导出工具 调用批量插入（bulkinsert）的方式 提高速度。
[解决办法]

探讨

并且期间还有其他程序运行 delete&insert StatusData.dbo.DataMissingReport 的操作。
这样的程序大概有 20个，但是删的数据量不大。

[解决办法]

探讨

时间不确定， 有时是 30分钟、1个小时左右，有时却是2个小时，
如果delete和insert的程序很多的话这个时间就会久一些。

[解决办法]
将后面查询的SQL上，所有连接的关联字段，建立索引，这样会提高查询的效率
我曾经将一个语句执行时，需要30秒的时间，优化到只要7秒，保持原SQL不变，只优化表

查看更多 下一篇