Performance difference using LIKE between :PARAM and INLINE ------------------------------------------------------------
Key: CORE-5740 URL: http://tracker.firebirdsql.org/browse/CORE-5740 Project: Firebird Core Issue Type: Bug Affects Versions: 3.0.3 Environment: Local database with Windows 10 and Firebird-3.0.3.32900-0_x64 Reporter: Rafael 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 -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira ------------------------------------------------------------------------------ Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel