SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Jan 2009
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    complex query vs multiple simpler queries with php logic

    Hello all.

    Hoping for some general guidance here.

    Question: Are there any good rules of thumb to follow when trying to decide whether to perform a complex sql query which may involve sub-queries to achieve "intersections" vs using multiple simple queries and then some array processing in php to get the correct result set?


    The case I'm running in to is a bit complex to describe here in detail, but the basic idea is:

    • suppose i have a "primary" table with ID and NAME columns
    • that ID will be linked to other db "secondary" tables' IDs via matching tables
    • now suppose i might like to query for a set of those "primary" table IDs based on whether they match any or all of a list of "secondary" tables' IDs.



    I basically have two choices:

    1. construct a rather complicated single query (which sometimes contains sub-queries) based on the "secondary" IDs and tables.

    2. construct multiple simpler queries and then perform some intersecting logic or so in order to produce the final "primary" ID result set.


    Any thoughts on which is better? I realize it very likely depends on my case, but any general guidance, ideas, trade offs, etc. you can identify are much appreciated!

    Thanks in advance!

    JSM

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    in general, a single query to the database is more efficient than multiple queries

    helps??

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Jan 2009
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes, and does that hold even if the single query contains sub-queries?

    perhaps there's less overhead in that case... whatever overhead mysql has in initiating a given query string or so?

    thanks for the quick reply!

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    a query with subqueries is not necessarily any slower than a query without subqueries

    we gotta stop talking in generlaities here

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •