MySQL Insert if not Updated

Question

I have three tables in MySQL

wi_indv_training(trn_id,ind_id,is_deleted)
wi_trn_org(trn_id,grp_id,is_deleted)
wi_indv_org(ind_id,grp_id,is_deleted)

Here, Multiple groups can be assigned to a training which is handled by wi_trn_org table. Multiple Individuals can be assigned to many groups. And Many Individuals are involved in training. Here, we are provided individuals are already associated with groups. Now while Inserting a group to training, individuals in that group must be associated with the training. There might already be the group and individuals(some or all) in the given training flagged with is_deleted=yes.

Now,While Inserting, I want to flag the deleted group and individual in training with is_deleted=no, if the data is already in respective tables else the data should be inserted as new data. For This I have implemented following approach which is very slow in performance;

foreach ($grp_id as $key => $value) {
    # code...
    $rs=$this->db->pdoQuery("SELECT * FROM wi_trn_org 
                             WHERE trn_id='$trn_id' 
                             AND grp_id='$value'")->results();            
    if(count($rs)>0)
    {
        $this->db->pdoQuery("UPDATE wi_trn_org SET is_deleted=0 
                            WHERE trn_id='$trn_id' AND grp_id='$value'");
        $grp_indv=$this->db->pdoQuery("SELECT ind_id 
                                       FROM wi_indv_org 
                                       WHERE grp_id='$value' 
                                       AND is_deleted=0")->results();
        foreach ($grp_indv as $ke => $va) 
        {
            $ind_id=$va['ind_id'];
            $rows=$this->db->pdoQuery("SELECT * FROM wi_indv_training 
                                      WHERE trn_id='$trn_id' 
                                      AND ind_id='$ind_id'")->results();
            if(count($rows)>0)
            {
                $this->db->pdoQuery("UPDATE wi_indv_training 
                                     SET is_deleted=0,indv_source='Group' 
                                     WHERE trn_id='$trn_id' 
                                       AND ind_id='$ind_id'");
            }
            else
            {
                $this->db->insert("wi_indv_training",
                                   array("ind_id"=>$ind_id,
                                         "indv_source"=>'Group',
                                         "trn_id"=>$trn_id,
                                         "attendance"=>"yes",
                                         "active"=>"yes"));
            }                
        }
    }
    else
    {
        $this->db->insert("wi_trn_org",array('trn_id'=>$trn_id,'grp_id'=>$value));
        $grp_indv=$this->db->pdoQuery("SELECT ind_id 
                                       FROM wi_indv_org 
                                       WHERE grp_id='$value' 
                                       AND is_deleted=0")->results();
                foreach ($grp_indv as $ke => $va) 
                {
                    $ind_id=$va['ind_id'];
                    $rows=$this->db->pdoQuery("SELECT * 
                                               FROM wi_indv_training 
                                               WHERE trn_id='$trn_id' 
                                               AND ind_id='$ind_id'")->results();
                    if(count($rows)>0)
                    {
                        $this->db->pdoQuery("UPDATE wi_indv_training 
                                             SET is_deleted=0,
                                                 indv_source='Group' 
                                             WHERE trn_id='$trn_id' 
                                               AND ind_id='$ind_id'");
                    }
                    else
                    {
                        $this->db->insert("wi_indv_training",
                                          array("ind_id"=>$ind_id,
                                                "indv_source"=>'Group',  
                                                "trn_id"=>$trn_id,
                                                "attendance"=>"yes",
                                                "active"=>"yes"));
                    }                
                }
            }
        }

Can anyone help regarding the best algorithm for performance enhancement?

JasonS · Accepted Answer · 2014-08-31 16:44:05Z

1

The main problem here is you have a multiple queries in a loop:

foreach ($grp_id as $key => $value) {
            # code...
            $rs=$this->db->pdoQuery("SELECT * FROM wi_trn_org WHERE trn_id='$trn_id' AND grp_id='$value'")->results();            
...
}

There is a performance hit for every SQL query, so if your loop ran 1,000 times, you've hit that performance bottleneck 1,001 times.

The way to solve this is to restructure your query inside the loop as part of the query outside the loop. The first query could be rewritten as a JOIN with the original query, and that may give you enough performance to solve your problem. If that isn't enough, you could portnetialy collapse all of this into a Stored Routine.

answered Aug 31, 2014 at 16:44

JasonS

1999 bronze badges

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

3 Comments

Sujit Baniya Over a year ago

Can you please elaborate with example. I tried with using INSERT INTO... ON DUPLICATE KEY UPDATE. but I couldn't get the result.

JasonS Over a year ago

did you do what I suggested re: trying to get the first SQL SELECT statement out of the loop? Did that work? That would be simpler than trying to tackle the whole thing -- depending on your database size, that may be sufficient.

Sujit Baniya Over a year ago

That reduced the overhead of multiple SELECT query in Loop which in turn reduces the time lag.

Sujit Baniya · Accepted Answer · 2014-09-01 14:51:26Z

0

I researched and found a solution to my query i.e.

        $this->db->pdoQuery("UPDATE wi_trn_org SET is_deleted=1 WHERE trn_id='$trn_id'");
        $this->db->pdoQuery("UPDATE wi_indv_training SET is_deleted=1 WHERE trn_id='$trn_id'");
        $this->db->pdoQuery("INSERT INTO wi_trn_org( trn_id, grp_id ) SELECT $trn_id AS trn_id, grp_id FROM wi_group WHERE grp_id IN ($grp) AND grp_id NOT IN (SELECT DISTINCT (grp_id) FROM wi_trn_org)");
        $this->db->pdoQuery("UPDATE wi_trn_org SET is_deleted=0 WHERE trn_id='$trn_id' AND grp_id IN ($grp)");
        $this->db->pdoQuery("INSERT INTO wi_indv_training( ind_id, trn_id ) SELECT ind_id, $trn_id AS trn_id FROM wi_indv_org WHERE grp_id IN ($grp) AND ind_id NOT IN ( SELECT DISTINCT (ind_id) FROM wi_indv_training WHERE trn_id =$trn_id)");
        $this->db->pdoQuery("UPDATE wi_indv_training SET is_deleted=0,indv_source='Group',attendance='yes',active='yes' WHERE trn_id='$trn_id' AND ind_id IN (SELECT DISTINCT(ind_id) FROM wi_indv_org WHERE grp_id IN ($grp))");

This provided me the solution with the performance enhancement in some extent. Does this algorithm really enhances the Query Performance?

answered Sep 1, 2014 at 14:51

Sujit Baniya

91512 silver badges34 bronze badges

2 Comments

N.B. Over a year ago

Let's see. You said you got some performance. You posted a few queries. The result is you got performance, according to what you wrote. Ergo, the only possible logical conclusion that the above code indeed improves performance. Question is, do you understand what's going on behind the scenes and how exactly databases are tuned for performance?

Sujit Baniya Over a year ago

Yes I understood what's going on behind.

Collectives™ on Stack Overflow

MySQL Insert if not Updated

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related