Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Sep 2012
    Posts
    76
    Thanks
    61
    Thanked 0 Times in 0 Posts

    Query within a query?

    Hello, I have a very quick question. I have written a while loop and have found myself producing two additional queries within the loop of the query. Is this bad practice?

    Thank you.

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Its really bad practice.
    Writing a query within a loop results in querying n times where n is governed by the size of the loop. With a query that returns a resultset of 50 and a loop that queries twice more is a minimum of 100 + 1 queries per load.
    DBMS can be locked down to a maximum number of queries (per hour I believe). Say its locked down to 20,000 per hour, that means you can only execute the script 200 times before you cannot query any more for the remaining hour. It doesn't take long to run through 200 executions.
    It will also be slowish. Since you need to keep going back to the dbms for more information you are shuffling an external connection for each one of these resultsets.

    Change your query to a join and that problem will go from 101 queries to 1.

  • Users who have thanked Fou-Lu for this post:

    Oatley (12-11-2012)

  • #3
    New Coder
    Join Date
    Sep 2012
    Posts
    76
    Thanks
    61
    Thanked 0 Times in 0 Posts
    Thanks Fou-Lu. That's a great piece of advice.

    I have one scenario though, which I'm stuck on then. I have 'table_a' which contains a number of entries and a column called 'id' and another column called 'is_set' which contains a value of 0 or 1.

    I wish to find all the Id's in 'table_a' which have a value in 'is_set' of 1

    Then UPDATE a different table 'table_b' which also has the columns 'id' and 'is_set' finding each of the entries where table_a.id = table_b.id

    So best way I can think of doing this would be in a loop, which is why I asked the question. Is there therefore. Better way of handing this?

    Thank you
    Last edited by Oatley; 12-11-2012 at 09:00 AM.

  • #4
    Senior Coder Dormilich's Avatar
    Join Date
    Jan 2010
    Location
    Behind the Wall
    Posts
    3,288
    Thanks
    12
    Thanked 345 Times in 341 Posts
    that can be done by a simple join.
    Code:
    SELECT
        -- fields here
    FROM
        table_a
    JOIN
        table_b USING (id)
    WHERE
        table_a.is_set = 1;
    The computer is always right. The computer is always right. The computer is always right. Take it from someone who has programmed for over ten years: not once has the computational mechanism of the machine malfunctioned.
    André Behrens, NY Times Software Developer

  • Users who have thanked Dormilich for this post:

    Oatley (12-11-2012)

  • #5
    Super Moderator Inigoesdr's Avatar
    Join Date
    Mar 2007
    Location
    Florida, USA
    Posts
    3,647
    Thanks
    2
    Thanked 406 Times in 398 Posts
    Quote Originally Posted by Dormilich View Post
    that can be done by a simple join.
    Also, make sure you have keys on the field you are joining on. The performance will be quite a bit better, especially on larger tables.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •