Post reply

Data types - Unexpected results

  • April 22, 2007 at 6:26 pm

    #177518

    Hello everyone,

    I am pretty new to this stuff so maybe my result is really not all that unexpected.  However, I have a table with WAY too many columns of information (just using it for testing / learning).  This table has aproximately 400,000 rows of information and there are aproximately 30 columns in the table.

    22 of these columns are of data type bigint

    My test:

    If I do a select for all rows in the table the query time is currently 19-20 seconds. 

    In learning about data types, I thought I would see a signifficant decrease in query time if I were to change the data type for the 23 bigint items to small int since bigint uses 8 bytes of storage/mem and smallint only uses 2 bytes of storage/mem.

    Since I have 23 columns of data that were using 8 bytes each my thinking was as follows.

    At least (23 x 8 x 400,000) 76MB of storage being used on disk as well as 76MB of memory used when querying the table.

    So, I went through and modified the table so that all of the bigint definitions were changed to smallint, thinking this would not only free up storage space (not the primary concern) but would also signifficantly increase the speed of the query.

    Much to my surprise, the query speed did not increase at all.

    Questions:::

    Is there anything I need to reset / clear out in SQL before the new column definitions will take affect?

    Am I way off base in what I was expecting to happen here?

    Any assistance in clearing this up would be greatly appreciated.

    Thanks all,

    Bob

  • April 23, 2007 at 12:50 am

    #701875

    It's highly unlikely to see much of a change in speed for a data type change.

    If you haven't rebuilt the clustered index, then the data will still occupy the same amount of pages, hence the tme for IOs is the same. Same amount of pages means memory usage is the same.

    If you'e filtereing on those columns and the parameters are of type bigint then you may actually see an increase in query time, due to implicit conversions. That will only happen however if you have an index on the colum in question.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply