SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Optimize query needed


Optimize query needed

Author
Message
haiao2000
haiao2000
Right there with Babe
Right there with Babe (766 reputation)Right there with Babe (766 reputation)Right there with Babe (766 reputation)Right there with Babe (766 reputation)Right there with Babe (766 reputation)Right there with Babe (766 reputation)Right there with Babe (766 reputation)Right there with Babe (766 reputation)

Group: General Forum Members
Points: 766 Visits: 438
Hello Expert.

I need to convert data from an existing column from BigInt to VarBinary type. I wrote the function to do the conversion; the function is fairly fast, however, it is not fast enough to use on a table with more than 40 millions records (takes about 27minutes). Could someone help optimize it so that it runs a bit faster?

Thanks in advance,
-Hai


ALTER function [dbo].[ConvertBigIntToVarBinary]((c)param BigInt)
returns varbinary(128) AS
begin

declare (c)result varchar(128)
set (c)result = ''

while 1=1
begin
select (c)result= convert(char(1),(c)param % 2)+(c)result, (c)param = convert(bigint, ((c)param / 2))
if (c)param = 0 return convert(varbinary(128), replace(replace(reverse((c)result),'0','00'),'1','01'),2)
end
return convert(varbinary(128),'00',2)
end


Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218223 Visits: 41995
Is there a reason why you couldn't just use CAST(SomeBigIntColumn AS VARBINARY(128))?

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
haiao2000
haiao2000
Right there with Babe
Right there with Babe (766 reputation)Right there with Babe (766 reputation)Right there with Babe (766 reputation)Right there with Babe (766 reputation)Right there with Babe (766 reputation)Right there with Babe (766 reputation)Right there with Babe (766 reputation)Right there with Babe (766 reputation)

Group: General Forum Members
Points: 766 Visits: 438
Yes there is, if you run the function and pass a parameter like 5, you will see the result. Just perform a normal conversion would not give the desire result
haiao2000
haiao2000
Right there with Babe
Right there with Babe (766 reputation)Right there with Babe (766 reputation)Right there with Babe (766 reputation)Right there with Babe (766 reputation)Right there with Babe (766 reputation)Right there with Babe (766 reputation)Right there with Babe (766 reputation)Right there with Babe (766 reputation)

Group: General Forum Members
Points: 766 Visits: 438
Some one suggested this function works
ALTER function [dbo].[ConvertBigIntToVarBinary1](@param bigint)
returns varbinary(1000) AS
begin

declare @iPower int;
declare @result varbinary(1000);
declare @powerOf2 bigint;

set @result = 0x;
set @powerOf2 = 1;
set @iPower = 0;

while @powerOf2 <= @param
begin
set @result = @result + CAST(@param / @powerOf2 % 2 AS binary(1));

set @iPower = @iPower + 1;

set @powerOf2 = power(2., @iPower);
end
return @result
end



Although I dont see much advance of it plus it fails to convert the max bigint value for some reasons
SELECT dbo.ConvertBigIntToVarBinary1(9223372036854775807)
TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32757 Visits: 8677
I am seriously missing something:


SELECT CAST(5 AS VARBINARY(128)) --0x00000005
SELECT CAST(0x00000005 AS bigint) --5


SELECT dbo.ConvertBigIntToVarBinary1(5) --0x010001
SELECT CAST(0x010001 AS bigint) --65537

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
haiao2000
haiao2000
Right there with Babe
Right there with Babe (766 reputation)Right there with Babe (766 reputation)Right there with Babe (766 reputation)Right there with Babe (766 reputation)Right there with Babe (766 reputation)Right there with Babe (766 reputation)Right there with Babe (766 reputation)Right there with Babe (766 reputation)

Group: General Forum Members
Points: 766 Visits: 438
TheSQLGuru (10/2/2012)
I am seriously missing something:


SELECT CAST(5 AS VARBINARY(128)) --0x00000005
SELECT CAST(0x00000005 AS bigint) --5


SELECT dbo.ConvertBigIntToVarBinary1(5) --0x010001
SELECT CAST(0x010001 AS bigint) --65537


NAH! you aren't missing anything. it was just our requirements written that way. In the nutshell, our existing application has capability of searching the varbinary field data bit-by-bit (or byte-by-byte whatever that is..) without first converingt the bit to character, that is from what I was told and understood. so this is the how conversion process works:
-first convert the bigint to binary value,
-reverse the entire string
-foreach bit in the string, add a 0 infront of it.
-then convert the value to varbinary field using this format: convert(varbinary(128),@result,2)
TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32757 Visits: 8677
haiao2000 (10/2/2012)
TheSQLGuru (10/2/2012)
I am seriously missing something:


SELECT CAST(5 AS VARBINARY(128)) --0x00000005
SELECT CAST(0x00000005 AS bigint) --5


