Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2011
    Location
    Centurion, South Africa
    Posts
    20

    Unanswered: Performances of Smallint, Integer and Bigint

    Hello

    Apart from storage benefits are there any other benefits of using datatypes i.e. SMALLINT, INTEGER, BIGINT for specific numeric columns.
    Can anyone please put some light on the performances of queries fired against Smallint, Integer and Bigint.

    1. Will a numeric column defined as SMALLINT for few thousand records perform better than defining it as an INTEGER or BIGINT data type. (Keeping in mind that the size is not going to be more than 5 INTEGER values)

    2. Will a numeric column defined as INTEGER perform better than defining it as an BIGINT data type. (Keeping in mind that the size is not going to be more than 10 INTEGER values)

    Thanks & Regards
    Satyajit

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ..dunno
    why don't you try it out for yourself, run some metrics and then report back.

    my expectation would be that there will be little if any difference between the datatypes unless the size of the item in bytes exceeds the bit width of the processor

    in modern times where disc space costs bobbins its arguable that you will see any significant advantages in using say SMALLINT over any other integer type.

    usually you can reclaim more resources from better query design and better operational analysis.. ie working out what queries are hammering the server and try to decide if thats just how it is for the required task or the process can benefit from redesigning

    it could be the queires need refining to eliminate unneeded rows early on as possible in a join, appropriate use of SQL constructs, appropriate split of the workload (not everything HAS to be done inside a query, you can use other layers to handle some work and take the load of the server

    but then agian that requires competent DBA's, Analyst's and Developers
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by satyajit View Post

    1. Will a numeric column defined as SMALLINT for few thousand records perform better
    Columns do not perform anything. Performance of what exactly you are concerned about?
    ---
    "It does not work" is not a valid problem statement.

Tags for this Thread

Posting Permissions

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