changes in component Zend_Auth_Adapter_DbTable

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

changes in component Zend_Auth_Adapter_DbTable

Diego Henrique Oliveira
In the adapter DbTable, the generated query to the database is a 'select *', and as it was being a bottleneck in my application (users table with many fields), i made these changes in the component Zend_Auth_Adapter_DbTable.


class Zend_Auth_Adapter_DbTable
{
   
    protected $_returnedFields;
   
    public function setReturnedFields( array $fields )
    {
        $this->_returnedFields = $fields;
    }
   
    public function getReturnedFields()
    {
        if (is_null($this->_returnedFields))
        {
            return array('*');
        }
        return $this->_returnedFields;
    }
   
    /**
     * _authenticateCreateSelect() - This method creates a Zend_Db_Select object that
     * is completely configured to be queried against the database.
     *
     * @return Zend_Db_Select
     */
    protected function _authenticateCreateSelect()
    {
        // build credential expression
        if (empty($this->_credentialTreatment) || (strpos($this->_credentialTreatment, '?') === false)) {
            $this->_credentialTreatment = '?';
        }

        $credentialExpression = new Zend_Db_Expr(
            '(CASE WHEN ' .
            $this->_zendDb->quoteInto(
                $this->_zendDb->quoteIdentifier($this->_credentialColumn, true)
                . ' = ' . $this->_credentialTreatment, $this->_credential
                )
            . ' THEN 1 ELSE 0 END) AS '
            . $this->_zendDb->quoteIdentifier(
                $this->_zendDb->foldCase('zend_auth_credential_match')
                )
            );

        // get select
        $fields   = $this->getReturnedFields();
        array_push( $fields, $credentialExpression );
        $dbSelect = clone $this->getDbSelect();
        $dbSelect->from($this->_tableName, $fields)
                 ->where($this->_zendDb->quoteIdentifier($this->_identityColumn, true) . ' = ?', $this->_identity);

        return $dbSelect;
    }
   
}



What you guys think about these changes? And should I submit them to the project?


Cheers

Diego
http://diegoholiveira.com

Reply | Threaded
Open this post in threaded view
|

Re: changes in component Zend_Auth_Adapter_DbTable

Kevin McArthur-2
The performance difference between a * and selected columns will be
almost nil.... short of something like an unmaterialized and complex
view [in the db sense] that is.

I'd think this would add a fair amount of complication to the api and
codebase for what will be, for most people, practically zero benefit.
In-fact, for most, the additional requirement of a couple method
invocations and string operations will likely negate the effect of not
returning the extraneous columns in the first place.

I'd vote no on this change.

Kevin


Diego Henrique Oliveira wrote:

> In the adapter DbTable, the generated query to the database is a
> 'select *', and as it was being a bottleneck in my application (users
> table with many fields), i made these changes in the component
> Zend_Auth_Adapter_DbTable.
>
>
> class Zend_Auth_Adapter_DbTable
> {
>    
>     protected $_returnedFields;
>    
>     public function setReturnedFields( array $fields )
>     {
>         $this->_returnedFields = $fields;
>     }
>    
>     public function getReturnedFields()
>     {
>         if (is_null($this->_returnedFields))
>         {
>             return array('*');
>         }
>         return $this->_returnedFields;
>     }
>    
>     /**
>      * _authenticateCreateSelect() - This method creates a
> Zend_Db_Select object that
>      * is completely configured to be queried against the database.
>      *
>      * @return Zend_Db_Select
>      */
>     protected function _authenticateCreateSelect()
>     {
>         // build credential expression
>         if (empty($this->_credentialTreatment) ||
> (strpos($this->_credentialTreatment, '?') === false)) {
>             $this->_credentialTreatment = '?';
>         }
>
>         $credentialExpression = new Zend_Db_Expr(
>             '(CASE WHEN ' .
>             $this->_zendDb->quoteInto(
>                
> $this->_zendDb->quoteIdentifier($this->_credentialColumn, true)
>                 . ' = ' . $this->_credentialTreatment, $this->_credential
>                 )
>             . ' THEN 1 ELSE 0 END) AS '
>             . $this->_zendDb->quoteIdentifier(
>                 $this->_zendDb->foldCase('zend_auth_credential_match')
>                 )
>             );
>
>         // get select
>         $fields   = $this->getReturnedFields();
>         array_push( $fields, $credentialExpression );
>         $dbSelect = clone $this->getDbSelect();
>         $dbSelect->from($this->_tableName, $fields)
>                  
> ->where($this->_zendDb->quoteIdentifier($this->_identityColumn, true)
> . ' = ?', $this->_identity);
>
>         return $dbSelect;
>     }
>    
> }
>
>
>
> What you guys think about these changes? And should I submit them to
> the project?
>
>
> Cheers
>
> Diego
> http://diegoholiveira.com
>

Reply | Threaded
Open this post in threaded view
|

Re: changes in component Zend_Auth_Adapter_DbTable

Dolf Schimmel
I would indeed like to see some (reproducable) benchmarks that show a
significant improvement

-- Freeaqingme

On Mon, Jan 25, 2010 at 5:39 PM, Kevin McArthur <[hidden email]> wrote:

> The performance difference between a * and selected columns will be almost
> nil.... short of something like an unmaterialized and complex view [in the
> db sense] that is.
>
> I'd think this would add a fair amount of complication to the api and
> codebase for what will be, for most people, practically zero benefit.
> In-fact, for most, the additional requirement of a couple method invocations
> and string operations will likely negate the effect of not returning the
> extraneous columns in the first place.
>
> I'd vote no on this change.
>
> Kevin
>
>
> Diego Henrique Oliveira wrote:
>>
>> In the adapter DbTable, the generated query to the database is a 'select
>> *', and as it was being a bottleneck in my application (users table with
>> many fields), i made these changes in the component
>> Zend_Auth_Adapter_DbTable.
>>
>>
>> class Zend_Auth_Adapter_DbTable
>> {
>>      protected $_returnedFields;
>>      public function setReturnedFields( array $fields )
>>    {
>>        $this->_returnedFields = $fields;
>>    }
>>      public function getReturnedFields()
>>    {
>>        if (is_null($this->_returnedFields))
>>        {
>>            return array('*');
>>        }
>>        return $this->_returnedFields;
>>    }
>>      /**
>>     * _authenticateCreateSelect() - This method creates a Zend_Db_Select
>> object that
>>     * is completely configured to be queried against the database.
>>     *
>>     * @return Zend_Db_Select
>>     */
>>    protected function _authenticateCreateSelect()
>>    {
>>        // build credential expression
>>        if (empty($this->_credentialTreatment) ||
>> (strpos($this->_credentialTreatment, '?') === false)) {
>>            $this->_credentialTreatment = '?';
>>        }
>>
>>        $credentialExpression = new Zend_Db_Expr(
>>            '(CASE WHEN ' .
>>            $this->_zendDb->quoteInto(
>>                $this->_zendDb->quoteIdentifier($this->_credentialColumn,
>> true)
>>                . ' = ' . $this->_credentialTreatment, $this->_credential
>>                )
>>            . ' THEN 1 ELSE 0 END) AS '
>>            . $this->_zendDb->quoteIdentifier(
>>                $this->_zendDb->foldCase('zend_auth_credential_match')
>>                )
>>            );
>>
>>        // get select
>>        $fields   = $this->getReturnedFields();
>>        array_push( $fields, $credentialExpression );
>>        $dbSelect = clone $this->getDbSelect();
>>        $dbSelect->from($this->_tableName, $fields)
>>
>> ->where($this->_zendDb->quoteIdentifier($this->_identityColumn, true) . ' =
>> ?', $this->_identity);
>>
>>        return $dbSelect;
>>    }
>>   }
>>
>>
>>
>> What you guys think about these changes? And should I submit them to the
>> project?
>>
>>
>> Cheers
>>
>> Diego
>> http://diegoholiveira.com
>>
>

Reply | Threaded
Open this post in threaded view
|

Re: changes in component Zend_Auth_Adapter_DbTable

Marc Bennewitz (private)
How would you create a reproducible benchmark ?
  - Is the DB local or reading from net ?
  - how many fields are in this table and which type ?
(In past I saw user tables have ALL configuration in it, incl. css styles.)

In my opinion this would be a good solution if the default is set to *.

@Diego
$fields[] = $credentialExpression is more high-performance than
array_push( $fields, $credentialExpression );

--
Marc

Am 25.01.2010 18:45, schrieb Dolf Schimmel:

> I would indeed like to see some (reproducable) benchmarks that show a
> significant improvement
>
> -- Freeaqingme
>
> On Mon, Jan 25, 2010 at 5:39 PM, Kevin McArthur <[hidden email]> wrote:
>  
>> The performance difference between a * and selected columns will be almost
>> nil.... short of something like an unmaterialized and complex view [in the
>> db sense] that is.
>>
>> I'd think this would add a fair amount of complication to the api and
>> codebase for what will be, for most people, practically zero benefit.
>> In-fact, for most, the additional requirement of a couple method invocations
>> and string operations will likely negate the effect of not returning the
>> extraneous columns in the first place.
>>
>> I'd vote no on this change.
>>
>> Kevin
>>
>>
>> Diego Henrique Oliveira wrote:
>>    
>>> In the adapter DbTable, the generated query to the database is a 'select
>>> *', and as it was being a bottleneck in my application (users table with
>>> many fields), i made these changes in the component
>>> Zend_Auth_Adapter_DbTable.
>>>
>>>
>>> class Zend_Auth_Adapter_DbTable
>>> {
>>>      protected $_returnedFields;
>>>      public function setReturnedFields( array $fields )
>>>    {
>>>        $this->_returnedFields = $fields;
>>>    }
>>>      public function getReturnedFields()
>>>    {
>>>        if (is_null($this->_returnedFields))
>>>        {
>>>            return array('*');
>>>        }
>>>        return $this->_returnedFields;
>>>    }
>>>      /**
>>>     * _authenticateCreateSelect() - This method creates a Zend_Db_Select
>>> object that
>>>     * is completely configured to be queried against the database.
>>>     *
>>>     * @return Zend_Db_Select
>>>     */
>>>    protected function _authenticateCreateSelect()
>>>    {
>>>        // build credential expression
>>>        if (empty($this->_credentialTreatment) ||
>>> (strpos($this->_credentialTreatment, '?') === false)) {
>>>            $this->_credentialTreatment = '?';
>>>        }
>>>
>>>        $credentialExpression = new Zend_Db_Expr(
>>>            '(CASE WHEN ' .
>>>            $this->_zendDb->quoteInto(
>>>                $this->_zendDb->quoteIdentifier($this->_credentialColumn,
>>> true)
>>>                . ' = ' . $this->_credentialTreatment, $this->_credential
>>>                )
>>>            . ' THEN 1 ELSE 0 END) AS '
>>>            . $this->_zendDb->quoteIdentifier(
>>>                $this->_zendDb->foldCase('zend_auth_credential_match')
>>>                )
>>>            );
>>>
>>>        // get select
>>>        $fields   = $this->getReturnedFields();
>>>        array_push( $fields, $credentialExpression );
>>>        $dbSelect = clone $this->getDbSelect();
>>>        $dbSelect->from($this->_tableName, $fields)
>>>
>>> ->where($this->_zendDb->quoteIdentifier($this->_identityColumn, true) . ' =
>>> ?', $this->_identity);
>>>
>>>        return $dbSelect;
>>>    }
>>>   }
>>>
>>>
>>>
>>> What you guys think about these changes? And should I submit them to the
>>> project?
>>>
>>>
>>> Cheers
>>>
>>> Diego
>>> http://diegoholiveira.com
>>>
>>>      
>>    
>  

Reply | Threaded
Open this post in threaded view
|

Re: changes in component Zend_Auth_Adapter_DbTable

Kevin McArthur-2
>
>> (In past I saw user tables have ALL configuration in it, incl. css
>> styles.)

Thats an app problem, not something the framework should strive to work
with.

K

Marc Bennewitz wrote:

> How would you create a reproducible benchmark ?
>   - Is the DB local or reading from net ?
>   - how many fields are in this table and which type ?
> (In past I saw user tables have ALL configuration in it, incl. css styles.)
>
> In my opinion this would be a good solution if the default is set to *.
>
> @Diego
> $fields[] = $credentialExpression is more high-performance than
> array_push( $fields, $credentialExpression );
>
> --
> Marc
>
> Am 25.01.2010 18:45, schrieb Dolf Schimmel:
>  
>> I would indeed like to see some (reproducable) benchmarks that show a
>> significant improvement
>>
>> -- Freeaqingme
>>
>> On Mon, Jan 25, 2010 at 5:39 PM, Kevin McArthur <[hidden email]> wrote:
>>  
>>    
>>> The performance difference between a * and selected columns will be almost
>>> nil.... short of something like an unmaterialized and complex view [in the
>>> db sense] that is.
>>>
>>> I'd think this would add a fair amount of complication to the api and
>>> codebase for what will be, for most people, practically zero benefit.
>>> In-fact, for most, the additional requirement of a couple method invocations
>>> and string operations will likely negate the effect of not returning the
>>> extraneous columns in the first place.
>>>
>>> I'd vote no on this change.
>>>
>>> Kevin
>>>
>>>
>>> Diego Henrique Oliveira wrote:
>>>    
>>>      
>>>> In the adapter DbTable, the generated query to the database is a 'select
>>>> *', and as it was being a bottleneck in my application (users table with
>>>> many fields), i made these changes in the component
>>>> Zend_Auth_Adapter_DbTable.
>>>>
>>>>
>>>> class Zend_Auth_Adapter_DbTable
>>>> {
>>>>      protected $_returnedFields;
>>>>      public function setReturnedFields( array $fields )
>>>>    {
>>>>        $this->_returnedFields = $fields;
>>>>    }
>>>>      public function getReturnedFields()
>>>>    {
>>>>        if (is_null($this->_returnedFields))
>>>>        {
>>>>            return array('*');
>>>>        }
>>>>        return $this->_returnedFields;
>>>>    }
>>>>      /**
>>>>     * _authenticateCreateSelect() - This method creates a Zend_Db_Select
>>>> object that
>>>>     * is completely configured to be queried against the database.
>>>>     *
>>>>     * @return Zend_Db_Select
>>>>     */
>>>>    protected function _authenticateCreateSelect()
>>>>    {
>>>>        // build credential expression
>>>>        if (empty($this->_credentialTreatment) ||
>>>> (strpos($this->_credentialTreatment, '?') === false)) {
>>>>            $this->_credentialTreatment = '?';
>>>>        }
>>>>
>>>>        $credentialExpression = new Zend_Db_Expr(
>>>>            '(CASE WHEN ' .
>>>>            $this->_zendDb->quoteInto(
>>>>                $this->_zendDb->quoteIdentifier($this->_credentialColumn,
>>>> true)
>>>>                . ' = ' . $this->_credentialTreatment, $this->_credential
>>>>                )
>>>>            . ' THEN 1 ELSE 0 END) AS '
>>>>            . $this->_zendDb->quoteIdentifier(
>>>>                $this->_zendDb->foldCase('zend_auth_credential_match')
>>>>                )
>>>>            );
>>>>
>>>>        // get select
>>>>        $fields   = $this->getReturnedFields();
>>>>        array_push( $fields, $credentialExpression );
>>>>        $dbSelect = clone $this->getDbSelect();
>>>>        $dbSelect->from($this->_tableName, $fields)
>>>>
>>>> ->where($this->_zendDb->quoteIdentifier($this->_identityColumn, true) . ' =
>>>> ?', $this->_identity);
>>>>
>>>>        return $dbSelect;
>>>>    }
>>>>   }
>>>>
>>>>
>>>>
>>>> What you guys think about these changes? And should I submit them to the
>>>> project?
>>>>
>>>>
>>>> Cheers
>>>>
>>>> Diego
>>>> http://diegoholiveira.com
>>>>
>>>>      
>>>>        
>>>    
>>>      
>>  
>>    

Reply | Threaded
Open this post in threaded view
|

Re: changes in component Zend_Auth_Adapter_DbTable

Diego Henrique Oliveira
In reply to this post by Marc Bennewitz (private)
@Kevin
I do not think this solution could complicate the API. It's just another option for the developer, he can use or not.

@Dolf
I have not done benchmarks, but in a query that was returning 20 fields now returns only 4 (which is the answer for my authentication).

@Marc
Thanks for the tip, I will change in my code.



Cheers.

Diego



From: Marc Bennewitz <[hidden email]>
To: [hidden email]
Sent: Mon, January 25, 2010 5:14:14 PM
Subject: Re: [zf-contributors] changes in component Zend_Auth_Adapter_DbTable

How would you create a reproducible benchmark ?
  - Is the DB local or reading from net ?
  - how many fields are in this table and which type ?
(In past I saw user tables have ALL configuration in it, incl. css styles.)

In my opinion this would be a good solution if the default is set to *.

@Diego
$fields[] = $credentialExpression is more high-performance than
array_push( $fields, $credentialExpression );

--
Marc

Am 25.01.2010 18:45, schrieb Dolf Schimmel:

> I would indeed like to see some (reproducable) benchmarks that show a
> significant improvement
>
> -- Freeaqingme
>
> On Mon, Jan 25, 2010 at 5:39 PM, Kevin McArthur <[hidden email]> wrote:

>> The performance difference between a * and selected columns will be almost
>> nil.... short of something like an unmaterialized and complex view [in the
>> db sense] that is.
>>
>> I'd think this would add a fair amount of complication to the api and
>> codebase for what will be, for most people, practically zero benefit.
>> In-fact, for most, the additional requirement of a couple method invocations
>> and string operations will likely negate the effect of not returning the
>> extraneous columns in the first place.
>>
>> I'd vote no on this change.
>>
>> Kevin
>>
>>
>> Diego Henrique Oliveira wrote:
>>   
>>> In the adapter DbTable, the generated query to the database is a 'select
>>> *', and as it was being a bottleneck in my application (users table with
>>> many fields), i made these changes in the component
>>> Zend_Auth_Adapter_DbTable.
>>>
>>>
>>> class Zend_Auth_Adapter_DbTable
>>> {
>>>      protected $_returnedFields;
>>>      public function setReturnedFields( array $fields )
>>>    {
>>>        $this->_returnedFields = $fields;
>>>    }
>>>      public function getReturnedFields()
>>>    {
>>>        if (is_null($this->_returnedFields))
>>>        {
>>>            return array('*');
>>>        }
>>>        return $this->_returnedFields;
>>>    }
>>>      /**
>>>    * _authenticateCreateSelect() - This method creates a Zend_Db_Select
>>> object that
>>>    * is completely configured to be queried against the database.
>>>    *
>>>    * @return Zend_Db_Select
>>>    */
>>>    protected function _authenticateCreateSelect()
>>>    {
>>>        // build credential expression
>>>        if (empty($this->_credentialTreatment) ||
>>> (strpos($this->_credentialTreatment, '?') === false)) {
>>>            $this->_credentialTreatment = '?';
>>>        }
>>>
>>>        $credentialExpression = new Zend_Db_Expr(
>>>            '(CASE WHEN ' .
>>>            $this->_zendDb->quoteInto(
>>>                $this->_zendDb->quoteIdentifier($this->_credentialColumn,
>>> true)
>>>                . ' = ' . $this->_credentialTreatment, $this->_credential
>>>                )
>>>            . ' THEN 1 ELSE 0 END) AS '
>>>            . $this->_zendDb->quoteIdentifier(
>>>                $this->_zendDb->foldCase('zend_auth_credential_match')
>>>                )
>>>            );
>>>
>>>        // get select
>>>        $fields  = $this->getReturnedFields();
>>>        array_push( $fields, $credentialExpression );
>>>        $dbSelect = clone $this->getDbSelect();
>>>        $dbSelect->from($this->_tableName, $fields)
>>>
>>> ->where($this->_zendDb->quoteIdentifier($this->_identityColumn, true) . ' =
>>> ?', $this->_identity);
>>>
>>>        return $dbSelect;
>>>    }
>>>  }
>>>
>>>
>>>
>>> What you guys think about these changes? And should I submit them to the
>>> project?
>>>
>>>
>>> Cheers
>>>
>>> Diego
>>> http://diegoholiveira.com
>>>
>>>     
>>   


Reply | Threaded
Open this post in threaded view
|

Re: changes in component Zend_Auth_Adapter_DbTable

Pieter Kokx
Really, this is just a micro optimalisation from what most people wont benefit at all!

This only makes the code more complicated, and this way you are 'optimizing' ZF where it doesn't really count.
Regards,

Pieter Kokx
PHP Developer
Zend Framework developer


Diego Henrique Oliveira schreef:
@Kevin
I do not think this solution could complicate the API. It's just another option for the developer, he can use or not.

@Dolf
I have not done benchmarks, but in a query that was returning 20 fields now returns only 4 (which is the answer for my authentication).

@Marc
Thanks for the tip, I will change in my code.



Cheers.

Diego



From: Marc Bennewitz [hidden email]
To: [hidden email]
Sent: Mon, January 25, 2010 5:14:14 PM
Subject: Re: [zf-contributors] changes in component Zend_Auth_Adapter_DbTable

How would you create a reproducible benchmark ?
  - Is the DB local or reading from net ?
  - how many fields are in this table and which type ?
(In past I saw user tables have ALL configuration in it, incl. css styles.)

In my opinion this would be a good solution if the default is set to *.

@Diego
$fields[] = $credentialExpression is more high-performance than
array_push( $fields, $credentialExpression );

--
Marc

Am 25.01.2010 18:45, schrieb Dolf Schimmel:
> I would indeed like to see some (reproducable) benchmarks that show a
> significant improvement
>
> -- Freeaqingme
>
> On Mon, Jan 25, 2010 at 5:39 PM, Kevin McArthur <[hidden email]> wrote:

>> The performance difference between a * and selected columns will be almost
>> nil.... short of something like an unmaterialized and complex view [in the
>> db sense] that is.
>>
>> I'd think this would add a fair amount of complication to the api and
>> codebase for what will be, for most people, practically zero benefit.
>> In-fact, for most, the additional requirement of a couple method invocations
>> and string operations will likely negate the effect of not returning the
>> extraneous columns in the first place.
>>
>> I'd vote no on this change.
>>
>> Kevin
>>
>>
>> Diego Henrique Oliveira wrote:
>>   
>>> In the adapter DbTable, the generated query to the database is a 'select
>>> *', and as it was being a bottleneck in my application (users table with
>>> many fields), i made these changes in the component
>>> Zend_Auth_Adapter_DbTable.
>>>
>>>
>>> class Zend_Auth_Adapter_DbTable
>>> {
>>>      protected $_returnedFields;
>>>      public function setReturnedFields( array $fields )
>>>    {
>>>        $this->_returnedFields = $fields;
>>>    }
>>>      public function getReturnedFields()
>>>    {
>>>        if (is_null($this->_returnedFields))
>>>        {
>>>            return array('*');
>>>        }
>>>        return $this->_returnedFields;
>>>    }
>>>      /**
>>>    * _authenticateCreateSelect() - This method creates a Zend_Db_Select
>>> object that
>>>    * is completely configured to be queried against the database.
>>>    *
>>>    * @return Zend_Db_Select
>>>    */
>>>    protected function _authenticateCreateSelect()
>>>    {
>>>        // build credential expression
>>>        if (empty($this->_credentialTreatment) ||
>>> (strpos($this->_credentialTreatment, '?') === false)) {
>>>            $this->_credentialTreatment = '?';
>>>        }
>>>
>>>        $credentialExpression = new Zend_Db_Expr(
>>>            '(CASE WHEN ' .
>>>            $this->_zendDb->quoteInto(
>>>                $this->_zendDb->quoteIdentifier($this->_credentialColumn,
>>> true)
>>>                . ' = ' . $this->_credentialTreatment, $this->_credential
>>>                )
>>>            . ' THEN 1 ELSE 0 END) AS '
>>>            . $this->_zendDb->quoteIdentifier(
>>>                $this->_zendDb->foldCase('zend_auth_credential_match')
>>>                )
>>>            );
>>>
>>>        // get select
>>>        $fields  = $this->getReturnedFields();
>>>        array_push( $fields, $credentialExpression );
>>>        $dbSelect = clone $this->getDbSelect();
>>>        $dbSelect->from($this->_tableName, $fields)
>>>
>>> ->where($this->_zendDb->quoteIdentifier($this->_identityColumn, true) . ' =
>>> ?', $this->_identity);
>>>
>>>        return $dbSelect;
>>>    }
>>>  }
>>>
>>>
>>>
>>> What you guys think about these changes? And should I submit them to the
>>> project?
>>>
>>>
>>> Cheers
>>>
>>> Diego
>>> http://diegoholiveira.com
>>>
>>>     
>>   


Reply | Threaded
Open this post in threaded view
|

Re: changes in component Zend_Auth_Adapter_DbTable

Ralph Schindler-2
In reply to this post by Diego Henrique Oliveira
I am not sure I see a lot of value here.  What kind of bottleneck are
you seeing?  Considering this function should only be run once during
the lifespan of a user's session, I am not sure why it would be that big
of an issue.

Returning 20 vs. 4 columns generally has no consequence on the result at
all, what vendor DB are you using where this is a concern?

Furthermore, perhaps you should break out authentication information
from the user table itself and instead use a join with the select
object? Just a thought.

-ralph



Diego Henrique Oliveira wrote:

> In the adapter DbTable, the generated query to the database is a 'select
> *', and as it was being a bottleneck in my application (users table with
> many fields), i made these changes in the component
> Zend_Auth_Adapter_DbTable.
>
>
> class Zend_Auth_Adapter_DbTable
> {
>    
>     protected $_returnedFields;
>    
>     public function setReturnedFields( array $fields )
>     {
>         $this->_returnedFields = $fields;
>     }
>    
>     public function getReturnedFields()
>     {
>         if (is_null($this->_returnedFields))
>         {
>             return array('*');
>         }
>         return $this->_returnedFields;
>     }
>    
>     /**
>      * _authenticateCreateSelect() - This method creates a
> Zend_Db_Select object that
>      * is completely configured to be queried against the database.
>      *
>      * @return Zend_Db_Select
>      */
>     protected function _authenticateCreateSelect()
>     {
>         // build credential expression
>         if (empty($this->_credentialTreatment) ||
> (strpos($this->_credentialTreatment, '?') === false)) {
>             $this->_credentialTreatment = '?';
>         }
>
>         $credentialExpression = new Zend_Db_Expr(
>             '(CASE WHEN ' .
>             $this->_zendDb->quoteInto(
>                
> $this->_zendDb->quoteIdentifier($this->_credentialColumn, true)
>                 . ' = ' . $this->_credentialTreatment, $this->_credential
>                 )
>             . ' THEN 1 ELSE 0 END) AS '
>             . $this->_zendDb->quoteIdentifier(
>                 $this->_zendDb->foldCase('zend_auth_credential_match')
>                 )
>             );
>
>         // get select
>         $fields   = $this->getReturnedFields();
>         array_push( $fields, $credentialExpression );
>         $dbSelect = clone $this->getDbSelect();
>         $dbSelect->from($this->_tableName, $fields)
>                  
> ->where($this->_zendDb->quoteIdentifier($this->_identityColumn, true) .
> ' = ?', $this->_identity);
>
>         return $dbSelect;
>     }
>    
> }
>
>
>
> What you guys think about these changes? And should I submit them to the
> project?
>
>
> Cheers
>
> Diego
> http://diegoholiveira.com
>

Reply | Threaded
Open this post in threaded view
|

Re: changes in component Zend_Auth_Adapter_DbTable

Diego Henrique Oliveira
I agree with you about breaking the table users in several tables is the best solution. However the application I'm working on is just a part of another larger application, which is developed by another company, so I can't just break the database tables.

The bank in question is MySQL and the bottleneck that I was having is the return of unnecessary data. Maybe I could be wrong and seeing the need for performance where it does not exist (hypothesis not discarded).

My point in posting here was just to ask the opinion of everyone, who obviously has more experience than me.

Regards.




From: Ralph Schindler <[hidden email]>
To: Diego Henrique Oliveira <[hidden email]>
Cc: [hidden email]
Sent: Mon, January 25, 2010 6:58:43 PM
Subject: Re: [zf-contributors] changes in component Zend_Auth_Adapter_DbTable

I am not sure I see a lot of value here.  What kind of bottleneck are you seeing?  Considering this function should only be run once during the lifespan of a user's session, I am not sure why it would be that big of an issue.

Returning 20 vs. 4 columns generally has no consequence on the result at all, what vendor DB are you using where this is a concern?

Furthermore, perhaps you should break out authentication information from the user table itself and instead use a join with the select object? Just a thought.

-ralph



Diego Henrique Oliveira wrote:

> In the adapter DbTable, the generated query to the database is a 'select *', and as it was being a bottleneck in my application (users table with many fields), i made these changes in the component Zend_Auth_Adapter_DbTable.
>
>
> class Zend_Auth_Adapter_DbTable
> {
>        protected $_returnedFields;
>        public function setReturnedFields( array $fields )
>    {
>        $this->_returnedFields = $fields;
>    }
>        public function getReturnedFields()
>    {
>        if (is_null($this->_returnedFields))
>        {
>            return array('*');
>        }
>        return $this->_returnedFields;
>    }
>        /**
>      * _authenticateCreateSelect() - This method creates a Zend_Db_Select object that
>      * is completely configured to be queried against the database.
>      *
>      * @return Zend_Db_Select
>      */
>    protected function _authenticateCreateSelect()
>    {
>        // build credential expression
>        if (empty($this->_credentialTreatment) || (strpos($this->_credentialTreatment, '?') === false)) {
>            $this->_credentialTreatment = '?';
>        }
>
>        $credentialExpression = new Zend_Db_Expr(
>            '(CASE WHEN ' .
>            $this->_zendDb->quoteInto(
>                $this->_zendDb->quoteIdentifier($this->_credentialColumn, true)
>                . ' = ' . $this->_credentialTreatment, $this->_credential
>                )
>            . ' THEN 1 ELSE 0 END) AS '
>            . $this->_zendDb->quoteIdentifier(
>                $this->_zendDb->foldCase('zend_auth_credential_match')
>                )
>            );
>
>        // get select
>        $fields  = $this->getReturnedFields();
>        array_push( $fields, $credentialExpression );
>        $dbSelect = clone $this->getDbSelect();
>        $dbSelect->from($this->_tableName, $fields)
>                  ->where($this->_zendDb->quoteIdentifier($this->_identityColumn, true) . ' = ?', $this->_identity);
>
>        return $dbSelect;
>    }
>    }
>
>
>
> What you guys think about these changes? And should I submit them to the project?
>
>
> Cheers
>
> Diego
> http://diegoholiveira.com
>

Reply | Threaded
Open this post in threaded view
|

Re: changes in component Zend_Auth_Adapter_DbTable

till
Hey Diego,

On Tue, Jan 26, 2010 at 2:21 AM, Diego Henrique Oliveira
<[hidden email]> wrote:
> I agree with you about breaking the table users in several tables is the
> best solution. However the application I'm working on is just a part of
> another larger application, which is developed by another company, so I
> can't just break the database tables.
>
> The bank in question is MySQL and the bottleneck that I was having is the
> return of unnecessary data. Maybe I could be wrong and seeing the need for
> performance where it does not exist (hypothesis not discarded).

I think the question is -- how did you determine this is a bottleneck.
Or did you just think it could be one? :-)

I don't want to lecture you (so excuse me when I come across like
that), but the general idea when it comes to working around
bottlenecks is to always measure (e.g. how long does it take to render
my homepage), then to identify potential bottlenecks  in the code,
implement improvements and then to measure again. Repeat, etc..

And that's what a lot of people in this thread are hinting on.

Regardless of the above, +1 to submitting this patch. Even though it
may not yield outrageously obvious performance improvements it's kinda
MIND BOGGLING how this piece of code didn't have this before.

It's actually also pretty hilarious. And I don't follow the general
notion that people always complain that feature X will complicate the
API. It's not even a BC break, it's a new feature. Take it or leave
it. :-) It doesn't make code slower.

I'd rather be able to filter in the database because a smaller set has
a lot of other implications -- aside from query speed. There's also
less to carry around with during the request. It's domain-specific and
adds more flexibility for the developer.

Till

>
> My point in posting here was just to ask the opinion of everyone, who
> obviously has more experience than me.
>
> Regards.
>
>
>
> ________________________________
> From: Ralph Schindler <[hidden email]>
> To: Diego Henrique Oliveira <[hidden email]>
> Cc: [hidden email]
> Sent: Mon, January 25, 2010 6:58:43 PM
> Subject: Re: [zf-contributors] changes in component
> Zend_Auth_Adapter_DbTable
>
> I am not sure I see a lot of value here.  What kind of bottleneck are you
> seeing?  Considering this function should only be run once during the
> lifespan of a user's session, I am not sure why it would be that big of an
> issue.
>
> Returning 20 vs. 4 columns generally has no consequence on the result at
> all, what vendor DB are you using where this is a concern?
>
> Furthermore, perhaps you should break out authentication information from
> the user table itself and instead use a join with the select object? Just a
> thought.
>
> -ralph
>
>
>
> Diego Henrique Oliveira wrote:
>> In the adapter DbTable, the generated query to the database is a 'select
>> *', and as it was being a bottleneck in my application (users table with
>> many fields), i made these changes in the component
>> Zend_Auth_Adapter_DbTable.
>>
>>
>> class Zend_Auth_Adapter_DbTable
>> {
>>        protected $_returnedFields;
>>        public function setReturnedFields( array $fields )
>>    {
>>        $this->_returnedFields = $fields;
>>    }
>>        public function getReturnedFields()
>>    {
>>        if (is_null($this->_returnedFields))
>>        {
>>            return array('*');
>>        }
>>        return $this->_returnedFields;
>>    }
>>        /**
>>      * _authenticateCreateSelect() - This method creates a Zend_Db_Select
>> object that
>>      * is completely configured to be queried against the database.
>>      *
>>      * @return Zend_Db_Select
>>      */
>>    protected function _authenticateCreateSelect()
>>    {
>>        // build credential expression
>>        if (empty($this->_credentialTreatment) ||
>> (strpos($this->_credentialTreatment, '?') === false)) {
>>            $this->_credentialTreatment = '?';
>>        }
>>
>>        $credentialExpression = new Zend_Db_Expr(
>>            '(CASE WHEN ' .
>>            $this->_zendDb->quoteInto(
>>                $this->_zendDb->quoteIdentifier($this->_credentialColumn,
>> true)
>>                . ' = ' . $this->_credentialTreatment, $this->_credential
>>                )
>>            . ' THEN 1 ELSE 0 END) AS '
>>            . $this->_zendDb->quoteIdentifier(
>>                $this->_zendDb->foldCase('zend_auth_credential_match')
>>                )
>>            );
>>
>>        // get select
>>        $fields  = $this->getReturnedFields();
>>        array_push( $fields, $credentialExpression );
>>        $dbSelect = clone $this->getDbSelect();
>>        $dbSelect->from($this->_tableName, $fields)
>>
>> ->where($this->_zendDb->quoteIdentifier($this->_identityColumn, true) . ' =
>> ?', $this->_identity);
>>
>>        return $dbSelect;
>>    }
>>    }
>>
>>
>>
>> What you guys think about these changes? And should I submit them to the
>> project?
>>
>>
>> Cheers
>>
>> Diego
>> http://diegoholiveira.com
>>
>
>