SQL SP creating certificates for shipping

Question

We ship large spools of copper wire and pallets of copper rod that have to meet specific chemical and mechanical requirements, this is what must be put on the certifications. I created an SP which builds and then runs a SQL query. This query is pulling in data from several tables and 2 DBs for creating certificates for shipping. Because I don't choose what goes on the certifications I have to pull all the data, even if it ends up not being used. Then someone else actually decides what they want and that is what gets put on the certs. That is why the SP creates a query dynamically. I have tried to limit the amount of data that has to be pulled.

Right now this is taking between 50 to 110 seconds to run. This is too long as we have a lot of shipments that go out and need to run this a lot. I would like to try getting it down to about 15 seconds if that is possible, but I have run out of ideas on what else I can try.

I have played around with Indexes and have added (and removed) clustered and non-clustered indexes on most of the tables involved. I've reorganized that building of the query and created the #TEMP table at the top to help with running faster, but nothing has made a big enough difference.

Here is the SP query:

@PackingSlipId varchar(25) = '',
@PackSales int = 0, -- 0 = Packingslip, 1 = SalesId
@PrintSQL int = 0
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here

declare @tsql varchar(max)
declare @SalesName varchar(150)

SELECT ProductionId, Max(ID) AS Id INTO #TEMP
FROM sdiProductionChemistry GROUP BY ProductionId 
--Set the SalesName for use in the dynamic sql below
set @SalesName = case when @PackSales = 0 THEN (
        select distinct st.SALESNAME
        FROM InventTrans                        AS IT
        LEFT OUTER JOIN InventTable             AS I    ON IT.ItemId            = I.ItemId
        LEFT OUTER JOIN InventDim               AS ID   ON IT.INVENTDIMID       = ID.InventDimId
        LEFT OUTER JOIN SalesTable              AS ST   ON IT.TransRefId        = ST.SalesId
        LEFT OUTER JOIN SDICustomerSpecs        AS SCS  ON ST.CustAccount       = SCS.CustomerId    AND IT.ItemId = SCS.ItemId
        LEFT OUTER JOIN SDIInventory            AS SI   ON ID.InventBatchId     = SI.BatchId
        LEFT OUTER JOIN SDIProduction           AS P    ON SI.ProductionId      = P.Id
        LEFT OUTER JOIN #Temp                           ON P.Id                 = #Temp.PRODUCTIONID
        LEFT OUTER JOIN SDIPRODUCTIONCHEMISTRY  AS SPC  ON #Temp.PRODUCTIONID   = SPC.ProductionId  AND SPC.Id = #Temp.Id
        WHERE IT.PackingSlipId = @PackingSlipId)
    when @PackSales = 1 then (
        select distinct st.SALESNAME
        FROM InventTrans                        AS IT
        LEFT OUTER JOIN InventTable             AS I    ON IT.ItemId            = I.ItemId
        LEFT OUTER JOIN InventDim               AS ID   ON IT.INVENTDIMID       = ID.InventDimId
        LEFT OUTER JOIN SalesTable              AS ST   ON IT.TransRefId        = ST.SalesId
        LEFT OUTER JOIN SDICustomerSpecs        AS SCS  ON ST.CustAccount       = SCS.CustomerId    AND IT.ItemId = SCS.ItemId
        LEFT OUTER JOIN SDIInventory            AS SI   ON ID.InventBatchId     = SI.BatchId
        LEFT OUTER JOIN SDIProduction           AS P    ON SI.ProductionId      = P.Id
        LEFT OUTER JOIN #Temp                           ON P.Id                 = #Temp.PRODUCTIONID
        LEFT OUTER JOIN SDIPRODUCTIONCHEMISTRY  AS SPC  ON #Temp.PRODUCTIONID   = SPC.ProductionId  AND SPC.Id = #Temp.Id
        WHERE IT.TransRefId = @PackingSlipId) end

set @tsql = '
SELECT DISTINCT P.Id,
    ID.InventBatchId AS CoilId, IT.DatePhysical AS DlvDate,'
        IF (@PackSales) = 1
            SET @tsql = @tsql   +  '''' + @PackingSlipId + ''' AS SalesOrderId, IT.PackingSlipId As PackingSlipId,'
        ELSE
            SET @tsql = @tsql   + 'IT.TransRefId AS SalesOrderId, ''' + @PackingSlipId + ''' As PackingSlipId,'
SET @tsql = @tsql   + ' 
    CASE
        WHEN NOT SI.Diameter IS NULL THEN SI.Diameter
        ELSE xSI.Diameter END AS Diameter,
    SI.Leco, SI.Tensilestrength, 
    CASE WHEN NOT SI.E200 IS NULL AND SI.E200 > 0 THEN convert(varchar,convert(numeric(10,1),SI.E200))
        WHEN NOT xSI.Elongation IS NULL AND xSI.Elongation > 0 THEN convert(varchar,convert(numeric(10,1),xSI.Elongation))
        ELSE ''> 35'' END AS E200, 
    CASE WHEN NOT P.HeatNumber IS NULL THEN P.HeatNumber
        ELSE xSI.BreakDownId END AS HeatNumber, 

    --xSA.Heatnumber as SpectroHeatNumber,

    CASE WHEN NOT SI.NetWeight IS NULL THEN SI.NetWeight
        ELSE xSI.GrossWeight - xSI.TareWeight END AS NetWeight, 
    CASE 
        WHEN SI.CertConductivity = 0 THEN
            SI.IACS_REAL
        WHEN SI.CertConductivity > 0 THEN
            SI.CertConductivity 
    END AS IACS, (SPC.CU + (SPC.AG / 10000)) AS CUAG,
    ST.SalesName, ST.PurchOrderFormNum AS CustomerPO,
    xSI.Grm,
    -- Customer Spec Min/Max Fields
    SCS.CUAGMin, SCS.CUAGMax, SCS.DiameterMin, SCS.DiameterMax, SCS.ElongMin, SCS.ElongMax,
    SCS.StrengthMin, SCS.StrengthMax, SCS.OxygenMin, SCS.OxygenMax, SCS.ConductivityMin, SCS.ConductivityMax,
    SCS.GrmMin, SCS.GrmMax, SCS.PopMin AS OxideMin, SCS.PopMax AS OxideMax,
    SCS.ZnMax, SCS.ZnMin, SCS.PbMax, SCS.PbMin, SCS.SnMax, SCS.SnMin, SCS.PMax, SCS.PMin, SCS.MnMax, SCS.MnMin,
    SCS.FeMax, SCS.FeMin, SCS.NiMax, SCS.NiMin, SCS.SiMax, SCS.SiMin, SCS.MgMax, SCS.MgMin, SCS.CrMax, SCS.CrMin,
    SCS.TeMax, SCS.TeMin, SCS.AsMax, SCS.AsMin, SCS.SeMax, SCS.SeMin, SCS.SbMax, SCS.SbMin, SCS.CdMax, SCS.CdMin,
    SCS.BiMax, SCS.BiMin, SCS.AgMax, SCS.AgMin, SCS.CoMax, SCS.CoMin, SCS.AlMax, SCS.AlMin, SCS.SMax, SCS.SMin,
    SCS.BeMax, SCS.BeMin, SCS.HRFMax, SCS.HRFMin,

    I.ItemName
    -- Element values to show
    ' 
    --/*
if(SELECT ZnShow FROM SDICustomerSpecSheets where CustomerName = @SalesName) = -1561783295
    SET @tsql = @tsql + ', xSA.Zn'
ELSE
    SET @tsql = @tsql + ', 0 Zn'
if(SELECT PbShow FROM SDICustomerSpecSheets where CustomerName = @SalesName) = -1561783295
    SET @tsql = @tsql + ', xSA.Pb'
ELSE
    SET @tsql = @tsql + ', 0 Pb'
if(SELECT SnShow FROM SDICustomerSpecSheets where CustomerName = @SalesName) = -1561783295
    SET @tsql = @tsql + ', xSA.Sn'
ELSE
    SET @tsql = @tsql + ', 0 Sn'
if(SELECT PShow FROM SDICustomerSpecSheets where CustomerName = @SalesName) = -1561783295
    SET @tsql = @tsql + ', xSA.P'
ELSE
    SET @tsql = @tsql + ', 0 P'
if(SELECT MnShow FROM SDICustomerSpecSheets where CustomerName = @SalesName) = -1561783295
    SET @tsql = @tsql + ', xSA.Mn'
ELSE
    SET @tsql = @tsql + ', 0 Mn'
if(SELECT FeShow FROM SDICustomerSpecSheets where CustomerName = @SalesName) = -1561783295
    SET @tsql = @tsql + ', xSA.Fe'
ELSE
    SET @tsql = @tsql + ', 0 Fe'
if(SELECT NiShow FROM SDICustomerSpecSheets where CustomerName = @SalesName) = -1561783295
    SET @tsql = @tsql + ', xSA.Ni'
ELSE
    SET @tsql = @tsql + ', 0 Ni'
if(SELECT SiShow FROM SDICustomerSpecSheets where CustomerName = @SalesName) = -1561783295
    SET @tsql = @tsql + ', xSA.Si'
ELSE
    SET @tsql = @tsql + ', 0 Si'
if(SELECT MgShow FROM SDICustomerSpecSheets where CustomerName = @SalesName) = -1561783295
    SET @tsql = @tsql + ', xSA.Mg'
ELSE
    SET @tsql = @tsql + ', 0 Mg'
if(SELECT CrShow FROM SDICustomerSpecSheets where CustomerName = @SalesName) = -1561783295
    SET @tsql = @tsql + ', xSA.Cr'
ELSE
    SET @tsql = @tsql + ', 0 Cr'
if(SELECT TeShow FROM SDICustomerSpecSheets where CustomerName = @SalesName) = -1561783295
    SET @tsql = @tsql + ', xSA.Te'
ELSE
    SET @tsql = @tsql + ', 0 Te'
if(SELECT AsShow FROM SDICustomerSpecSheets where CustomerName = @SalesName) = -1561783295
    SET @tsql = @tsql + ', xSA.As'
ELSE
    SET @tsql = @tsql + ', 0 [As]'
if(SELECT SeShow FROM SDICustomerSpecSheets where CustomerName = @SalesName) = -1561783295
    SET @tsql = @tsql + ', xSA.Se'
ELSE
    SET @tsql = @tsql + ', 0 Se'
if(SELECT SbShow FROM SDICustomerSpecSheets where CustomerName = @SalesName) = -1561783295
    SET @tsql = @tsql + ', xSA.Sb'
ELSE
    SET @tsql = @tsql + ', 0 Sb'
if(SELECT CdShow FROM SDICustomerSpecSheets where CustomerName = @SalesName) = -1561783295
    SET @tsql = @tsql + ', xSA.Cd'
ELSE
    SET @tsql = @tsql + ', 0 Cd'
if(SELECT BiShow FROM SDICustomerSpecSheets where CustomerName = @SalesName) = -1561783295
    SET @tsql = @tsql + ', xSA.Bi'
ELSE
    SET @tsql = @tsql + ', 0 Bi'
if(SELECT AgShow FROM SDICustomerSpecSheets where CustomerName = @SalesName) = -1561783295
    SET @tsql = @tsql + ', xSA.Ag'
ELSE
    SET @tsql = @tsql + ', 0 Ag'
if(SELECT CoShow FROM SDICustomerSpecSheets where CustomerName = @SalesName) = -1561783295
    SET @tsql = @tsql + ', xSA.Co'
ELSE
    SET @tsql = @tsql + ', 0 Co' 
if(SELECT AlShow FROM SDICustomerSpecSheets where CustomerName = @SalesName) = -1561783295
    SET @tsql = @tsql + ', xSA.Al'
ELSE
    SET @tsql = @tsql + ', 0 Al'
if(SELECT SShow FROM SDICustomerSpecSheets where CustomerName = @SalesName) = -1561783295
    SET @tsql = @tsql + ', xSA.S'
ELSE
    SET @tsql = @tsql + ', 0 S'
if(SELECT BeShow FROM SDICustomerSpecSheets where CustomerName = @SalesName) = -1561783295
    SET @tsql = @tsql + ', xSA.Be'
ELSE
    SET @tsql = @tsql + ', 0 Be'
if(SELECT HRFShow FROM SDICustomerSpecSheets where CustomerName = @SalesName) = -1561783295
    SET @tsql = @tsql + ', 0 HRF'
ELSE
    SET @tsql = @tsql + ', 0 HRF'
if(SELECT OxideShow FROM SDICustomerSpecSheets where CustomerName = @SalesName) = -1561783295
    SET @tsql = @tsql + ',  CASE WHEN SI.POP_EXTERNAL < SI.POP_INTERNAL THEN SI.POP_EXTERNAL
                                WHEN SI.POP_INTERNAL < SI.POP_EXTERNAL THEN SI.POP_INTERNAL
                                ELSE 0 END AS SurfaceOxide'
ELSE
    SET @tsql = @tsql + ', 0 SurfaceOxide'

set @tsql = @tsql + '
-- What to show
, case SCSS.ZnShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as ZnShow
, case SCSS.PbShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as PbShow
, case SCSS.SnShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as SnShow
, case SCSS.PShow               when -1561783295 then 1 when -1561783296 then 0 else 0 end as PShow
, case SCSS.MnShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as MnShow
, case SCSS.FeShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as FeShow
, case SCSS.NiShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as NiShow
, case SCSS.SiShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as SiShow
, case SCSS.MgShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as MgShow
, case SCSS.CrShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as CrShow
, case SCSS.TeShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as TeShow
, case SCSS.AsShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as AsShow
, case SCSS.SeShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as SeShow
, case SCSS.SbShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as SbShow
, case SCSS.CdShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as CdShow
, case SCSS.BiShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as BiShow
, case SCSS.AgShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as AgShow
, case SCSS.CoShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as CoShow
, case SCSS.AlShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as AlShow
, case SCSS.SShow               when -1561783295 then 1 when -1561783296 then 0 else 0 end as SShow
, case SCSS.BeShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as BeShow
, case SCSS.HRFShow             when -1561783295 then 1 when -1561783296 then 0 else 0 end as HRFShow
, case SCSS.OxideShow           when -1561783295 then 1 when -1561783296 then 0 else 0 end as OxideShow
, case SCSS.CuAgShow            when -1561783295 then 1 when -1561783296 then 0 else 0 end as CuAgShow
, case SCSS.DiameterShow        when -1561783295 then 1 when -1561783296 then 0 else 0 end as DiameterShow
, case SCSS.ElongationShow      when -1561783295 then 1 when -1561783296 then 0 else 0 end as ElongationShow
, case SCSS.StrengthShow        when -1561783295 then 1 when -1561783296 then 0 else 0 end as StrengthShow
, case SCSS.OxygenShow          when -1561783295 then 1 when -1561783296 then 0 else 0 end as OxygenShow
, case SCSS.ConductivityShow    when -1561783295 then 1 when -1561783296 then 0 else 0 end as ConductivityShow
, case SCSS.GRMShow             when -1561783295 then 1 when -1561783296 then 0 else 0 end as GRMShow'

    --*/
    set @tsql = @tsql + '
FROM InventTrans AS IT
LEFT OUTER JOIN InventTable                             AS I    ON IT.ItemId            = I.ItemId
LEFT OUTER JOIN InventDim                               AS ID   ON IT.INVENTDIMID       = ID.InventDimId
LEFT OUTER JOIN SalesTable                              AS ST   ON IT.TransRefId        = ST.SalesId
LEFT OUTER JOIN SDICustomerSpecs                        AS SCS  ON ST.CustAccount       = SCS.CustomerId AND IT.ItemId = SCS.ItemId
LEFT OUTER JOIN SDIInventory                            AS SI   ON ID.InventBatchId     = SI.BatchId
LEFT OUTER JOIN SDICustomerSpecSheets                   AS SCSS ON SCSS.CustomerName    = ST.SalesName
LEFT OUTER JOIN LAFARGA.LaFargaProd.dbo.BreakdownItem   AS xSI  ON ID.InventBatchId     = xSI.BatchId
LEFT OUTER JOIN SDIProduction                           AS P    ON SI.ProductionId      = P.Id
LEFT OUTER JOIN #Temp                                   AS T    ON P.Id                 = T.PRODUCTIONID
LEFT OUTER JOIN SDIPRODUCTIONCHEMISTRY                  AS SPC  ON T.PRODUCTIONID   = SPC.ProductionId AND SPC.Id = T.Id
LEFT OUTER JOIN LAFARGA.LaFargaProd.dbo.vSpectroAssays  AS xSA  ON xSA.BatchID          = ID.InventBatchId
WHERE '
IF (@PackSales) = 1
    SET @tsql = @tsql   + 'IT.TransRefId = ''' + @PackingSlipId + ''''
ELSE
    SET @tsql = @tsql   + 'IT.PackingSlipId = ''' + @PackingSlipId  + ''''

SET @tsql = @tsql   + '
ORDER BY ID.InventBatchId'

IF (@PrintSQL = 1)
BEGIN 
    print @tsql
END
IF (@PrintSQL = 0)
BEGIN
    execute (@tsql)
END

Drop Table #Temp
END

Then here is what this creates and runs at the end (or prints if I'm testing):

SELECT DISTINCT P.Id,
    ID.InventBatchId AS CoilId, IT.DatePhysical AS DlvDate,IT.TransRefId AS SalesOrderId, 'RPS115898' As PackingSlipId, 
    CASE
        WHEN NOT SI.Diameter IS NULL THEN SI.Diameter
        ELSE xSI.Diameter END AS Diameter,
    SI.Leco, SI.Tensilestrength, 
    CASE WHEN NOT SI.E200 IS NULL AND SI.E200 > 0 THEN convert(varchar,convert(numeric(10,1),SI.E200))
        WHEN NOT xSI.Elongation IS NULL AND xSI.Elongation > 0 THEN convert(varchar,convert(numeric(10,1),xSI.Elongation))
        ELSE '> 35' END AS E200, 
    CASE WHEN NOT P.HeatNumber IS NULL THEN P.HeatNumber
        ELSE xSI.BreakDownId END AS HeatNumber, 

    --xSA.Heatnumber as SpectroHeatNumber,

    CASE WHEN NOT SI.NetWeight IS NULL THEN SI.NetWeight
        ELSE xSI.GrossWeight - xSI.TareWeight END AS NetWeight, 
    CASE 
        WHEN SI.CertConductivity = 0 THEN
            SI.IACS_REAL
        WHEN SI.CertConductivity > 0 THEN
            SI.CertConductivity 
    END AS IACS, (SPC.CU + (SPC.AG / 10000)) AS CUAG,
    ST.SalesName, ST.PurchOrderFormNum AS CustomerPO,
    xSI.Grm,
    -- Customer Spec Min/Max Fields
    SCS.CUAGMin, SCS.CUAGMax, SCS.DiameterMin, SCS.DiameterMax, SCS.ElongMin, SCS.ElongMax,
    SCS.StrengthMin, SCS.StrengthMax, SCS.OxygenMin, SCS.OxygenMax, SCS.ConductivityMin, SCS.ConductivityMax,
    SCS.GrmMin, SCS.GrmMax, SCS.PopMin AS OxideMin, SCS.PopMax AS OxideMax,
    SCS.ZnMax, SCS.ZnMin, SCS.PbMax, SCS.PbMin, SCS.SnMax, SCS.SnMin, SCS.PMax, SCS.PMin, SCS.MnMax, SCS.MnMin,
    SCS.FeMax, SCS.FeMin, SCS.NiMax, SCS.NiMin, SCS.SiMax, SCS.SiMin, SCS.MgMax, SCS.MgMin, SCS.CrMax, SCS.CrMin,
    SCS.TeMax, SCS.TeMin, SCS.AsMax, SCS.AsMin, SCS.SeMax, SCS.SeMin, SCS.SbMax, SCS.SbMin, SCS.CdMax, SCS.CdMin,
    SCS.BiMax, SCS.BiMin, SCS.AgMax, SCS.AgMin, SCS.CoMax, SCS.CoMin, SCS.AlMax, SCS.AlMin, SCS.SMax, SCS.SMin,
    SCS.BeMax, SCS.BeMin, SCS.HRFMax, SCS.HRFMin,

    I.ItemName
    -- Element values to show
    , 0 Zn, xSA.Pb, 0 Sn, 0 P, 0 Mn, 0 Fe, 0 Ni, 0 Si, 0 Mg, 0 Cr, 0 Te, 0 [As], 0 Se, 0 Sb, 0 Cd, 0 Bi, 0 Ag, 0 Co, 0 Al, 0 S, 0 Be, 0 HRF, 0 SurfaceOxide
-- What to show
, case SCSS.ZnShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as ZnShow
, case SCSS.PbShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as PbShow
, case SCSS.SnShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as SnShow
, case SCSS.PShow               when -1561783295 then 1 when -1561783296 then 0 else 0 end as PShow
, case SCSS.MnShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as MnShow
, case SCSS.FeShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as FeShow
, case SCSS.NiShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as NiShow
, case SCSS.SiShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as SiShow
, case SCSS.MgShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as MgShow
, case SCSS.CrShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as CrShow
, case SCSS.TeShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as TeShow
, case SCSS.AsShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as AsShow
, case SCSS.SeShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as SeShow
, case SCSS.SbShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as SbShow
, case SCSS.CdShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as CdShow
, case SCSS.BiShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as BiShow
, case SCSS.AgShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as AgShow
, case SCSS.CoShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as CoShow
, case SCSS.AlShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as AlShow
, case SCSS.SShow               when -1561783295 then 1 when -1561783296 then 0 else 0 end as SShow
, case SCSS.BeShow              when -1561783295 then 1 when -1561783296 then 0 else 0 end as BeShow
, case SCSS.HRFShow             when -1561783295 then 1 when -1561783296 then 0 else 0 end as HRFShow
, case SCSS.OxideShow           when -1561783295 then 1 when -1561783296 then 0 else 0 end as OxideShow
, case SCSS.CuAgShow            when -1561783295 then 1 when -1561783296 then 0 else 0 end as CuAgShow
, case SCSS.DiameterShow        when -1561783295 then 1 when -1561783296 then 0 else 0 end as DiameterShow
, case SCSS.ElongationShow      when -1561783295 then 1 when -1561783296 then 0 else 0 end as ElongationShow
, case SCSS.StrengthShow        when -1561783295 then 1 when -1561783296 then 0 else 0 end as StrengthShow
, case SCSS.OxygenShow          when -1561783295 then 1 when -1561783296 then 0 else 0 end as OxygenShow
, case SCSS.ConductivityShow    when -1561783295 then 1 when -1561783296 then 0 else 0 end as ConductivityShow
, case SCSS.GRMShow             when -1561783295 then 1 when -1561783296 then 0 else 0 end as GRMShow
FROM InventTrans AS IT
LEFT OUTER JOIN InventTable                             AS I    ON IT.ItemId            = I.ItemId
LEFT OUTER JOIN InventDim                               AS ID   ON IT.INVENTDIMID       = ID.InventDimId
LEFT OUTER JOIN SalesTable                              AS ST   ON IT.TransRefId        = ST.SalesId
LEFT OUTER JOIN SDICustomerSpecs                        AS SCS  ON ST.CustAccount       = SCS.CustomerId AND IT.ItemId = SCS.ItemId
LEFT OUTER JOIN SDIInventory                            AS SI   ON ID.InventBatchId     = SI.BatchId
LEFT OUTER JOIN SDICustomerSpecSheets                   AS SCSS ON SCSS.CustomerName    = ST.SalesName
LEFT OUTER JOIN LAFARGA.LaFargaProd.dbo.BreakdownItem   AS xSI  ON ID.InventBatchId     = xSI.BatchId
LEFT OUTER JOIN SDIProduction                           AS P    ON SI.ProductionId      = P.Id
LEFT OUTER JOIN #Temp                                   AS T    ON P.Id                 = T.PRODUCTIONID
LEFT OUTER JOIN SDIPRODUCTIONCHEMISTRY                  AS SPC  ON T.PRODUCTIONID   = SPC.ProductionId AND SPC.Id = T.Id
LEFT OUTER JOIN LAFARGA.LaFargaProd.dbo.vSpectroAssays  AS xSA  ON xSA.BatchID          = ID.InventBatchId
WHERE IT.PackingSlipId = 'RPS115898'
ORDER BY ID.InventBatchId

Any help with making these queries run faster would be appreciated. Here is a link to the query execution plan if that will help: Execution Plan

UPDATE

Per the request below here is the vSpectroAssays view:

SELECT DISTINCT sa.ID
    , SUBSTRING(sa.SampleName, CHARINDEX('as cast', sa.SampleName) - 5, CHARINDEX('as cast', sa.SampleName) - (CHARINDEX('as cast', sa.SampleName) - 5)) AS HeatNumber
    , bdi.BreakdownId
    , bdi.BatchId
    , bdi.BDHeatNumber
    , sa.DATE
    , sa.Zn
    , sa.Pb
    , sa.Sn
    , sa.P
    , sa.Mn
    , sa.Fe
    , sa.Ni
    , sa.Si
    , sa.Mg
    , sa.Cr
    , sa.Te
    , sa.[As]
    , sa.Se
    , sa.Sb
    , sa.Cd
    , sa.Bi
    , sa.Ag
    , sa.Co
    , sa.Al
    , sa.S
    , sa.Be
    , sa.TestType
    , sa.SampleName
    , bdi.HRF
FROM dbo.SpectroAssays AS sa
LEFT JOIN (
    SELECT OrderId
        , BatchId
        , GrossWeight
        , NetWeight
        , TareWeight
        , PeakLoad
        , Resistance
        , Diameter
        , GRM
        , Elongation
        , PassFail
        , CoilId
        , CoilId2
        , Spec
        , Customer
        , CreateDate
        , BreakdownId
        , Line
        , HRF
        , CASE WHEN SUBSTRING(CoilId, 1, 6) = 'A-Null' THEN '' WHEN CoilId = 'A-000' THEN '' WHEN SUBSTRING(CoilId, 2, 2) = '--' THEN '' WHEN SUBSTRING(CoilId, 1, 2) = 'A-' THEN CONVERT(INT, SUBSTRING(CoilId, 3, (CHARINDEX('-', CoilId, (CHARINDEX('-', CoilId) + 1))) - (CHARINDEX('-', CoilId) + 1))) ELSE '' END AS BDHeatNumber
    FROM dbo.BreakdownItem
    ) AS bdi ON bdi.BDHeatNumber = SUBSTRING(sa.SampleName, CHARINDEX('as cast', sa.SampleName) - 5, CHARINDEX('as cast', sa.SampleName) - (CHARINDEX('as cast', sa.SampleName) - 5))
WHERE (sa.TestType = 'CU-10')
    AND (sa.SampleName LIKE '%as cast%')

Answer 1

The following are a few suggestions on how I'd write the stored procedure.

Source Control

If you don't already have a database project, create one in Visual Studio. Then check it in to source control. Microsoft Azure DevOps Services is free & private for teams of 5 or less (this is per project, so 5 developers per project). Then you'll be able to track changes you make to your stored procedures, views, tables, etc.

Formatting

I would download the following tool for SSMS and Visual Studio, Poor Man's T-Sql Formatter and on GitHub. I use it when I have to edit other developer's code. It's a great way to standardize your SQL. I find it does most of the formatting for me, but I'll still make a few changes after.

Here are the settings I used:

Copy & Paste

If you find yourself copying and pasting the same string or number over and over in your query, then you should define it as a variable. Copy and paste is a design error ~ David Parnas

e.g. ...when -1561783295 then 1 when -1561783296...

Commas

I would put the commas in front to clearly define new columns. Versus code wrapped in multiple lines. It also makes trouble-shooting code easier. The only exception in your procedure is that you may want to show the Min and Max for the same Customer Spec field on the same line to edit it a bit easier.

e.g. , SCS.[MnMax], SCS.[MnMin]

Where Clause

If you put 1=1 at the top of a WHERE condition, it enables you to freely change the rest of the conditions when debugging a query. The SQL query engine will end up ignoring the 1=1 so it should have no performance impact. Reference

Common Table Expressions (CTE)

CTE's in your SQL help with documentation. The expression name can then let other developers know why you used that expression e.g. current_suppliers or active_projects.

Schema Names

Always reference the schema when selecting an object e.g. [dbo].[SalesTable].

Estimated Execution Plan

It's a good idea to check the Estimated Execution Plan. The shortcut in Microsoft SQL Server Management Studio (SSMS) is Ctrl + L. You can even run 2 queries in the same tab to compare the plans.

Keywords

Avoid using keywords as object names. Microsoft Reference

• Also check out the book Clean Code. It will change the way you think about naming conventions.

---

Revised SQL

Without table definitions and sample records I was unable to test this, but it should give you a good start.

CREATE PROCEDURE [dbo].[Create_Certificate] 
(
      @PackingSlipId VARCHAR(25) = '' -- I would default this to NULL; Also, I would rename it since it references more than one column. e.g. @ReferenceId or @Id
    , @PackSales INT = 0  -- A better naming convention would be @CertificateType and pass in the values either 'Packing' or 'Sales'. Then it becomes self documenting.
)
AS
BEGIN

    BEGIN TRY

        SET NOCOUNT ON; --Stops the message that shows the count of the number of rows affected

        DECLARE @ShowTrue AS INT = -1561783295; --I'm guessing at what column definition is, so you might need to change the data type
        DECLARE @ShowFalse AS INT = -1561783296;

        WITH
        max_sdiProductionChemistry
        AS
        (
            SELECT 
                  [ProductionId]
                , [Id] = MAX([Id])
            FROM 
                [dbo].[sdiProductionChemistry] --I'm guessing at the schema name here
            GROUP BY 
                [ProductionId]
        )
        SELECT DISTINCT
              P.[Id]
            , [CoilId] = ID.[InventBatchId]
            , [DlvDate] = IT.[DatePhysical]
            , [SalesOrderId] =  CASE WHEN @PackSales = 1 THEN @PackingSlipId ELSE IT.[TransRefId] END 
            , [PackingSlipId] = CASE WHEN @PackSales = 1 THEN IT.[PackingSlipId] ELSE @PackingSlipId END 
            , [Diameter] = CASE WHEN NOT SI.[Diameter] IS NULL THEN SI.[Diameter] ELSE xSI.[Diameter] END  
            , SI.[Leco]
            , SI.[Tensilestrength]
            , [E200] = 
                CASE 
                    WHEN NOT SI.[E200] IS NULL AND SI.[E200] > 0 THEN CONVERT(VARCHAR, CONVERT(NUMERIC(10, 1), SI.[E200]))
                    WHEN NOT xSI.[Elongation] IS NULL AND xSI.[Elongation] > 0 THEN CONVERT(VARCHAR, CONVERT(NUMERIC(10,1), xSI.[Elongation]))
                    ELSE '> 35'
                END
            , [HeatNumber] = CASE WHEN NOT P.[HeatNumber] IS NULL THEN P.[HeatNumber] ELSE xSI.[BreakDownId] END  
            --, [SpectroHeatNumber] = xSA.[Heatnumber]
            , [NetWeight] = CASE WHEN NOT SI.[NetWeight] IS NULL THEN SI.[NetWeight] ELSE xSI.[GrossWeight] - xSI.[TareWeight] END 
            , [IACS] = CASE WHEN SI.[CertConductivity] = 0 THEN SI.[IACS_REAL] WHEN SI.[CertConductivity] > 0 THEN SI.[CertConductivity] END 
            , [CUAG] = (SPC.[CU] + (SPC.[AG] / 10000))  
            , ST.[SalesName]
            , [CustomerPO] = ST.[PurchOrderFormNum]
            , xSI.[Grm]
            -- Customer Spec Min/Max Fields
            , SCS.[CUAGMin] 
            , SCS.[CUAGMax]
            , SCS.[DiameterMin]
            , SCS.[DiameterMax]
            , SCS.[ElongMin]
            , SCS.[ElongMax]
            , SCS.[StrengthMin]
            , SCS.[StrengthMax]
            , SCS.[OxygenMin]
            , SCS.[OxygenMax]
            , SCS.[ConductivityMin]
            , SCS.[ConductivityMax]
            , SCS.[GrmMin]
            , SCS.[GrmMax]
            , [OxideMin] = SCS.[PopMin]
            , [OxideMax] = SCS.[PopMax]
            , SCS.[ZnMax], SCS.[ZnMin]
            , SCS.[PbMax], SCS.[PbMin]
            , SCS.[SnMax], SCS.[SnMin]
            , SCS.[PMax], SCS.[PMin]
            , SCS.[MnMax], SCS.[MnMin]
            , SCS.[FeMax], SCS.[FeMin]
            , SCS.[NiMax], SCS.[NiMin]
            , SCS.[SiMax], SCS.[SiMin]
            , SCS.[MgMax], SCS.[MgMin]
            , SCS.[CrMax], SCS.[CrMin]
            , SCS.[TeMax], SCS.[TeMin]
            , SCS.[AsMax], SCS.[AsMin]
            , SCS.[SeMax], SCS.[SeMin]
            , SCS.[SbMax], SCS.[SbMin]
            , SCS.[CdMax], SCS.[CdMin]
            , SCS.[BiMax], SCS.[BiMin]
            , SCS.[AgMax], SCS.[AgMin]
            , SCS.[CoMax], SCS.[CoMin]
            , SCS.[AlMax], SCS.[AlMin]
            , SCS.[SMax], SCS.[SMin]
            , SCS.[BeMax], SCS.[BeMin]
            , SCS.[HRFMax], SCS.[HRFMin]
            , I.[ItemName]
            -- Element values to show
            , [Zn] = CASE WHEN SCSS.[ZnShow] = @ShowTrue THEN xSA.[Zn] ELSE 0 END
            , [Pb] = CASE WHEN SCSS.[PbShow] = @ShowTrue THEN xSA.[Pb] ELSE 0 END
            , [Sn] = CASE WHEN SCSS.[SnShow] = @ShowTrue THEN xSA.[Sn] ELSE 0 END
            , [P] = CASE WHEN SCSS.[PShow] = @ShowTrue THEN xSA.[P] ELSE 0 END
            , [Mn] = CASE WHEN SCSS.[MnShow] = @ShowTrue THEN xSA.[Mn] ELSE 0 END
            , [Fe] = CASE WHEN SCSS.[FeShow] = @ShowTrue THEN xSA.[Fe] ELSE 0 END
            , [Ni] = CASE WHEN SCSS.[NiShow] = @ShowTrue THEN xSA.[Ni] ELSE 0 END
            , [Si] = CASE WHEN SCSS.[SiShow] = @ShowTrue THEN xSA.[Si] ELSE 0 END
            , [Mg] = CASE WHEN SCSS.[MgShow] = @ShowTrue THEN xSA.[Mg] ELSE 0 END
            , [Cr] = CASE WHEN SCSS.[CrShow] = @ShowTrue THEN xSA.[Cr] ELSE 0 END
            , [Te] = CASE WHEN SCSS.[TeShow] = @ShowTrue THEN xSA.[Te] ELSE 0 END
            , [As] = CASE WHEN SCSS.[AsShow] = @ShowTrue THEN xSA.[As] ELSE 0 END
            , [Se] = CASE WHEN SCSS.[SeShow] = @ShowTrue THEN xSA.[Se] ELSE 0 END
            , [Sb] = CASE WHEN SCSS.[SbShow] = @ShowTrue THEN xSA.[Sb] ELSE 0 END
            , [Cd] = CASE WHEN SCSS.[CdShow] = @ShowTrue THEN xSA.[Cd] ELSE 0 END
            , [Bi] = CASE WHEN SCSS.[BiShow] = @ShowTrue THEN xSA.[Bi] ELSE 0 END
            , [Ag] = CASE WHEN SCSS.[AgShow] = @ShowTrue THEN xSA.[Ag] ELSE 0 END
            , [Co] = CASE WHEN SCSS.[CoShow] = @ShowTrue THEN xSA.[Co] ELSE 0 END
            , [Al] = CASE WHEN SCSS.[AlShow] = @ShowTrue THEN xSA.[Al] ELSE 0 END
            , [S] = CASE WHEN SCSS.[SShow] = @ShowTrue THEN xSA.[S] ELSE 0 END
            , [Be] = CASE WHEN SCSS.[BeShow] = @ShowTrue THEN xSA.[Be] ELSE 0 END
            , [HRF] = CASE WHEN SCSS.[HRFShow] = @ShowTrue THEN 0 ELSE 0 END
            , [SurfaceOxide] = CASE WHEN SCSS.[OxideShow] = @ShowTrue THEN 
                                    CASE 
                                        WHEN SI.[POP_EXTERNAL] < SI.[POP_INTERNAL] THEN SI.[POP_EXTERNAL]
                                        WHEN SI.[POP_INTERNAL] < SI.[POP_EXTERNAL] THEN SI.[POP_INTERNAL]
                                        ELSE 0 
                                    END
                                ELSE 0 END
            -- What to show
            , [ZnShow] = CASE SCSS.[ZnShow] WHEN @ShowTrue THEN 1 WHEN @ShowFalse THEN 0 ELSE 0 END 
            , [PbShow] = CASE SCSS.[PbShow] WHEN @ShowTrue THEN 1 WHEN @ShowFalse THEN 0 ELSE 0 END 
            , [SnShow] = CASE SCSS.[SnShow] WHEN @ShowTrue THEN 1 WHEN @ShowFalse THEN 0 ELSE 0 END 
            , [PShow] = CASE SCSS.[PShow] WHEN @ShowTrue THEN 1 WHEN @ShowFalse THEN 0 ELSE 0 END 
            , [MnShow] = CASE SCSS.[MnShow] WHEN @ShowTrue THEN 1 WHEN @ShowFalse THEN 0 ELSE 0 END 
            , [FeShow] = CASE SCSS.[FeShow] WHEN @ShowTrue THEN 1 WHEN @ShowFalse THEN 0 ELSE 0 END 
            , [NiShow] = CASE SCSS.[NiShow] WHEN @ShowTrue THEN 1 WHEN @ShowFalse THEN 0 ELSE 0 END 
            , [SiShow] = CASE SCSS.[SiShow] WHEN @ShowTrue THEN 1 WHEN @ShowFalse THEN 0 ELSE 0 END 
            , [MgShow] = CASE SCSS.[MgShow] WHEN @ShowTrue THEN 1 WHEN @ShowFalse THEN 0 ELSE 0 END 
            , [CrShow] = CASE SCSS.[CrShow] WHEN @ShowTrue THEN 1 WHEN @ShowFalse THEN 0 ELSE 0 END
            , [TeShow] = CASE SCSS.[TeShow] WHEN @ShowTrue THEN 1 WHEN @ShowFalse THEN 0 ELSE 0 END
            , [AsShow] = CASE SCSS.[AsShow] WHEN @ShowTrue THEN 1 WHEN @ShowFalse THEN 0 ELSE 0 END
            , [SeShow] = CASE SCSS.[SeShow] WHEN @ShowTrue THEN 1 WHEN @ShowFalse THEN 0 ELSE 0 END
            , [SbShow] = CASE SCSS.[SbShow] WHEN @ShowTrue THEN 1 WHEN @ShowFalse THEN 0 ELSE 0 END 
            , [CdShow] = CASE SCSS.[CdShow] WHEN @ShowTrue THEN 1 WHEN @ShowFalse THEN 0 ELSE 0 END
            , [BiShow] = CASE SCSS.[BiShow] WHEN @ShowTrue THEN 1 WHEN @ShowFalse THEN 0 ELSE 0 END 
            , [AgShow] = CASE SCSS.[AgShow] WHEN @ShowTrue THEN 1 WHEN @ShowFalse THEN 0 ELSE 0 END 
            , [CoShow] = CASE SCSS.[CoShow] WHEN @ShowTrue THEN 1 WHEN @ShowFalse THEN 0 ELSE 0 END 
            , [AlShow] = CASE SCSS.[AlShow] WHEN @ShowTrue THEN 1 WHEN @ShowFalse THEN 0 ELSE 0 END
            , [SShow] = CASE SCSS.[SShow] WHEN @ShowTrue THEN 1 WHEN @ShowFalse THEN 0 ELSE 0 END
            , [BeShow] = CASE SCSS.[BeShow] WHEN @ShowTrue THEN 1 WHEN @ShowFalse THEN 0 ELSE 0 END 
            , [HRFShow] = CASE SCSS.[HRFShow] WHEN @ShowTrue THEN 1 WHEN @ShowFalse THEN 0 ELSE 0 END
            , [OxideShow] = CASE SCSS.[OxideShow] WHEN @ShowTrue THEN 1 WHEN @ShowFalse THEN 0 ELSE 0 END 
            , [CuAgShow] = CASE SCSS.[CuAgShow] WHEN @ShowTrue THEN 1 WHEN @ShowFalse THEN 0 ELSE 0 END 
            , [DiameterShow] = CASE SCSS.[DiameterShow] WHEN @ShowTrue THEN 1 WHEN @ShowFalse THEN 0 ELSE 0 END
            , [ElongationShow] = CASE SCSS.[ElongationShow] WHEN @ShowTrue THEN 1 WHEN @ShowFalse THEN 0 ELSE 0 END
            , [StrengthShow] = CASE SCSS.[StrengthShow] WHEN @ShowTrue THEN 1 WHEN @ShowFalse THEN 0 ELSE 0 END
            , [OxygenShow] = CASE SCSS.[OxygenShow] WHEN @ShowTrue THEN 1 WHEN @ShowFalse THEN 0 ELSE 0 END
            , [ConductivityShow] = CASE SCSS.[ConductivityShow]WHEN @ShowTrue THEN 1 WHEN @ShowFalse THEN 0 ELSE 0 END
            , [GRMShow] = CASE SCSS.[GRMShow] WHEN @ShowTrue THEN 1 WHEN @ShowFalse THEN 0 ELSE 0 END
        FROM 
            [dbo].[InventTrans] AS IT
            LEFT JOIN [dbo].[InventTable] AS I ON IT.[ItemId] = I.[ItemId]
            LEFT JOIN [dbo].[InventDim] AS ID ON IT.[INVENTDIMID] = ID.[InventDimId]
            LEFT JOIN [dbo].[SalesTable] AS ST ON IT.[TransRefId] = ST.[SalesId]
            LEFT JOIN [dbo].[SDICustomerSpecs] AS SCS ON ST.[CustAccount] = SCS.[CustomerId] AND IT.[ItemId] = SCS.[ItemId]
            LEFT JOIN [dbo].[SDIInventory] AS SI ON ID.[InventBatchId] = SI.[BatchId]
            LEFT JOIN [dbo].[SDICustomerSpecSheets] AS SCSS ON SCSS.[CustomerName] = ST.[SalesName]
            LEFT JOIN [LAFARGA].[LaFargaProd].[dbo].[BreakdownItem] AS xSI  ON ID.[InventBatchId] = xSI.[BatchId]
            LEFT JOIN max_sdiProductionChemistry AS mSPC ON P.[Id] = mSPC.[ProductionId] --Should this be an INNER JOIN?
            LEFT JOIN [dbo].[SDIProduction] AS P ON SI.[ProductionId] = P.[Id]
            LEFT JOIN [dbo].[sdiProductionChemistry] AS SPC ON T.[PRODUCTIONID] = SPC.[ProductionId] AND SPC.[Id] = T.[Id]
            LEFT JOIN [LAFARGA].[LaFargaProd].[dbo].[vSpectroAssays] AS xSA  ON xSA.[BatchID] = ID.[InventBatchId]
        WHERE 
            1=1
            AND 
            (
                (@PackSales = 0 AND IT.[PackingSlipId] = @PackingSlipId)
                OR
                (@PackSales = 1 AND IT.[TransRefId] = @PackingSlipId)
            )
        ORDER BY 
            ID.[InventBatchId]

    END TRY
    BEGIN CATCH
        SELECT 
              [ErrorNumber] = ERROR_NUMBER()
            , [ErrorSeverity] = ERROR_SEVERITY()
            , [ErrorState] = ERROR_STATE()
            , [ErrorProcedure] = ERROR_PROCEDURE()
            , [ErrorLine] = ERROR_LINE()
            , [ErrorMessage] = ERROR_MESSAGE();
    END CATCH

END