Calling all Gurus - Evaluating Data-based Formulas

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Calling all Gurus - Evaluating Data-based Formulas

Author

Topic

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2004-06-09 : 18:14:15

Hello all,

I've recently been introduced to a very complex problem that requires a user-based formula located within a data element to be evaluated and used as part of key to a corresponding table.

Some background first. Manufacturers pay rebates on products based on how the product performs within a given market basket. That is to say, if product A competes with B & C, the manufacturer is willing to pay a higher rebate per product if that product had a higher market share than their competitors. However, the calculations to determine this are quite diverse and depend greatly on external factors as well (such as the national market share of their product in relation to the market share generated at the client).

I spent hours researching the best methods to solve this problem and have come upon the following. Please review and test my solution, and provide any performance or usability feedback you can muster. I appreciate all responses.

CREATE TABLE dbo.tblRebatePerformanceVariableType (
[VariableTypePK] tinyint NOT NULL ,
[Description] varchar (50) NOT NULL ,
[VariableCode] varchar (10) NOT NULL ,
[VariableDefaultAmount] decimal(9, 6) NOT NULL,
CONSTRAINT PK_tblRebatePerformanceVariableType PRIMARY KEY CLUSTERED
(
[VariableTypePK]
)
) ON [PRIMARY];
GO

CREATE TABLE dbo.tblRebatePerformance (
[PerformancePK] int NOT NULL ,
[RebateFK] uniqueidentifier NOT NULL,
[PerformanceFormula] varchar (200) NOT NULL ,
[PerformanceCalculation] decimal (9,6) NULL,
CONSTRAINT PK_tblRebatePerformance PRIMARY KEY CLUSTERED
(
[PerformancePK]
)
) ON [PRIMARY];
GO

CREATE TABLE dbo.tblRebatePerformanceVariable (
[RebateFK] uniqueidentifier NOT NULL ,
[VariableTypeFK] tinyint NOT NULL ,
[VariableAmount] decimal(9, 6) NOT NULL DEFAULT (0),
CONSTRAINT PK_tblRebatePerformanceVariable PRIMARY KEY CLUSTERED
(
[RebateFK],
[VariableTypeFK]
)
) ON [PRIMARY];
GO
CREATE TABLE [dbo].[tblRebatePerformanceTier] (
[PerformanceTierPK] int NOT NULL identity(1,1),
[PerformanceFK] int NOT NULL ,
[TierBegin] decimal(9, 6) NOT NULL ,
[TierEnd] decimal(9, 6) NOT NULL ,
[TierValue] decimal(9,6) NOT NULL ,
CONSTRAINT PK_tblRebateTier PRIMARY KEY CLUSTERED
(
[PerformanceTierPK]
)
) ON [PRIMARY]
GO

INSERT [dbo].[tblRebatePerformanceVariableType] VALUES(1,'Variable 1','[VAR1]',.000000);
INSERT [dbo].[tblRebatePerformanceVariableType] VALUES(2,'Variable 2','[VAR2]',.000000);
INSERT [dbo].[tblRebatePerformanceVariableType] VALUES(3,'Variable 3','[VAR3]',.000000);

INSERT [dbo].[tblRebatePerformance] VALUES(1,'{127DFF6C-BA9D-4453-B417-CD57A7FC768C}','[VAR1]*[VAR2]',NULL);
INSERT [dbo].[tblRebatePerformance] VALUES(2,'{127DFF6C-BA9D-4453-B417-CD57A7FC768C}','0',NULL);
INSERT [dbo].[tblRebatePerformance] VALUES(3,'{127DFF6C-BA9D-4453-B417-CD57A7FC768C}','([VAR1]-[VAR3])/100+5',NULL);
INSERT [dbo].[tblRebatePerformance] VALUES(4,'{B82E3845-B4CB-4D94-BC68-B82A31025324}','[VAR4]+80',NULL);
INSERT [dbo].[tblRebatePerformance] VALUES(5,'{B82E3845-B4CB-4D94-BC68-B82A31025324}','1.5*[VAR3]',NULL);
INSERT [dbo].[tblRebatePerformance] VALUES(6,'{B82E3845-B4CB-4D94-BC68-B82A31025324}','[VAR2]-5',NULL);
INSERT [dbo].[tblRebatePerformance] VALUES(7,'{D044AFA1-9BD4-4852-B448-2C0A893C5600}','[VAR3]*4)',NULL);
INSERT [dbo].[tblRebatePerformance] VALUES(8,'{D044AFA1-9BD4-4852-B448-2C0A893C5600}','0',NULL);

