# Thread: smallint vs int join with stats - suprizing!

1. Registered User
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. Registered User
Join Date
Jan 2003
Location
Massachusetts
Posts
5,862
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. Registered User
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. Window Washer
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...

#### Posting Permissions

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