Query optimization - Select Into .. Alter Table ADD CONSTRAINT PRIMARY KEY ([ID] ASC)

  • Shanmuga Raj

    Ten Centuries

    Points: 1178

    I need help to Query optimization.
    Query is part of StoredProcedure which executes for more than one hour.
    I need to optimize the query in such a way the action is done faster.
    StoredProcedure does the below action.
    1. Drop Table
    2. Create Table with data from various joins using "Into Table"
    3.Alter Table Add CONSTRAINT [PK_DimProjectHeader_1] PRIMARY KEY

    I am mentioning the steps followed since the query will have lot of columns and tables..Below are the steps followed with few columns and joins used. 

      If Object_Id ('CAPRIS_Application_DB.DWH.DimProjectHeader') is not null
    Drop table [dwh].[DimProjectHeader]

    SELECT skm.[SID],
         skm.[SalesProjectHeaderGUID]
         ,COALESCE(sph.LongDescription,sph.MediumDescription,sph.ShortDescription,spho.LongDescription
         ,spho.LongDescription,spho.MediumDescription,spho.ShortDescription,N'#N/A') AS SalesProjectHeaderGUIDTextEN
      ,COALESCE(ph.[TrainingType], '') as [TrainingType]
         ,COALESCE(tt.ShortDescription,N'#N/A') AS TrainingTypeTextEN
      ,COALESCE(ph.[LostToCompetitor], '') as [LostToCompetitor]
         ,COALESCE(CASE WHEN ph.salesstagesince > 0 then
                 CONVERT(varchar(10),CONVERT (datetime,convert(char(8),ph.salesstagesince)) , 104)
            ELSE
                 CAST(ph.salesstagesince as char(1))-- '0'
            END ,'') as SalesStageSince
         ,ph.[_AuditModifiedDate]
         
    into [dwh].[DimProjectHeader]

    FROM [cdw].[DimProjectHeaderSKMap] SKM
    left join [cdw].[DimProjectHeader] PH on skm.sid = ph.sid 
    LEFT OUTER JOIN [cdw].[RefOEMText] om ON om.OEM=ph.[OEMLevel1]
    AND om.textID =304
    LEFT OUTER JOIN [cdw].[RefDomainValueText] rfp ON rfp.LowerValue=ph.[ReasonforProductProzessUpgrade]
        and rfp.Domain= 'ZADTEL00004Z'
        and rfp.LanguageKey='E'
    LEFT OUTER JOIN (SELECT DISTINCT SalesProjectHeaderGUID,SalesProjectHeaderNumber FROM cdw.DimProjectHeader) refph
        ON ph.ReferenceNumber = refph.SalesProjectHeaderNumber
         

    GO

    ALTER TABLE [dwh].[DimProjectHeader]
    ADD CONSTRAINT [PK_DimProjectHeader_1] PRIMARY KEY ([SID] ASC)

  • John Mitchell-245523

    SSC Guru

    Points: 148358

    What is your question?  Remember we can't see your screen and we don't know anything about your data.  We'll need the actual execution plan as a minimum here to help you, please.

    John

  • Shanmuga Raj

    Ten Centuries

    Points: 1178

    John Mitchell-245523 - Tuesday, August 22, 2017 2:33 AM

    What is your question?  Remember we can't see your screen and we don't know anything about your data.  We'll need the actual execution plan as a minimum here to help you, please.

    John

    hi John,
    The above query is part of StoredProcedure which executes for more than one hour.
    I need to optimize the query in such a way the action is done faster.
    StoredProcedure does the below action.
    1. Drop Table
    2. Create Table with data from various joins using "Into Table"
    3.Alter Table Add CONSTRAINT [PK_DimProjectHeader_1] PRIMARY KEY

  • DesNorton

    SSC-Insane

    Points: 22810

    Shanmuga Raj - Tuesday, August 22, 2017 2:14 AM

    I need help to Query optimization.
    Query is part of StoredProcedure which executes for more than one hour.
    I need to optimize the query in such a way the action is done faster.
    StoredProcedure does the below action.
    1. Drop Table
    2. Create Table with data from various joins using "Into Table"
    3.Alter Table Add CONSTRAINT [PK_DimProjectHeader_1] PRIMARY KEY

    I am mentioning the steps followed since the query will have lot of columns and tables..Below are the steps followed with few columns and joins used. 

      If Object_Id ('CAPRIS_Application_DB.DWH.DimProjectHeader') is not null
    Drop table [dwh].[DimProjectHeader]

    SELECT skm.[SID],
         skm.[SalesProjectHeaderGUID]
         ,COALESCE(sph.LongDescription,sph.MediumDescription,sph.ShortDescription,spho.LongDescription
         ,spho.LongDescription,spho.MediumDescription,spho.ShortDescription,N'#N/A') AS SalesProjectHeaderGUIDTextEN
      ,COALESCE(ph.[TrainingType], '') as [TrainingType]
         ,COALESCE(tt.ShortDescription,N'#N/A') AS TrainingTypeTextEN
      ,COALESCE(ph.[LostToCompetitor], '') as [LostToCompetitor]
         ,COALESCE(CASE WHEN ph.salesstagesince > 0 then
                 CONVERT(varchar(10),CONVERT (datetime,convert(char(8),ph.salesstagesince)) , 104)
            ELSE
                 CAST(ph.salesstagesince as char(1))-- '0'
            END ,'') as SalesStageSince
         ,ph.[_AuditModifiedDate]
         
    into [dwh].[DimProjectHeader]

    FROM [cdw].[DimProjectHeaderSKMap] SKM
    left join [cdw].[DimProjectHeader] PH on skm.sid = ph.sid 
    LEFT OUTER JOIN [cdw].[RefOEMText] om ON om.OEM=ph.[OEMLevel1]
    AND om.textID =304
    LEFT OUTER JOIN [cdw].[RefDomainValueText] rfp ON rfp.LowerValue=ph.[ReasonforProductProzessUpgrade]
        and rfp.Domain= 'ZADTEL00004Z'
        and rfp.LanguageKey='E'
    LEFT OUTER JOIN (SELECT DISTINCT SalesProjectHeaderGUID,SalesProjectHeaderNumber FROM cdw.DimProjectHeader) refph
        ON ph.ReferenceNumber = refph.SalesProjectHeaderNumber
         

    GO

    ALTER TABLE [dwh].[DimProjectHeader]
    ADD CONSTRAINT [PK_DimProjectHeader_1] PRIMARY KEY ([SID] ASC)

    This query does not appear to be complete

    • Your field [SalesProjectHeaderGUIDTextEN] references aliases [sph] and [spho] which do not exist in the query
    • Your field [TrainingTypeTextEN] references alias [tt] which does not exist in the query

    From a performance perspective, you have 2 LEFT JOINs which do not get used anywhere in the query.  They are just wasting resources while they go on a wild goose chase for data that you don't need/want.

  • Shanmuga Raj

    Ten Centuries

    Points: 1178

    DesNorton - Tuesday, August 22, 2017 3:09 AM

    Shanmuga Raj - Tuesday, August 22, 2017 2:14 AM

    I need help to Query optimization.
    Query is part of StoredProcedure which executes for more than one hour.
    I need to optimize the query in such a way the action is done faster.
    StoredProcedure does the below action.
    1. Drop Table
    2. Create Table with data from various joins using "Into Table"
    3.Alter Table Add CONSTRAINT [PK_DimProjectHeader_1] PRIMARY KEY

    I am mentioning the steps followed since the query will have lot of columns and tables..Below are the steps followed with few columns and joins used. 

      If Object_Id ('CAPRIS_Application_DB.DWH.DimProjectHeader') is not null
    Drop table [dwh].[DimProjectHeader]

    SELECT skm.[SID],
         skm.[SalesProjectHeaderGUID]
         ,COALESCE(sph.LongDescription,sph.MediumDescription,sph.ShortDescription,spho.LongDescription
         ,spho.LongDescription,spho.MediumDescription,spho.ShortDescription,N'#N/A') AS SalesProjectHeaderGUIDTextEN
      ,COALESCE(ph.[TrainingType], '') as [TrainingType]
         ,COALESCE(tt.ShortDescription,N'#N/A') AS TrainingTypeTextEN
      ,COALESCE(ph.[LostToCompetitor], '') as [LostToCompetitor]
         ,COALESCE(CASE WHEN ph.salesstagesince > 0 then
                 CONVERT(varchar(10),CONVERT (datetime,convert(char(8),ph.salesstagesince)) , 104)
            ELSE
                 CAST(ph.salesstagesince as char(1))-- '0'
            END ,'') as SalesStageSince
         ,ph.[_AuditModifiedDate]
         
    into [dwh].[DimProjectHeader]

    FROM [cdw].[DimProjectHeaderSKMap] SKM
    left join [cdw].[DimProjectHeader] PH on skm.sid = ph.sid 
    LEFT OUTER JOIN [cdw].[RefOEMText] om ON om.OEM=ph.[OEMLevel1]
    AND om.textID =304
    LEFT OUTER JOIN [cdw].[RefDomainValueText] rfp ON rfp.LowerValue=ph.[ReasonforProductProzessUpgrade]
        and rfp.Domain= 'ZADTEL00004Z'
        and rfp.LanguageKey='E'
    LEFT OUTER JOIN (SELECT DISTINCT SalesProjectHeaderGUID,SalesProjectHeaderNumber FROM cdw.DimProjectHeader) refph
        ON ph.ReferenceNumber = refph.SalesProjectHeaderNumber
         

    GO

    ALTER TABLE [dwh].[DimProjectHeader]
    ADD CONSTRAINT [PK_DimProjectHeader_1] PRIMARY KEY ([SID] ASC)

    This query does not appear to be complete

    • Your field [SalesProjectHeaderGUIDTextEN] references aliases [sph] and [spho] which do not exist in the query
    • Your field [TrainingTypeTextEN] references alias [tt] which does not exist in the query

    From a performance perspective, you have 2 LEFT JOINs which do not get used anywhere in the query.  They are just wasting resources while they go on a wild goose chase for data that you don't need/want.

    The query is part of my stored procedure.. i have not pasted the part of the query.. i just want to know how to optimize the query

  • Thom A

    SSC Guru

    Points: 98406

    Shanmuga Raj - Tuesday, August 22, 2017 3:17 AM

    The query is part of my stored procedure.. i have not pasted the part of the query.. i just want to know how to optimize the query

    We can't help optimise a query we can't see the data for, or run ourselves. At the very least, like John mentioned, a execution plan is needed. The easiest way to obtain this and upload it would be to do the following:

    1. Open SSMS
    2. Connect to your instance
    3. Open a new Query Window
    4. Click the button to include the Actual Execution plan (Shortcut key Ctrl+M in SSMS 2017). The Icon looks like 3 boxes linked together.
    5. Execute your query/SP.
    6. After it has executed, go to the Execution Plan tab in the Result Pane
    7. Right click an area of white space in the Execution Plan and select "Save Execution Plan As...". If you have multiple Execution plans (due to a multi part statement, you'll need to do this for each plan).
    8. Reply to this topic, and click Add file. Select the files that you saved (".sqlplan") files.

    You may want to obfuscate your query plan, if it contains personal/sensitive information.

    Also, you duplicated this topic in the 2012 forum. Which version of SQL Server are you using?

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • Shanmuga Raj

    Ten Centuries

    Points: 1178

    let me rephase my query ;
    i am using sql server 2012
    I need help to Query optimization. Query is part of StoredProcedure which executes for more than one hour.
    I need to optimize the query in such a way the action is done faster. 
    StoredProcedure does the below action. 1. Drop Table 2. Create Table with data from various joins using "Into Table" 3.
    The below query is part of my stored procedure.. i have not pasted the full of the query which have more left joins and columns for each respective join table..

    If Object_Id ('CAPRIS_Application_DB.DWH.DimProjectHeader') is not null
    Drop table [dwh].[DimProjectHeader]

    SELECT skm.[SID],
      skm.[SalesProjectHeaderGUID]
      ,COALESCE(ph.[TrainingType], '') as [TrainingType]
      ,COALESCE(CASE WHEN ph.salesstagesince > 0 then
         CONVERT(varchar(10),CONVERT (datetime,convert(char(8),ph.salesstagesince)) , 104)
       ELSE
         CAST(ph.salesstagesince as char(1))-- '0'
       END ,'') as SalesStageSince
      ,ph.[_AuditModifiedDate]
     
    INTO [dwh].[DimProjectHeader]

    FROM [cdw].[DimProjectHeaderSKMap] SKM
    left join [cdw].[DimProjectHeader] PH on skm.sid = ph.sid
    LEFT OUTER JOIN [cdw].[RefOEMText] om ON om.OEM=ph.[OEMLevel1]
    AND om.textID =304
    LEFT OUTER JOIN [cdw].[RefDomainValueText] rfp ON rfp.LowerValue=ph.[ReasonforProductProzessUpgrade]
      and rfp.Domain= 'ZADTEL00004Z'
      and rfp.LanguageKey='E'
    LEFT OUTER JOIN (SELECT DISTINCT SalesProjectHeaderGUID,SalesProjectHeaderNumber FROM cdw.DimProjectHeader) refph
      ON ph.ReferenceNumber = refph.SalesProjectHeaderNumber
     
    GO

    ALTER TABLE [dwh].[DimProjectHeader]
    ADD CONSTRAINT [PK_DimProjectHeader_1] PRIMARY KEY ([SID] ASC)

  • Gail Shaw

    SSC Guru

    Points: 1004446

    Please post the full query, its execution plan and the definition of the tables it references and the indexes on those tables.

    We can't tune a query from seeing part of its text only.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • TheSQLGuru

    SSC Guru

    Points: 134017

    Also, did you do any analysis while the code was running for that 1 hour? What if someone had a lock on a table and you simply waited for THAT to complete? What if your query did a bunch of physical IOs and your IO system took 10000 SECONDS to get the IO into RAM?

    Use sp_whoisactive to find the first issue and differential file IO stall analysis for the other. I would add in differential wait stats analysis too. The latter two are where you take a snapshot of those two things, wait for a while (I often use 3 minutes), take another snapshot and diff the two and divide by time. That gives you "something that could be causing a problem right now"/millisecond.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply