Comparison of the efficiency of database primary key int and STR
Source: Internet
Author: User
Compare | data | database
And see this passage:
The primary key uses the integral type to be able to greatly improve the query efficiency, but the character type compares the cost to compare the integral type the expense to be much much, uses the character data as the primary key to cause the data inserts, the update and the inquiry efficiency to reduce. This reduction may not be noticed when the amount of data is small, but small improvements can increase the response speed of the system when the volume of data is large.
I did an experiment, using MSSQL Enterprise Manager to build two sheets of tint and tstr,
TInt
{
intId int PRIMARY KEY
Intvalue int
}
Tstr
{
Strid varchar () PRIMARY KEY
strvalue varchar (20)
}
The data to these two tables are intid = Intvalue = 1 ~ 300000,strid = strvalue = ' 1 ' 300000 '. The MFC encapsulated ODBC is then executed separately
Intrslt.open (CRecordset::d ynaset, "select * from TInt WHERE intId = 123456", crecordset::readonly);
And
Strrslt.open (CRecordset::d ynaset, "select * from tstr WHERE Strid = ' 123456 '", crecordset::readonly);
1000 times.
Time is 1392 milliseconds and 1452 milliseconds, respectively.
Same execution
Intrslt.open (CRecordset::d ynaset, "select * from TInt WHERE intvalue = 123456", crecordset::readonly);
And
Strrslt.open (CRecordset::d ynaset, "select * from tstr WHERE strvalue = ' 123456 '", crecordset::readonly);
1000 times.
Time is 58955 milliseconds and 172227 milliseconds, respectively.
If you create a new primary key (Primary key) with MS SQL Server, SQL Server creates a clustered (CLUSTERED) index by default, and the number of comparisons needed to locate a data using a clustered index should be minimal. The INSERT or update operation may cause the entire row to move a large amount of data, but this is the same for the INT and STR primary keys.
Why would the author say that? Now I don't think that changing the STR primary key to int is the key to improving database efficiency, which is trivial compared to using database connection pooling or optimizing SQL statements.
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
A Free Trial That Lets You Build Big!
Start building with 50+ products and up to 12 months usage for Elastic Compute Service