Issue Bug Closed

#22716Speed up Oracle layer by not defaulting to dbms_lob.instr for LIKE query

Michiel Roos 05/26/10, 10:48 updated 06/25/10, 23:15

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)

Reviews 0

No change resolves or names this issue in its commit message, and no comment links one.