June 13, 2003 at 12:22 am
Background:
I work in the clothing industry and historically clothes have always been counted in dozens in the format 9.99 where 1.04 = 16, 1.11 = 23, 2.00 = 24. We have always stored our data in our database in singles and convert to and from dozens for screen displays. We receive our goods in boxes from the factories. For example is we might receive a box of boxer shorts. The boxer shorts are pre-packaged in packs of 3. The box has 6 packs of box shorts making a total of 18 pairs of boxer shorts in a box. With me so far?
So I have a box of 1.06 dozens, a pack size of 3 and I want to know how many packs are in the box. For the past couple of years we have been using this code:
declare @PacksPerBox tinyint
declare @boxsize real
declare @packsize smallint
set @boxsize = 1.06
set @packsize = 3
select @PacksPerBox = convert(tinyint,(((Round(@Boxsize,0,1)*12)+(CAST((
@Boxsize * 100) as int)%100))/@Packsize))
print @PacksPerBox
This code runs in a SQL Server 2000 UDF but the error also occurs in a SQL Server v7.0 SP. The result of this calculation should be 6 but is in fact 5. The issue is with the code CAST((@Boxsize * 100) as int) which returns 105 instead of 106.
My question is:
Is this an actual error in SQL Server? Is it poor practice by ourselves on the way we have put together the SQL statement?
If you try many other box sizes e.g. 4.06 the formula works fine. I have solved the problem by changing the boxsize declaration from real to float but I'm wondering whether I am just moving the problem about rather than solving it?
Shandy
Shandy
June 13, 2003 at 1:24 am
Change the datatype for @boxsize to deciaml(5,2) instead of real and it works.
[font="Verdana"]Markus Bohse[/font]
June 13, 2003 at 1:55 am
Hi Shandy,
quote:
Change the datatype for @boxsize to deciaml(5,2) instead of real and it works.
That's the way real (and float) work. They are more or less accurate approximations. This is just what they are, and cannot be changed.
(BTW, haven't some programmers got rich funneling those trailing bits of floating point numbers into their checking accounts?)
As Markus has suggested, you use NUMERIC/DECIMAL to get precise numbers.
If possible, change also the underlying data type in the db to these type, so you don't run into these problems. They take up a little bit more disk space, but that shouldn't be a serious issue today.
From my knowledge approximate data types are used when very large numbers are to be calculated, for example in astronomy and not in commerce or industry
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
June 13, 2003 at 4:37 am
Thanks very much to both of you. I shall implement the changes to our database as you suggest 🙂
Shandy
Shandy
June 13, 2003 at 4:53 am
Or you could store it as an int with an implicit decimal point,
eg 1=1, 11=11, 100=12, 200=24 etc.
June 16, 2003 at 8:32 am
Hi planet115,
"Or you could store it as an int with an implicit decimal point -
eg 1=1, 11=11, 100=12, 200=24 etc."
I think, storing it as integer in such manner might not be a good idea. It will pose limitations on all scripts and sqls.
Is such a technique normally used in practice? What do others have to say on this technique?
Ram
June 19, 2003 at 7:17 am
>>I think, storing it as integer in such manner might not be a good idea.
>>It will pose limitations on all scripts and sqls.
Can you give an example?
In this particular case, the proposed solution is almost certainly better as it will not require rewriting existing code.
However, in the general case, I think an integer would serve as well, with the following (marginal) benefits:
-- Smaller data type
-- Quicker for mathematical operations (unresearched assertion - comments?)
-- Tenuously ... an integer should be good as gold if there is ever a need to port the system to another platform.
To go way beyond the scope of the original question, given free choice (I know, in real life it's not often like that) I'ld probably be looking into the pros and cons of storing the actual number of items as an integer, and converting to and from the dozen's notation as the need arose ... of course this might not be appropriate in the context, particularly if supplier information is being imported in the x.xx format.
June 19, 2003 at 7:52 am
planet115,
Basically, you were suggesting to use a kind of encryption. (eg 1=1, 11=11, 100=12, 200=24 etc).
If you use encryption while storing the data, you should use decription while reading it. I do not know the merits of this technique or the feasibility of this.
Perhaps you have done this before and can handle this in some stored procedures.
Thanks for your input.
Ram
June 19, 2003 at 9:38 am
Look back at the original post. The number of items are represented as dozens (12's), e.g.
1.04=16
That means (1 * 12) + (10 * 0.4) = 16
If you decide to 'encrypt' with an integer then this doesn't change much, except now:
104 = 16
Which you convert as: ((104 div 100) * 12) + (104 mod 12) = 16
When I made my suggestion, I was guessing that the maths of converting to and fro would execute quicker in the second (integer) case. As this was pure supposition on my part, I decided to run a rough and ready test of this. The results suggest that in the example calculation presented by the original poster, the integer method executes approximately 25% quicker. The test I've done could be unfair (I'm not jedi enough to know if my code is comparing apples with apples - maybe caching is playing an unfair role). Anyway I'll post the code separately and welcome comments from people with relevant expertise.
For 10 million iterations on my PC with no other applications running (but didn't shut down services etc.), this is what I got.
Test 1:
Method 1 (decimal): 85973ms
Method 2 (integer): 66076ms
Test 2
Method 1 (decimal): 84513ms
Method 2 (integer): 63680ms
Test 3
Method 1 (decimal): 84943ms
Method 2 (integer): 63893ms
June 19, 2003 at 9:39 am
The test code (I'm sure someone can find a problem with this 🙂 )
------------------------
CREATE PROCEDURE up_TestSpeed
AS
declare @PacksPerBox tinyint
declare @decBoxsize decimal(5,2)
declare @intBoxsize int
declare @packsize smallint
declare @dteTimer as datetime
declare @intCounter as int
declare @intIterations as int
set @decBoxsize = 1.06
set @intBoxsize = 106
set @packsize = 3
set @intIterations=10000000
set @intCounter=0
set @dteTimer=getdate()
while (@intCounter<@intIterations)
begin
select @PacksPerBox = convert(tinyint,(((Round(@decBoxsize,0,1)*12)+(CAST((@decBoxsize * 100) as int)%100))/@Packsize))
set @intCounter=@intCounter+1
end
select datediff(ms, @dteTimer, getdate()),'Method 1 (decimal)'
set @intCounter=0
set @dteTimer=getdate()
while (@intCounter<@intIterations)
begin
select @PacksPerBox = (((@intBoxsize / 100) * 12) + (@intBoxsize % 100)) / @PackSize
set @intCounter=@intCounter+1
end
select datediff(ms, @dteTimer, getdate()), 'Method 2 (integer)'
GO
June 19, 2003 at 10:24 am
Hi planet115,
Thanks for your detailed posts. I have no contest to the argument that integer math is faster than fractional math.
As long as you use stored procedures while retrieving and storing the data, I think your suggestion will work faster than the original method used by Shandy.
In my earlier post, I mentioned that 'It will pose limitations on all scripts and sqls.'
The limitation is, it mandates the use of stored procedure in all sqls and scripts accessing the data (to encrypt and decrypt). If this is not going to be a limitation, ie, you will never have to query the data outside of stored procedure, there will be no problems.
I appreciate your insights.
Have a good one.
Ram
June 19, 2003 at 10:30 am
>>The limitation is, it mandates the use of stored procedure in all sqls and scripts
>>accessing the data
Can't see this myself but no matter.
>>Have a good one.
Thanks. You too 🙂
June 20, 2003 at 9:30 pm
Not answering the question, but the concept is too cool!
May be my inexperience, but I've not seen a numbering system like this before.
Is there an official name for this. Twelvish?
June 22, 2003 at 12:41 pm
Only in middle-earth.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply