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 
Updated by