I am building an application that will need to handle extremely high amounts of traffic. The traffic will consist of incoming requests for information from an MSSQLa SQL Server 2012 database. When a request is made, a unique KEY is passed that will be used to identify the account that the information belongs to. This KEY is an alphanumeric string and the table that contains the KEYs can contain millions of rows of data. The length and data type of the KEY can change based on situation.
Example Request
KEY=a123f-5n12d1km9-3cmc32mc32-32cm0429c-243g4453-g43f43
TYPE=123
--The system will look up data for type '123' in the database for account 'a123f-5n12d1km9-3cmc32mc32-32cm0429c-243g4453-g43f43'
The main focus will be on the speed of the query, so I am not concerned too much with database size or memory utilization. I know that searching for integers is a lot more efficient than searching text, so I came up with a way to easily convert the strings into integers. The goal is to make an efficient way to take a string, convert it to an integer, and then perform a quick search to return the results. The problem is that the integers generated by the strings will either take up two BIGINTBIGINT columns or 4 INTINT columns.
My question is would it be more efficient to search and compare 4 INTINT columns or 2 BIGINTBIGINT columns? Or would searching for the text be faster?
BIGINT Sample Query
SELECT (FIELDS)
FROM KEYS (JOIN .......)
WHERE C1 = xxxxxxxxxxxxxxxxxxxx and C2 = xxxxxxxxxxxxxxxxxxxx
INT Sample Query
SELECT (FIELDS)
FROM KEYS (JOIN .......)
WHERE C1 = xxxxxxxxxx and C2 = xxxxxxxxxx and C3 = xxxxxxxxxx and C4 = xxxxxxxxxx
Text Sample Query
SELECT (FIELDS)
FROM KEYS (JOIN ........)
WHERE KEY = 'a123f-5n12d1km9-3cmc32mc32-32cm0429c-243g4453-g43f43'
(NOTE: All columns are either type INTINT or BIGINTBIGINT respectively)
