Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

Converting Hexadecimal String Values to Alpha (ASCII) Strings Expand / Collapse
Author
Message
Posted Saturday, May 19, 2007 1:53 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, March 14, 2010 10:55 PM
Points: 4, Visits: 12
Thank you Jeff, I was too sleepy to run comparison tests myself last night.
Your input length condition is a nice touch.
But no Jeff, it not a cross-join nor generates 65,536 internal rows. It's a n inner join. You can see it if you check out its execution plan, and it can be replaced with:
----------------------------------------
select @output = @output + char(b.dec)
from dec2hex a inner join dec2hex b
on b.hex = substring(@input,a.dec*2+1,2)
where a.dec*2+1 < LEN(@input)
order by a.dec
----------------------------------------
without affecting its semantics at all.
And dec2hex table is looked-up just LEN(@output) times.(It was 256 times before you add the criteria, though.)

I think it's weird that Richard's function is still much faster. I thought the join routine of the server should be most efficient. Moreover, my function calls substring() half the times of Richard's.

Anyway, it was a very interesting quiz. Thank you all!
Post #367435
Posted Saturday, May 19, 2007 6:06 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:36 PM
Points: 36,793, Visits: 31,252

Heh... and I was too sleepy this morning... I shouldn't post when I'm pooped...

It is still a bit of a half cross join (more specifically, a triangular join and does an index scan of 76 rows) but it certainly isn't 65,536.  My mistake.  That would also explain why Richard's is still faster...



--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #367452
Posted Tuesday, May 22, 2007 6:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 25, 2010 3:01 AM
Points: 3, Visits: 18

Another function for the mix:

if objectproperty (object_id ('dbo.fn_HexToAlpha'), 'istablefunction') = 0 drop function dbo.fn_HexToAlpha
go
create function dbo.fn_HexToAlpha (@HexString char (40))
returns varchar (20) as
begin
declare @Loop tinyint, @AsciiString varchar (20), @Nibble1 char (1), @Nibble2 char (1)
select @AsciiString = '', @Loop = 1
while @Loop < len (@HexString)
 begin
 select @Nibble1 = upper (substring (@HexString, @Loop, 1)), @Nibble2 = upper (substring (@HexString, @Loop + 1, 1))
 set @AsciiString = @AsciiString + char (16 * case @Nibble1 when 'A' then 10 when 'B' then 11 when 'C' then 12 when 'D' then 13 when 'E' then 14 when 'F' then 15 else @Nibble1 end + case @Nibble2 when 'A' then 10 when 'B' then 11 when 'C' then 12 when 'D' then 13 when 'E' then 14 when 'F' then 15 else @Nibble2 end)
 set @Loop = @Loop + 2
 end
return @AsciiString
end
go
select dbo.fn_HexToAlpha (HexString) from #HexToAlpha

Eddie Lee.

 




Post #367888
Posted Tuesday, May 22, 2007 8:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 25, 2010 3:01 AM
Points: 3, Visits: 18

 

I ran mine and Richard's functions on Jeffs test data (pushed up to 1000000 rows) and my function above (at 00:01:25.603) just pipped Richards (at 00:01:57.740).

 

alter table #HexTest add ASCIIValue varchar (20)
go


if object_id ('tempdb.dbo.#TimeTrial') is not null drop table #TimeTrial
create table #TimeTrial (Event varchar (20), DateStamp datetime default getdate ())

update #HexTest set ASCIIValue = null       -- reset
insert #TimeTrial (Event) values ('RichardStart')
update #HexTest set ASCIIValue = dbo.f_hextostr (HexValue)
insert #TimeTrial (Event) values ('RichardFinish')
select * from #HexTest

update #HexTest set ASCIIValue = null       -- reset
insert #TimeTrial (Event) values ('EddieStart')
update #HexTest set ASCIIValue = dbo.fn_HexToAlpha (HexValue)
insert #TimeTrial (Event) values ('EddieFinish')
select * from #HexTest

