Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Mar 2007
    Posts
    97

    Unanswered: Convert int to float

    Hi,

    why does converting integer to float take so long? Its a column with about 5 Million rows.
    I want to avoid cast(inumber1 as float) / cast(inmuber2 as float), thats why converting them. Queries should be a bit faster after that.. hope so
    Thanks a lot

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What method are you using to convert?
    How long is so long?

    Your last sentence doesn't make a lot of sense - if you're converting datatypes during your select statements that's adding processing time to your query, so surely it will not make them faster.

    In fact, please post the SQL statement in question as well
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    First of all, converting anything to float is a "computationally noisy" operation... It means a lot of work for the CPU no matter how you do it, just because of how the FLOAT datatype is stored.

    My preference for doing what you've described is peculiar, but it works well for me... I would try using:
    Code:
    SELECT 1e0 * inumber1 / inmuber2
    -PatP

  4. #4
    Join Date
    Mar 2007
    Posts
    97
    Thanks a lot Pat, that's a nice trick!

  5. #5
    Join Date
    Mar 2007
    Posts
    97
    Quote Originally Posted by georgev
    What method are you using to convert?
    How long is so long?
    It was
    Code:
    Alter Table A Alter Column C float NOT NULL
    and took about 20 Minutes.

    Quote Originally Posted by georgev
    if you're converting datatypes during your select statements that's adding processing time to your query, so surely it will not make them faster.
    That's why I did the Alter Command. I just wondered why its such a big thing from int to float
    Last edited by silas; 06-22-07 at 03:57.

  6. #6
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    for very large tables, it probably would be faster to export the table to a flat file using bcp, then reimport to a different table with the new datatype, again with bcp.

    I tend to shy away from ALTER TABLE.

  7. #7
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    As I see it, there is three main reasons for this operation to take take quite a bit of time:

    First, creating the float value requires processing power. However, this does not explain how updating a table with 5M records takes so long.

    Second, A float takes 8 bytes, whereas int takes 4 bytes. So, depending on the width of the table, you may experience page splits (There is no longer room for all the rows in the page, so some rows have to be moved to a new page). I'm pretty sure this will be especially bad if there is a clustered index on the table.

    And last, if this is done in an active environment, keep in mind that you'll require exclusive schema lock on the table, so your command will have to wait until a point in time when there is no activity against the table.

    So, finally... I don't think queries will run any faster. You'll probably end up having more data pages, and I'm pretty sure that the negative impact from this is of greater magnitude than the cast from int to float.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  8. #8
    Join Date
    Mar 2007
    Posts
    97
    Wow, that was a really detailed comment :-) thank you

    As I already have converted, does it make any sense to reconvert to integer regarding the negative impact of greater magnitude?

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    int to float

    What the hell for?

    There's no precision in int. Are you looking for precision? Then use decimal.

    Let me ask...does anyone use float or real, and if so what for?

    I gotta do a poll
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Mar 2007
    Posts
    97
    Have you ever divided two integer values?

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Brett Kaiser
    int to float

    What the hell for?

    There's no precision in int. Are you looking for precision? Then use decimal.

    Let me ask...does anyone use float or real, and if so what for?

    I gotta do a poll
    I think you mean "There's no precision in float". And a float is more precise when you do complex math (the sort of complex I don't really understand frankly). Michael Valentine Jones did a good proof on SQLTeam. I'm sure Pat could explain too. I've got a link to a page that goes into this in depth but have never had chance to read it yet.
    EDIT - linky: http://docs.sun.com/source/806-3568/ncg_goldberg.html

    Anyway - you are right though - for most purpses you would prefer decimal over floats.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by silas
    Have you ever divided two integer values?
    You get an integer return unless you explicitly cast or do what Pat did. Did you seriously get a performance improvement starting off with floats?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Mar 2007
    Posts
    97
    Correct me, if I'm wrong, but float takes less space than decimal, doesn't it?

  14. #14
    Join Date
    Mar 2007
    Posts
    97
    Quote Originally Posted by pootle flump
    You get an integer return unless you explicitly cast or do what Pat did. Did you seriously get a performance improvement starting off with floats?
    Not really, but 5M rows is still pretty small. Frankly, I thought one of you could give me that answer ;-)

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    BOL

    float and real
    Approximate number data types for use with floating point numeric data. Floating point data is approximate; not all values in the data type range can be precisely represented.

    Decimal

    Precision Storage bytes
    1 - 9 5
    10-19 9
    20-28 13
    29-38 17


    bigint

    Integer (whole number) data from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807). Storage size is 8 bytes.

    int

    Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647). Storage size is 4 bytes. The SQL-92 synonym for int is integer.

    smallint

    Integer data from -2^15 (-32,768) through 2^15 - 1 (32,767). Storage size is 2 bytes.

    tinyint

    Integer data from 0 through 255. Storage size is 1 byte.

    float [ ( n ) ]

    Is a floating point number data from - 1.79E + 308 through 1.79E + 308. n is the number of bits used to store the mantissa of the float number in scientific notation and thus dictates the precision and storage size. n must be a value from 1 through 53.


    So How many bits in a byte...I think it's 8...but bol doesn't say what the default for float alone is


    Looks like in EM in table design that float defaults to 8 bytes, so that would be half of what int is
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •