Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 a 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 BIGINT columns or 4 INT columns.

My question is would it be more efficient to search and compare 4 INT columns or 2 BIGINT 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 INT or BIGINT respectively)

share|improve this question
2  
Did you try it? You are in a much better position to test your scenario, since you have your schema, your data, and your queries to run on your hardware and with your usage patterns. Asking us which is more efficient just means we have to somehow attempt to replicate your exact environment. –  Aaron Bertrand Feb 11 at 17:16
    
Well that's the issue, it's a system I am building, so technically it doesn't exist yet. I'm sure I'm not the first person to ask if there is a difference between searching for INT or BIGINT. I am just looking to see if anyone already has first hand experience with the two types and knows if there are any potential drawbacks, as well as if I am over complicating things. –  Nicholas Post Feb 11 at 17:26
    
@NicholasPost you seem to have a pretty good test scenario already built. I suggest you build a mock up and try them out. –  Zane Feb 11 at 17:33
    
Well that is what I will end up doing either way, I am just trying to get some guidance on which path to take so I don't have to go back and make major changes partway through the project. –  Nicholas Post Feb 11 at 17:46
    
Is the "text" version of the key supposed to be only 0-9, a-f (i.e., hex representation of a number), or does it contain any alphanumeric? –  Jon Seigel Feb 11 at 18:02

1 Answer 1

As other suggested, sometimes the best choice is to test it out yourself. I hate answering my own question, so if someone can provide a better answer then I might chose it if it better explains my results.

It turns out there is not UBIGINT64 in SQL, so I created the table as I described as follows and used decimal instead of BigInt:

CREATE TABLE [dbo].[KEYS](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [KEY] [nvarchar](max) NOT NULL,
    [BI1] [decimal](28, 0) NOT NULL,
    [BI2] [decimal](28, 0) NOT NULL,
    [i1] [int] NOT NULL,
    [i2] [int] NOT NULL,
    [i3] [int] NOT NULL,
    [i4] [int] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

I then wrote a script to populate the table with approximately 2.25 million rows of data and setup the following loop to see how long each one would take.

declare @x integer
set @x = 0
while @x < 100
begin
    --QUERIES WERE INSERTED HERE
    set @x = @x + 1
end

I then plugged the following three queries into the loop and recorded the results for 50 loops and 100 loops.

select * from IntTypes where i1 = 64003101 and i2 = 64003107 and i3 = 64003144 and i4 = 640031031


50 Loops: 9 seconds
100 Loops: 18 seconds

select * from IntTypes where BI1 = 15284527576400310788 and BI2 = 16825458760271544958


50 Loops: 11 seconds
100 Loops: 23 seconds

select * from IntTypes where [KEY] = 'a123f-5n12d1km9-3cmc32mc32-32cm0429c-243g4453-g43f43'


50 Loops: 54 seconds
100 Loops: 1 minute 48 seconds

Perhaps the results would be different if I did use a BIGINT instead of a DECIMAL, but comparing 4 integer columns seems to outperform comparing the two decimal columns. Both of those completely destroyed the text search results timing, but overall it looks like I will be using integer columns.

So to sum it up and answer my own question (sort of), it is faster to search 4 integer columns than searching two decimal columns.

share|improve this answer
    
Any chance you'd test your queries with some indexes in place? One obvious choice would be an index on all 4 INT columns (as key cols), and another on both decimal columns? Also, are you always searching with AND conditions, or also have the possibility for OR/IN (as this changes the indexing options a bit)? Why decimal(28) if you can manage with bigint (storage difference is 13 bytes vs 8 bytes -> reading/writing ~50% more)? –  Marian Feb 12 at 8:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.