I am using SQL Server 2016 Express and a Java application with the JDBC driver version 4.2.
My database has sorting Latin1_General_CI_AS(case insensitive). My table has a type column VARCHAR(128) NOT NULL. This column has a unique index.
My test case is as follows:
After entering 150,000 lines with a length of 48 characters, I make 200 selections of randomly selected existing lines. I measure the total execution time of all requests.
Then I drop the index, modify the table to change the sorting of the columns to Latin1_General_CS_AS(case sensitive) and again create a unique index.
Then 200 choose the total time longer.
In both cases (CI and CS), the execution plans are simple and identical (the search is performed using the index).
The query execution time depends not only on case sensitivity. When matching CS, it grows faster if the strings have the same prefixes. Here are my results (runtime in seconds):
+----+---------+------------------+-------------------+-------------------+
| + RND(48) + CONST(3)+RND(45) + CONST(10)+RND(38) + CONST(20)+RND(28) +
+----+---------+------------------+-------------------+-------------------+
| CI + 6 + 6 + 7 + 9 +
| CS + 10 + 20 + 45 + 78 +
+----+---------+------------------+-------------------+-------------------+
The longer the identical random-line prefix, the longer the case-sensitive query takes.
- Why is case-insensitive search for a column insensitive faster than for case-sensitive case insensitive?
- What is the reason for the same prefix behavior?
source
share