Hi Fredrik,
I can't be sure, because I don't have your database schema (your table
definitions etc) but I would say that the following query does what your
function does, except it actually uses indexes, unlike the function you
posted that makes heavy use of cursors.
SELECT PRODUCT_ID,
CASE @sType
WHEN 'A' THEN PRICE_1
WHEN 'B' THEN PRICE_2
WHEN 'C' THEN COST
ELSE
ISNULL(
(SELECT PRICE_1 FROM INVENTORY_FIXED_PRICE WHERE PRODUCT_ID =
INVENTORY.PRODUCT_ID AND CUSTOMER_TYPE = @sType),
ISNULL(
(
SELECT TOP 1 CASE CALCULATION_TYPE
WHEN 0 THEN INVENTORY.COST / (1 - (UNITS / 100.0001))
WHEN 1 THEN INVENTORY.COST + UNITS
END
FROM CUSTOMER_PRICE_RULES
WHERE RULE_ID <= ISNULL((SELECT MIN(RULE_ID) FROM CUSTOMER_PRICE_RULES
WHERE TYPE = @sType AND EXIT_AFTER_HIT = 1), RULE_ID) AND
NOT ((FIELD = 'GROUP_NAME_1' AND VALUE = INVENTORY.GROUP_NAME_1) OR
(FIELD = 'MANUFACTURE' AND VALUE = INVENTORY.MANUFACTURE) OR (FIELD =
'GROUP_NAME_2' AND VALUE = INVENTORY.GROUP_NAME_2) OR (FIELD =
'GROUP_NAME_3' AND VALUE = INVENTORY.GROUP_NAME_3) OR (FIELD = 'DEFAULT'))
OR (MANUFACTURE IS NOT NULL AND MANUFACTURE != '' AND MANUFACTURE =
INVENTORY.MANUFACTURE)
ORDER BY RULE_ID
),
INVENTORY.PRICE_2
)
)
END AS PRICE
FROM INVENTORY
WHERE PRODUCT_ID = @sProductID
It is critical to understand that the use of databases requires following a
set based approached of manipulating data, not an interative approach that
using cursors provides.
I don't mean to be rude, but I think the logic and design of your
application is messed up along with your data model. However, you can use
the query I am posting to speed your function, but even better than that now
with the logic presented as a table you can actually throw away the UDF's
entirely and use the set based nature of the database to return your pricing
in a single SQL statement.
please note again, your logic is quite complicated and I may have missed
something or got something wrong but I think the query will do what your
function was doing.
hope that helps
cheers,
Luke Amery
"Fredrik Melin" <m...@dacsa.net> wrote in message
news:eA88thlxBHA.1740@tkmsftngp04...
> Here is the UDF if anyone want to take a look at it.
> CREATE FUNCTION [dbo].[fnCalculated_Price] (@sProduct_ID as Varchar(30),
> @sType as VARCHAR(5) )
> RETURNS DECIMAL(18,2) AS
> BEGIN
> DECLARE @sManufacturer as VARCHAR(150);
> DECLARE @sGroup_Name_1 as VARCHAR(50);
> DECLARE @sGroup_Name_2 as VARCHAR(50);
> DECLARE @sGroup_Name_3 as VARCHAR(50);
> DECLARE @sField AS VARCHAR(50);
> DECLARE @sValue AS VARCHAR(150);
> DECLARE @sManuf AS VARCHAR(150);
> DECLARE @nUnits AS DECIMAL(18,2);
> DECLARE @nCalculationType AS TINYINT;
> DECLARE @nCalculatedPrice AS DECIMAL(18,2);
> DECLARE @nTestPrice AS DECIMAL(18,2);
> DECLARE @nExitOnHit as TINYINT;
> DECLARE @nPrice_1 as DECIMAL(18,2)
> DECLARE @nPrice_2 as DECIMAL(18,2)
> DECLARE @nCost as DECIMAL(18,2)
> SELECT @sManufacturer = MANUFACTURE, @sGroup_Name_1 = GROUP_NAME_1,
> @sGroup_Name_2 = GROUP_NAME_2, @sGroup_Name_3 = GROUP_NAME_3, @nCost =
COST,
> @nPrice_2 = PRICE_2, @nPrice_1 = PRICE_1 FROM INVENTORY (NOLOCK),
> INVENTORY_PRICE (NOLOCK) WHERE INVENTORY.PRODUCT_ID =
> INVENTORY_PRICE.PRODUCT_ID AND INVENTORY.PRODUCT_ID = @sProduct_ID;
> IF @sType = 'A'
> BEGIN
> RETURN @nPrice_1
> END
> IF @sType = 'B'
> BEGIN
> RETURN @nPrice_2
> END
> IF @sType = 'C'
> BEGIN
> RETURN @nCost
> END
> SELECT @nTestPrice = PRICE_1 FROM INVENTORY_FIXED_PRICE (NOLOCK) WHERE
> PRODUCT_ID = @sProduct_ID AND CUSTOMER_TYPE = @sType
> If @@ROWCOUNT > 0
> BEGIN
> RETURN @nTestPrice
> END
> SET @nCalculatedPrice = @nPrice_2
> SET @nTestPrice = @nPrice_2
> DECLARE Cur_1 CURSOR
> FOR
> SELECT FIELD, VALUE, MANUFACTURE, CALCULATION_TYPE, UNITS,
EXIT_AFTER_HIT
> FROM CUSTOMER_PRICE_RULES (NOLOCK) WHERE TYPE = @sType ORDER BY RULE_ID
ASC
> OPEN Cur_1
> FETCH NEXT FROM Cur_1 INTO @sField, @sValue, @sManuf,
@nCalculationType,
> @nUnits, @nExitOnHit
> WHILE (@@FETCH_STATUS <> -1)
> BEGIN
> IF (@@FETCH_STATUS <> -2)
> BEGIN
> IF (( @sField = 'GROUP_NAME_1' AND @sValue = @sGroup_Name_1) OR (
> @sField = 'MANUFACTURE' AND @sValue = @sManufacturer ) OR ( @sField =
> 'GROUP_NAME_2' AND @sValue = @sGroup_Name_2) OR ( @sField = 'GROUP_NAME_3'
> AND @sValue = @sGroup_Name_3) OR ( @sField = 'DEFAULT'))
> BEGIN
> IF @sManuf IS NOT NULL AND @sManuf != ''
> BEGIN
> IF @sManuf = @sManufacturer
> BEGIN
> IF @nCalculationType = 0
> BEGIN
> SET @nTestPrice = ( @nCost / (1- (@nUnits /100.0001)))
> END
> IF @nCalculationType = 1
> BEGIN
> SET @nTestPrice = @nCost + @nUnits
> END
> IF @nExitOnHit = 1
> BEGIN
> SET @nCalculatedPrice = @nTestPrice
> RETURN @nCalculatedPrice
> BREAK
> END
> END
> END
> ELSE
> BEGIN
> IF @nCalculationType = 0
> BEGIN
> SET @nTestPrice = ( @nCost / (1- (@nUnits /100.0001)))
> END
> IF @nCalculationType = 1
> BEGIN
> SET @nTestPrice = @nCost + @nUnits
> END
> IF @nExitOnHit = 1
> BEGIN
> SET @nCalculatedPrice = @nTestPrice
> RETURN @nCalculatedPrice
> BREAK
> END
> END
> END
> END
> IF @nTestPrice < @nCalculatedPrice
> BEGIN
> SET @nCalculatedPrice = @nTestPrice
> END
> FETCH NEXT FROM Cur_1 INTO @sField, @sValue, @sManuf,
@nCalculationType,
> @nUnits, @nExitOnHit
> END
> CLOSE Cur_1
> DEALLOCATE Cur_1
> RETURN @nCalculatedPrice;
> END
> "Fredrik Melin" <m...@dacsa.net> wrote in message
> news:uifYvUhxBHA.1648@tkmsftngp04...
> > Well, the execution plan is nothing more to do about what I can see.
> > The problem is that the function does have to do up to 100 subquery's
for
> > each row, so that takes time, I have indexes correctly, so its "speeded"
> up
> > as much as possible.
> > |--Compute
Scalar(DEFINE:([Expr1002]=[dbo].[fnCALCULATED_PRICE](Convert([INVENTORY].[P
R
> > ODUCT_ID]), 'STA')))
> > |--Index
> > Scan(OBJECT:([D1000].[dbo].[INVENTORY].[INVENTORY_WEB_HINT_3]),
> > WHERE:([INVENTORY].[GROUP_NAME_1]= 'BILDSK?RMAR'))
> > "Reinaldo Kibel (MS)" <reina...@online.microsoft.com> wrote in message
> > news:cBInjefxBHA.1876@cpmsftngxa07...
> > > Great discussion.
> > > Can you check Execution Plan? What does it do when 1 only call to
> > > dbo.fnCalcPrice.
> > > dbo.fnCalcPrice uses PRODUC_ID as a parameter. Is this part of the
> index?
> > > The WHERE clause is on PRODUCT_NAME. Is there index on both? Check
from
> > the
> > > query plan that the optimizer is using this index.
> > > Reinaldo Kibel
> > > Microsoft? Corp. - SQL Server Support Engineer
> > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > > Additional support can be obtained at http://support.microsoft.com
> > > Are you secure? For information about the Strategic Technology
> Protection
> > > Program and to order your FREE Security Tool Kit, please visit
> > > http://www.microsoft.com/security.
> > > --------------------
> > > | From: "Ivan Arjentinski" <jar...@hotmail.com_>
> > > | References: <ezxQlyexBHA.1892@tkmsftngp03>
> > > | Subject: Re: Select Speed with slow function?
> > > | Date: Thu, 7 Mar 2002 18:13:51 +0200
> > > | Lines: 65
> > > | X-Priority: 3
> > > | X-MSMail-Priority: Normal
> > > | X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
> > > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
> > > | Message-ID: <O1WYtLfxBHA.1952@tkmsftngp07>
> > > | Newsgroups: microsoft.public.sqlserver.programming
> > > | NNTP-Posting-Host: 212.91.170.206
> > > | Path: cpmsftngxa07!tkmsftngp01!tkmsftngp07
> > > | Xref: cpmsftngxa07 microsoft.public.sqlserver.programming:238681
> > > | X-Tomcat-NG: microsoft.public.sqlserver.programming
> > > |
> > > | Fredrik,
> > > |
> > > | Are you sure that the function is executed 2 times? What happens if
> you
> > > | remove only 1 of the expressions?
> > > |
> > > | If you are sure that the function is executed 2 times, try this:
> > > | SELECT Customer_Price * Tax_Percent AS Customer_Price_With_Tax, *
> > > | FROM (
> > > | SELECT dbo.fnCalcPrice(PRODUC_ID, 'XZY') AS CUSTOMER_PRICE,*
> > > | FROM INVENTORY
> > > | WHERE PRODUCT_NAME = 'TEST%'
> > > | ) X
> > > |
> > > | --
> > > | Ivan Arjentinski
> > > | Please reply to newsgroups. Inclusion of table schemas (CREATE TABLE
> > > | scripts), sample data (INSERT INTO....) and desired result set when
> > asking
> > > | for assistance is highly appreciated
| ----------------------------------------------------------------------
> > > |
> > > | "Fredrik Melin" <m...@dacsa.net> wrote in message
> > > | news:ezxQlyexBHA.1892@tkmsftngp03...
> > > | > Hi,
> > > | >
> > > | > I have this select statement (simplified)
> > > | >
> > > | > SELECT dbo.fnCalcPrice(PRODUC_ID, 'XZY') AS CUSTOMER_PRICE,
> > > | > dbo.fnCalcPrice(PRODUC_ID, 'XZY') * TAX_PERCENT AS
> > > | CUSTOMER_PRICE_WITH_TAX,
> > > | > * FROM INVENTORY WHERE PRODUCT_NAME = 'TEST%'
> > > | >
> > > | > Now, this execution takes 16 seconds.
> > > | > If I remove one call to dbo.fnCalcPrice the execution takes 9
> seconds.
> > > | >
> > > | > So, each dbo.fnCalcPrice (which itself does serveral selects,
> checking
> > > | > prices, depending on type (xyz) etc ) takes about 7 seconds.
> > > | > Speeding up the function it self any more isnt possible.
> > > | >
> > > | > Is there anyway to goaround having it to be running 2 times in the
> SQL
> > > | query
> > > | > with changing the clients? (I could do the tax calc on the client
> but
> > > dont
> > > | > want to) and I need the price with and without tax (tax
... read more »
by