Bug #22716
closed
Speed up Oracle layer by not defaulting to dbms_lob.instr for LIKE query
0%
Description
I have a dbal tweak which will bring a big speed improvement on Oracle.
One of the most heavily used queries in TYPO3 is that for fe_user permission checking. This is done multiple times on every (first) page request. The field that is checked is fe_group which is created as a string field on oracle.
The LIKE clause always uses dbms_lob.instr. This is very expensive on non-lob fields. The code should be changed to check the field type and adjust the query based on that information. I don't know a lot about the dbal but there seems to be some field type cache that may be used for this.
In dbal/class.ux_t3lib_sqlparser.php (compileWhereClause), an extra check can be done on the field. if it is fe_group (or a normal string type), then perform a normal instr instead of dbms_lob.instr.
Index: dbal/class.ux_t3lib_sqlparser.php
===================================================================
--- dbal/class.ux_t3lib_sqlparser.php (revision 33480)
++ dbal/class.ux_t3lib_sqlparser.php (working copy)@ -529,7 +529,12 @
} else {
$compareValue = $v['value']1 . $this->compileAddslashes(trim($v['value']0, '%')) . $v['value']1;
}
- $output .= '(dbms_lob.instr(' . trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']) . ', ' . $compareValue . ',1,1) > 0)';
if ($v['field'] == 'fe_group') {
+ $output .= '(instr(' . trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']) . ', ' . $compareValue . ',1,1) > 0)';
+ }
+ else {
+ $output .= '(dbms_lob.instr(' . trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']) . ', ' . $compareValue . ',1,1) > 0)';
+ }
break;
default:
$output .= ' ' . $v['comparator'];@ -572,4 +577,4 @
include_once($TYPO3_CONF_VARS[TYPO3_MODE]['XCLASS']['ext/dbal/class.ux_t3lib_sqlparser.php']);
}
-?>
\ No newline at end of file
+?>
This will greatly speed up TYPO3 FE.
(issue imported from #M14479)
Files
Updated by Xavier Perseguers over 14 years ago
I confirm that this greatly improve the performance:
SELECT * FROM "be_users" WHERE dbms_lob.instr("be_users"."password", '0f5366', 1, 1) > 0
around 0.8 sec.
SELECT * FROM "be_users" WHERE instr("be_users"."password", '0f5366', 1, 1) > 0
around 0.4 sec
This is for first query because after that each of them is cached by Oacle and the difference is nearly not noticeable. However in TYPO3 nearly no query is done twice (as many queries include a timestamp).
Updated by Xavier Perseguers over 14 years ago
v2 checks the underlying type.
Limitation: the condition must include the table name. Perhaps there is a way to retrieve the default table name when empty but it's too late for me to properly think about it.
Michiel, does it still works as expected for you? If not, could you write here the full query (for best history purpose).
Updated by Michiel Roos over 14 years ago
I'll try to create a testing environment in the coming weeks.
This is the query that slows things down:
SELECT * FROM "pages" WHERE "uid" = 1 AND "pages"."deleted" = 0 AND "pages"."hidden" = 0 AND ("pages"."starttime" <= 1264345260) AND ("pages"."endtime" = 0 OR "pages"."endtime" > 1264345260) AND NOT ("pages"."t3ver_state" > 0) AND "pages"."doktype" < 200 AND ("pages"."fe_group" = '' OR "pages"."fe_group" IS NULL OR "pages"."fe_group" = '0' OR ((dbms_lob.instr("pages"."fe_group", ', 0, ', 1, 1) > 0) OR (dbms_lob.instr("pages"."fe_group", '0, ', 1, 1) > 0) OR (dbms_lob.instr("pages"."fe_group", ', 0', 1, 1) > 0) OR "pages"."fe_group" = '0') OR ((dbms_lob.instr("pages"."fe_group", ', -1, ', 1, 1) > 0) OR (dbms_lob.instr("pages"."fe_group", '-1, ', 1, 1) > 0) OR (dbms_lob.instr("pages"."fe_group", ', -1', 1, 1) > 0) OR "pages"."fe_group" = '-1'))
And here is another one that is used more frequently (for every content element on a page):
SELECT * FROM "tt_content" WHERE "uid" IN (62) AND "tt_content"."deleted" = 0 AND "tt_content"."t3ver_state" <= 0 AND "tt_content"."hidden" = 0 AND ("tt_content"."starttime" <= 1264487640) AND ("tt_content"."endtime" = 0 OR "tt_content"."endtime" > 1264487640) AND ("tt_content"."fe_group" = '' OR "tt_content"."fe_group" IS NULL OR "tt_content"."fe_group" = '0' OR ((dbms_lob.instr("tt_content"."fe_group", ', 0, ', 1, 1) > 0) OR (dbms_lob.instr("tt_content"."fe_group", '0, ', 1, 1) > 0) OR (dbms_lob.instr("tt_content"."fe_group", ', 0', 1, 1) > 0) OR "tt_content"."fe_group" = '0') OR ((dbms_lob.instr("tt_content"."fe_group", ', -1, ', 1, 1) > 0) OR (dbms_lob.instr("tt_content"."fe_group", '-1, ', 1, 1) > 0) OR (dbms_lob.instr("tt_content"."fe_group", ', -1', 1, 1) > 0) OR "tt_content"."fe_group" = '-1'))
Updated by Xavier Perseguers over 14 years ago
_v3 includes your "most frequent" query as unit test.
Updated by Xavier Perseguers over 14 years ago
Committed to:
- DBAL_trunk (rev. 33885)
- DBAL_1-1 (rev. 33886)