The test of performance string UDFs

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

SQL Server Development (2000)

The test of performance string UDFs

Author

Topic

Igor2004
More clever than you

78 Posts

Posted - 2005-08-27 : 12:14:00

declare @i smallint, @j smallint, @cycle int, @cSameString nvarchar(4000), @cSameDelimiters nvarchar(256), @bt1 datetime
declare @t table (WORD nvarchar(4000))
select @cycle = 1000, @cSameDelimiters = ' ,.'

select @cSameString = 'The performance is probably awful, and I wouldn''t choose to do this job in SQL - but ... for a one-off import I expect it will save-my-bacon one day, probably in the near future.
acctually the performance isn''t that bad at all...
i ran it on a table of about 10k rows... and his was faster up to 30% compared to mine which took 10 minutes of work to write and no perf testing...
as i said... his functions are preety ok if you ask me...'

select @bt1 = getdate(), @i = 0
while @i < @cycle
select @j = charindex(@cSameString, @cSameDelimiters), @i = @i +1
select 'charindex '+ cast(@cycle as varchar(10)) + ' cycle works Milliseconds '+ cast(datediff(ms, @bt1, getdate()) as varchar(10))

select @bt1 = getdate(), @i = 0
while @i < @cycle
begin
update @t set WORD = B.WORD from dbo.GETALLWORDS(@cSameString, @cSameDelimiters) B
select @i = @i +1
end
select 'GETALLWORDS '+ cast(@cycle as varchar(10)) + ' cycle works Milliseconds '+ cast(datediff(ms, @bt1, getdate()) as varchar(10))

select @bt1 = getdate(), @i = 0
while @i < @cycle
begin
update @t set WORD = B.YourField from dbo.YourFunction_10 minutes_of_work(@cSameString, @cSameDelimiters) B
select @i = @i +1
end

select 'YourFunction_10 minutes_of_work '+ cast(@cycle as varchar(10)) + ' cycle works Milliseconds '+ cast(datediff(ms, @bt1, getdate()) as varchar(10))

to spirit1
_______________________________
charindex 1000 cycle works Milliseconds 63
GETALLWORDS 1000 cycle works Milliseconds 6110
Your functon Split 1000 cycle works Milliseconds 17513
but Split is very erroneous
____________________________________________________
Means there are people (at the given forum) wishing to use my functions. I mean has published my functions here knowingly.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-08-27 : 12:48:19

damn it... can you tell me the results you get... i won't be around a SQL server until wednesday...
and i'd like to know the results...

Go with the flow & have fun! Else fight the flow

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-08-27 : 14:18:29

Igor-

This original thread was moved to the Developer forum because it was not a script of a function for the script library. Please do not post duplicates or cross posts of the same topic, and leave the new thread in its current forum.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources