#1
  1. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,042
    Rep Power
    535

    Are triggers all that evil afterall?


    From an application perspective, are SQL triggers really so horrible? I've heard two basic arguments.

    1. It hides part of the application. I guess that might be necessary if there are different applications accessing the data, however, I just will be using PHP.
    2. There is a significant performance hit. For my needs, about 12% (see below).


    I will probably use them, but limit their use to just one or two tasks. One task would be to insert the users_id into ever inserted or updated row. I am both the PHP guy and the DB guy, so no problems today, however, I might forget I did it and probably should figure out how to best document it. Any opinions would be appreciated.

    Test Count Total Time Average Time Rank
    No Trigger No Prepare 284 13.731467485428 0.048350237624746 4.2404255330105%
    No Trigger Yes Prepare 248 11.503079414368 0.046383384735354 0%
    Yes Trigger No Prepare 245 12.715950012207 0.051901836784518 11.897475961815%
    Yes Trigger Yes Prepare 223 11.486883878708 0.051510690039049 11.05418531431%
    Total 1000 49.43738079071 0.04943738079071 6.5842457871108%

    PHP Code:
    <?php
        
    class db {
            private static 
    $instance NULL;
            private function 
    __construct() {}   //Make private
            
    private function __clone(){}   //Make private
            
    public static function db() //Get instance of DB
            
    {
                if (!
    self::$instance)
                {
                    
    self::$instance = new PDO("mysql:host=localhost;dbname=triggerTest"'xxx''xxx');
                    
    self::$instance->setAttribute(PDO::ATTR_ERRMODEPDO::ERRMODE_EXCEPTION);
                }
                return 
    self::$instance;
            }
        }

        
    $data=array(
            
    0=>array('name'=>'No Trigger No Prepare','count'=>0,'time'=>0),
            
    1=>array('name'=>'No Trigger Yes Prepare','count'=>0,'time'=>0),
            
    2=>array('name'=>'Yes Trigger No Prepare','count'=>0,'time'=>0),
            
    3=>array('name'=>'Yes Trigger Yes Prepare','count'=>0,'time'=>0)
        );
        
    $c=100;

        for (
    $j=1$j<=1000$j++)
        {
            
    $start=microtime(true);
            
    $rand=rand(0,3);
            switch(
    $rand)
            {
                case 
    0:
                    for (
    $i=1$i<=$c$i++)
                    {
                        
    $sql ='INSERT INTO myNonTriggerTable (id, data, users_id) VALUES (NULL,'.$rand.',1)';
                        
    db::db()->exec($sql);
                    }
                    break;
                case 
    1:
                    
    $sql ='INSERT INTO myNonTriggerTable (id, data, users_id) VALUES (NULL,?,1)';
                    
    $stmt db::db()->prepare($sql);
                    for (
    $i=1$i<=$c$i++)
                    {
                        
    $stmt->execute(array($rand));
                    }
                    break;
                case 
    2:
                    
    db::db()->exec('SET @users_id=1');
                    for (
    $i=1$i<=$c$i++)
                    {
                        
    $sql ='INSERT INTO myTriggerTable (id, data) VALUES (NULL,'.$rand.')';
                        
    db::db()->exec($sql);
                    }
                    break;
                case 
    3:
                    
    db::db()->exec('SET @users_id=1');
                    
    $sql ='INSERT INTO myTriggerTable (id, data) VALUES (NULL,?)';
                    
    $stmt db::db()->prepare($sql);
                    for (
    $i=1$i<=$c$i++)
                    {
                        
    $stmt->execute(array($rand));
                    }
                    break;
            }
            
    $data[$rand]['count']=$data[$rand]['count']+1;
            
    $data[$rand]['time']=$data[$rand]['time']+(microtime(true)-$start);       
        }

        foreach(
    $data as $key=>$test)
        {
            
    $data[$key]['average']=$test['time']/$test['count'];
        }

        
    $count=$data[0]['count']+$data[1]['count']+$data[2]['count']+$data[3]['count'];
        
    $time=$data[0]['time']+$data[1]['time']+$data[2]['time']+$data[3]['time'];
        
    $average=$time/$count;

        
    $minimum=min(array_map(function($details) {return $details['average'];}, $data));
        
    $rank=100*($average-$minimum)/$minimum;

        echo(
    '<table><tr><th>Test</th><th>Count</th><th>Total Time</th><th>Average Time</th><th>Rank</th></tr>');
        foreach(
    $data as $key=>$test) {
            
    $data[$key]['rank']=100*($data[$key]['average']-$minimum)/$minimum;
            echo(
    "<tr><td>{$test['name']}</td><td>{$test['count']}</td><td>{$test['time']}</td><td>{$data[$key]['average']}</td><td>{$data[$key]['rank']}%</td></tr>");
        }
        echo(
    "<tr><td>Total</td><td>{$count}</td><td>{$time}</td><td>{$average}</td><td>{$rank}%</td></tr></table>");
    ?>
  2. #2
  3. Jealous Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,303
    Rep Power
    9400

    Ah, an unanswered thread! Also, moved from PHP


    I very much think along the lines of #1. I personally work best as a developer when I have a complete understanding of an entire application, and as such I naturally dislike any kind of magical or spooky behavior (meaning behavior that I didn't explicitly add in my code) like triggers and cascading updates and deletions.
    As I understand it, the DBA stereotype is quite the opposite: would rather have the database do as much work as possible, thus triggers (and cascades) are preferable.

    #2 I couldn't speak to and would defer to someone more familiar with MySQL.

    [edit] In fact I think I'll move this: the efficiency question is harder to answer than one about preferences and opinions. Maybe there's some tuning you can do to get them back in line.
    Last edited by requinix; April 20th, 2013 at 05:39 AM.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,042
    Rep Power
    535
    Thanks requinix,

    I agree with you regarding cascading and mostly agree with you regarding triggers. I think triggers might be okay if the task is divorced from the application such as potentially audit trails. I have since adding another post on the subject, however, have received little comments.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,042
    Rep Power
    535
    Also, I feel this post belongs more in the PHP section, since I am asking from a PHP application perspective opinions about triggers.

    [edit] Just saw your edit section stating that you moved it because of the efficiency question. Maybe it does belong here, I don't know...
    Last edited by NotionCommotion; April 20th, 2013 at 08:56 AM.

IMN logo majestic logo threadwatch logo seochat tools logo