Insert into select 大量数据 时间

sound02 2012-05-28 03:22:06
Insert into select 大量数据 时间。 这样的插入一次性大概插入60W数据，但时间上不能接受，有没有更快的方法？
 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)
...全文
4746 15 打赏 收藏 转发到动态 举报
写回复
用AI写文章
15 条回复
切换为时间正序
请发表友善的回复…
发表回复
财富实验室 2012-05-29
  • 打赏
  • 举报
 回复
先来个中间态。
先把数据个弄出来，再整到你这个对于的表里面去。
可以考虑先放到一个临时的表里面，或者内存表里面。
当然60万，可能数据量太大，可以分多次处理。
内存表，强烈推荐。
sound02 2012-05-29
  • 打赏
  • 举报
 回复
多谢各位！
那个生成Missing 就是存储过程。那只是关键的代码。
gogodiy 2012-05-29
  • 打赏
  • 举报
 回复
方法一：
尽可能优化，比如2楼提到的一些方法。
方法二：
写成存储过程，然后设定为自动作业，放在半夜等空闲时间自动执行。
方法三：
直接使用DTS工具导入。
方法四：
使用ETL工具，比如MSSQL可以使用SSIS导入。
sound02 2012-05-29
  • 打赏
  • 举报
 回复 


SupportData.dbo.PerformanceFeedInfoHistory  16W

BasicData.dbo.InvestmentStaticInfo          10W

StatusData.dbo.DataPointReportTypeIdMapping  <100

BasicData.dbo.DataUnitUniverseSetting        <500

BasicData.dbo.DataUnitDomicileSetting        <100

BasicData.dbo.PerformanceMarketHolidayMapping 100W

StatusData.dbo.DataMissingReport              2000w
sound02 2012-05-29
  • 打赏
  • 举报
 回复 


CREATE PROCEDURE [dbo].[chgDMRForLatestDate]  

   @p_EffectiveDate    SMALLDATETIME,    

   @p_ReportCategory   TINYINT  

AS  

   SET NOCOUNT ON  

     

   DECLARE @l_Err      INT,  

           @l_Msg      VARCHAR(500),  

           @l_Id       NVARCHAR(15),  

           @l_ProcName VARCHAR(30),  

           @l_ProcDB   VARCHAR(30),  

           @i          TINYINT,  

           @r_affectedRows  INT  

     

   -- Initialize error handle-related constants  

   SET @l_Id       = ''  

   SET @l_ProcName = OBJECT_NAME (@@PROCID)  

   SET @l_ProcDB   = DB_NAME()  

     

-- Business logic  

   BEGIN TRY        

     ;WITH latest AS  

     (           

        SELECT Id,ReportTypeId ,MAX(KeyDate) AS LatestDate   

        FROM NotificationHistoryData.dbo.PresenceMissingRecord  nd  WITH(NOLOCK)  

        GROUP BY Id, ReportTypeId        

     )  

      UPDATE dmr  

      SET dmr.LatestDate =latest.LatestDate  

      FROM DataMissingReport dmr    

       INNER JOIN latest  

         ON dmr.InvestmentId=latest.Id AND dmr.ReportTypeId=latest.ReportTypeId  

      WHERE EffectiveDate=@p_EffectiveDate

    SET @r_affectedRows= @@ROWCOUNT

    SELECT @r_affectedRows; 

   END TRY     

   -- Exception handle

   BEGIN CATCH

   

      -- Rollback transaction if needed

      IF @@TRANCOUNT > 0

      BEGIN

         ROLLBACK TRAN

      END

      

      -- Log error message

      SET @l_Msg = 'Number:    ' + CONVERT(VARCHAR, ERROR_NUMBER()) + CHAR(10) +

                   'Line:      ' + CONVERT(VARCHAR, ERROR_LINE()) + CHAR(10) +

                   'Severity:  ' + CONVERT(VARCHAR, ERROR_SEVERITY()) + CHAR(10) +

                   'State:     ' + CONVERT(VARCHAR, ERROR_STATE()) + CHAR(10) +

                   'Procedure: ' + ISNULL(ERROR_PROCEDURE(), '') + CHAR(10) +

                   'Message:   ' + ERROR_MESSAGE()

      

      SET @l_Err = ERROR_NUMBER()

      

      -- Store error and raise error

      EXECUTE dbo.sp_LogError @l_Err, @l_Msg, @l_Id, @l_ProcName, @l_ProcDB

      RAISERROR(@l_Msg, 18, 1)

      RETURN @l_Err

   

   END CATCH

   

   RETURN @@ERROR

GO

这个表就有6000W数据，
haitao 2012-05-29
  • 打赏
  • 举报
 回复
先把待插入记录插入到一个临时表——因为多表join，会比较慢，但不影响别人对最终表的操作
再一次性把临时表插入到最终表——无须join，会比较快，影响的时间就短了
gw6328 2012-05-29
  • 打赏
  • 举报
 回复
应该是查询慢。优化一下查询
可以在上层，会多次插入，如10w条一次
用 select xxx into t from xxx 好像会快一点。
寒寒 2012-05-28
  • 打赏
  • 举报
 回复
将后面查询的SQL上，所有连接的关联字段，建立索引，这样会提高查询的效率
我曾经将一个语句执行时，需要30秒的时间，优化到只要7秒，保持原SQL不变，只优化表
我腫了 2012-05-28
  • 打赏
  • 举报
 回复
你联接的那些表数据量多大?
sound02 2012-05-28
  • 打赏
  • 举报
 回复
