You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
I have a local database with 1.2 million "PARCEIROS" records. The NOME is a index column declared as:
NOME VARCHAR(50) CHARACTER SET ISO8859_1 NOT NULL COLLATE PT_BR
Using ibexpert, there are substancial performance difference using LIKE between 1 and 2 SQL modes above with same PLAN:
**STARTING doesn´t occur the same problem.
-----------------------------------------
--1. When I Inform 'RAFAEL%' in SQL inline
-----------------------------------------
SELECT NOME
FROM PARCEIROS
WHERE (NOME LIKE 'RAFAEL%')
ORDER BY NOME
-----------------------------------------
Plan
PLAN (PARCEIROS ORDER K_PARCEIROS_NOME_ASC)
------ Performance info ------
Prepare time = 16ms
Execute time = 16ms
Avg fetch time = 0,70 ms
Current memory = 21.836.360
Max memory = 25.249.672
Memory buffers = 2.048
Reads from disk to cache = 1.911
Writes from cache to disk = 0
Fetches from cache = 6.043
-----------------------------------------
--2. When I Inform 'RAFAEL%' in the NOME param
-----------------------------------------
SELECT NOME
FROM PARCEIROS
WHERE (NOME LIKE :NOME)
ORDER BY NOME
-----------------------------------------
Plan
PLAN (PARCEIROS ORDER K_PARCEIROS_NOME_ASC)
------ Performance info ------
Prepare time = 31ms
Execute time = 15s 156ms
Avg fetch time = 658,96 ms
Current memory = 24.125.112
Max memory = 25.249.672
Memory buffers = 2.048
Reads from disk to cache = 926.161
Writes from cache to disk = 1
Fetches from cache = 2.992.118
The text was updated successfully, but these errors were encountered:
Not a bug. With a literal provided, the engine is clever enough to understand that the pattern starts with non-wildcard character, so LIKE becomes equivalent to STARTING WITH and range index scan can be used. With a parameter provided, the engine expects the worst case (e.g. pattern starting with a wildcard) and performs a full table/index scan instead.
You may see the difference in the detailed (aka explained) plan output.
Submitted by: Rafael Dipold (dipold)
I have a local database with 1.2 million "PARCEIROS" records. The NOME is a index column declared as:
NOME VARCHAR(50) CHARACTER SET ISO8859_1 NOT NULL COLLATE PT_BR
Using ibexpert, there are substancial performance difference using LIKE between 1 and 2 SQL modes above with same PLAN:
**STARTING doesn´t occur the same problem.
-----------------------------------------
--1. When I Inform 'RAFAEL%' in SQL inline
-----------------------------------------
SELECT NOME
FROM PARCEIROS
WHERE (NOME LIKE 'RAFAEL%')
ORDER BY NOME
-----------------------------------------
Plan
PLAN (PARCEIROS ORDER K_PARCEIROS_NOME_ASC)
------ Performance info ------
Prepare time = 16ms
Execute time = 16ms
Avg fetch time = 0,70 ms
Current memory = 21.836.360
Max memory = 25.249.672
Memory buffers = 2.048
Reads from disk to cache = 1.911
Writes from cache to disk = 0
Fetches from cache = 6.043
-----------------------------------------
--2. When I Inform 'RAFAEL%' in the NOME param
-----------------------------------------
SELECT NOME
FROM PARCEIROS
WHERE (NOME LIKE :NOME)
ORDER BY NOME
-----------------------------------------
Plan
PLAN (PARCEIROS ORDER K_PARCEIROS_NOME_ASC)
------ Performance info ------
Prepare time = 31ms
Execute time = 15s 156ms
Avg fetch time = 658,96 ms
Current memory = 24.125.112
Max memory = 25.249.672
Memory buffers = 2.048
Reads from disk to cache = 926.161
Writes from cache to disk = 1
Fetches from cache = 2.992.118
The text was updated successfully, but these errors were encountered: