CODEWITHSUNDEEP
sql-servert-sqlset-based
Mike
Mike

Reputation: 1938

Can a SQL Select statement be converted to a Set-Based select?

I have an SP that creates and runs a SQL query. The end result is used for some reporting. This query though takes a long time to run (greater than 30 seconds is a long time and sometimes goes for over a minute).

I have read a little about set-based queries, but I am not clear if this particular query would be a good fit or if there is a way to convert it. Most of what I have read about set-based seems to be talking about changing loops into sets like these two Programming SQL in a Set-Based Way and An introduction to set-based vs procedural programming approaches in T-SQL

The query ends up looking like this:

SELECT ProductionId, Max(ID) AS Id INTO #TEMP
FROM sdiProductionChemistry GROUP BY ProductionId

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

It's kind of long and the SP is longer that creates it, but can this be converted to a set-based query?

If so, how would I begin to do this?

UPDATE

Here is the Actual Execution Query Plan

Upvotes: 0

Views: 66

Answers (1)

TTeeple
TTeeple

Reputation: 2989

Based on the execution plan you posted, a few indexes might help. Specifically on SDIPRODUCTIONCHEMISTRY.ProductionId. It is doing a Table Scan on ~1 mil rows. Is Id an IDENTITY column and set as the PK?

Take those index suggestions with a grain of salt. They are specific to the query the plan was ran against only and won't tell you how they will affect other queries hitting that table. They are good starting points but you should still analyze what they are having you change to make sure the index isn't too much.

A good reference link on general rules for index creation from gbn: https://dba.stackexchange.com/questions/12922/hard-and-fast-rule-for-include-columns-in-index

There seems to a couple of remote queries being ran. Also a small note: if you are using a linked server to connect to those, the entire contents of those tables will have to come over the wire before they can be joined/filtered. The 2 in your plan are fairly small (4700 and 20700) but your join criteria could possibly multiply that.

Upvotes: 1

Related Questions