Strip this!!!
-
Same way as REPLACE, ISNULL functions.
But you don't worry about those function, so why do you about this one?
Try it first, than say.
_____________
Code for TallyGenerator -
Can be???
I don't see any real solution in this topic except mine.
_____________
Code for TallyGenerator -
Reading is fundamental....
select SUBSTRING(column1, PATINDEX('%[^0]%', column1+'A'), LEN(column1))
A.J.
DBA with an attitude -
There is a big difference between calling in-built functions and your own user defined functions.
Take a look at a profiler trace and you will see exactly what awilbur77 is talking about. You will find that using the inbuilt function you get a single execution.
Try it first, than say.
--------------------
Colt 45 - the original point and click interface
-
Did you ACTUALLY try it?
Don't fool yourself with scaring trace.
Switch off trace and run simple select from big table, one time with UDF, second time with, say, ISNULL.
I have UDF to cut off time from datetime value.
SELECT dbo.DateOnly(DateTimeColumn) from 2ThuosandRowsTable
takes 4 seconds,
SELECT ISNULL(DateTimeColumn, 0) from 2ThuosandRowsTable
takes 7 seconds.
Try it first, than say.
_____________
Code for TallyGenerator -
Well yes I did take your suggestion and try it, given the result I got I presumed you hadn't tried it first.
I changed the query in my initial post to insert into a temp table and copied the insert lines quite a few times. Ended up with just over 3500 rows in the temp table. Then I ran the query in Query Analyzer with the Server Trace and Client Statistics selected.
Using a user defined function against a table with 3500 rows
SET STATISTICS PROFILE ON SQL:StmtCompleted 0 0 0 0 CREATE TABLE #Var (id int IDENTITY(1,1), col1 varchar(10)) SQL:StmtCompleted 0 0 14 0 INSERT INTO #Var VALUES ( '000H.K6' ) SQL:StmtCompleted 16 0 25 0 INSERT INTO #Var VALUES ( '000H.K6' ) SQL:StmtCompleted 0 0 11 0 INSERT INTO #Var VALUES ( '004ghs' ) SQL:StmtCompleted 0 0 11 0 INSERT INTO #Var VALUES ( '00000l.sa' ) SQL:StmtCompleted 0 0 11 0
etc..., etc... while loading the temp table
SET @Stripped = @p1 SP:StmtCompleted 0 0 0 0 WHILE LEFT(@Stripped, 1) = '0' SP:StmtCompleted 0 0 0 0 SET @Stripped = RIGHT(@Stripped, LEN(@Stripped)-1) SP:StmtCompleted 0 0 0 0 RETURN @Stripped SP:StmtCompleted 0 0 0
etc..., etc... for each record in the temp table
SET @Stripped = @p1 SP:StmtCompleted 0 0 0 WHILE LEFT(@Stripped, 1) = '0' SP:StmtCompleted 0 0 0 RETURN @Stripped SP:StmtCompleted 0 0 0 select DBA.dbo.udf_stripzeros(col1) FROM #Var SQL:StmtCompleted 17 17 98 DROP TABLE #var SQL:StmtCompleted 0 0 79 SET STATISTICS PROFILE OFF SQL:StmtCompleted 0 0 0 0
Total execution time 2:05
Using in-built function against a table with 3500 rows
INSERT INTO #Var VALUES ( '000H.K6' ) SQL:StmtCompleted 0 0 11 0 INSERT INTO #Var VALUES ( '004ghs' ) SQL:StmtCompleted 0 0 11 0 INSERT INTO #Var VALUES ( '00000l.sa' ) SQL:StmtCompleted 0 0 11 0
etc..., etc... while I load up the temp table
select SUBSTRING(col1, PATINDEX('%[^0]%', col1+'A'), LEN(col1)) FROM #Var SQL:StmtCompleted 197 15 32 0 DROP TABLE #var SQL:StmtCompleted 0 0 74 0Total execution time 0:45
I dunno, maybe that super server I seem have at time is kicking in again.
--------------------
Colt 45 - the original point and click interface -
What I've tried:
CREATE TABLE #Var (id int IDENTITY(1,1), col1 varchar(10))
declare @I int
set @I = 0
while @I < 100000
BEGIN
INSERT INTO #Var VALUES ( '000H.K6' )
INSERT INTO #Var VALUES ( '000H.K6' )
INSERT INTO #Var VALUES ( '004ghs' )
INSERT INTO #Var VALUES ( '00000l.sa' )
SET @I = @I + 1
END
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = N'TrimZeros')
DROP FUNCTION TrimZeros
GO
CREATE FUNCTION dbo.TrimZeros
(@InputString nvarchar(4000))
RETURNS nvarchar(4000)
AS
BEGIN
WHILE Left(@InputString, 1) = '0'
begin
select @InputString = substring(@InputString, 2, len(@InputString)-1)
end
RETURN @InputString
END
GO
I tried on 2 different servers:
1. Real server: 4 CPU, 1.5G RAM for SQL, RAID HDD, etc.
2. My local machine: Pentium 800, 512M RAM, some miserable HDD.
select * from #Var
1. 28 s
2. 11 s
Network traffic?
SELECT dbo.TrimZeros (col1)
from #Var
1. 06 s
2. 24 s
select SUBSTRING(col1, PATINDEX('%[^0]%', col1+'A'), LEN(col1))
from #Var
1. 10 s
2. 11 S
As you can see, it really depends.
_____________
Code for TallyGenerator -
You could make the REPLACE solution work by replacing spaces with a 'safe' character first, doing the original process and then resetting the spaces. Of course, you now have four nested replaces so performance might suffer!
-
Sergiy,
I don't know exactly what are you trying to prove, on your results you must compare all the 1's or all the 2's but not 1's with 2's... ever heard oranges to oranges and apples to apples.
In no case a UDF is faster than a built-in ... unless they are doing very different things
Cheers,
* Noel -
If you look at my post again you may notice that on real server the query with UDF took 6 (six) seconds, and query with built-in functions took 10 (ten) seconds.
Is it "apple-to-apple"?
_____________
Code for TallyGenerator
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply