Please let me know optimization tips for this insert query.

Question

1.00/5 (3 votes)

See more:

SQL

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]

Posted 23-May-14 1:14am

sarath Nath

Add a Solution

Comments

Maciej Los 23-May-14 7:16am

What kind of optimization do you expect?
We can't read in your mind...
What's wrong with this query?

sarath Nath 23-May-14 7:19am

my sp contains these type of bunch of insert queries here i wants to make the sp execution faster with replacing the optimized queries.

Sunasara Imdadhusen 23-May-14 7:37am

Need more details what you want exactly in terms of optimization.

ArunRajendra 23-May-14 7:51am

I see lots of fields are NULL. We can restrict insert to only the fields which has data. I am not sure if this will optimize the query.

RDBurmon 24-May-14 2:23am

Does table "AllAssetSurveyReport" have any indexes?

sarath Nath 26-May-14 1:55am

"AllAssetSurveyReport" does not contain any type of indexes..

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mandip Grewal · Answer 1 · 2014-05-27T01:44:00

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

SQL

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]

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.. :)