How to make my php code faster?

Question

I'am using php on server side to manage data with MySQL. I have to request a API that gives me an list of users. I need to check for each user if he is in the database. If yes, I update his information. If not, I insert him in the data base.

The issue is that there is more than 2000+ users each times and my code in PHP is really slow (sometimes I get 504 Gateway Time-out). We will have even more users very soon.

How can I make my code faster ? Is Php ok ?

EDIT my codeV3 after improvement:

    $userList = getFromAPI();

      foreach ($userList as $userId){

        $db = dbConnect();


  $tagList = implode(",", $user["tagid_list"]);



          $query = $db->prepare(
      "INSERT INTO USERS(id, name, group) VALUES(:id, :name, :group)
       ON DUPLICATE KEY UPDATE name=values(name), group=values(group)"
          );

          $query->execute([
        "id"=>$id,
        "name"=>$name,
        "group"=>$group
          ]);
        }

Have you considered select id from users where id = :userid and seeing if it returns a row? Alternatively, how about scrapping that whole bit and instead doing insert into users ... on duplicate key update ...? — Niet the Dark Absol
– Niet the Dark Absol, Commented Jul 3, 2017 at 8:56
Don't fetch every single user when checking to see if specific user exists on the database, modify your select to select by the id that you want to check.... then you can also eliminate that array_column() and in_array() check — Mark Baker
– Mark Baker, Commented Jul 3, 2017 at 8:57
@NiettheDarkAbsol I make a updateEdit of my post with my new code with insert into on duplicate key update. But it seems not working. the "id" column is my primary key. — Jilu
– Jilu, Commented Jul 3, 2017 at 9:37
@MarkBaker Thank for the suggestion, I've modify the select and put a "!empty()" to check if there is result. Edited post with my new code if you have any improvement — Jilu
– Jilu, Commented Jul 3, 2017 at 9:38

Carl Kroeger Ihl · Accepted Answer · 2017-07-03 08:58:15Z

4

Maybe try with putting $db = dbConnect(); outside of your foreach? I don't know if it is needed to open the connection in each cycle. It may be time consuming aswell.

answered Jul 3, 2017 at 8:58

Carl Kroeger Ihl

3414 silver badges13 bronze badges

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

3 Comments

M. Eriksson Over a year ago

It depends on what that function does. If it returns a singleton, it doesn't matter (even if it still makes more sense to have it outside). If it actually connects in each iteration, then yes, that would definitely matter. There are other, way more, obvious things that affects the speed in that code, though.

Jilu Over a year ago

The foreach you see is in an other function. So I should put $db = dbConnect out the function and put $db in parameters for this one ?

Carl Kroeger Ihl Over a year ago

Yes, that is possible. You can also read about this in this question

mingos · Accepted Answer · 2017-07-03 09:07:49Z

3

You can use a single query for that:

INSERT INTO users (id, name)
VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Cecil')
ON DUPLICATE KEY UPDATE name = VALUES(name);

In a nutshell: you insert new rows, but if one already exists (the key is duplicated), it is updated instead. You can build your insert values in a loop so you end up with a single query instead of 4000+.

5 Comments

Jilu Over a year ago

$query = $db->prepare( "INSERT INTO USERS(id, name, group) VALUES(:id, :name, :group) ON DUPLICATE KEY UPDATE name=:name, group=:group");. Not seems to work.

mingos Over a year ago

That's awful! Maybe if you give some details about what's wrong, what error message you're getting, etc., then I can try to guess what the issue is.

Jilu Over a year ago

There is no error message, but the data aren't insert or update.

mingos Over a year ago

Try building your query the way I suggested, using VALUES(name) and VALUES(group) in the update part. Do not repeat the same parametre twice.

Jilu Over a year ago

Ok, it's done, but still no error msg and no data insert or update. (edited the post)

W. Nowogrodzki · Accepted Answer · 2017-07-03 09:04:07Z

2

First of all get fetching all users ids from database out of foreach lopp and buffer it in some variable. Should be better.

answered Jul 3, 2017 at 9:04

W. Nowogrodzki

213 bronze badges

4 Comments

Jilu Over a year ago

Thanks, I replaced the fetch all users by a select where id=id. Is it better?

W. Nowogrodzki Over a year ago

I would put fetchall before foreach and do not connect to db each cycle as @carl-kroeger-ihl noticed.

Jilu Over a year ago

I delete the select, found a alternative way to do this. For the dbConnect() the issue is that all the code you see in the foreach is in a function. So I need to transfer the db in parameters?

W. Nowogrodzki Over a year ago

I would to it like you've said (connected $db as function parameter).

Collectives™ on Stack Overflow

How to make my php code faster?

3 Answers 3

3 Comments

5 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

5 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related