Oracle select queries speed up

Yii 1.1 Tips, Snippets and Tutorials
1

Hi!

For speed up Oracle queries on large (or medium) tables, we can use "FIRST_ROWS" SQL hint.

Where we call fildAll(), yii makes query like this:

[sql]

WITH USER_SQL AS (SELECT * FROM FB_CLIENTS ORDER BY id desc),

 PAGINATION AS (SELECT USER_SQL.*, rownum as rowNumId FROM USER_SQL)


 SELECT *   FROM PAGINATION    WHERE rowNumId > 75 and rownum <= 25;

[/sql]

We need only 25 rows, but select all present in table, then select 100 from all, then select 25.

We can optimize this process by adding hint to query:

[sql]

WITH USER_SQL AS (SELECT /+ FIRST_ROWS(100)/ * FROM FB_CLIENTS ORDER BY id desc),

 PAGINATION AS (SELECT USER_SQL.*, rownum as rowNumId FROM USER_SQL)


 SELECT *   FROM PAGINATION    WHERE rowNumId > 75 and rownum <= 25;

[/sql]

These query first selects 100 rows from table, then selects them once more with rownum, then selects 25, those we need.

In my FB_CLIENTS table only 45000 records. First query (without hinting) executes 1.2 sec, second (with hint) - 0.18 sec.

Feel the difference :)

We can add this hint to all Select queries with limits applied by adding these lines before "$sql = <<<EOD" line in applyLimit function in <yii_dir>/framework/db/schema/oci/COciCommandBuilder.php




	if (substr($sql,0,7)=='SELECT ')

	{

	    $sql = 'SELECT /*+ FIRST_ROWS('.($limit+$offset).') */ '.substr($sql,7);

	}

See also: Oracle hints PL/SQL

And yes, sorry for my English…

2

Hi,

While load test, I observed that find() & findAll() both the functions are taking pagination query added in any select command.

When checked in framework file its written hard coded there.

This will be applicable for all, is there any way to remove it by sending some extra parameter in function, so when we need a grid/list view it should be avail in select query but when its just to be use to get data it should not be applicable.

Or any alternative approach? As above given solution(limit) is not working at my end.