Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    2

    Unanswered: smallint vs int join with stats - suprizing!

    Is an index based on a smallint (16 bit) really faster than an index based on an int (32 bit)
    If so, how much...

    Four tables

    Table A: ID smallint (PK)
    Text varchar(50)

    Table B: ForeginID smallint (indexed - non unique)
    Text varchar(50)
    rowID int (PK)

    Table C: ID int (PK)
    Text varchar(50)

    Table D: ForeginID int (indexed - non unique)
    Text varchar(50)
    rowID int (PK)


    Table A and C contain identical data
    Table B and D contain identical data
    (Tables A and B were filled and then copied to Tables C and D)

    Tables A/C are loaded with 64,000 records (-32,000 to 32,000)
    Tables B/D are loaded with 6,400,000 records ForeginID loaded randomly with values between -32,000 and 32,000


    The purpose of this test is to find out if identical queries joined on a smallint are actually faster than int based.

    I ran 3 queries on each set:
    - Full select
    - Select on ID/Foregin ID
    - Select on Table2 RowID joined to table 1


    Here are the queries:

    #1. Full select (smallint) - grouped to limit result set
    -----------------------------------------------------------
    SELECT intAID, COUNT(intBID)
    FROM TESTintA
    INNER JOIN TESTintB ON intAID = intBID
    GROUP BY intAID
    ORDER BY COUNT(intBID) desc

    #2. Select on ID/Foregin ID (smallint)
    ------------------------------------------
    SELECT intAID, intBID, strATXT, strBTXT
    FROM TESTintA
    INNER JOIN TESTintB ON intAID = intBID
    WHERE intAID = 29120


    #3. Select on Table2 RowID joined to table 1 (smallint)
    ------------------------------------------
    SELECT intAID, intBID, strATXT, strBTXT
    FROM TESTintA
    INNER JOIN TESTintB ON intAID = intBID
    WHERE intPK = 1050


    #4. Full select (int) - grouped to limit result set
    ------------------------------------------
    SELECT lngCID, COUNT(lngDID)
    FROM TESTlngC
    INNER JOIN TEXTlngD ON lngCID = lngDID
    GROUP BY lngCID
    ORDER BY COUNT(lngDID) desc


    #5. Select on ID/Foregin ID (int)
    ------------------------------------------
    SELECT lngCID, lngDID, strTXTC, strTXTD
    FROM TESTlngC
    INNER JOIN TEXTlngD ON lngCID = lngDID
    WHERE lngCID = 29120

    #6. Select on Table2 RowID joined to table 1 (int)
    ------------------------------------------
    SELECT lngCID, lngDID, strTXTC, strTXTD
    FROM TESTlngC
    INNER JOIN TEXTlngD
    ON lngCID = lngDID
    WHERE intPK = 1050



    Here are the results: (run multiple times to verify)

    #1. Full select (smallint) - grouped to limit result set
    -----------------------------------------------------------
    (8 seconds) - before computing statistics on table
    (13 seconds) - after computing statistics on table


    #2. Select on ID/Foregin ID (smallint)
    ------------------------------------------
    (0 seconds)


    #3. Select on Table2 RowID joined to table 1 (smallint)
    ------------------------------------------
    (0 seconds)


    #4. Full select (int) - grouped to limit result set
    ------------------------------------------
    (8 seconds) - before computing statistics on table
    (7 seconds) - after computing statistics on table

    #5. Select on ID/Foregin ID (int)
    ------------------------------------------
    (0 seconds)


    #6. Select on Table2 RowID joined to table 1 (int)
    ------------------------------------------
    (0 seconds)


    Conclusion: Not only is there a negligible difference in select performance, generating stats on the smallint actually makes it slower.
    (perhaps there is some kind of conversion going on here behind the scenes?)

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I believe constants are considered integer for purposes of comparison. Try running the query with

    convert(smallint, value)

    for all of the constants in the smallint tests.

  3. #3
    Join Date
    Feb 2004
    Posts
    2
    The response time in the queries with criteria was negligible. My reference to the conversion was on the full select / join after statisitics were built.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well....it's still a GREAT 1st POST...

    I don't think I've seen it's equal...
    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
  •