SQL Server Performance Forum – Threads Archive

smallint vs int index plus statistics. Surprizing

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?)

---

look at the execution plans,
when you have:
ColA = 123 (any number) where ColA is a small int, SQL Server interprets the number as being type "int"
hence the smallint column must be converted to int,
then form good measure SQL Server checks +/-1 of that value hence, try: ColA = CONVERT(smallint, 123)

---

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.

---

]]>