Yii2 Oracle performance

Question

I use Yii2 a lot but normally with MySQL. This is my first time using it with Oracle. I am finding the performance quite poor though. I've done some googling around and see that although it doesn't appear that common to use Yii2 + Oracle there does seem to be people talking about it being quite slow.

I'm wondering what advice/tips/processes/packages can be employed to improve performance?

I am loading a very small table. It has 8 rows, the entire table has 4 columns. They're are 27 DB calls using 6.4MB of data, that takes about 3 seconds to run. Looking at the logs I see a lot of stuff I don't really understand:

 SELECT D.CONSTRAINT_NAME, D.CONSTRAINT_TYPE, C.COLUMN_NAME, C.POSITION, D.R_CONSTRAINT_NAME, E.TABLE_NAME AS TABLE_REF, F.COLUMN_NAME AS COLUMN_REF, C.TABLE_NAME 
 FROM 
 ALL_CONS_COLUMNS C 
 INNER JOIN ALL_CONSTRAINTS D ON D.OWNER = C.OWNER AND D.CONSTRAINT_NAME = C.CONSTRAINT_NAME
 LEFT JOIN ALL_CONSTRAINTS E ON E.OWNER = D.R_OWNER AND E.CONSTRAINT_NAME = D.R_CONSTRAINT_NAME 
 LEFT JOIN ALL_CONS_COLUMNS F ON F.OWNER = E.OWNER AND F.CONSTRAINT_NAME = E.CONSTRAINT_NAME AND F.POSITION = C.POSITION

and

SELECT a.column_name, a.data_type, a.data_precision, a.data_scale, a.data_length,
a.nullable, a.data_default,
(   SELECT D.constraint_type
    FROM ALL_CONS_COLUMNS C
    inner join ALL_constraints D on D.OWNER = C.OWNER and D.constraint_name = C.constraint_name
    WHERE C.OWNER = B.OWNER
       and C.table_name = B.object_name
       and C.column_name = A.column_name
       and D.constraint_type = 'P') as Key,
com.comments as column_comment
FROM ALL_TAB_COLUMNS A
inner join ALL_OBJECTS B ON b.owner = a.owner and ltrim(B.OBJECT_NAME) =  ltrim(A.TABLE_NAME)
LEFT JOIN all_col_comments com ON (A.owner = com.owner AND A.table_name = com.table_name AND A.column_name = com.column_name)

Then more stuff about triggers.

I also get errors when I try to use relations such as

Model::find()->with('relationName')->all();

I get

'ORA-01795: maximum number of expressions in a list is 1000 error.

These tables collectively have about 17k rows. There's about 11k in one table and about 7k in the other that are linked.

I'm using fk constraints as references within the tables.

The above queries request information about your database schema. I've never used yii2 and can't tell why it needs this information. — Codo
– Codo, Commented Nov 30, 2015 at 13:50

tryingToLearn · Accepted Answer · 2019-11-21 04:30:54Z

0

Add/Use following settings in your database configurations.

'schemaCacheDuration' => 7200,
'schemaCache' => 'cache',
'enableSchemaCache' => true,

And

change YII_DEBUG from true to false and YII_ENV from dev to prod.

This will reduce unnecessary SQL executions.

edited Nov 21, 2019 at 4:30

tryingToLearn

11.8k13 gold badges87 silver badges127 bronze badges

answered Nov 21, 2019 at 3:56

Waseem Hassan

4513 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Yii2 Oracle performance

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related