Database performance with looped update queries?

Question

I am currently structuring my queries for a database update model with CodeIgniter.

I getting the form posted input keys with $keys = array_keys($_POST)

To update according database fields I was wanting to do something like this

foreach($keys as $key){
    $data = $this->input->post($key);
    $this->db->query("Update $table SET '$key'='$data' WHERE user_id='$the_user->id'");  }

(this is not production code, just trying to illustrate my question, hence no escaping, validation, etc...)

My question is, does this pose a performace problem by running a seperate query for each individual piece of form data submitted?

EDIT: sorry didn't intend for it to sound like a yes or no question, obviously if yes or no, the answer is yes, but I am looking for more of to what degree does it hamper the performance?

Answer 1

Yes.

You're issuing multiple updates, rather than one. The performance impact is less likely to be in your php loop, and more in the database connection; thus minimizing the amount of transactions between your application and the database will improve performances.

You can try:

1. build your SQL string before submitting:

   UPDATE <table> SET key1=data1, key2=data2, key3=data3 WHERE user_id=<user>
   

2. executing multiple updates in the same transaction:

   $this->db->query("
       UPDATE <table> SET key1=data1 WHERE user_id=<user>;
       UPDATE <table> SET key2=data2 WHERE user_id=<user>;
       UPDATE <table> SET key3=data3 WHERE user_id=<user>;"
   );
   

I'm not sure how PHP/MySQL handle the second option. Perl/Postgres can do it. Also, I would think that the first option would be faster.

Another thing to consider, if you're doing this for multiple users, is to use an IN (eg WHERE user_id IN ($user_list), where $user_list is a string of users in the format 'user1','user2','user3').