select (select DateStamp from #TimeTrial where Event = 'RichardFinish') - (select DateStamp from #TimeTrial where Event = 'RichardStart') Richard
select (select DateStamp from #TimeTrial where Event = 'EddieFinish') - (select DateStamp from #TimeTrial where Event = 'EddieStart') Eddie

Eddie.

 




Post #367961
Posted Tuesday, May 22, 2007 9:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 11, 2014 1:04 AM
Points: 5, Visits: 27

Hey Eddie!

I like this conversation very much!

Try this with only 100.000 recs!

if object_id ('tempdb.dbo.#TimeTrial') is not null drop table #TimeTrial
create table #TimeTrial (id integer identity(1,1), Event varchar (20), datestart datetime default getdate (), dateend datetime null)

declare @counter integer
set @counter=1
while @counter<13
 begin
  insert #TimeTrial (Event) values ('Richard')
  update HexTest set ASCIIValue = dbo.f_hextostr (HexValue)
  update #TimeTrial set dateend=getdate() where id=@counter
  set @counter=@counter+1

  insert #TimeTrial (Event) values ('Eddie')
  update HexTest set ASCIIValue = dbo.fn_HexToAlpha (HexValue)
  update #TimeTrial set dateend=getdate() where id=@counter
  set @counter=@counter+1
 end

select *, dateend-datestart as diff from #TimeTrial order by id

The result:

1 Richard 00:00:17.333
2 Eddie 00:00:08.283
3 Richard 00:00:06.610
4 Eddie 00:00:08.010
5 Richard 00:00:06.420
6 Eddie 00:00:08.000
7 Richard 00:00:06.520
8 Eddie 00:01:02.360
9 Richard 00:00:06.460
10 Eddie 00:00:07.780
11 Richard 00:00:06.510
12 Eddie 00:00:07.803

Conclusions: The first runs (1-2) must be eliminated, our data not in cash yet. The runs 7-8 must be eliminated, Your result totally differs from others ( really sometimes I don't know, what the hell is running on background), the rest results are comparable.

By the way: our functions are able to run faster, if:

You eliminate the UPPER function (suppose the HEX string is correct);

I eliminate the IF part (suppose the HEX string is correct)

Best regards: Richard

Post #367993
Posted Wednesday, May 23, 2007 7:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 25, 2010 3:01 AM
Points: 3, Visits: 18

Richard yes, your loop does prove your fn to be a bit quicker. Weird how the run time of mine fluctuates so?!

Ho hum.

Good chatting.

Regards, Eddie.

 




Post #368258
Posted Monday, May 28, 2007 4:15 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 5, 2012 12:03 PM
Points: 117, Visits: 163
I have found using STUFF to create the output to be a little bit faster. I am assuming that it is due to traditional string concantination issues...



ALTER FUNCTION hex2alpha( @input VARCHAR(256) ) RETURNS VARCHAR(256) AS
BEGIN
DECLARE
@output VARCHAR(256); SET @output = SPACE(LEN(@input)/2)

SELECT @output = STUFF(@output,a.dec+1,1,CHAR(b.dec))
FROM
dec2hex AS a
INNER JOIN dec2hex AS b ON b.hex = SUBSTRING(@input,a.dec*2+1,2)
WHERE
a.dec < LEN(@input)/2
ORDER BY a.dec

RETURN @output
END
Post #369359
Posted Thursday, June 28, 2007 7:09 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 1, 2011 2:05 PM
Points: 32, Visits: 47

Firstly my apologies for not responding sooner

Secondly, thank you all for the marvellous responses and ensuing competition.  Sadly I must now say my original SQL was a very poor contender over 1,000,000 records, and some of the alternatives, well let's just say WOW!

I have really learned some stuff from you people, so much appreciated.

Stephen

 




Post #377657
Posted Thursday, June 28, 2007 11:02 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:36 PM
Points: 36,793, Visits: 31,252
Thanks for coming back with that... lots of us end up wondering if we actually helped or confused.  Really appreciate the feedback even if it is a bit later than most.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #377684
Posted Friday, April 25, 2008 4:32 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 11, 2013 2:42 AM
Points: 150, Visits: 245
Surely the real lesson from this should be that if something is this hard in SQL you should be doing it in a different language?
I rarely come across an application that is written purely in SQL, so there is usually a presentation layer that is more capable of handling what looks like a purely presentational issue.


Throw away your pocket calculators; visit www.calcResult.com

Post #490506
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse