SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    725
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Question Running a query within a loop - Good Practice?

    Hi

    At times we need to run a SQL query within a WHILE or FOR loop to get data based on the value during the loop.

    I wonder if its a good practice at all?

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,048
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Well hitting the db is one of the most expensive processes. Therefore, minimizing queries should be of the most absolute importance for efficiency and scalability reasons. I try to avoid running queries in loops like I believe your referring to with every sense of my being for those reasons. Seems like in *most cases it can be avoided using joins. Though I wouldn't go as far to say it always being avoided but probably most the time it can be with a little SQL fu. One of the only cases I can think of where it is accepted is when using an ORM that has a smart cache layer. There aren't many good ways to map relational objects back and forth without extra queries or creating custom eager loading methods for specific data sets when dealing with ORMs. This is actually one of the common problems that come up with known ORMs and relational objects leading to usage of memcache and things of that nature.

  3. #3
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    725
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Please Consider the following scenario.

    I am developing an FB app where a user needs to select their FB friends (that was fetched from the FB API) from a list (checkboxes) and when they submit the button the selected friend's FB id is saved in the database. Now, what i need to do is, before inserting the FB ids in the db, i need to check if those ids are already in the db. Ids that are already in the db does not gets inserted.

    To achieve this, I need to run a loop of the checkbox array, and within each loop I will run a SELECT query to check if that particular Id exits in the db.

    If you think this is not a standard and efficient way, can you please suggest a better one?


    Many many thanks

  4. #4
    SitePoint Wizard wonshikee's Avatar
    Join Date
    Jan 2007
    Posts
    1,223
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    As a realist I'd say that unless you're talking real traffic, you can be pretty inefficient and it won't matter much. In my early days of learning PHP I had sites that did loops within loops within loops for queries and it runs fine because most sites just don't have much traffic anyway. Generally queries in loops should be avoided because it's the wrong way to do it.

    However, in your situation - it would be more ideal to check them all in a single query. Then simply filter out the ones that you do/don't want to insert and then generate another single query for inserting them all.

    One big query is far more efficient than splitting those up into individual queries, because there is an overhead to run a query.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by cancer10 View Post
    ... i need to check if those ids are already in the db. Ids that are already in the db does not gets inserted.
    if you are using mysql, you can improve the performance here immensely

    get rid of the loop, and just insert all of them at the same time using INSERT IGNORE
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,079
    Mentioned
    53 Post(s)
    Tagged
    0 Thread(s)
    If you need to run the same type of check without inserting, create an array of the IDs, convert array into a string that would equate to part of a "ID in ()" where criteria, return results to a seperate array. Then run a for each to check if item in array 1 is existant in array 2 from your recordset.


Bookmarks

Posting Permissions

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