When relating two tables, is the correct one on the SQL side, PHP or whatever?

Asked

Viewed 70 times

3

I have this doubt of what is preferred to do when I need data from the two tables to get the result.

I always did the comparison in PHP, but I learned the SQL relationship commands that could help. What is the most correct way to do, regarding runtime, standard and performance?

I used to do this:

 <?php
          $consultaRecomendados=$con->prepare("SELECT * FROM  hf_recomendados");
          $consultaRecomendados->execute();
          $resultadoRecomendados=$consultaRecomendados->fetchAll();
          foreach($resultadoRecomendados as $produtoRecomendado){
               $contultaProdutoRecomendado=$con->prepare("SELECT * FROM  hf_produtos WHERE id=:id");
               $contultaProdutoRecomendado->execute(array(":id"=>$produtoRecomendado['idProduto']));
               $resultadoProdutoRecomendado=$contultaProdutoRecomendado->fetch();
                echo $resultadoProdutoRecomendado['nome']; 
          }
          ?>

And I went like this:

  <?php
              $contultaRecomendados=$con->prepare("SELECT * FROM  hf_recomendados r JOIN  hf_produtos p
ON r.idProduto=p.id");
              $contultaRecomendados->execute();
              $resultadoRecomendados=$contultaRecomendados->fetchAll();
              foreach($resultadoRecomendados as $produtoRecomendado){
                  echo $produtoRecomendado['nome'];
              }
      ?>

Is there a better or more correct way? What technical considerations should I have?

php sql pdo performance
  • 3

    Line of code is irrelevant. An efficient 100-line code is usually worth more than a 2-line pig code, unless in rare cases. Qq way, the 1st example of yours is a programming failure even working. The 2nd case is delegating the task to the right place.

  • So that’s why I came up with this question...

  • 3

    Note that in the first case you are requesting a series of queries from the server, without the minimum optimization. In the second case, it is a query only, and the relation is done on the server. If you do a test with a lot of data you will notice an absurd difference even in speed. I am not going to tell you that it should NEVER be done the first way, but in normal situations there is no reason and there is no reason. And if you ever need it, it pays to do an internal query, and use bind. (and if it is Mysql, forget this PDO business, which is an unnecessary and ineffective layer especially in these cases)

  • Because it says that "your first example is a programming fault"?

  • Pq is extremely stressful for script execution to do a number of queries within a loop - the difference in performance is brutal, and loses atomicity. You can have data variation between a select and another, generating a number of inconsistencies. There are other problems as well. If no one posts anything cool explaining to you, and there’s time, I try to work something out.

  • I understood. I don’t really like doing various things within a loop. It feels heavier. It’s the same feeling of using loop input from another loop

  • 1

    Loop inside Loop if used normally has no problem. As long as it is suitable for the specific case, of course.

  • And depending on the size of each loop as well. Thank you Bacco, either way already helped enough

  • Only one comment, the first example should only be used if and only if there is no other resource

  • @Marcelobonifazio, you say that about performance? something that complements this comment?

  • 3

    It’s more in line than @Acco said, never say you’ll never need to use any feature like this, but if you can avoid it, it’s the best performance-oriented decision

  • 3

    JOIN SQL serves precisely to prevent the software from being responsible for this data junction.

  • 2

    If you want efficiency and delegate responsibility to manipulate the data to your code, work with "txt" files, as this killing the purpose of a DBMS

  • 1

    You need to analyze, make a join may be unnecessary. Everything @Bacco said I agree with, however the join should be done only if at that time the information is relevant, for example, a list of records where there is an "edit" button. For the list I won’t do the join if the information is irrelevant, then I’ll just do the join when the client clicks to edit. I’m doing more than one database query, it’s true, but in that case there’s performance gain.

Show 9 more comments

1 answer

4


In short, it is best to use the DBMS to join the queries.

Every time you run a query in a database it will basically do the following:

  1. Interpret the SQL command to know if the syntax is correct and if all objects are available for execution. Then convert SQL to a relational algebra structure.
  2. Perform an analysis to make an execution plan of the query to separate the parties and define the execution order. This involves deciding which indexes are available and which algorithms are most efficient for joining the tables, based on the internal statistics it collects from the database such as number of records, indexes, etc.
  3. Execute each part of the algorithm and join each result to then make the result available to the caller, where he can do the "fetch" of data.

So when do you put the JOIN within the SQL query you are telling the database to resolve these junctions for you using the best possible mechanism and it will use its code, which internally is optimized to do this.

If you choose to do the work it would do internally, it is likely that your code will be less efficient. There are several ways to do Joins between tables and this is one of the major problem points for a relational database solve. Even the way you write your queries, it is used JOIN or Subquery, whether to use an operator IN or a EXISTS affects the execution time internally because it changes the way the database performs the query.

If you want to delve into the subject research on "Relational Algebra" and "Query Optimization" and you will find quite interesting material.

  • Thank you Pagotti, it was the answer I was waiting for. Grateful also for the indications :)

  • @Localhost :) If you can, mark the answer as the correct one for your question or if you want, you can wait to see other answers that will come.

  • I was hoping, but I think that was enough :)

Browser other questions tagged php sql pdo performance

You are not signed in. Login or sign up in order to post.