Redundant queries in AuthBehavior leading to high db usage.

[Ilko Kacharov]

Hi Christoffer,

Methods AuthBehavior::getAncestors() and AuthBehavior::getPermissions() are leading to poor performance when the permissions table grows.

getAncestors method is getting all items from the authitems table and it checks every one of them if it is a parent of the one that we are looking for. 

In a database with more items (700 authitems) it is generating 3000 queries to display 10-15 rows in the operations web interface.

Is there a way to fix this issue with querying only the items where the $itemName is in the child column in authitemchild table instead of looping all of the items?

I can provide you sample data for testing purposes.

Regards,

Ilko

[Ilko Kacharov]

Btw most of the queries are repeated several times (3 to 15 times) for the same parameters.

[Christoffer Niska]

Hey Ilko,

I don't have time to look at this right now but please let know if you find a solution and I'll see when I can implement it. Otherwise I need to look at this later.

-Chris

-- 

Christoffer Niska

CTO

Nord Software

+358 500 980 565

christof...@nordsoftware.com

Runeberginkatu 43 B 12

00100 Helsinki, Finland

www.nordsoftware.com

-----------------------------------------------------------------------

The information contained in this message, its responses or attachments

are confidential. The message is intended solely for the adressee(s).

If you are not the intended recipient, you are hereby notified that any

use, dissemination, or reproduction is strictly prohibited. If you are

not the intended recipient, please contact the sender by return e-mail

and destroy all copies of the original message. Unlawful use of this

email may be prosecuted.

----------------------------------------------------------------------- 

On Wed, Jun 5, 2013 at 5:32 PM, Ilko Kacharov <kach...@gmail.com> wrote:

Btw most of the queries are repeated several times (3 to 15 times) for the same parameters.

--
You received this message because you are subscribed to the Google Groups "yii-auth" group.
To unsubscribe from this group and stop receiving emails from it, send an email to yii-auth+u...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Ilko Kacharov]

Hi Christoffer,

I went into the problem and it seems that there are two main issues. 

The first one is that we get all items and iterate over them to get the relations. This one can be suppressed with a plain query to the items child table and getting just the relations between the items.

The second problem is in the displaying the results - for example: AuthItemTypeColumn::renderDataCellContent()

$am = Yii::app()->getAuthManager();

$labelType = $this->active || $am->hasParent($this->itemName, $data['name']) || $am->hasChild($this->itemName, $data['name']) ? 'info' : '';

Here we call hasParent() or hasChild() methods which trigger getItemPermissions() -> getAuthItem() method which queries the db 3 times for each column (AuthItemTypeColumn, AuthItemRemoveColumn, AuthItemDescriptionColumn).

If we cache the items and their relations when we get the descendants and ancestors this process gets very clean with limited number of queries.

I've made some implementation and I can send you a pull request in github to review it if you want ?

There is only one bit of a problem: If the methods hasParent and hasChild are triggered before the getAncestors and getDescendants methods will return false since the records are not cached yet. This can be solved with a single query to the db checking for the needed relation. I haven't implemented that since these two methods are used only in the view's table and the records are already available.

Br,

Ilko

[Ilko Kacharov]

Here is the pull request: https://github.com/Crisu83/yii-auth/pull/77

Please let me know your opinion about changes

[Christoffer Niska]

I'm extemely busy at work right now, but I'll check this asap. Thanks for the PR.

-Christoffer

-- 

Christoffer Niska

CTO

Nord Software

+358 500 980 565

christof...@nordsoftware.com

Runeberginkatu 43 B 12

00100 Helsinki, Finland

www.nordsoftware.com

-----------------------------------------------------------------------

The information contained in this message, its responses or attachments

are confidential. The message is intended solely for the adressee(s).

If you are not the intended recipient, you are hereby notified that any

use, dissemination, or reproduction is strictly prohibited. If you are

not the intended recipient, please contact the sender by return e-mail

and destroy all copies of the original message. Unlawful use of this

email may be prosecuted.

----------------------------------------------------------------------- 

On Tue, Jun 11, 2013 at 3:54 PM, Ilko Kacharov <kach...@gmail.com> wrote:

Here is the pull request: https://github.com/Crisu83/yii-auth/pull/77

Please let me know your opinion about changes

--