项目背景是这样的一个操作。
预测明天会有 100W数据要来，就生成100W的Missing信息。
然后明天数据来了就会删除这100W 数据，
生成Missing 的程序，与 删除Missing 的程序，不是一个。并且删除Missing 的程序有很多个。另外很多用户会在有些Missing信息上加些备注（update)
你可以看出来我的生成Missing的存储过程就上面的代码。删除的都
CREATE PROCEDURE [dbo].[delDataMissingReport]

   @p_InvestmentId    CHAR(10),

   @p_InvestmentType  TINYINT,

   @p_ReportTypeId    TINYINT,

   @p_EffectiveDate   SMALLDATETIME

AS

   SET NOCOUNT ON

   

   DECLARE @l_Err      INT,

           @l_Msg      VARCHAR(500),

           @l_Id       NVARCHAR(15),

           @l_ProcName VARCHAR(30),

           @l_ProcDB   VARCHAR(30)

   

   -- Initialize error handle-related constants

   SET @l_Id       = ''

   SET @l_ProcName = OBJECT_NAME (@@PROCID)

   SET @l_ProcDB   = DB_NAME()

   

   -- Business logic

   BEGIN TRY

   

      DELETE

        FROM dbo.DataMissingReport

       WHERE InvestmentId = @p_InvestmentId

         AND InvestmentType = @p_InvestmentType

         AND ReportTypeId = @p_ReportTypeId

         AND EffectiveDate = @p_EffectiveDate

         

   

   END TRY

   

   -- Exception handle

   BEGIN CATCH

   

      -- Rollback transaction if needed

      IF @@TRANCOUNT > 0

      BEGIN

         ROLLBACK TRAN

      END

      

      -- Log error message

      SET @l_Msg = 'Number:    ' + CONVERT(VARCHAR, ERROR_NUMBER()) + CHAR(10) +

                   'Line:      ' + CONVERT(VARCHAR, ERROR_LINE()) + CHAR(10) +

                   'Severity:  ' + CONVERT(VARCHAR, ERROR_SEVERITY()) + CHAR(10) +

                   'State:     ' + CONVERT(VARCHAR, ERROR_STATE()) + CHAR(10) +

                   'Procedure: ' + ISNULL(ERROR_PROCEDURE(), '') + CHAR(10) +

                   'Message:   ' + ERROR_MESSAGE()

      

      SET @l_Err = ERROR_NUMBER()

      

      -- Store error and raise error

      EXECUTE dbo.sp_LogError @l_Err, @l_Msg, @l_Id, @l_ProcName, @l_ProcDB

      RAISERROR(@l_Msg, 18, 1)

      RETURN @l_Err

   

   END CATCH

   

   RETURN @@ERROR

GO

我感觉这些代码都很简单啊。没什么可优化的空间。
Mr_Nice 2012-05-28
  • 打赏
  • 举报
 回复
[Quote=引用 4 楼 的回复:]

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

LZ这是典型的阻塞状态啊。等待上一个事务提交后，处理后一事务。

如果是固定条件，插入固定的数据，比如历史数据移入移出。可以考虑使用分区表。
如果不是，LZ需要查看具体删除及插入语句的执行效率（索引的使用）情况，来进行优化。比如插入时，禁用索引等。



sound02 2012-05-28
  • 打赏
  • 举报
 回复
时间不确定， 有时是 30分钟、1个小时左右，有时却是2个小时，
如果delete和insert的程序很多的话这个时间就会久一些。
Mr_Nice 2012-05-28
  • 打赏
  • 举报
 回复
[Quote=引用 1 楼 的回复:]

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

LZ同时有delete和insert， 这个后面的事务就需要等待前面的事务处理完成了。
一次性插入60W，还可以啊！
LZ用时多少？

Felixzhaowenzhong 2012-05-28
  • 打赏
  • 举报
 回复
1、在查询的表上建相应的索引以提高查询效率
2、在插入的目标表上先禁用索引，插入完毕后再启用。
或者
将查询出的结果集（60W)数据导入到 MDB 文件中。然后再将MDB中的数据导入目标表中。
利用导入导出工具 调用批量插入（bulkinsert）的方式 提高速度。
sound02 2012-05-28
  • 打赏
  • 举报
 回复
并且期间还有其他程序运行 delete&insert StatusData.dbo.DataMissingReport 的操作。
这样的程序大概有 20个，但是删的数据量不大。
MySQL DML 语言（插入、更新与删除数据）
Re： MySQL DML 语言（插入、更新与删除数据）=======================# DML 数据操作语言 包括动词：INSERT、UPDATE、DELETE 等等 它们分别用于添加、修改和删除表中的行（数据） # 插入、更新与删除数据 MySQL中提供了功能丰富的数据库管理语句，包括有效地向数据库中插入数据的 INSERT 语句，更新数据的UPDATE语句以及当数据不在使用时删除数据的DELETE 语句等。  # 高阶 DML 操作 将查询结果插入到新表（相当于表复制功能） 根据另一张表的条件，更改本表记录 根据另一张表的条件，删除本表记录-------------------------------------------------------------   
使用insert into select from批量增加数据
我们在开发、测试过程中，经常会遇到需要表复制的情况，如将一个table1的数据的部分字段复制到table2中，或者将整个table1复制到table2中，这时候我们就要使用SELECT INTO 和 INSERT INTO SELECT 表复制语句了。...
INSERT INTO SELECT 用法
       INSERT INTO SELECT 语句从一个表复制数据，然后把数据插入到一个已存在的表中。
Oracle insert into select大数据量踩过的坑
insert into table1 select * from table2; commit; 灰度环境机器配置不好，二百多万数据十来分钟没有导完，产生大量归档日志。删除索引约束后可能要好点。大数量有风险，可能会导致归档日志撑爆。 2、...
Insert into select语句把生产服务器炸了,Insert into select非常慢时如何优化
insert into select 的优化方案,当数据量比较大时候可以优化提高效率
疑难问题

22,210

社区成员

121,726

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章