INSERT [dbo].[tblRebatePerformanceVariable] VALUES('{127DFF6C-BA9D-4453-B417-CD57A7FC768C}',1,13.05);
INSERT [dbo].[tblRebatePerformanceVariable] VALUES('{127DFF6C-BA9D-4453-B417-CD57A7FC768C}',2,.005);
INSERT [dbo].[tblRebatePerformanceVariable] VALUES('{127DFF6C-BA9D-4453-B417-CD57A7FC768C}',3,12.745);
INSERT [dbo].[tblRebatePerformanceVariable] VALUES('{B82E3845-B4CB-4D94-BC68-B82A31025324}',2,4);
INSERT [dbo].[tblRebatePerformanceVariable] VALUES('{B82E3845-B4CB-4D94-BC68-B82A31025324}',3,4);

INSERT [dbo].[tblRebatePerformanceTier] (PerformanceFK,TierBegin,TierEnd,TierValue) VALUES(1,0,0.05,1);
INSERT [dbo].[tblRebatePerformanceTier] (PerformanceFK,TierBegin,TierEnd,TierValue) VALUES(1,0.05,.10,2);
INSERT [dbo].[tblRebatePerformanceTier] (PerformanceFK,TierBegin,TierEnd,TierValue) VALUES(1,0.10,.15,3);
INSERT [dbo].[tblRebatePerformanceTier] (PerformanceFK,TierBegin,TierEnd,TierValue) VALUES(1,0.15,1,4);
INSERT [dbo].[tblRebatePerformanceTier] (PerformanceFK,TierBegin,TierEnd,TierValue) VALUES(2,0,.00,1);
INSERT [dbo].[tblRebatePerformanceTier] (PerformanceFK,TierBegin,TierEnd,TierValue) VALUES(3,0,5,1);
INSERT [dbo].[tblRebatePerformanceTier] (PerformanceFK,TierBegin,TierEnd,TierValue) VALUES(3,5,10,2);
INSERT [dbo].[tblRebatePerformanceTier] (PerformanceFK,TierBegin,TierEnd,TierValue) VALUES(4,0,10,1);
INSERT [dbo].[tblRebatePerformanceTier] (PerformanceFK,TierBegin,TierEnd,TierValue) VALUES(4,10,20,2);
INSERT [dbo].[tblRebatePerformanceTier] (PerformanceFK,TierBegin,TierEnd,TierValue) VALUES(5,0,6,1);
INSERT [dbo].[tblRebatePerformanceTier] (PerformanceFK,TierBegin,TierEnd,TierValue) VALUES(5,6,20,2);
INSERT [dbo].[tblRebatePerformanceTier] (PerformanceFK,TierBegin,TierEnd,TierValue) VALUES(6,0,10,1);
INSERT [dbo].[tblRebatePerformanceTier] (PerformanceFK,TierBegin,TierEnd,TierValue) VALUES(6,10,20,2);
INSERT [dbo].[tblRebatePerformanceTier] (PerformanceFK,TierBegin,TierEnd,TierValue) VALUES(7,0,10,1);
INSERT [dbo].[tblRebatePerformanceTier] (PerformanceFK,TierBegin,TierEnd,TierValue) VALUES(7,10,20,2);
INSERT [dbo].[tblRebatePerformanceTier] (PerformanceFK,TierBegin,TierEnd,TierValue) VALUES(8,0,10,1);
INSERT [dbo].[tblRebatePerformanceTier] (PerformanceFK,TierBegin,TierEnd,TierValue) VALUES(8,10,20,2);

