请让我知道此插入查询的优化提示。 [英] Please let me know optimization tips for this insert query.

问题描述

insert into [AllAssetSurveyReport]
          SELECT
          null as CRMID,
           null as [TPID]
          ,REPLACE(SRNO,'string;#','') as SRNO
          , 'Yes' as Responded
          , 'Yes' as IsOldReport
          ,Null as [Status]
          ,Null as [Assigned FY]
          ,Null as [Assigned FM]
          ,Null as [Delivered FY]
          ,Null as [Delivered FM]
          ,Null as [Delivery Date]
          ,null as [No of Weeks Since Delivery]
          ,Created as [Responded Date]
          ,null as [Responded FY]
          ,null as [Responded FM]
          ,Null as [Area Name (A13)]
          ,Null as SUBSIDIARY
          ,Null as [Region]
          ,Null as District
          ,Null as [WEG Name]
          ,SUBSTRING(Author, charindex('#', Author)+1,len(Author)) as Author
          ,Null as [Account Manager]
          ,Null as [Sales Desk Agent]
          ,Null as [Utilization Hub]
          ,Null as Segment
          ,null as [Service Level]
          ,null as [Proposal Type]
          ,null as [Solution For Proposal]
          ,null as [Deal Source]
          ,[Pleaserateyour_x00] as [Overall Satisfaction]
          ,[EaseOfUse] as [Ease of Engaging]
          ,Responsiveness as Professionalism
          ,[QualityoftheDeliverable] as [Quality of Document]
          ,null as [Hours with SD]
          ,null as [Hours without SD]
          ,null as [Hours saved]
          ,null as [Most impactful experience]
          ,null as [Improve your experience]
          ,null as [Quad Alignment]
            ,NULL as ATTraining
      ,NULL as txtothers
      ,NULL as BulkUploadReason
      ,NULL as [Requested FY]
      ,NULL AS [Requested FM]
      ,NULL AS [Requested FW]
      ,NULL AS RequestDate
      ,NULL AS SubSegmentName
      ,NULL as [Engage more accounts]
      ,NULL as [[Percentage of Accounts]
      ,NULL as [Like Dislike SD]
      ,Null AS [Stakeholder Hub]
       ,NULL As RequestOrigin
       ,null as Vertical
     ,null as Industry
     ,null as PrimaryContact
     ,null As [Topic for Proposal]
FROM [RFX_Survey]

推荐答案

首先，您应该删除查询中的NULL插入，使其更具可读性，如下所示：

First of all, you should remove the NULL insertions in your query to make it more readable as given below:

INSERT INTO [AllAssetSurveyReport] (SRNO, Responded,IsOldReport, [Responded Date], Author, [Overall Satisfaction], [Ease of Engaging], Professionalism, [Quality of Document])

SELECT
       REPLACE(SRNO,'string;#','') AS SRNO
       ,'Yes' AS Responded
       ,'Yes' AS IsOldReport
       ,Created AS [Responded Date]
       ,SUBSTRING(Author, CHARINDEX('#', Author)+1,len(Author)) AS Author
       ,[Pleaserateyour_x00] AS [Overall Satisfaction]
       ,[EaseOfUse] AS [Ease of Engaging]
       ,Responsiveness AS Professionalism
       ,[QualityoftheDeliverable] AS [Quality of Document]
FROM [RFX_Survey]

其次，你必须确保你没有不必要的索引在表[AllAssetSurveyReport]上定义，因为索引可以降低 BULK INSERTION或UPDATION 的性能。



您可以使用数据库引擎优化顾问工具来更多地优化此查询。要使用它，您必须首先使用 SQL Profiler 创建跟踪日志，然后您可以在跟踪帮助的情况下在数据库引擎优化顾问中检查更多优化。



请查看以下链接，了解有关数据库引擎优化顾问的更多信息



< a href =http://technet.microsoft.com/en-us/library/ms173494(v=sql.105).aspx> http://technet.microsoft.com/en-us/library/ms173494（ v = sql.105）.aspx [ ^ ]



我希望这会对你有所帮助.. :)

Secondly, you have to make sure that you don't have unnecessary indexes defined on table "[AllAssetSurveyReport]" as Indexes can lower the performance in case of BULK INSERTION or UPDATION.

You can use Database Engine Tuning Advisor tool to optimize this query more. For using it, you have to first create a trace log using SQL Profiler and then you can check for more optimizations in Database Engine Tuning Advisor with help of traces.

Please check below link to know more about the Database Engine Tuning Advisor

http://technet.microsoft.com/en-us/library/ms173494(v=sql.105).aspx[^]

I hope this will help you.. :)

这篇关于请让我知道此插入查询的优化提示。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！