Huge not performant tabled valued Function
-
Hi all, I'm having a hard time figure this one out.
A few months back, I built this function, that would query the Matrices (Prices matrices) for a product, and get me the price from it.
Let's make this one easy and say there can be 4 different level of instruction in a matrix.
Level 4 applies to a specific "product"
Level 3 applies to a specific "Product category"
Level 2 applies to a specific "Product Manufacturer"
Level 1 applies to every product of this vendor.
Some prices percentages can be specified in the matrix i.e. 90% of the Distributor Cost or 10% over the Distributor Cost, but that is not the issue.
So I build this huge function, that will query the different matrix levels of a Vendor matrix, for a given product. It looks like mean ugly code, but I couldn't find a way to improve it at all.
For 1 product, it runs in about 300ms, and everyone was impressed. Since they wanted to query a certain product, and get it's price, according to the different matrices that could be linked to the product.
Now, they are asking me to insert that "Price calcullation" within a query that will be used in the products screen. So when they will query a vendor with 50k products in the screen, they want the prices to be calculated for every of these products... That results in a wonderful timeout... (Doh!)
Can you guys see anything I could do to replace this function, and still be able to calculate this hard to understand piece of code?
I'll explain anything you need to know about the code if it's not clear enough..
IF EXISTS (SELECT *FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[fnGICSPFCalcReplCost]')
AND TYPE IN (N'FN',N'IF',N'TF',N'FS',
N'FT'))
DROP FUNCTION [dbo].[fnGICSPFCalcReplCost]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fnGICSPFCalcReplCost]
(@ProductID INT,
@ZoneID NVARCHAR(10),
@VendorMatrixType NVARCHAR(3),
@MatrixEffDate DATETIME)
RETURNS @Result TABLE(ProductID INT,
COST MONEY,
CostUOM NVARCHAR(1),
MatrixDetailID INT/*,
ord int*/
)
AS
BEGIN
/*DECLARE @MatrixEffDate DATETIME
SET @MatrixEffDate = null
DECLARE @VendorMatrixType NVARCHAR(3)
SET @VendorMatrixType = 'SPF'
DECLARE @ProductID INT
SET @ProductID = 59211
DECLARE @ZoneID NVARCHAR(10)
SET @ZoneID = '2'*/
DECLARE @CompanyID NVARCHAR(36),
@DepartmentID NVARCHAR(36),
@DivisionID NVARCHAR(36),
@FoundVendorID INT
IF (@MatrixEffDate IS NULL)
BEGIN
SET @MatrixEffDate = GetDate()
END
SELECT @CompanyID = p.CompanyID,
@DivisionID = p.DivisionID,
@DepartmentID = p.DepartmentID,
@FoundVendorID = vm.VendorID
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
WHERE p.ProductID = @ProductID
IF @FoundVendorID IS NULL
BEGIN
RETURN
END
DECLARE @MatrixTable TABLE(
CompanyID NVARCHAR(36),
DivisionID NVARCHAR(36),
DepartmentID NVARCHAR(36),
MatrixDetailID INT,
MatrixDiscMultOp NVARCHAR(4),
MatrixHeaderID INT,
MatrixLevel INT,
MfrId INT,
CategoryID INT,
CategoryName NVARCHAR(30),
ProductID INT,
ProductCatalog NVARCHAR(27),
MatrixPriceBaseColumn NVARCHAR(1),
MatrixDiscMult NUMERIC(7,4),
MatrixNetPrice MONEY,
PurUOM NVARCHAR(1),
ZoneID NVARCHAR(10)
)
DECLARE @PrdTable TABLE(
VendorID INT,
MFRID INT,
MFRCode NVARCHAR(5),
ProductID INT,
SPFCatalog NVARCHAR(27),
CategoryID INT,
CategoryName NVARCHAR(30),
ZoneID NVARCHAR(10),
ListPrice MONEY,
Col1 MONEY,
Col2 MONEY,
Col3 MONEY,
DistrCost MONEY,
SPFPurUOM NVARCHAR(1)
)
IF @VendorMatrixType = 'Sls'
BEGIN
INSERT INTO @MatrixTable
SELECT md.CompanyID,
md.DivisionID,
md.DepartmentID,
MatrixDetailID,
MatrixDiscMultOp,
md.MatrixHeaderID,
MatrixLevel,
MfrId,
CategoryID,
CategoryName,
ProductID,
PRoductCatalog,
MatrixPriceBaseColumn,
MatrixDiscMult,
MatrixNetPrice,
PurUOM,
ZoneID
FROM Enterprise..GICVendorMatrixDetail md
INNER JOIN Enterprise..GICVendorMatrixHeader mh
ON md.CompanyID = mh.CompanyID
AND md.DivisionID = mh.DivisionID
AND md.DepartmentID = mh.DepartmentID
AND md.MatrixHeaderID = mh.MatrixHeaderID
WHERE md.CompanyID = @CompanyID
AND md.DivisionID = @DivisionID
AND md.DepartmentID = @DepartmentID
AND md.MAtrixHeaderID = Enterprise.dbo.fnGICMatrixFindActiveByVendor(@FoundVendorID,1--IsSalesMatrix
,@MatrixEffDate)
END
IF @VendorMatrixType = 'PC'
BEGIN
INSERT INTO @MatrixTable
SELECT md.CompanyID,
md.DivisionID,
md.DepartmentID,
MatrixDetailID,
MatrixDiscMultOp,
md.MatrixHeaderID,
MatrixLevel,
MfrId,
CategoryID,
CategoryName,
ProductID,
PRoductCatalog,
MatrixPriceBaseColumn,
MatrixDiscMult,
MatrixNetPrice,
PurUOM,
ZoneID
FROM Enterprise..GICVendorMatrixDetail md
INNER JOIN Enterprise..GICVendorMatrixHeader mh
ON md.CompanyID = mh.CompanyID
AND md.DivisionID = mh.DivisionID
AND md.DepartmentID = mh.DepartmentID
AND md.MatrixHeaderID = mh.MatrixHeaderID
WHERE md.CompanyID = @CompanyID
AND md.DivisionID = @DivisionID
AND md.DepartmentID = @DepartmentID
AND md.MAtrixHeaderID = Enterprise.dbo.fnGICMatrixFindActiveByVendor(@FoundVendorID,0,-- isSalesMatrix
@MatrixEffDate)
END
IF @VendorMatrixType = 'SPF'
BEGIN
INSERT INTO @MatrixTable
SELECT md.CompanyID,
md.DivisionID,
md.DepartmentID,
MatrixDetailID,
MatrixDiscMultOp,
md.MatrixHeaderID,
MatrixLevel,
MfrId,
CategoryID,
CategoryName,
ProductID,
PRoductCatalog,
MatrixPriceBaseColumn,
MatrixDiscMult,
MatrixNetPrice,
PurUOM,
ZoneID
FROM GICSPFMatrixDetail md
INNER JOIN GICSPFMatrixHeader mh
ON md.CompanyID = mh.CompanyID
AND md.DivisionID = mh.DivisionID
AND md.DepartmentID = mh.DepartmentID
AND md.MatrixHeaderID = mh.MatrixHeaderID
WHERE md.CompanyID = @CompanyID
AND md.DivisionID = @DivisionID
AND md.DepartmentID = @DepartmentID
AND md.MAtrixHeaderID = GICSPF.dbo.fnGICSPFMatrixFindActiveByVendor(@FoundVendorID,@MatrixEffDate)
END
IF (SELECT COUNT(* )
FROM @MatrixTable) = 0
BEGIN
-- No matrix found associated with that product.
RETURN
END
--Create a table that holds only the information about the product to match.
INSERT INTO @PrdTable
SELECT vm.VendorID,
vm.MFRID,
m.MFRCode,
p.ProductID,
p.SPFCatalog,
cd.CategoryID,
cd.CategoryName,
mz.ZoneID,
pz.ListPrice,
pz.Col1,
pz.Col2,
pz.Col3,
pz.DistrCost,
SPFPurUOM
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 GICSPFMfrZone mz
ON vm.CompanyID = mz.CompanyID
AND vm.DivisionID = mz.DivisionID
AND vm.DepartmentID = mz.DepartmentID
AND vm.MfrVendorID = mz.MfrVendorID
INNER JOIN GICSPFProductToZone pz
ON p.CompanyID = pz.CompanyID
AND p.DivisionID = pz.DivisionID
AND p.DepartmentID = pz.DepartmentID
AND mz.MfrZoneID = pz.MfrZoneID
AND p.ProductID = pz.ProductID
INNER JOIN GICSPFMfr m
ON vm.MfrID = m.MfrID
LEFT JOIN GICSPFVendorCategoryDetail cd
ON p.CompanyID = cd.CompanyID
AND p.DivisionID = cd.DivisionID
AND p.DepartmentID = cd.DepartmentID
AND p.vndCategory = cd.CategoryID
WHERE p.ProductID = @ProductID
AND mz.ZoneID = @ZoneID
INSERT INTO @Result
SELECT TOP 1 ProductID,
CASE
WHEN MatrixNetPrice IS NOT NULL
AND MatProductID = ProductID THEN MatrixNetPrice
WHEN MatrixDiscMultOp = 'Mult'
AND MatrixPriceBaseColumn = 'L' THEN ListPrice
* MatrixDiscMult
WHEN MatrixDiscMultOp = 'Mult'
AND MatrixPriceBaseColumn = '1' THEN Col1
* MatrixDiscMult
WHEN MatrixDiscMultOp = 'Mult'
AND MatrixPriceBaseColumn = '2' THEN Col2
* MatrixDiscMult
WHEN MatrixDiscMultOp = 'Mult'
AND MatrixPriceBaseColumn = '3' THEN Col3
* MatrixDiscMult
WHEN MatrixDiscMultOp = 'Mult'
AND MatrixPriceBaseColumn = 'D' THEN DistrCost
* MatrixDiscMult
WHEN MatrixDiscMultOp = 'Disc'
AND MatrixPriceBaseColumn = 'L' THEN ListPrice
+ (ListPrice
* MatrixDiscMult
/ 100)
WHEN MatrixDiscMultOp = 'Disc'
AND MatrixPriceBaseColumn = '1' THEN Col1
+ (Col1
* MatrixDiscMult
/ 100)
WHEN MatrixDiscMultOp = 'Disc'
AND MatrixPriceBaseColumn = '2' THEN Col2
+ (Col2
* MatrixDiscMult
/ 100)
WHEN MatrixDiscMultOp = 'Disc'
AND MatrixPriceBaseColumn = '3' THEN Col3
+ (Col3
* MatrixDiscMult
/ 100)
WHEN MatrixDiscMultOp = 'Disc'
AND MatrixPriceBaseColumn = 'D' THEN DistrCost
+ (DistrCost
* MatrixDiscMult
/ 100)
END AS COST,
CASE
WHEN MatrixNetPrice IS NOT NULL THEN PurUOM --MatrixUOM
ELSE SPFPurUOM --ProductUOM
END AS CostUOM,
MatrixDetailID
FROM (SELECT ord,
MatrixDetailID,
MatrixLevel,
MatrixDiscMultOp,
md.MfrID AS MatMfrID,
md.CategoryID AS MatCategoryID,
md.CategoryName AS MatCategoryName,
md.ProductID AS MatProductID,
md.ProductCatalog AS MatSPFCatalog,
md.MatrixPriceBaseColumn,
md.MatrixDiscMult,
md.MatrixNetPrice,
md.PurUOM,
md.ZoneID AS MatZoneID,
Prd44.VendorID,
Prd44.ProductID,
Prd44.SpfCatalog,
Prd44.CategoryName,
Prd44.ZoneID,
Prd44.ListPrice,
Prd44.Col1,
Prd44.Col2,
Prd44.Col3,
Prd44.DistrCost,
Prd44.SPFPurUOM
FROM @MatrixTable md
LEFT JOIN (SELECT 44 AS Ord,
*
FROM @PrdTable prd) Prd44
ON (md.MatrixLevel = 4
AND md.MfrID = Prd44.MFRID
AND md.ProductID = Prd44.ProductID
AND md.ZoneID = Prd44.ZoneID)
UNION ALL
SELECT ord,
MatrixDetailID,
MatrixLevel,
MatrixDiscMultOp,
md.MfrID AS MatMfrID,
md.CategoryID AS MatCategoryID,
md.CategoryName AS MatCategoryName,
md.ProductID AS MatProductID,
md.ProductCatalog AS MatSPFCatalog,
md.MatrixPriceBaseColumn,
md.MatrixDiscMult,
md.MatrixNetPrice,
md.PurUOM,
md.ZoneID AS MatZoneID,
Prd43.VendorID,
Prd43.ProductID,
Prd43.SpfCatalog,
Prd43.CategoryName,
Prd43.ZoneID,
Prd43.ListPrice,
Prd43.Col1,
Prd43.Col2,
Prd43.Col3,
Prd43.DistrCost,
Prd43.SPFPurUOM
FROM @MatrixTable md
LEFT JOIN (SELECT 43 AS Ord,
*
FROM @PrdTable prd) Prd43
ON (md.MatrixLevel = 4
AND md.MfrID = prd43.MFRID
AND prd43.SpfCatalog LIKE REPLACE(md.ProductCatalog,'*','%')
AND md.ZoneID = prd43.ZoneID)
UNION ALL
SELECT ord,
MatrixDetailID,
MatrixLevel,
MatrixDiscMultOp,
md.MfrID AS MatMfrID,
md.CategoryID AS MatCategoryID,
md.CategoryName AS MatCategoryName,
md.ProductID AS MatProductID,
md.ProductCatalog AS MatSPFCatalog,
md.MatrixPriceBaseColumn,
md.MatrixDiscMult,
md.MatrixNetPrice,
md.PurUOM,
md.ZoneID AS MatZoneID,
Prd42.VendorID,
Prd42.ProductID,
Prd42.SpfCatalog,
Prd42.CategoryName,
Prd42.ZoneID,
Prd42.ListPrice,
Prd42.Col1,
Prd42.Col2,
Prd42.Col3,
Prd42.DistrCost,
Prd42.SPFPurUOM
FROM @MatrixTable md
LEFT JOIN (SELECT 42 AS Ord,
*
FROM @PrdTable prd) Prd42
ON (md.MatrixLevel = 4
AND md.MfrID = prd42.MFRID
AND md.ProductID = prd42.ProductID
AND (md.ZoneID IS NULL
OR md.ZoneID = ''))
UNION ALL
SELECT ord,
MatrixDetailID,
MatrixLevel,
MatrixDiscMultOp,
md.MfrID AS MatMfrID,
md.CategoryID AS MatCategoryID,
md.CategoryName AS MatCategoryName,
md.ProductID AS MatProductID,
md.ProductCatalog AS MatSPFCatalog,
md.MatrixPriceBaseColumn,
md.MatrixDiscMult,
md.MatrixNetPrice,
md.PurUOM,
md.ZoneID AS MatZoneID,
Prd41.VendorID,
Prd41.ProductID,
Prd41.SpfCatalog,
Prd41.CategoryName,
Prd41.ZoneID,
Prd41.ListPrice,
Prd41.Col1,
Prd41.Col2,
Prd41.Col3,
Prd41.DistrCost,
Prd41.SPFPurUOM
FROM @MatrixTable md
LEFT JOIN (SELECT 41 AS Ord,
*
FROM @PrdTable prd) Prd41
ON (md.MatrixLevel = 4
AND md.MfrID = prd41.MFRID
AND prd41.SpfCatalog LIKE REPLACE(md.ProductCatalog,'*','%')
AND (md.ZoneID IS NULL
OR md.ZoneID = ''))
UNION ALL
SELECT ord,
MatrixDetailID,
MatrixLevel,
MatrixDiscMultOp,
md.MfrID AS MatMfrID,
md.CategoryID AS MatCategoryID,
md.CategoryName AS MatCategoryName,
md.ProductID AS MatProductID,
md.ProductCatalog AS MatSPFCatalog,
md.MatrixPriceBaseColumn,
md.MatrixDiscMult,
md.MatrixNetPrice,
md.PurUOM,
md.ZoneID AS MatZoneID,
Prd34.VendorID,
Prd34.ProductID,
Prd34.SpfCatalog,
Prd34.CategoryName,
Prd34.ZoneID,
Prd34.ListPrice,
Prd34.Col1,
Prd34.Col2,
Prd34.Col3,
Prd34.DistrCost,
Prd34.SPFPurUOM
FROM @MatrixTable md
LEFT JOIN (SELECT 34 AS Ord,
*
FROM @PrdTable prd) Prd34
ON (md.MatrixLevel = 3
AND md.CategoryID = prd34.CategoryID
AND md.ZoneID = prd34.ZoneID)
UNION ALL
SELECT ord,
MatrixDetailID,
MatrixLevel,
MatrixDiscMultOp,
md.MfrID AS MatMfrID,
md.CategoryID AS MatCategoryID,
md.CategoryName AS MatCategoryName,
md.ProductID AS MatProductID,
md.ProductCatalog AS MatSPFCatalog,
md.MatrixPriceBaseColumn,
md.MatrixDiscMult,
md.MatrixNetPrice,
md.PurUOM,
md.ZoneID AS MatZoneID,
Prd33.VendorID,
Prd33.ProductID,
Prd33.SpfCatalog,
Prd33.CategoryName,
Prd33.ZoneID,
Prd33.ListPrice,
Prd33.Col1,
Prd33.Col2,
Prd33.Col3,
Prd33.DistrCost,
Prd33.SPFPurUOM
FROM @MatrixTable md
LEFT JOIN (SELECT 33 AS Ord,
*
FROM @PrdTable prd) Prd33
ON (md.MatrixLevel = 3
AND prd33.CategoryName LIKE REPLACE(md.CategoryName,'*','%')
AND md.ZoneID = prd33.ZoneID)
UNION ALL
SELECT ord,
MatrixDetailID,
MatrixLevel,
MatrixDiscMultOp,
md.MfrID AS MatMfrID,
md.CategoryID AS MatCategoryID,
md.CategoryName AS MatCategoryName,
md.ProductID AS MatProductID,
md.ProductCatalog AS MatSPFCatalog,
md.MatrixPriceBaseColumn,
md.MatrixDiscMult,
md.MatrixNetPrice,
md.PurUOM,
md.ZoneID AS MatZoneID,
Prd32.VendorID,
Prd32.ProductID,
Prd32.SpfCatalog,
Prd32.CategoryName,
Prd32.ZoneID,
Prd32.ListPrice,
Prd32.Col1,
Prd32.Col2,
Prd32.Col3,
Prd32.DistrCost,
Prd32.SPFPurUOM
FROM @MatrixTable md
LEFT JOIN (SELECT 32 AS Ord,
*
FROM @PrdTable prd) Prd32
ON (md.MatrixLevel = 3
AND prd32.CategoryID = md.CategoryID
AND (md.ZoneID IS NULL
OR md.ZoneID = ''))
UNION ALL
SELECT ord,
MatrixDetailID,
MatrixLevel,
MatrixDiscMultOp,
md.MfrID AS MatMfrID,
md.CategoryID AS MatCategoryID,
md.CategoryName AS MatCategoryName,
md.ProductID AS MatProductID,
md.ProductCatalog AS MatSPFCatalog,
md.MatrixPriceBaseColumn,
md.MatrixDiscMult,
md.MatrixNetPrice,
md.PurUOM,
md.ZoneID AS MatZoneID,
Prd31.VendorID,
Prd31.ProductID,
Prd31.SpfCatalog,
Prd31.CategoryName,
Prd31.ZoneID,
Prd31.ListPrice,
Prd31.Col1,
Prd31.Col2,
Prd31.Col3,
Prd31.DistrCost,
Prd31.SPFPurUOM
FROM @MatrixTable md
LEFT JOIN (SELECT 31 AS Ord,
*
FROM @PrdTable prd) Prd31
ON (md.MatrixLevel = 3
AND prd31.CategoryName LIKE REPLACE(md.CategoryName,'*','%')
AND (md.ZoneID IS NULL
OR md.ZoneID = ''))
UNION ALL
SELECT ord,
MatrixDetailID,
MatrixLevel,
MatrixDiscMultOp,
md.MfrID AS MatMfrID,
md.CategoryID AS MatCategoryID,
md.CategoryName AS MatCategoryName,
md.ProductID AS MatProductID,
md.ProductCatalog AS MatSPFCatalog,
md.MatrixPriceBaseColumn,
md.MatrixDiscMult,
md.MatrixNetPrice,
md.PurUOM,
md.ZoneID AS MatZoneID,
Prd22.VendorID,
Prd22.ProductID,
Prd22.SpfCatalog,
Prd22.CategoryName,
Prd22.ZoneID,
Prd22.ListPrice,
Prd22.Col1,
Prd22.Col2,
Prd22.Col3,
Prd22.DistrCost,
Prd22.SPFPurUOM
FROM @MatrixTable md
LEFT JOIN (SELECT 22 AS Ord,
*
FROM @PrdTable prd) Prd22
ON (md.MatrixLevel = 2
AND md.MfrID = prd22.MfrID
AND md.ZoneID = prd22.ZoneID)
UNION ALL
SELECT ord,
MatrixDetailID,
MatrixLevel,
MatrixDiscMultOp,
md.MfrID AS MatMfrID,
md.CategoryID AS MatCategoryID,
md.CategoryName AS MatCategoryName,
md.ProductID AS MatProductID,
md.ProductCatalog AS MatSPFCatalog,
md.MatrixPriceBaseColumn,
md.MatrixDiscMult,
md.MatrixNetPrice,
md.PurUOM,
md.ZoneID AS MatZoneID,
Prd21.VendorID,
Prd21.ProductID,
Prd21.SpfCatalog,
Prd21.CategoryName,
Prd21.ZoneID,
Prd21.ListPrice,
Prd21.Col1,
Prd21.Col2,
Prd21.Col3,
Prd21.DistrCost,
Prd21.SPFPurUOM
FROM @MatrixTable md
LEFT JOIN (SELECT 21 AS Ord,
*
FROM @PrdTable prd) Prd21
ON (md.MatrixLevel = 2
AND md.MfrID = prd21.MfrID
AND (md.ZoneID IS NULL
OR md.ZoneID = ''))
UNION ALL
SELECT ord,
MatrixDetailID,
MatrixLevel,
MatrixDiscMultOp,
md.MfrID AS MatMfrID,
md.CategoryID AS MatCategoryID,
md.CategoryName AS MatCategoryName,
md.ProductID AS MatProductID,
md.ProductCatalog AS MatSPFCatalog,
md.MatrixPriceBaseColumn,
md.MatrixDiscMult,
md.MatrixNetPrice,
md.PurUOM,
md.ZoneID AS MatZoneID,
Prd12.VendorID,
Prd12.ProductID,
Prd12.SpfCatalog,
Prd12.CategoryName,
Prd12.ZoneID,
Prd12.ListPrice,
Prd12.Col1,
Prd12.Col2,
Prd12.Col3,
Prd12.DistrCost,
Prd12.SPFPurUOM
FROM @MatrixTable md
LEFT JOIN (SELECT 12 AS Ord,
*
FROM @PrdTable prd) Prd12
ON (md.MatrixLevel = 1
AND md.ZoneID = prd12.ZoneID)
UNION ALL
SELECT ord,
MatrixDetailID,
MatrixLevel,
MatrixDiscMultOp,
md.MfrID AS MatMfrID,
md.CategoryID AS MatCategoryID,
md.CategoryName AS MatCategoryName,
md.ProductID AS MatProductID,
md.ProductCatalog AS MatSPFCatalog,
md.MatrixPriceBaseColumn,
md.MatrixDiscMult,
md.MatrixNetPrice,
md.PurUOM,
md.ZoneID AS MatZoneID,
Prd11.VendorID,
Prd11.ProductID,
Prd11.SpfCatalog,
Prd11.CategoryName,
Prd11.ZoneID,
Prd11.ListPrice,
Prd11.Col1,
Prd11.Col2,
Prd11.Col3,
Prd11.DistrCost,
Prd11.SPFPurUOM
FROM @MatrixTable md
LEFT JOIN (SELECT 11 AS Ord,
*
FROM @PrdTable prd) Prd11
ON (md.MatrixLevel = 1
AND (md.ZoneID IS NULL
OR md.ZoneID = ''))) AS Prd
WHERE productId IS NOT NULL
ORDER BY ord DESC
RETURN
END
GO
Thanks in advance,
Cheers,
J-F
-
It would be very helpful if you posted the create scripts for the tables the function references, and insert statements for some sample data for each of those. That would make it a lot easier to analyze what the function does and how it does it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
J-F Bergeron (4/6/2009)
Now, they are asking me to insert that "Price calcullation" within a query that will be used in the products screen. So when they will query a vendor with 50k products in the screen, they want the prices to be calculated for every of these products... That results in a wonderful timeout... (Doh!)
Multi statement table valued functions do not perform well on large numbers of rows (and by large I mean anything past about 100)
What I would suggest, instead of rewriting all of that, is that you change it into a stored procedure, then for that screen insert the results of the procedure into a temp table, apply any useful indexes (if appropriate) and join the temp table into the query. It's just about guaranteed to perform way better on large row counts.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
I was somehow foreseeing this answer, and I do not blame you, it's just it implies so many tables, views and procedures that it will be really hard to post the data here. Can you actually just look at the code and view the functionnality? I only want a rough idea of what to do...
Cheers,
J-F
-
You also seem to be using a couple of scalar function, can you post the code for thse as well.
Enterprise.dbo.fnGICMatrixFindActiveByVendor
GICSPF.dbo.fnGICSPFMatrixFindActiveByVendor
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
GilaMonster (4/6/2009)
J-F Bergeron (4/6/2009)
Now, they are asking me to insert that "Price calcullation" within a query that will be used in the products screen. So when they will query a vendor with 50k products in the screen, they want the prices to be calculated for every of these products... That results in a wonderful timeout... (Doh!)
Multi statement table valued functions do not perform well on large numbers of rows (and by large I mean anything past about 100)
What I would suggest, instead of rewriting all of that, is that you change it into a stored procedure, then for that screen insert the results of the procedure into a temp table, apply any useful indexes (if appropriate) and join the temp table into the query. It's just about guaranteed to perform way better on large row counts.
Nevermind. I took a closer look and it's got Product as a parameter, which means for the 50 000 products, the function has to run 50 000 times.
What I'd suggest is that you leave this function for the one-by-one products and see if you can write one that instead takes a vendor as a parameter and works on all the products for that vendor. I can't really help you with the details without seeing more of the table structures, sample data etc.
Even then, a multi-statement table valued function returning 50 000 rows is going to be slooooooooow. Probably better off as a procedure that returns a resultset that can be inserted into a temp table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Mark (4/6/2009)
You also seem to be using a couple of scalar function, can you post the code for thse as well.Enterprise.dbo.fnGICMatrixFindActiveByVendor
GICSPF.dbo.fnGICSPFMatrixFindActiveByVendor
Sure Mark, here's the function, but this one should not be a problem, it's pretty straight forward.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnGICSPFMatrixFindActiveByVendor]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))DROP FUNCTION [dbo].[fnGICSPFMatrixFindActiveByVendor]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION fnGICSPFMatrixFindActiveByVendor
(@VendorID INT,
@MatrixEffDate datetime = NULL)
RETURNS INT
AS
BEGIN
/*DECLARE @MatrixEffDate datetime
SET @MatrixEffDate = NULL*/
if @MatrixEffdate is null
BEGIN
SET @MatrixEffDate = GetDate()
END
-- Return the most recent active Matrix.
RETURN
(SELECT TOP 1 MatrixHeaderID
FROM GICSPFMatrixHeader
WHERE VendorID = @VendorID
AND MatrixEffDate <= @MatrixEffDate
ORDER BY MatrixEffDate DESC)
END
GO
Both functions do the same job, but on a different BD, since different matrix types are stored in separate BD's.
Cheers,
J-F
-
GilaMonster (4/6/2009)
GilaMonster (4/6/2009)
J-F Bergeron (4/6/2009)
Now, they are asking me to insert that "Price calcullation" within a query that will be used in the products screen. So when they will query a vendor with 50k products in the screen, they want the prices to be calculated for every of these products... That results in a wonderful timeout... (Doh!)
Multi statement table valued functions do not perform well on large numbers of rows (and by large I mean anything past about 100)
What I would suggest, instead of rewriting all of that, is that you change it into a stored procedure, then for that screen insert the results of the procedure into a temp table, apply any useful indexes (if appropriate) and join the temp table into the query. It's just about guaranteed to perform way better on large row counts.
Nevermind. I took a closer look and it's got Product as a parameter, which means for the 50 000 products, the function has to run 50 000 times.
What I'd suggest is that you leave this function for the one-by-one products and see if you can write one that instead takes a vendor as a parameter and works on all the products for that vendor. I can't really help you with the details without seeing more of the table structures, sample data etc.
Even then, a multi-statement table valued function returning 50 000 rows is going to be slooooooooow. Probably better off as a procedure that returns a resultset that can be inserted into a temp table.
First of all, thanks for your answer, I didn't expect so many answers that quickly, and I'm really enjoying all sorts of answers, since it will end out helping me!
Here's the table definition of a Matrix, and some "Sample" data so you all understand better.
When a product is in input, I have to check certain information to see if it matches with the matrix, in a priority order, and that's really complicated. Here's some sample data..
USE tempdbGO
IF EXISTS (SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[GICSPFMatrixDetail]')
AND TYPE IN (N'U'))
DROP TABLE [dbo].[GICSPFMatrixDetail]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[GICSPFMatrixDetail] (
[CompanyID] [NVARCHAR](36) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DivisionID] [NVARCHAR](36) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DepartmentID] [NVARCHAR](36) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[MatrixDetailID] [INT] NOT NULL IDENTITY ( 1 , 1 ),
[MatrixHeaderID] [INT] NOT NULL,
[MatrixLevel] [INT] NOT NULL,
[MfrID] [INT] NULL,
[CategoryID] [INT] NULL,
[CategoryName] [NVARCHAR](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductID] [INT] NULL,
[ProductCatalog] [NVARCHAR](27) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MatrixPriceBaseColumn] [NVARCHAR](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MatrixDiscMult] [NUMERIC](7,4) NULL,
[MatrixNetPrice] [MONEY] NULL,
[PurUOM] [NVARCHAR](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ZoneID] [NVARCHAR](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LockedBy] [NVARCHAR](36) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LockTS] [DATETIME] NULL)
ON [PRIMARY]
GO
ALTER TABLE [dbo].[GICSPFMatrixDetail]
ADD CONSTRAINT [PK_Clus_GICSPFMatrixDetail_CoDivDepMatrixDetailID] PRIMARY KEY CLUSTERED ( [CompanyID] ASC,[DivisionID] ASC,[DepartmentID] ASC,[MatrixDetailID] ASC ) WITH ( PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF,IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_GICSPFMatrixDetail_CategoryID] ON [dbo].[GICSPFMatrixDetail] (
[CategoryID] ASC)
ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_GICSPFMatrixDetail_CoDivDepMatrixHeaderId] ON [dbo].[GICSPFMatrixDetail] (
[CompanyID] ASC,
[DivisionID] ASC,
[DepartmentID] ASC,
[MatrixHeaderID] ASC)
ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_GICSPFMatrixDetail_MatrixLevel] ON [dbo].[GICSPFMatrixDetail] (
[MatrixLevel] ASC)
ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_GICSPFMatrixDetail_MfrID] ON [dbo].[GICSPFMatrixDetail] (
[MfrID] ASC)
ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_GICSPFMatrixDetail_ProductID] ON [dbo].[GICSPFMatrixDetail] (
[ProductID] ASC)
ON [PRIMARY]
GO
insert into GICSPFMatrixDetail
select 'Guillevin International Co.','9820','2028',1,4,486,NULL,NULL,86578,NULL,'1', .90,NULL,NULL,'1',NULL,NULL
UNION ALL
select 'Guillevin International Co.','9820','2028',1,4,486,NULL,NULL,NULL,'QO115%','2', .99,NULL,NULL,'1',NULL,NULL
UNION ALL
select 'Guillevin International Co.','9820','2028',1,3,486,283,NULL,NULL,NULL,'2', .85,NULL,NULL,'1',NULL,NULL
UNION ALL
select 'Guillevin International Co.','9820','2028',1,3,486,NULL,'Fire%',NULL,NULL,'3', .80,NULL,NULL,'1',NULL,NULL
UNION ALL
select 'Guillevin International Co.','9820','2028',1,3,486,NULL,NULL,NULL,NULL,'3', .80,NULL,NULL,'1',NULL,NULL
UNION ALL
select 'Guillevin International Co.','9820','2028',1,2,486,NULL,NULL,NULL,NULL,'D', .75,NULL,NULL,'1',NULL,NULL
UNION ALL
select 'Guillevin International Co.','9820','2028',1,1,NULL,NULL,NULL,NULL,NULL,'L', .60,NULL,NULL,'1',NULL,NULL
select * from GICSPFMatrixDetail
It will not permit you to run the function, but it might help understand.
I built a few matrices instructions to you can all see how it works, and explain a few things.
Like I said earlier, there has to be some validation, it can be a level (4) to (1), and in these levels, some constraints come before others.
Like, for a level 4, if a products is selected (ProductID), it has to be selected first.
If a wildcard is saved ('Qo115%') directly in the "ProductCatalog" field, then that comes second, etc.
And the ZONE comes in the validation too, but let's make it simple, and keep it with the productID or productCatalog wildcard.
You see, in the first code I posted, there is this huge validation with a zillion of left outer joins, giving different ID's to the validation, well I do every validation, for a product, and return the most PRECISE instruction. This is what I found was the best for my requirements, is there any other way to do that (I don't think that a million If Else will help.. maybe I'm Wrong)
Thanks,
Cheers,
J-F
-
Try this, tell me if the function works any better this way than the way it's currently built. I can't test this, since I don't have all the table definitions, etc., but I think it'll work.
IF EXISTS ( SELECT*
FROM
sys.objects
WHERE
object_id = OBJECT_ID(N'[dbo].[fnGICSPFCalcReplCost]')
AND TYPE IN (N'FN', N'IF', N'TF', N'FS', N'FT') )
DROP FUNCTION [dbo].[fnGICSPFCalcReplCost]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fnGICSPFCalcReplCost] (
@ProductID INT,
@ZoneID NVARCHAR(10),
@VendorMatrixType NVARCHAR(3),
@MatrixEffDate DATETIME)
RETURNS TABLE
AS
return
(
with Vendor(CompanyID, DivisionID, DepartmentID, VendorID)
as (SELECT
p.CompanyID,
p.DivisionID,
p.DepartmentID,
vm.VendorID
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
WHERE
p.ProductID = @ProductID) ,
Matrix(CompanyID, DivisionID, DepartmentID, MatrixDetailID, MatrixDiscMultOp, MatrixHeaderID, MatrixLevel, MfrId, CategoryID, CategoryName, ProductID, ProductCatalog, MatrixPriceBaseColumn, MatrixDiscMult, MatrixNetPrice, PurUOM, ZoneID)
as (SELECT
md.CompanyID,
md.DivisionID,
md.DepartmentID,
MatrixDetailID,
MatrixDiscMultOp,
md.MatrixHeaderID,
MatrixLevel,
MfrId,
CategoryID,
CategoryName,
ProductID,
PRoductCatalog,
MatrixPriceBaseColumn,
MatrixDiscMult,
MatrixNetPrice,
PurUOM,
ZoneID
FROM
Enterprise..GICVendorMatrixDetail md
INNER JOIN Enterprise..GICVendorMatrixHeader mh
ON md.CompanyID = mh.CompanyID
AND md.DivisionID = mh.DivisionID
AND md.DepartmentID = mh.DepartmentID
AND md.MatrixHeaderID = mh.MatrixHeaderID
inner join vendor
on md.companyid = vendor.companyid
and md.DivisionID = vendor.DivisionID
AND md.DepartmentID = vendor.DepartmentID
where
(@VendorMatrixType = 'Sls'
and md.MAtrixHeaderID = Enterprise.dbo.fnGICMatrixFindActiveByVendor(VendorID, 1--IsSalesMatrix
, @MatrixEffDate)
OR @VendorMatrixType = 'PC'
and md.MAtrixHeaderID = Enterprise.dbo.fnGICMatrixFindActiveByVendor(VendorID, 0,-- isSalesMatrix
@MatrixEffDate)
OR @VendorMatrixType = 'SPF'
and md.MAtrixHeaderID = GICSPF.dbo.fnGICSPFMatrixFindActiveByVendor(VendorID, @MatrixEffDate))) ,
Prd(VendorID, MFRID, MFRCode, ProductID, SPFCatalog, CategoryID, CategoryName, ZoneID, ListPrice, Col1, Col2, Col3, DistrCost, SPFPurUOM)
as (SELECT
vm.VendorID,
vm.MFRID,
m.MFRCode,
p.ProductID,
p.SPFCatalog,
cd.CategoryID,
cd.CategoryName,
mz.ZoneID,
pz.ListPrice,
pz.Col1,
pz.Col2,
pz.Col3,
pz.DistrCost,
SPFPurUOM
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 GICSPFMfrZone mz
ON vm.CompanyID = mz.CompanyID
AND vm.DivisionID = mz.DivisionID
AND vm.DepartmentID = mz.DepartmentID
AND vm.MfrVendorID = mz.MfrVendorID
INNER JOIN GICSPFProductToZone pz
ON p.CompanyID = pz.CompanyID
AND p.DivisionID = pz.DivisionID
AND p.DepartmentID = pz.DepartmentID
AND mz.MfrZoneID = pz.MfrZoneID
AND p.ProductID = pz.ProductID
INNER JOIN GICSPFMfr m
ON vm.MfrID = m.MfrID
LEFT JOIN GICSPFVendorCategoryDetail cd
ON p.CompanyID = cd.CompanyID
AND p.DivisionID = cd.DivisionID
AND p.DepartmentID = cd.DepartmentID
AND p.vndCategory = cd.CategoryID
WHERE
p.ProductID = @ProductID
AND mz.ZoneID = @ZoneID) ,
Final
as (SELECT
matrixlevel * 10
+ (case when isnull(md.ZoneID, '') = ''
and prd.SpfCatalog LIKE REPLACE(md.ProductCatalog,
'*', '%') then 1
when isnull(md.ZoneID, '') = '' then 2
when md.ZoneID = Prd.ZoneID
and prd.SpfCatalog LIKE REPLACE(md.ProductCatalog,
'*', '%') then 3
when md.ZoneID = Prd.ZoneID then 4
end) as Ord,
MatrixDetailID,
MatrixDiscMultOp,
md.MatrixNetPrice,
Prd.ListPrice,
Prd.Col1,
Prd.Col2,
Prd.Col3,
md.MatrixDiscMult,
md.MfrID AS MatMfrID,
md.CategoryID AS MatCategoryID,
md.CategoryName AS MatCategoryName,
md.ProductID AS MatProductID,
md.ProductCatalog AS MatSPFCatalog,
md.MatrixPriceBaseColumn,
md.PurUOM,
md.ZoneID AS MatZoneID,
Prd.VendorID,
Prd.ProductID,
Prd.SpfCatalog,
Prd.CategoryName,
Prd.ZoneID,
Prd.DistrCost,
Prd.SPFPurUOM
FROM
Matrix md
LEFT JOIN Prd
ON md.MfrID = Prd.MFRID
AND md.ProductID = Prd.ProductID)
SELECT TOP 1
ProductID,
CASE WHEN MatrixNetPrice IS NOT NULL
AND MatProductID = ProductID THEN MatrixNetPrice
WHEN MatrixDiscMultOp = 'Mult'
AND MatrixPriceBaseColumn = 'L'
THEN ListPrice * MatrixDiscMult
WHEN MatrixDiscMultOp = 'Mult'
AND MatrixPriceBaseColumn = '1' THEN Col1 * MatrixDiscMult
WHEN MatrixDiscMultOp = 'Mult'
AND MatrixPriceBaseColumn = '2' THEN Col2 * MatrixDiscMult
WHEN MatrixDiscMultOp = 'Mult'
AND MatrixPriceBaseColumn = '3' THEN Col3 * MatrixDiscMult
WHEN MatrixDiscMultOp = 'Mult'
AND MatrixPriceBaseColumn = 'D'
THEN DistrCost * MatrixDiscMult
WHEN MatrixDiscMultOp = 'Disc'
AND MatrixPriceBaseColumn = 'L'
THEN ListPrice + (ListPrice * MatrixDiscMult / 100)
WHEN MatrixDiscMultOp = 'Disc'
AND MatrixPriceBaseColumn = '1'
THEN Col1 + (Col1 * MatrixDiscMult / 100)
WHEN MatrixDiscMultOp = 'Disc'
AND MatrixPriceBaseColumn = '2'
THEN Col2 + (Col2 * MatrixDiscMult / 100)
WHEN MatrixDiscMultOp = 'Disc'
AND MatrixPriceBaseColumn = '3'
THEN Col3 + (Col3 * MatrixDiscMult / 100)
WHEN MatrixDiscMultOp = 'Disc'
AND MatrixPriceBaseColumn = 'D'
THEN DistrCost + (DistrCost * MatrixDiscMult / 100)
END AS COST,
CASE WHEN MatrixNetPrice IS NOT NULL THEN PurUOM --MatrixUOM
ELSE SPFPurUOM --ProductUOM
END AS CostUOM,
MatrixDetailID
FROM
Final
ORDER BY
ord DESC) ;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
Thanks GSquared for taking all that time for building something that you cannot even test.
It compiles perfectly, but some minor changes (like duplicate column names), but that's not an issue.
What I need to do, is change the final part.
You changed my left joins to a simpler one, but it does not cover every aspect of the scenarios.
You tell me if there is another way, but will I have to rewrite all my left joins to cover these scenarios?
Matrix level = 4 -- Product Match
44 - > a product ID matches, and the ZoneID also matches
43 -> a productID matches, and the zoneID is null (applies to all zones)
42 -> a ProductCatalog (wildcard) matches and the zoneID also matches
41 -> a ProductCatalog (wildcard) matches and the zoneID is null (applies to all zones)
Matrix Level = 3 -- category Match
34 -> a CategoryID matches, and the zoneID also matches
33 -> a CategoryID matches, but the zoneID is null (applies to all zones)
32 -> a CategoryName (wildcard) matches, and the zoneID also matches
31 -> a CategoryName (wildcard) matches, and the zoneID is null (applies to all zones)
Matrix Level = 2 -- Manufacturer Match
22 -> MfrID matches, and the zoneID matches (Perfect match, no nulls)
21 -> MfrId matches, but the zoneID is null (applies to all zones)
Matrix Level = 1 -- General price
12 -> ZoneID matches (no nulls)
11 -> ZoneID is null (Applies to all zones).
I couldn't get a way to get all these conditions working in a single join, can you think of one?
By the way, a lot of thanks for your help, I think it will be a lot better once I can get this inline function working, I've heard it's a lot faster, but I couldn't think of a way to make it Inline.
Thanks in advance,
Cheers,
J-F
-
The conditions you're asking about are the ones I included in the Case statement. Those tests will accomplish the same thing as all the various left joins and unions that you had. They will need to be reviewed to make sure you're getting the right data, but they should work as written.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
Ok, I changed the code to fit the requirements as per the matrix levels:
IF EXISTS ( SELECT*
FROM
sys.objects
WHERE
object_id = OBJECT_ID(N'[dbo].[fnGICSPFCalcReplCost]')
AND TYPE IN (N'FN', N'IF', N'TF', N'FS', N'FT') )
DROP FUNCTION [dbo].[fnGICSPFCalcReplCost]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fnGICSPFCalcReplCost] (
@ProductID INT,
@ZoneID NVARCHAR(10),
@VendorMatrixType NVARCHAR(3),
@MatrixEffDate DATETIME)
RETURNS TABLE
AS
return
(
with Vendor(CompanyID, DivisionID, DepartmentID, VendorID)
as (SELECT
p.CompanyID,
p.DivisionID,
p.DepartmentID,
vm.VendorID
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
WHERE
p.ProductID = @ProductID) ,
Matrix(CompanyID, DivisionID, DepartmentID, MatrixDetailID, MatrixDiscMultOp, MatrixHeaderID, MatrixLevel, MfrId, CategoryID, CategoryName, ProductID, ProductCatalog, MatrixPriceBaseColumn, MatrixDiscMult, MatrixNetPrice, PurUOM, ZoneID)
as (SELECT
md.CompanyID,
md.DivisionID,
md.DepartmentID,
MatrixDetailID,
MatrixDiscMultOp,
md.MatrixHeaderID,
MatrixLevel,
MfrId,
CategoryID,
CategoryName,
ProductID,
PRoductCatalog,
MatrixPriceBaseColumn,
MatrixDiscMult,
MatrixNetPrice,
PurUOM,
ZoneID
FROM
Enterprise..GICVendorMatrixDetail md
INNER JOIN Enterprise..GICVendorMatrixHeader mh
ON md.CompanyID = mh.CompanyID
AND md.DivisionID = mh.DivisionID
AND md.DepartmentID = mh.DepartmentID
AND md.MatrixHeaderID = mh.MatrixHeaderID
inner join vendor
on md.companyid = vendor.companyid
and md.DivisionID = vendor.DivisionID
AND md.DepartmentID = vendor.DepartmentID
where
(@VendorMatrixType = 'Sls'
and md.MAtrixHeaderID = Enterprise.dbo.fnGICMatrixFindActiveByVendor(vendor.VendorID, 1---IsSalesMatrix
, @MatrixEffDate)
OR @VendorMatrixType = 'PC'
and md.MAtrixHeaderID = Enterprise.dbo.fnGICMatrixFindActiveByVendor(vendor.VendorID, 0,--- isSalesMatrix
@MatrixEffDate)
OR @VendorMatrixType = 'SPF'
and md.MAtrixHeaderID = GICSPF.dbo.fnGICSPFMatrixFindActiveByVendor(vendor.VendorID, @MatrixEffDate))) ,
Prd(VendorID, MFRID, MFRCode, ProductID, SPFCatalog, CategoryID, CategoryName, ZoneID, ListPrice, Col1, Col2, Col3, DistrCost, SPFPurUOM)
as (SELECT
vm.VendorID,
vm.MFRID,
m.MFRCode,
p.ProductID,
p.SPFCatalog,
cd.CategoryID,
cd.CategoryName,
mz.ZoneID,
pz.ListPrice,
pz.Col1,
pz.Col2,
pz.Col3,
pz.DistrCost,
SPFPurUOM
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 GICSPFMfrZone mz
ON vm.CompanyID = mz.CompanyID
AND vm.DivisionID = mz.DivisionID
AND vm.DepartmentID = mz.DepartmentID
AND vm.MfrVendorID = mz.MfrVendorID
INNER JOIN GICSPFProductToZone pz
ON p.CompanyID = pz.CompanyID
AND p.DivisionID = pz.DivisionID
AND p.DepartmentID = pz.DepartmentID
AND mz.MfrZoneID = pz.MfrZoneID
AND p.ProductID = pz.ProductID
INNER JOIN GICSPFMfr m
ON vm.MfrID = m.MfrID
LEFT JOIN GICSPFVendorCategoryDetail cd
ON p.CompanyID = cd.CompanyID
AND p.DivisionID = cd.DivisionID
AND p.DepartmentID = cd.DepartmentID
AND p.vndCategory = cd.CategoryID
WHERE
p.ProductID = @ProductID
AND mz.ZoneID = @ZoneID) ,
Final
as (SELECT
matrixlevel * 10
+ (case when (prd.ProductID = md.ProductID and prd.ZoneID = md.ZoneID and MatrixLevel = 4)
or (prd.CategoryID = md.CategoryID and prd.ZoneID = md.ZoneID and MatrixLevel = 3) then 4
when (prd.ProductID = md.ProductID and md.ZoneID is null and MatrixLevel = 4)
or (prd.CategoryID = md.CategoryID and md.ZoneId is null and MatrixLevel = 3) then 3
when (prd.SpfCatalog LIKE REPLACE(md.ProductCatalog, '*','%') AND md.ZoneID = prd.ZoneID and MatrixLevel = 4)
or (prd.CategoryName LIKE REPLACE(md.CategoryName,'*','%') and prd.ZoneID = md.ZoneID and MatrixLevel = 3)
or (prd.MfrID = md.MfrID and prd.ZoneID = md.ZoneID and MatrixLevel = 2)
or (prd.ZoneID = md.ZoneID and MatrixLevel = 1) then 2
when (prd.SpfCatalog LIKE REPLACE(md.ProductCatalog, '*','%') AND md.ZoneID is null AND MatrixLevel = 4)
or (prd.CategoryName LIKE REPLACE(md.CategoryName,'*','%') and md.ZoneID is null and MatrixLevel = 3)
or (prd.MfrID = md.MfrID and md.ZoneID is null and MatrixLevel = 2)
or (md.ZoneID is null and MatrixLevel = 1) then 1
end) as Ord,
MatrixDetailID,
MatrixDiscMultOp,
md.MatrixNetPrice,
Prd.ListPrice,
Prd.Col1,
Prd.Col2,
Prd.Col3,
md.MatrixDiscMult,
md.MfrID AS MatMfrID,
md.CategoryID AS MatCategoryID,
md.CategoryName AS MatCategoryName,
md.ProductID AS MatProductID,
md.ProductCatalog AS MatSPFCatalog,
md.MatrixPriceBaseColumn,
md.PurUOM,
md.ZoneID AS MatZoneID,
Prd.VendorID,
Prd.ProductID,
Prd.SpfCatalog,
Prd.CategoryName,
Prd.ZoneID,
Prd.DistrCost,
Prd.SPFPurUOM
FROM
Matrix md
cross join Prd
/*ON md.MfrID = Prd.MFRID
AND md.ProductID = Prd.ProductID*/)
SELECT TOP 1 -- 100000
ProductID,
CASE WHEN MatrixNetPrice IS NOT NULL
AND MatProductID = ProductID THEN MatrixNetPrice
WHEN MatrixDiscMultOp = 'Mult'
AND MatrixPriceBaseColumn = 'L'
THEN ListPrice * MatrixDiscMult
WHEN MatrixDiscMultOp = 'Mult'
AND MatrixPriceBaseColumn = '1' THEN Col1 * MatrixDiscMult
WHEN MatrixDiscMultOp = 'Mult'
AND MatrixPriceBaseColumn = '2' THEN Col2 * MatrixDiscMult
WHEN MatrixDiscMultOp = 'Mult'
AND MatrixPriceBaseColumn = '3' THEN Col3 * MatrixDiscMult
WHEN MatrixDiscMultOp = 'Mult'
AND MatrixPriceBaseColumn = 'D'
THEN DistrCost * MatrixDiscMult
WHEN MatrixDiscMultOp = 'Disc'
AND MatrixPriceBaseColumn = 'L'
THEN ListPrice + (ListPrice * MatrixDiscMult / 100)
WHEN MatrixDiscMultOp = 'Disc'
AND MatrixPriceBaseColumn = '1'
THEN Col1 + (Col1 * MatrixDiscMult / 100)
WHEN MatrixDiscMultOp = 'Disc'
AND MatrixPriceBaseColumn = '2'
THEN Col2 + (Col2 * MatrixDiscMult / 100)
WHEN MatrixDiscMultOp = 'Disc'
AND MatrixPriceBaseColumn = '3'
THEN Col3 + (Col3 * MatrixDiscMult / 100)
WHEN MatrixDiscMultOp = 'Disc'
AND MatrixPriceBaseColumn = 'D'
THEN DistrCost + (DistrCost * MatrixDiscMult / 100)
END AS COST,
CASE WHEN MatrixNetPrice IS NOT NULL THEN PurUOM ---MatrixUOM
ELSE SPFPurUOM ---ProductUOM
END AS CostUOM,
MatrixDetailID,
ord
FROM
Final
ORDER BY
ord DESC) ;
GO
select * from dbo.[fnGICSPFCalcReplCost] (
278616, -- ProductID
'0', --ZoneID
'PC',
getDate())
It works perfectly, for every scenarios, but, it's not performant, and that is because I had to do a cross join between Prd table and Matrix table. I think I don't have the choice since i'm trying different combinations in the case statements, and I can think of no filters between these two tables.
So, for 1 product, that ends out being 20k rows returned, when i put the top 1 away, that is because we have 20k detail lines in that test matrix. That looks like a bit huge for me, trying to think of this when I will query several products...
EDIT : Attached Execution plan for more clarity...
Cheers,
J-F
-
Why would you do a cross join between those two? So far as I can tell, all the joins in the original query had the same two columns in the join criteria.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
Actually no, they are a lot different.
I added them in the case statements, but as you can see
level 4 match on productID or ProductCatalog
Level 3 match on CategoryID or categoryName
level 2 match on MfrID
level 1 is the final match, so all matches
and every level match on zone being either exact, or null.
Anywayz, I added a where statement, at the end
Where ord is not nullin the final select, and it takes me about 150 ms to calculate 1 product, which is great, since I just had a 2000 ms execution time. The where saved my day! 😉
I got one final question, I heard people talking about inline functions, and.. not inline functions, is this function inline now? is that why it's so fast compared to the one I had?
Thanks GSquared,
Cheers,
J-F
-
Yes. This is what's called an "Inline Table Value Function". The prior version was a "Multi-Select Table Value Function". Both are types of "User Defined Functions".
The difference is that the inline ones can be built as a single select statement, without using table variables, flow-control, etc. That inherently makes them faster, because SQL Server can use indexes and stats to estimate how many rows they will work on, instead of having to assume they will always work on 1 row. The multi-select ones always assume they will work on 1 row, which can kill performance if they actually work on more than 1.
Another advantage of this version is that Union statements are expensive. Union All is less expensive than Union, but either one takes a lot of extra processing, compared to doing it all in the one select statement.
Those two reasons are why it's faster.
Now, on the one where you want to select all the products for a given vendor, you'll be better off setting something up that works from VendorID instead of from ProductID, and works on the full set of that data, but I can't help much on that without more data on table structures, sample data, etc.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply