Rewriting a complex function as Inline

Question

Post reply

Rewriting a complex function as Inline

J-F Bergeron

SSCrazy Eights

Points: 8593
More actions
July 16, 2009 at 2:30 pm

#136182

Hi all,
i've been trying to rewrite a complex function as an inline, and for now, I got a really good speed improvement, which is really good. So I'm really wanting to write it all as inline, but I'm stuck rewriting a scenario.
Here's the function for now:
USE GICSPF
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GICSPFGetSPFProductInfoNew]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[GICSPFGetSPFProductInfoNew]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[GICSPFGetSPFProductInfoNew]
(@InfoRequested NVARCHAR(5),
@ProductID INT,
@GICUPC VARCHAR(11),
@ZoneID NVARCHAR(10),
@PersonalMatrixEffDate DATETIME,
@SPFMatrixEffDate DATETIME)
RETURNS /*@Result TABLE(MfrCode NVARCHAR(5),
[Catalog] NVARCHAR(27),
VendorCatalog NVARCHAR(55),
UPC14 NVARCHAR(55),
DescEng NVARCHAR(57),
COST MONEY,
CostUOM NVARCHAR(1),
CostSource NVARCHAR(1),
DistributorCost MONEY,
PurchasingUOM NVARCHAR(1),
VendorCurrency NVARCHAR(3),
MatrixDetailID INT)*/
TABLE AS
RETURN (
WITH Info(InfoRequested,ProductID,ZoneID,PersonalMatrixEffDate,SPFMatrixEffDate)
AS (SELECT TOP 1 @InfoRequested,
ProductID,
@ZoneID,
coalesce(@PersonalMatrixEffDate,GETDATE()) AS PersonalMatrixEffDate,
coalesce(@SPFMatrixEffDate,GETDATE()) AS SPFMatrixEffDate
FROM GICSPFProduct
WHERE SPFGICUPC = @GICUPC
AND @ProductID IS NULL),
I
AS (SELECT Coalesce(MfrCode,'') AS MfrCode,
Coalesce(SPFCatalog,'') AS SPFCatalog,
Coalesce([VndCatalog/VndPrdCode],'') AS [VndCatalog/VndPrdCode],
Coalesce(SPFUPC,'') AS SPFUPC,
Coalesce(SPFDescEN,'') AS SPFDescEn
FROM (SELECT m.MfrCode,
p.SPFCatalog,
CASE
WHEN v.VendorKeyColumn = 'VndPrdCode'
THEN p.VndPrdCode
ELSE p.VndCatalog
END AS [VndCatalog/VndPrdCode],
SPFUPC,
SPFDescEn
FROM GICSPFProduct p
INNER JOIN GICSPFVendorToMFR vm
ON p.CompanyID = vm.CompanyID
AND p.DivisionID = vm.DivisionID
AND p.DepartmentID = vm.DepartmentID
AND p.MfrVendorID = vm.MfrVendorID
INNER JOIN GICSPFVendor v
ON vm.VendorID = v.VendorID
INNER JOIN GICSPFMFR m
ON vm.CompanyID = m.CompanyID
AND vm.DivisionID = m.DivisionID
AND vm.DepartmentID = m.DepartmentID
AND vm.MFrID = m.MFrID
INNER JOIN Info
ON p.ProductID = Info.ProductID) AS a),
MatrixPC
AS (SELECT mfrcode,
spfcatalog,
VndCatalog,
SPFUPC,
SPFDescEn,
COST,
Coalesce(CostUOM,SPFPurUOM) AS CostUOM,
Source,
DistrCost,
SPFPurUOM,
VendorCurrency,
MatrixDetailID
FROM (SELECT TOP 1 m.mfrcode,
p.spfcatalog,
p.VndCatalog,
p.SPFUPC,
p.SPFDescEn,
2 AS source,
pz.DistrCost,
p.SPFPurUOM,
v.VendorCurrency,
info.ProductID,
info.ZoneID,
info.PersonalMatrixEffDate
FROM GICSPFProduct p
INNER JOIN GICSPFVendorToMFR vm
ON p.CompanyID = vm.CompanyID
AND p.DivisionID = vm.DivisionID
AND p.DepartmentID = vm.DepartmentID
AND p.MfrVendorID = vm.MfrVendorID
INNER JOIN GICSPFVendor v
ON vm.VendorID = v.VendorID
INNER JOIN GICSPFMFR m
ON vm.CompanyID = m.CompanyID
AND vm.DivisionID = m.DivisionID
AND vm.DepartmentID = m.DepartmentID
AND vm.MFrID = m.MFrID
INNER JOIN GICSPFMfrZone mz
ON vm.CompanyID = mz.CompanyID
AND vm.DivisionID = mz.DivisionID
AND vm.DepartmentID = mz.DepartmentID
AND vm.MfrVendorID = mz.MfrVendorID
AND @ZoneID = mz.ZoneID
INNER JOIN GICSPF..GICSPFProductToZone pz
ON mz.CompanyID = pz.CompanyID
AND mz.DivisionID = pz.DivisionID
AND mz.DepartmentID = pz.DepartmentID
AND mz.MfrZoneID = pz.MfrZoneID
AND p.ProductID = pz.ProductID
INNER JOIN Info
ON Info.ProductID = p.ProductID
AND info.ZoneID = mz.ZoneID) AS UniqProduct
CROSS APPLY GICSPF..fnGICSPFCalcReplCostNewNewNew(UniqProduct.ProductID,UniqProduct.ZoneID,
'PC',UniqProduct.PersonalMatrixEffDate)),
MatrixSPF
AS (SELECT mfrcode,
spfcatalog,
VndCatalog,
SPFUPC,
SPFDescEn,
COST,
Coalesce(CostUOM,SPFPurUOM) AS CostUOM,
Source,
DistrCost,
SPFPurUOM,
VendorCurrency,
MatrixDetailID
FROM (SELECT TOP 1 m.mfrcode,
p.spfcatalog,
p.VndCatalog,
p.SPFUPC,
p.SPFDescEn,
1 AS source,
pz.DistrCost,
p.SPFPurUOM,
v.VendorCurrency,
info.ProductID,
info.ZoneID,
info.SPFMatrixEffDate
FROM GICSPFProduct p
INNER JOIN GICSPFVendorToMFR vm
ON p.CompanyID = vm.CompanyID
AND p.DivisionID = vm.DivisionID
AND p.DepartmentID = vm.DepartmentID
AND p.MfrVendorID = vm.MfrVendorID
INNER JOIN GICSPFVendor v
ON vm.VendorID = v.VendorID
INNER JOIN GICSPFMFR m
ON vm.CompanyID = m.CompanyID
AND vm.DivisionID = m.DivisionID
AND vm.DepartmentID = m.DepartmentID
AND vm.MFrID = m.MFrID
INNER JOIN GICSPFMfrZone mz
ON vm.CompanyID = mz.CompanyID
AND vm.DivisionID = mz.DivisionID
AND vm.DepartmentID = mz.DepartmentID
AND vm.MfrVendorID = mz.MfrVendorID
AND @ZoneID = mz.ZoneID
INNER JOIN GICSPF..GICSPFProductToZone pz
ON mz.CompanyID = pz.CompanyID
AND mz.DivisionID = pz.DivisionID
AND mz.DepartmentID = pz.DepartmentID
AND mz.MfrZoneID = pz.MfrZoneID
AND p.ProductID = pz.ProductID
INNER JOIN Info
ON p.ProductID = info.ProductID
AND mz.ZoneID = Info.ZoneID) AS UniqProduct
CROSS APPLY GICSPF..fnGICSPFCalcReplCostNewNewNew(UniqProduct.ProductID,UniqProduct.ZoneID,
'SPF',UniqProduct.SPFMatrixEffDate)),
MatrixNone
AS (SELECT TOP 1 m.mfrcode,
p.spfcatalog,
p.VndCatalog,
p.SPFUPC,
p.SPFDescEn,
NULL AS COST,
p.SPFPurUOM AS CostUOM, --Return the Product UOM when there is no match in the Matrix.
CASE
WHEN pz.DistrCost = 0
THEN NULL
ELSE 'S'
END AS source,
CASE
WHEN pz.DistrCost = 0
THEN NULL
ELSE pz.DistrCost
END AS DistrCost,
CASE
WHEN pz.DistrCost = 0
THEN NULL
ELSE p.SPFPurUOM
END AS SPFPurUOM,
v.VendorCurrency
FROM GICSPFProduct p
INNER JOIN GICSPFVendorToMFR vm
ON p.CompanyID = vm.CompanyID
AND p.DivisionID = vm.DivisionID
AND p.DepartmentID = vm.DepartmentID
AND p.MfrVendorID = vm.MfrVendorID
INNER JOIN GICSPFVendor v
ON vm.VendorID = v.VendorID
INNER JOIN GICSPFMFR m
ON vm.CompanyID = m.CompanyID
AND vm.DivisionID = m.DivisionID
AND vm.DepartmentID = m.DepartmentID
AND vm.MFrID = m.MFrID
INNER JOIN GICSPFMfrZone mz
ON vm.CompanyID = mz.CompanyID
AND vm.DivisionID = mz.DivisionID
AND vm.DepartmentID = mz.DepartmentID
AND vm.MfrVendorID = mz.MfrVendorID
AND @ZoneID = mz.ZoneID
INNER JOIN GICSPF..GICSPFProductToZone pz
ON mz.CompanyID = pz.CompanyID
AND mz.DivisionID = pz.DivisionID
AND mz.DepartmentID = pz.DepartmentID
AND mz.MfrZoneID = pz.MfrZoneID
AND p.ProductID = pz.ProductID
INNER JOIN info
ON p.ProductID = info.ProductID
AND mz.ZoneID = info.ZoneID)
USE GICSPF
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GICSPFGetSPFProductInfoNew]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[GICSPFGetSPFProductInfoNew]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[GICSPFGetSPFProductInfoNew]
(@InfoRequested NVARCHAR(5),
@ProductID INT,
@GICUPC VARCHAR(11),
@ZoneID NVARCHAR(10),
@PersonalMatrixEffDate DATETIME,
@SPFMatrixEffDate DATETIME)
RETURNS /*@Result TABLE(MfrCode NVARCHAR(5),
[Catalog] NVARCHAR(27),
VendorCatalog NVARCHAR(55),
UPC14 NVARCHAR(55),
DescEng NVARCHAR(57),
COST MONEY,
CostUOM NVARCHAR(1),
CostSource NVARCHAR(1),
DistributorCost MONEY,
PurchasingUOM NVARCHAR(1),
VendorCurrency NVARCHAR(3),
MatrixDetailID INT)*/
TABLE AS
RETURN (
WITH Info(InfoRequested,ProductID,ZoneID,PersonalMatrixEffDate,SPFMatrixEffDate)
AS (SELECT TOP 1 @InfoRequested,
ProductID,
@ZoneID,
coalesce(@PersonalMatrixEffDate,GETDATE()) AS PersonalMatrixEffDate,
coalesce(@SPFMatrixEffDate,GETDATE()) AS SPFMatrixEffDate
FROM GICSPFProduct
WHERE SPFGICUPC = @GICUPC
AND @ProductID IS NULL),
I
AS (SELECT Coalesce(MfrCode,'') AS MfrCode,
Coalesce(SPFCatalog,'') AS SPFCatalog,
Coalesce([VndCatalog/VndPrdCode],'') AS [VndCatalog/VndPrdCode],
Coalesce(SPFUPC,'') AS SPFUPC,
Coalesce(SPFDescEN,'') AS SPFDescEn
FROM (SELECT m.MfrCode,
p.SPFCatalog,
CASE
WHEN v.VendorKeyColumn = 'VndPrdCode'
THEN p.VndPrdCode
ELSE p.VndCatalog
END AS [VndCatalog/VndPrdCode],
SPFUPC,
SPFDescEn
FROM GICSPFProduct p
INNER JOIN GICSPFVendorToMFR vm
ON p.CompanyID = vm.CompanyID
AND p.DivisionID = vm.DivisionID
AND p.DepartmentID = vm.DepartmentID
AND p.MfrVendorID = vm.MfrVendorID
INNER JOIN GICSPFVendor v
ON vm.VendorID = v.VendorID
INNER JOIN GICSPFMFR m
ON vm.CompanyID = m.CompanyID
AND vm.DivisionID = m.DivisionID
AND vm.DepartmentID = m.DepartmentID
AND vm.MFrID = m.MFrID
INNER JOIN Info
ON p.ProductID = Info.ProductID) AS a),
MatrixPC
AS (SELECT mfrcode,
spfcatalog,
VndCatalog,
SPFUPC,
SPFDescEn,
COST,
Coalesce(CostUOM,SPFPurUOM) AS CostUOM,
Source,
DistrCost,
SPFPurUOM,
VendorCurrency,
MatrixDetailID
FROM (SELECT TOP 1 m.mfrcode,
p.spfcatalog,
p.VndCatalog,
p.SPFUPC,
p.SPFDescEn,
2 AS source,
pz.DistrCost,
p.SPFPurUOM,
v.VendorCurrency,
info.ProductID,
info.ZoneID,
info.PersonalMatrixEffDate
FROM GICSPFProduct p
INNER JOIN GICSPFVendorToMFR vm
ON p.CompanyID = vm.CompanyID
AND p.DivisionID = vm.DivisionID
AND p.DepartmentID = vm.DepartmentID
AND p.MfrVendorID = vm.MfrVendorID
INNER JOIN GICSPFVendor v
ON vm.VendorID = v.VendorID
INNER JOIN GICSPFMFR m
ON vm.CompanyID = m.CompanyID
AND vm.DivisionID = m.DivisionID
AND vm.DepartmentID = m.DepartmentID
AND vm.MFrID = m.MFrID
INNER JOIN GICSPFMfrZone mz
ON vm.CompanyID = mz.CompanyID
AND vm.DivisionID = mz.DivisionID
AND vm.DepartmentID = mz.DepartmentID
AND vm.MfrVendorID = mz.MfrVendorID
AND @ZoneID = mz.ZoneID
INNER JOIN GICSPF..GICSPFProductToZone pz
ON mz.CompanyID = pz.CompanyID
AND mz.DivisionID = pz.DivisionID
AND mz.DepartmentID = pz.DepartmentID
AND mz.MfrZoneID = pz.MfrZoneID
AND p.ProductID = pz.ProductID
INNER JOIN Info
ON Info.ProductID = p.ProductID
AND info.ZoneID = mz.ZoneID) AS UniqProduct
CROSS APPLY GICSPF..fnGICSPFCalcReplCostNewNewNew(UniqProduct.ProductID,UniqProduct.ZoneID,
'PC',UniqProduct.PersonalMatrixEffDate)),
MatrixSPF
AS (SELECT mfrcode,
spfcatalog,
VndCatalog,
SPFUPC,
SPFDescEn,
COST,
Coalesce(CostUOM,SPFPurUOM) AS CostUOM,
Source,
DistrCost,
SPFPurUOM,
VendorCurrency,
MatrixDetailID
FROM (SELECT TOP 1 m.mfrcode,
p.spfcatalog,
p.VndCatalog,
p.SPFUPC,
p.SPFDescEn,
1 AS source,
pz.DistrCost,
p.SPFPurUOM,
v.VendorCurrency,
info.ProductID,
info.ZoneID,
info.SPFMatrixEffDate
FROM GICSPFProduct p
INNER JOIN GICSPFVendorToMFR vm
ON p.CompanyID = vm.CompanyID
AND p.DivisionID = vm.DivisionID
AND p.DepartmentID = vm.DepartmentID
AND p.MfrVendorID = vm.MfrVendorID
INNER JOIN GICSPFVendor v
ON vm.VendorID = v.VendorID
INNER JOIN GICSPFMFR m
ON vm.CompanyID = m.CompanyID
AND vm.DivisionID = m.DivisionID
AND vm.DepartmentID = m.DepartmentID
AND vm.MFrID = m.MFrID
INNER JOIN GICSPFMfrZone mz
ON vm.CompanyID = mz.CompanyID
AND vm.DivisionID = mz.DivisionID
AND vm.DepartmentID = mz.DepartmentID
AND vm.MfrVendorID = mz.MfrVendorID
AND @ZoneID = mz.ZoneID
INNER JOIN GICSPF..GICSPFProductToZone pz
ON mz.CompanyID = pz.CompanyID
AND mz.DivisionID = pz.DivisionID
AND mz.DepartmentID = pz.DepartmentID
AND mz.MfrZoneID = pz.MfrZoneID
AND p.ProductID = pz.ProductID
INNER JOIN Info
ON p.ProductID = info.ProductID
AND mz.ZoneID = Info.ZoneID) AS UniqProduct
CROSS APPLY GICSPF..fnGICSPFCalcReplCostNewNewNew(UniqProduct.ProductID,UniqProduct.ZoneID,
'SPF',UniqProduct.SPFMatrixEffDate)),
MatrixNone
AS (SELECT TOP 1 m.mfrcode,
p.spfcatalog,
p.VndCatalog,
p.SPFUPC,
p.SPFDescEn,
NULL AS COST,
p.SPFPurUOM AS CostUOM, --Return the Product UOM when there is no match in the Matrix.
CASE
WHEN pz.DistrCost = 0
THEN NULL
ELSE 'S'
END AS source,
CASE
WHEN pz.DistrCost = 0
THEN NULL
ELSE pz.DistrCost
END AS DistrCost,
CASE
WHEN pz.DistrCost = 0
THEN NULL
ELSE p.SPFPurUOM
END AS SPFPurUOM,
v.VendorCurrency
FROM GICSPFProduct p
INNER JOIN GICSPFVendorToMFR vm
ON p.CompanyID = vm.CompanyID
AND p.DivisionID = vm.DivisionID
AND p.DepartmentID = vm.DepartmentID
AND p.MfrVendorID = vm.MfrVendorID
INNER JOIN GICSPFVendor v
ON vm.VendorID = v.VendorID
INNER JOIN GICSPFMFR m
ON vm.CompanyID = m.CompanyID
AND vm.DivisionID = m.DivisionID
AND vm.DepartmentID = m.DepartmentID
AND vm.MFrID = m.MFrID
INNER JOIN GICSPFMfrZone mz
ON vm.CompanyID = mz.CompanyID
AND vm.DivisionID = mz.DivisionID
AND vm.DepartmentID = mz.DepartmentID
AND vm.MfrVendorID = mz.MfrVendorID
AND @ZoneID = mz.ZoneID
INNER JOIN GICSPF..GICSPFProductToZone pz
ON mz.CompanyID = pz.CompanyID
AND mz.DivisionID = pz.DivisionID
AND mz.DepartmentID = pz.DepartmentID
AND mz.MfrZoneID = pz.MfrZoneID
AND p.ProductID = pz.ProductID
INNER JOIN info
ON p.ProductID = info.ProductID
AND mz.ZoneID = info.ZoneID)
IF @InfoRequested = 'I'
BEGIN
SELECT *
FROM I
END
IF @InfoRequested = 'B'
OR InfoRequested = 'C'
BEGIN
SELECT *
FROM MatrixPC
IF @@ROWCOUNT = 0
SELECT *
FROM MatrixSPF
IF @@ROWCOUNT = 0
SELECT *
FROM MatrixNone
END
Now, what I'm trying to rewrite, as you may have guessed, is the last part, where I decide which possible table I should take the data from... But I'm stuck, since I cannot keep the IF statements, but I need that kind of logic. I know it may not work like this, but I simply wrote the IF statements quickly to show what was the expected results.
Now I know I did not provide any scripts for the table definitions, but I'm only looking to see what the logic would be to select the right rows from the tables.
Thanks in advance all,
Cheers,
J-F

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

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

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 1

J-F,

Did you ever figure out how to do this?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)