#22716Speed up Oracle layer by not defaulting to dbms_lob.instr for LIKE query
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 $output .= ‘(dbms_lob.instr(’ . trim(($v[‘table’] ? $v[‘table’] . ‘.’ : ‘’) . $v[‘field’]) . ‘, ‘ . $compareValue . ‘,1,1) > 0)’;
} else {
$compareValue = $v[‘value’]1 . $this->compileAddslashes(trim($v[‘value’]0, ‘%’)) . $v[‘value’]1;
}
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)
Reviews 0
No change resolves or names this issue in its commit message, and no comment links one.