GO

DECLARE @PK int;
DECLARE @Formula varchar(200);

SELECT p.PerformancePK,
p.PerformanceFormula,
cast(null as varchar(200)) PerformanceCalculation,
COALESCE(vt.VariableCode,'') VariableCode,
COALESCE(v.VariableAmount,vt.VariableDefaultAmount,0) VariableAmount
into #tblPerformanceCalculation
from [dbo].[tblRebatePerformance] p
LEFT JOIN [dbo].[tblRebatePerformanceVariableType] vt
ON CHARINDEX(vt.VariableCode,PerformanceFormula,1) > 0
LEFT JOIN [dbo].[tblRebatePerformanceVariable] v
ON v.VariableTypeFK = vt.VariableTypePK
AND v.RebateFK = p.RebateFK
ORDER BY p.PerformancePK;

SET @PK = -1;

UPDATE c
SET @Formula = CASE
WHEN @PK <> c.PerformancePK THEN
REPLACE(c.PerformanceFormula,c.VariableCode,c.VariableAmount)
ELSE
REPLACE(@Formula,c.VariableCode,c.VariableAmount)
END,
PerformanceCalculation = @Formula,
@PK = c.PerformancePK
FROM #tblPerformanceCalculation c;

DECLARE @PerformancePK int;
DECLARE @EvalString varchar(200);
DECLARE @stmt nvarchar(217)
DECLARE @param nvarchar(30);
DECLARE @Eval decimal(9,6);
SET @Param = '@StmResult decimal(9,6) out';

DECLARE PerformanceCursor CURSOR
FOR
SELECT p.PerformancePK,
COALESCE(c.PerformanceCalculation,'0')
from dbo.[tblRebatePerformance] p
LEFT JOIN #tblPerformanceCalculation c
ON c.PerformancePK = p.PerformancePK
AND CHARINDEX('[',c.PerformanceCalculation,0) = 0;
OPEN PerformanceCursor
FETCH NEXT FROM PerformanceCursor
INTO @PerformancePK,
@EvalString
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @Eval = NULL;
SET @stmt='SET @StmResult = ' + @EvalString
EXEC sp_executesql @stmt, @Param, @Eval OUT

UPDATE p
SET PerformanceCalculation = @Eval
FROM [dbo].[tblRebatePerformance] p
WHERE p.PerformancePK = @PerformancePK;

FETCH NEXT FROM PerformanceCursor
INTO @PerformancePK,
@EvalString;
END

CLOSE PerformanceCursor;
DEALLOCATE PerformanceCursor;

DROP TABLE #tblPerformanceCalculation;
GO

SELECT *
FROM [dbo].[tblRebatePerformance] p
LEFT JOIN [dbo].[tblRebatePerformanceTier] t
ON p.PerformancePK = t.PerformanceFK
AND p.PerformanceCalculation
BETWEEN t.TierBegin
AND t.TierEnd-.000001
GO

DROP TABLE [dbo].[tblRebatePerformanceVariableType];
DROP TABLE [dbo].[tblRebatePerformanceVariable];
DROP TABLE [dbo].[tblRebatePerformanceTier];
DROP TABLE [dbo].[tblRebatePerformance];
GO

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2004-06-09 : 18:23:25

Just in case anyone was wondering, I specifically included bad/suspect data to simulate poor upfront validation. I have a formula that does not parse correctly, and a variable that doesn't exist, as well as a tier that has the same begin and end ranges. The application will not need to act on Formulas that do not match tiers, but I included the left join at the end so you can see what the results are.

Additionally, I have not implemented contraints in the sample, but will in the final product. I even plan to evalute the formula (using defaults) in a trigger on tblRebatePerformance to verify its integrity.

