Can a UDF be forced into being considered deterministic?

I have a UDF which uses regular expressions vicariously through a
VBScript.RegExp COM object, and does many replaces with it (which
could probably be optimized to a single regexp, but is much more
readable as-is).
This function operates 100% deterministically, but is extremely slow.
The type of queries it is used for are along the lines of
SELECT MIN(GETBASEMODEL(ModelNumber)), AVG(Weight) FROM CompletedItems
GROUP BY GETBASEMODEL(ModelNumber)
As any information these regular expressions remove from our item
codes has absolutely nothing to do with its weight (i.e. color).
My end goal is to speed up the above query, as it currently takes
about an hour to run when given < 100k rows.
My thought was to create a indexed computed column in the
CompletedItems table: "BaseModel", with a function of
GETBASEMODEL(ModelNumber), though because my function uses the EXEC
command, it is inherently non-deterministic.
Is there a keyword that I can use to force my function to be
deterministic? Or (even better) is my mindset completely wrong?
The functions appear below
---
CREATE FUNCTION dbo.GETBASEMODEL
(
@.model varchar(15)
)
RETURNS VARCHAR(255)
AS
BEGIN
DECLARE @.ret varchar(255)
DECLARE @.RegexObject integer
SET @.RegexObject = dbo.REGEX_CREATE_OBJECT()
SET @.ret = @.model
--trim trailing spaces
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'\s+$','')
--remove trailing dash and color code
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'-..$','')
--remove common trailing color codes in case of no dash
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'(WH|BO|BC|SS)$','')
--remove jetting code
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'/\d*[LRC]{0,2}$','')
--remove trailing "/S" or "/F" or "/E"
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'/[FES]$','')
--BF## -> BF
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'BF\d+','BF')
--BFMD## -> BF
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'BFMD\d*','BF')
--LBF/RBF -> BF
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'[LR]BF','BF')
--TSL/TSR -> TS(L or R)
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'TS[LR]','TS(L or
R)')
--trailing M
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'M$','')
--handing for specific models
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3632[LR]','3632')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3636[LR]','3636')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3660[LR]','3660')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3660[LR]','3660')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3660F[LR]','3660F')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3672F[LR]','3672F')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3717[LR]','3717')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3748[LR]','3748')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3837[LR]','3837')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3939[LR]','3939')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3940[LR]','3940')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4232[LR]','4232')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4236[LR]','4236')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4436[LR]','4436')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'440[12][LR]','440(1L
or 2R)')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'460[12][LR]','460(1L
or 2R)')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4630[LR]','4630')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4630[LR]','4630')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4830[LR]','4830')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4835[LR]','4835')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4836[LR]','4836')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4837[LR]','4837')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'5000[LR]','5000')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'5435[LR]','5435')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'5836[LR]','5836')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'5843[LR]','5843')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'5845[LR]','5845')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'600[01][LR]','600(1R
or 0L)')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6032[LR]','6032')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6033[LR]','6033')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6036[LR]','6036')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6042[LR]','6042')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6048[LR]','6048')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6133[LR]','6133')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6333[LR]','6333')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6843[LR]','6843')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'7236[LR]','7236')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'740[34][LR]','740(3L
or 4R)')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'7532[LR]','7532')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'7900[LR]','7900')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'790[34][LR]','790(3L
or 4R)')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'791[12][LR]','791(1L
or 2R)')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'793[34][LR]','793(3L
or 4R)')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'7936[LR]','7936')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'8000[LR]','8000')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'801[12][LR]','801(1L
or 2R)')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'840[34][LR]','840(3L
or 4R)')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'862[34][LR]','862(3L
or 4R)')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'863[34][LR]','863(3L
or 4R)')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'8802[LR]','8802')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'8848D[LR]','8848D')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'8848[LR]','8848')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'8864[LR]','8864')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'962[34][LR]','962(3L
or 4R)')
SET @.RegexObject = dbo.REGEX_DESTROY_OBJECT(@.RegexObject)
RETURN @.ret
END
CREATE FUNCTION dbo.REGEX_CREATE_OBJECT
(
)
RETURNS integer
AS
BEGIN
DECLARE @.hr integer
DECLARE @.objRegExp integer
DECLARE @.ret integer
-- create regex object
EXEC @.hr = sp_OACreate 'VBScript.RegExp', @.objRegExp OUTPUT
IF @.hr <> 0 BEGIN
SET @.ret = NULL
RETURN @.ret
END
EXEC @.hr = sp_OASetProperty @.objRegExp, 'Global', false
IF @.hr <> 0 BEGIN
SET @.ret = NULL
RETURN @.ret
END
EXEC @.hr = sp_OASetProperty @.objRegExp, 'IgnoreCase', true
IF @.hr <> 0 BEGIN
SET @.ret = NULL
RETURN @.ret
END
SET @.ret = @.objRegExp
RETURN @.ret
END
CREATE FUNCTION dbo.REGEX_DESTROY_OBJECT
(
@.objRegExp integer
)
RETURNS integer
AS
BEGIN
DECLARE @.hr integer
--destroy regex object
EXEC @.hr = sp_OADestroy @.objRegExp
RETURN @.hr
END
CREATE FUNCTION dbo.REGEX_REPLACE
(
@.objRegExp integer,
@.original varchar(255),
@.pattern varchar(255),
@.replace_with varchar(255)
)
RETURNS VARCHAR(255)
AS
BEGIN
DECLARE @.hr integer
DECLARE @.ret varchar(255)
EXEC @.hr = sp_OASetProperty @.objRegExp, 'Pattern', @.pattern
IF @.hr <> 0 BEGIN
SET @.ret = NULL
RETURN @.ret
END
EXEC @.hr = sp_OAMethod @.objRegExp, 'Replace', @.ret OUTPUT, @.original,
@.replace_with
IF @.hr <> 0 BEGIN
SET @.ret = NULL
RETURN @.ret
END
RETURN @.ret
ENDAdd a new column to your table that will contain the result of the
GetBaseModel function. From a quick look at your function, the ModelNumber
seem to be a very complex object; so it's quite possible that for having the
best performance of your database; you might have to create more than one
new column in order to capture all the features of this object.
For your deterministic problem, you must always create your UDF with the
WITH SCHEMABINDING option if you want to use it with a computed column in a
View; even when the function is already clearly deterministic. Maybe this
will help in your case.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Walbert" <wkalata@.gmail.com> wrote in message
news:1187902579.443728.320620@.q4g2000prc.googlegroups.com...
>I have a UDF which uses regular expressions vicariously through a
> VBScript.RegExp COM object, and does many replaces with it (which
> could probably be optimized to a single regexp, but is much more
> readable as-is).
> This function operates 100% deterministically, but is extremely slow.
> The type of queries it is used for are along the lines of
> SELECT MIN(GETBASEMODEL(ModelNumber)), AVG(Weight) FROM CompletedItems
> GROUP BY GETBASEMODEL(ModelNumber)
> As any information these regular expressions remove from our item
> codes has absolutely nothing to do with its weight (i.e. color).
> My end goal is to speed up the above query, as it currently takes
> about an hour to run when given < 100k rows.
> My thought was to create a indexed computed column in the
> CompletedItems table: "BaseModel", with a function of
> GETBASEMODEL(ModelNumber), though because my function uses the EXEC
> command, it is inherently non-deterministic.
> Is there a keyword that I can use to force my function to be
> deterministic? Or (even better) is my mindset completely wrong?
> The functions appear below
> ---
> CREATE FUNCTION dbo.GETBASEMODEL
> (
> @.model varchar(15)
> )
> RETURNS VARCHAR(255)
> AS
> BEGIN
> DECLARE @.ret varchar(255)
> DECLARE @.RegexObject integer
> SET @.RegexObject = dbo.REGEX_CREATE_OBJECT()
> SET @.ret = @.model
> --trim trailing spaces
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'\s+$','')
> --remove trailing dash and color code
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'-..$','')
> --remove common trailing color codes in case of no dash
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'(WH|BO|BC|SS)$','')
> --remove jetting code
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'/\d*[LRC]{0,2}$','')
> --remove trailing "/S" or "/F" or "/E"
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'/[FES]$','')
> --BF## -> BF
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'BF\d+','BF')
> --BFMD## -> BF
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'BFMD\d*','BF')
> --LBF/RBF -> BF
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'[LR]BF','BF')
> --TSL/TSR -> TS(L or R)
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'TS[LR]','TS(L or
> R)')
> --trailing M
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'M$','')
>
> --handing for specific models
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3632[LR]','3632')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3636[LR]','3636')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3660[LR]','3660')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3660[LR]','3660')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3660F[LR]','3660F')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3672F[LR]','3672F')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3717[LR]','3717')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3748[LR]','3748')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3837[LR]','3837')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3939[LR]','3939')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3940[LR]','3940')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4232[LR]','4232')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4236[LR]','4236')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4436[LR]','4436')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'440[12][LR]','440(1L
> or 2R)')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'460[12][LR]','460(1L
> or 2R)')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4630[LR]','4630')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4630[LR]','4630')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4830[LR]','4830')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4835[LR]','4835')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4836[LR]','4836')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4837[LR]','4837')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'5000[LR]','5000')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'5435[LR]','5435')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'5836[LR]','5836')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'5843[LR]','5843')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'5845[LR]','5845')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'600[01][LR]','600(1R
> or 0L)')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6032[LR]','6032')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6033[LR]','6033')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6036[LR]','6036')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6042[LR]','6042')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6048[LR]','6048')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6133[LR]','6133')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6333[LR]','6333')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6843[LR]','6843')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'7236[LR]','7236')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'740[34][LR]','740(3L
> or 4R)')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'7532[LR]','7532')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'7900[LR]','7900')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'790[34][LR]','790(3L
> or 4R)')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'791[12][LR]','791(1L
> or 2R)')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'793[34][LR]','793(3L
> or 4R)')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'7936[LR]','7936')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'8000[LR]','8000')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'801[12][LR]','801(1L
> or 2R)')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'840[34][LR]','840(3L
> or 4R)')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'862[34][LR]','862(3L
> or 4R)')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'863[34][LR]','863(3L
> or 4R)')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'8802[LR]','8802')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'8848D[LR]','8848D')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'8848[LR]','8848')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'8864[LR]','8864')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'962[34][LR]','962(3L
> or 4R)')
> SET @.RegexObject = dbo.REGEX_DESTROY_OBJECT(@.RegexObject)
> RETURN @.ret
> END
>
> CREATE FUNCTION dbo.REGEX_CREATE_OBJECT
> (
> )
> RETURNS integer
> AS
> BEGIN
> DECLARE @.hr integer
> DECLARE @.objRegExp integer
> DECLARE @.ret integer
> -- create regex object
> EXEC @.hr = sp_OACreate 'VBScript.RegExp', @.objRegExp OUTPUT
> IF @.hr <> 0 BEGIN
> SET @.ret = NULL
> RETURN @.ret
> END
> EXEC @.hr = sp_OASetProperty @.objRegExp, 'Global', false
> IF @.hr <> 0 BEGIN
> SET @.ret = NULL
> RETURN @.ret
> END
> EXEC @.hr = sp_OASetProperty @.objRegExp, 'IgnoreCase', true
> IF @.hr <> 0 BEGIN
> SET @.ret = NULL
> RETURN @.ret
> END
> SET @.ret = @.objRegExp
> RETURN @.ret
> END
>
>
> CREATE FUNCTION dbo.REGEX_DESTROY_OBJECT
> (
> @.objRegExp integer
> )
> RETURNS integer
> AS
> BEGIN
> DECLARE @.hr integer
> --destroy regex object
> EXEC @.hr = sp_OADestroy @.objRegExp
> RETURN @.hr
> END
>
>
> CREATE FUNCTION dbo.REGEX_REPLACE
> (
> @.objRegExp integer,
> @.original varchar(255),
> @.pattern varchar(255),
> @.replace_with varchar(255)
> )
> RETURNS VARCHAR(255)
> AS
> BEGIN
> DECLARE @.hr integer
> DECLARE @.ret varchar(255)
> EXEC @.hr = sp_OASetProperty @.objRegExp, 'Pattern', @.pattern
> IF @.hr <> 0 BEGIN
> SET @.ret = NULL
> RETURN @.ret
> END
> EXEC @.hr = sp_OAMethod @.objRegExp, 'Replace', @.ret OUTPUT, @.original,
> @.replace_with
> IF @.hr <> 0 BEGIN
> SET @.ret = NULL
> RETURN @.ret
> END
> RETURN @.ret
> END
>|||On Aug 23, 9:38 pm, "Sylvain Lafontaine" <sylvain aei ca (fill the
blanks, no spam please)> wrote:
> Add a new column to your table that will contain the result of the
> GetBaseModel function. From a quick look at your function, the ModelNumber
> seem to be a very complex object; so it's quite possible that for having the
> best performance of your database; you might have to create more than one
> new column in order to capture all the features of this object.
> For your deterministic problem, you must always create your UDF with the
> WITH SCHEMABINDING option if you want to use it with a computed column in a
> View; even when the function is already clearly deterministic. Maybe this
> will help in your case.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
> "Walbert" <wkal...@.gmail.com> wrote in message
> news:1187902579.443728.320620@.q4g2000prc.googlegroups.com...
> >I have a UDF which uses regular expressions vicariously through a
> > VBScript.RegExp COM object, and does many replaces with it (which
> > could probably be optimized to a single regexp, but is much more
> > readable as-is).
> > This function operates 100% deterministically, but is extremely slow.
> > The type of queries it is used for are along the lines of
> > SELECT MIN(GETBASEMODEL(ModelNumber)), AVG(Weight) FROM CompletedItems
> > GROUP BY GETBASEMODEL(ModelNumber)
> > As any information these regular expressions remove from our item
> > codes has absolutely nothing to do with its weight (i.e. color).
> > My end goal is to speed up the above query, as it currently takes
> > about an hour to run when given < 100k rows.
> > My thought was to create a indexed computed column in the
> > CompletedItems table: "BaseModel", with a function of
> > GETBASEMODEL(ModelNumber), though because my function uses the EXEC
> > command, it is inherently non-deterministic.
> > Is there a keyword that I can use to force my function to be
> > deterministic? Or (even better) is my mindset completely wrong?
> > The functions appear below
> > ---
> > CREATE FUNCTION dbo.GETBASEMODEL
> > (
> > @.model varchar(15)
> > )
> > RETURNS VARCHAR(255)
> > AS
> > BEGIN
> > DECLARE @.ret varchar(255)
> > DECLARE @.RegexObject integer
> > SET @.RegexObject = dbo.REGEX_CREATE_OBJECT()
> > SET @.ret = @.model
> > --trim trailing spaces
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'\s+$','')
> > --remove trailing dash and color code
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'-..$','')
> > --remove common trailing color codes in case of no dash
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'(WH|BO|BC|SS)$','')
> > --remove jetting code
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'/\d*[LRC]{0,2}$','')
> > --remove trailing "/S" or "/F" or "/E"
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'/[FES]$','')
> > --BF## -> BF
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'BF\d+','BF')
> > --BFMD## -> BF
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'BFMD\d*','BF')
> > --LBF/RBF -> BF
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'[LR]BF','BF')
> > --TSL/TSR -> TS(L or R)
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'TS[LR]','TS(L or
> > R)')
> > --trailing M
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'M$','')
> > --handing for specific models
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3632[LR]','3632')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3636[LR]','3636')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3660[LR]','3660')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3660[LR]','3660')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3660F[LR]','3660F')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3672F[LR]','3672F')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3717[LR]','3717')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3748[LR]','3748')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3837[LR]','3837')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3939[LR]','3939')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3940[LR]','3940')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4232[LR]','4232')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4236[LR]','4236')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4436[LR]','4436')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'440[12][LR]','440(1L
> > or 2R)')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'460[12][LR]','460(1L
> > or 2R)')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4630[LR]','4630')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4630[LR]','4630')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4830[LR]','4830')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4835[LR]','4835')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4836[LR]','4836')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4837[LR]','4837')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'5000[LR]','5000')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'5435[LR]','5435')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'5836[LR]','5836')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'5843[LR]','5843')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'5845[LR]','5845')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'600[01][LR]','600(1R
> > or 0L)')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6032[LR]','6032')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6033[LR]','6033')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6036[LR]','6036')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6042[LR]','6042')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6048[LR]','6048')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6133[LR]','6133')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6333[LR]','6333')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6843[LR]','6843')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'7236[LR]','7236')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'740[34][LR]','740(3L
> > or 4R)')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'7532[LR]','7532')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'7900[LR]','7900')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'790[34][LR]','790(3L
> > or 4R)')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'791[12][LR]','791(1L
> > or 2R)')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'793[34][LR]','793(3L
> > or 4R)')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'7936[LR]','7936')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'8000[LR]','8000')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'801[12][LR]','801(1L
> > or 2R)')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'840[34][LR]','840(3L
> > or 4R)')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'862[34][LR]','862(3L
> > or 4R)')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'863[34][LR]','863(3L
> > or 4R)')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'8802[LR]','8802')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'8848D[LR]','8848D')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'8848[LR]','8848')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'8864[LR]','8864')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'962[34][LR]','962(3L
> > or 4R)')
> > SET @.RegexObject = dbo.REGEX_DESTROY_OBJECT(@.RegexObject)
> > RETURN @.ret
> > END
> > CREATE FUNCTION dbo.REGEX_CREATE_OBJECT
> > (
> > )
> > RETURNS integer
> > AS
> > BEGIN
> > DECLARE @.hr integer
> > DECLARE @.objRegExp integer
> > DECLARE @.ret integer
> > -- create regex object
> > EXEC @.hr = sp_OACreate 'VBScript.RegExp', @.objRegExp OUTPUT
> > IF @.hr <> 0 BEGIN
> > SET @.ret = NULL
> > RETURN @.ret
> > END
> > EXEC @.hr = sp_OASetProperty @.objRegExp, 'Global', false
> > IF @.hr <> 0 BEGIN
> > SET @.ret = NULL
> > RETURN @.ret
> > END
> > EXEC @.hr = sp_OASetProperty @.objRegExp, 'IgnoreCase', true
> > IF @.hr <> 0 BEGIN
> > SET @.ret = NULL
> > RETURN @.ret
> > END
> > SET @.ret = @.objRegExp
> > RETURN @.ret
> > END
> > CREATE FUNCTION dbo.REGEX_DESTROY_OBJECT
> > (
> > @.objRegExp integer
> > )
> > RETURNS integer
> > AS
> > BEGIN
> > DECLARE @.hr integer
> > --destroy regex object
> > EXEC @.hr = sp_OADestroy @.objRegExp
> > RETURN @.hr
> > END
> > CREATE FUNCTION dbo.REGEX_REPLACE
> > (
> > @.objRegExp integer,
> > @.original varchar(255),
> > @.pattern varchar(255),
> > @.replace_with varchar(255)
> > )
> > RETURNS VARCHAR(255)
> > AS
> > BEGIN
> > DECLARE @.hr integer
> > DECLARE @.ret varchar(255)
> > EXEC @.hr = sp_OASetProperty @.objRegExp, 'Pattern', @.pattern
> > IF @.hr <> 0 BEGIN
> > SET @.ret = NULL
> > RETURN @.ret
> > END
> > EXEC @.hr = sp_OAMethod @.objRegExp, 'Replace', @.ret OUTPUT, @.original,
> > @.replace_with
> > IF @.hr <> 0 BEGIN
> > SET @.ret = NULL
> > RETURN @.ret
> > END
> > RETURN @.ret
> > END
Well, Model Numbers aren't very complex as much as they've evolved
over the last thirty years without anyone ever dreaming they'd have to
be dealt with pragmatically. A "RE7903L/6-WH" would be a "Residential
Tub/Shower, model "7903", colored white, with six jets and the
shower's wall on the left side. At the time it hits the scale,
though, it's only a white model "7903", which is what the
GETBASEMODEL() function tries to reduce it to. (oh, and we make bath
tubs)
Two problems:
any UDF which uses the EXECUTE function is ineligable to be used with
WITH SCHEMABINDING
and
Performance without being able to index the computed column is just as
bas as using GETBASEMODEL() in a query.
I was thinking a good solution would be a non-computed column, with a
default value of GETBASEMODEL(ModelNumber), and hopefully being able
to work security around not being able to overwrite its value. Does
this seem viable or prone to error?|||> I was thinking a good solution would be a non-computed column, with a
> default value of GETBASEMODEL(ModelNumber), and hopefully being able
> to work security around not being able to overwrite its value. Does
> this seem viable or prone to error?
Another option is to maintain the persisted value via an INSERT/UPDATE
trigger. This will ensure the value is kept in sync as long as the
trigger(s) fires.
FWIW, the underlying issue is that it's best to store atomic data in a
relational database. However, I understand that this isn't always feasible
for legacy applications.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Walbert" <wkalata@.gmail.com> wrote in message
news:1187923624.672726.6270@.z24g2000prh.googlegroups.com...
> On Aug 23, 9:38 pm, "Sylvain Lafontaine" <sylvain aei ca (fill the
> blanks, no spam please)> wrote:
>> Add a new column to your table that will contain the result of the
>> GetBaseModel function. From a quick look at your function, the
>> ModelNumber
>> seem to be a very complex object; so it's quite possible that for having
>> the
>> best performance of your database; you might have to create more than one
>> new column in order to capture all the features of this object.
>> For your deterministic problem, you must always create your UDF with the
>> WITH SCHEMABINDING option if you want to use it with a computed column in
>> a
>> View; even when the function is already clearly deterministic. Maybe
>> this
>> will help in your case.
>> --
>> Sylvain Lafontaine, ing.
>> MVP - Technologies Virtual-PC
>> E-mail: sylvain aei ca (fill the blanks, no spam please)
>> "Walbert" <wkal...@.gmail.com> wrote in message
>> news:1187902579.443728.320620@.q4g2000prc.googlegroups.com...
>> >I have a UDF which uses regular expressions vicariously through a
>> > VBScript.RegExp COM object, and does many replaces with it (which
>> > could probably be optimized to a single regexp, but is much more
>> > readable as-is).
>> > This function operates 100% deterministically, but is extremely slow.
>> > The type of queries it is used for are along the lines of
>> > SELECT MIN(GETBASEMODEL(ModelNumber)), AVG(Weight) FROM CompletedItems
>> > GROUP BY GETBASEMODEL(ModelNumber)
>> > As any information these regular expressions remove from our item
>> > codes has absolutely nothing to do with its weight (i.e. color).
>> > My end goal is to speed up the above query, as it currently takes
>> > about an hour to run when given < 100k rows.
>> > My thought was to create a indexed computed column in the
>> > CompletedItems table: "BaseModel", with a function of
>> > GETBASEMODEL(ModelNumber), though because my function uses the EXEC
>> > command, it is inherently non-deterministic.
>> > Is there a keyword that I can use to force my function to be
>> > deterministic? Or (even better) is my mindset completely wrong?
>> > The functions appear below
>> > ---
>> > CREATE FUNCTION dbo.GETBASEMODEL
>> > (
>> > @.model varchar(15)
>> > )
>> > RETURNS VARCHAR(255)
>> > AS
>> > BEGIN
>> > DECLARE @.ret varchar(255)
>> > DECLARE @.RegexObject integer
>> > SET @.RegexObject = dbo.REGEX_CREATE_OBJECT()
>> > SET @.ret = @.model
>> > --trim trailing spaces
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'\s+$','')
>> > --remove trailing dash and color code
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'-..$','')
>> > --remove common trailing color codes in case of no dash
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'(WH|BO|BC|SS)$','')
>> > --remove jetting code
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'/\d*[LRC]{0,2}$','')
>> > --remove trailing "/S" or "/F" or "/E"
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'/[FES]$','')
>> > --BF## -> BF
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'BF\d+','BF')
>> > --BFMD## -> BF
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'BFMD\d*','BF')
>> > --LBF/RBF -> BF
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'[LR]BF','BF')
>> > --TSL/TSR -> TS(L or R)
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'TS[LR]','TS(L or
>> > R)')
>> > --trailing M
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'M$','')
>> > --handing for specific models
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3632[LR]','3632')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3636[LR]','3636')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3660[LR]','3660')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3660[LR]','3660')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3660F[LR]','3660F')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3672F[LR]','3672F')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3717[LR]','3717')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3748[LR]','3748')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3837[LR]','3837')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3939[LR]','3939')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3940[LR]','3940')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4232[LR]','4232')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4236[LR]','4236')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4436[LR]','4436')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'440[12][LR]','440(1L
>> > or 2R)')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'460[12][LR]','460(1L
>> > or 2R)')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4630[LR]','4630')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4630[LR]','4630')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4830[LR]','4830')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4835[LR]','4835')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4836[LR]','4836')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4837[LR]','4837')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'5000[LR]','5000')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'5435[LR]','5435')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'5836[LR]','5836')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'5843[LR]','5843')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'5845[LR]','5845')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'600[01][LR]','600(1R
>> > or 0L)')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6032[LR]','6032')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6033[LR]','6033')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6036[LR]','6036')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6042[LR]','6042')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6048[LR]','6048')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6133[LR]','6133')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6333[LR]','6333')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6843[LR]','6843')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'7236[LR]','7236')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'740[34][LR]','740(3L
>> > or 4R)')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'7532[LR]','7532')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'7900[LR]','7900')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'790[34][LR]','790(3L
>> > or 4R)')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'791[12][LR]','791(1L
>> > or 2R)')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'793[34][LR]','793(3L
>> > or 4R)')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'7936[LR]','7936')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'8000[LR]','8000')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'801[12][LR]','801(1L
>> > or 2R)')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'840[34][LR]','840(3L
>> > or 4R)')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'862[34][LR]','862(3L
>> > or 4R)')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'863[34][LR]','863(3L
>> > or 4R)')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'8802[LR]','8802')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'8848D[LR]','8848D')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'8848[LR]','8848')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'8864[LR]','8864')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'962[34][LR]','962(3L
>> > or 4R)')
>> > SET @.RegexObject = dbo.REGEX_DESTROY_OBJECT(@.RegexObject)
>> > RETURN @.ret
>> > END
>> > CREATE FUNCTION dbo.REGEX_CREATE_OBJECT
>> > (
>> > )
>> > RETURNS integer
>> > AS
>> > BEGIN
>> > DECLARE @.hr integer
>> > DECLARE @.objRegExp integer
>> > DECLARE @.ret integer
>> > -- create regex object
>> > EXEC @.hr = sp_OACreate 'VBScript.RegExp', @.objRegExp OUTPUT
>> > IF @.hr <> 0 BEGIN
>> > SET @.ret = NULL
>> > RETURN @.ret
>> > END
>> > EXEC @.hr = sp_OASetProperty @.objRegExp, 'Global', false
>> > IF @.hr <> 0 BEGIN
>> > SET @.ret = NULL
>> > RETURN @.ret
>> > END
>> > EXEC @.hr = sp_OASetProperty @.objRegExp, 'IgnoreCase', true
>> > IF @.hr <> 0 BEGIN
>> > SET @.ret = NULL
>> > RETURN @.ret
>> > END
>> > SET @.ret = @.objRegExp
>> > RETURN @.ret
>> > END
>> > CREATE FUNCTION dbo.REGEX_DESTROY_OBJECT
>> > (
>> > @.objRegExp integer
>> > )
>> > RETURNS integer
>> > AS
>> > BEGIN
>> > DECLARE @.hr integer
>> > --destroy regex object
>> > EXEC @.hr = sp_OADestroy @.objRegExp
>> > RETURN @.hr
>> > END
>> > CREATE FUNCTION dbo.REGEX_REPLACE
>> > (
>> > @.objRegExp integer,
>> > @.original varchar(255),
>> > @.pattern varchar(255),
>> > @.replace_with varchar(255)
>> > )
>> > RETURNS VARCHAR(255)
>> > AS
>> > BEGIN
>> > DECLARE @.hr integer
>> > DECLARE @.ret varchar(255)
>> > EXEC @.hr = sp_OASetProperty @.objRegExp, 'Pattern', @.pattern
>> > IF @.hr <> 0 BEGIN
>> > SET @.ret = NULL
>> > RETURN @.ret
>> > END
>> > EXEC @.hr = sp_OAMethod @.objRegExp, 'Replace', @.ret OUTPUT, @.original,
>> > @.replace_with
>> > IF @.hr <> 0 BEGIN
>> > SET @.ret = NULL
>> > RETURN @.ret
>> > END
>> > RETURN @.ret
>> > END
>
> Well, Model Numbers aren't very complex as much as they've evolved
> over the last thirty years without anyone ever dreaming they'd have to
> be dealt with pragmatically. A "RE7903L/6-WH" would be a "Residential
> Tub/Shower, model "7903", colored white, with six jets and the
> shower's wall on the left side. At the time it hits the scale,
> though, it's only a white model "7903", which is what the
> GETBASEMODEL() function tries to reduce it to. (oh, and we make bath
> tubs)
> Two problems:
> any UDF which uses the EXECUTE function is ineligable to be used with
> WITH SCHEMABINDING
> and
> Performance without being able to index the computed column is just as
> bas as using GETBASEMODEL() in a query.
> I was thinking a good solution would be a non-computed column, with a
> default value of GETBASEMODEL(ModelNumber), and hopefully being able
> to work security around not being able to overwrite its value. Does
> this seem viable or prone to error?
>|||> I was thinking a good solution would be a non-computed column, with a
> default value of GETBASEMODEL(ModelNumber), and hopefully being able
> to work security around not being able to overwrite its value. Does
> this seem viable or prone to error?
This was grosso modo my suggestion when I was talking about adding a new
column; with the possibility of adding other columns for the possibility of
other functions beside GetBaseModel(). Don't forget that much of the work
that SQL-Server is doing with a materialized view is something like that: it
computes all the values for the column but instead of storing it in the
table, it store it in a hidden location.
Of course, having your own column is not exactly the same thing because you
must take into account a few things like the insertion or edition of the
column. However, in most cases, this shouldn't be a big problem.
Another possibility - and maybe the best one - would be to get rid of all
these regular expression manipulations and doing your own string
manipulation character by character in a UDF. Your use of regular
expressions seems to be overkill to me for what you are doing. Not only you
will be able to use a materialized view but your code will perform many
orders of magnitude faster.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Walbert" <wkalata@.gmail.com> wrote in message
news:1187923624.672726.6270@.z24g2000prh.googlegroups.com...