SELECT dbo.ConvertBigIntToVarBinary1(5) --0x010001
SELECT CAST(0x010001 AS bigint) --65537


NAH! you aren't missing anything. it was just our requirements written that way. In the nutshell, our existing application has capability of searching the varbinary field data bit-by-bit (or byte-by-byte whatever that is..) without first converingt the bit to character, that is from what I was told and understood. so this is the how conversion process works:
-first convert the bigint to binary value,
-reverse the entire string
-foreach bit in the string, add a 0 infront of it.
-then convert the value to varbinary field using this format: convert(varbinary(128),@result,2)


Ahh - what I was missing was the actual requirements! :-)

This may get you part-way there: http://pratchev.blogspot.com/2008/04/convert-hex-string-to-binary-string.html. You will need to efficiently deal with leading zeros though before doing the reverse/replace thing. The key is to do this as a set-based operation and not the iterative way you have now. I am confident there is a solution, but it goes beyond what I am willing to invest for free to get you there. Cool

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
haiao2000
haiao2000
Right there with Babe
Right there with Babe (766 reputation)Right there with Babe (766 reputation)Right there with Babe (766 reputation)Right there with Babe (766 reputation)Right there with Babe (766 reputation)Right there with Babe (766 reputation)Right there with Babe (766 reputation)Right there with Babe (766 reputation)

Group: General Forum Members
Points: 766 Visits: 438
[b]
Ahh - what I was missing was the actual requirements! :-)

This may get you part-way there: http://pratchev.blogspot.com/2008/04/convert-hex-string-to-binary-string.html. You will need to efficiently deal with leading zeros though before doing the reverse/replace thing. The key is to do this as a set-based operation and not the iterative way you have now. I am confident there is a solution, but it goes beyond what I am willing to invest for free to get you there. Cool


Not big of a deal. Believe it or not...I did this entire thing for free too :-). It wasn't my assignment. Anyway what I got so far should be good for them to start with, they should work on the fine tune it.

Thanks for direction!
-Hai
Ben Teraberry
Ben Teraberry
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2941 Visits: 1199
I'm not sure how logical this project is, but it seemed interesting to do this without looping. It was fun.

declare @b bigint = 31;
declare @out varbinary(128) = 0x;

-- simple inline tally table
with cteTen as
( select N from (values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) as t(N) ),
cteNums as
( select N = 0
union all
select top (63) -- the 64th bit would be bigger than a bigint max value
N = row_number() over(order by (select null))
from cteTen as cte1
cross join cteTen as cte2 ),
-- the meat of the query
cteInit as
( select cte.N
, ValThis = power(convert(decimal(20,0), 2), N)
, ValNext = power(convert(decimal(20,0), 2), N + 1)
from cteNums as cte )

select @out = @out + convert(varbinary(1), case when (@b - (floor(@b / cte.ValNext)) * cte.ValNext) >= cte.ValThis then 1 else 0 end)
from cteInit as cte
where cte.ValThis <= @b
order by cte.N desc;

select @out;



This is verified to work with the largest possible bigint. I did not code it with negative numbers in mind.

└> bt


Forum Etiquette: How to post data/code on a forum to get the best help
haiao2000
haiao2000
Right there with Babe
Right there with Babe (766 reputation)Right there with Babe (766 reputation)Right there with Babe (766 reputation)Right there with Babe (766 reputation)Right there with Babe (766 reputation)Right there with Babe (766 reputation)Right there with Babe (766 reputation)Right there with Babe (766 reputation)

Group: General Forum Members
Points: 766 Visits: 438
bteraberry (10/2/2012)
I'm not sure how logical this project is, but it seemed interesting to do this without looping. It was fun.

declare @b bigint = 31;
declare @out varbinary(128) = 0x;

-- simple inline tally table
with cteTen as
( select N from (values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) as t(N) ),
cteNums as
( select N = 0
union all
select top (63) -- the 64th bit would be bigger than a bigint max value
N = row_number() over(order by (select null))
from cteTen as cte1
cross join cteTen as cte2 ),
-- the meat of the query
cteInit as
( select cte.N
, ValThis = power(convert(decimal(20,0), 2), N)
, ValNext = power(convert(decimal(20,0), 2), N + 1)
from cteNums as cte )

select @out = @out + convert(varbinary(1), case when (@b - (floor(@b / cte.ValNext)) * cte.ValNext) >= cte.ValThis then 1 else 0 end)
from cteInit as cte
where cte.ValThis <= @b
order by cte.N desc;

select @out;



This is verified to work with the largest possible bigint. I did not code it with negative numbers in mind.


Wow this is way over my head :-) It works, I will runs it against the database and see how faster compare to the original code.

Thanks for spending time on this. alot to learn from this code. btw, can you help me understand the logics behind this query?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search