Also, please comment on my naming (fields & tables). I am seeking any and all criticism.

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-06-09 : 18:44:09

Don't put tbl as a part of your Table name, and find a Set-Based way to get rid of that cursor. I've not looked at the code hard enough to solve that one, but I'm sure someone can.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-09 : 22:57:10

This isn't a complete solution or anything.... but it should help you move away from those dreaded cursors

quote:

Create Table ##tmpTable
(
eq nvarchar(100),
value decimal(9,6)
)
Insert Into ##tmpTable
Select eq = '1.5+2.5', value = null
Union all Select eq = '2*3', value = null

Select * From ##tmpTable
Select 'update #tmpTable Set Value = ' + eq + ' from ##tmpTable where eq = ''' + eq + '''' From ##tmpTable

EXEC master..xp_execresultset N'Select ''update ##tmpTable Set Value = '' + eq + '' Where eq = '''''' + eq + '''''''' From ##tmpTable', N'sbo'

Select * From ##tmpTable

Drop Table ##tmpTable

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-06-09 : 23:15:26

I hate to rain on your parade SeventhNight, but have you seen the underlying code for xp_execresultset? It calls the underlying proc sp_execresultset.. Try not to gag when you see it...

DavidM

"Always pre-heat the oven"

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-09 : 23:46:02

Well I originally start messing with the sp_execresultset and I wasn't getting anywhere. I then remembered skimming over an article about deterministic/non-deterministic UDFs (for concat or other custom functions...)

It was based around the idea that you can call xps or functions from functions.

So i ended where I did... where could I see the code for xp_execresultset??? I'll try not to gag

I still think it would be better than a cursor... sigh... off to bed...

Corey

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2004-06-10 : 10:31:08

Believe me, I will use a set-based solution instead of that cursor any day. However, it was my belief that I could not solve this problem in a set-based way. Seventhnight has provided a method that may work, however I have my doubts on whether or not it will perform better. I will give it a shot and throw it up against my approach.

Thanks for the advice. As far as table names are concerned, the DB I inherited uses an object prefix such as 'tbl' for tables and 'vw' for views. I don't personally subscribe to hungarian notation, however I need to maintain consistency within this realm.

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2004-06-10 : 11:48:22

I have just tested the approach recommended by Seventhnight against my cursor approach using 136 formulas and the cursor performs better (1110 ms vs 846 ms). Aside from that, xp_execresultset is undocumented and I don't even know if it would pass code review.

Thank you Seventhnight and DavidM for taking the time to review this. If anyone else sees a way to improve the performance of this solution, please don't hesitate to post.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-10 : 13:43:50

Out of curiosity... would you post your test code. I'd like to experiment with it a bit more....

If you don't have it anymore - no problem

Sorry it wasn't the answer...

Corey

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2004-06-10 : 13:54:24

[Edited by lazerath]
Oops! Sorry for the invalid code. The code below now works correctly, but please note that it will crash completely due to invalid formulas (PerformancePK 7 has '[VAR3]*4)', which will manifest as Server: Msg 170, Level 15, State 1, Line 20
Line 20: Incorrect syntax near ')')

DECLARE @DB_Name nvarchar(400)
SELECT @DB_Name=DB_NAME();

EXEC master..xp_execresultset
N'
SELECT
''UPDATE p
SET PerformanceCalculation = '' + COALESCE(c.PerformanceCalculation,''0'') + ''
FROM dbo.tblRebatePerformance p
WHERE p.PerformancePK = '' + CAST(p.PerformancePK as varchar(12)) + ''''
FROM dbo.tblRebatePerformance p
LEFT JOIN #tblPerformanceCalculation c
ON c.PerformancePK = p.PerformancePK
AND CHARINDEX(''['',c.PerformanceCalculation,0) = 0',
@DB_Name;

Replaces the following:

DECLARE @PerformancePK int;
..
..Cursor Stuff!
..
DEALLOCATE PerformanceCursor;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources