Professional Web Applications Themes

Lots of small queries vs one big query - MySQL

Hi, Pretty new to PHP and MySQL. I have a page on my site that displays a lot of information from various tables. Currently I have lots of small PHP wrapper functions around SQL queries to get each bit of information. This results in maybe 10 queries to display one page, but they are all very small and simple, and it keeps the PHP looking nice too as they are simple function calls to get each piece of info, which can be called from any page in my site. However, after reading up on SQL a bit, I am wondering ...

  1. #1

    Default Lots of small queries vs one big query

    Hi,

    Pretty new to PHP and MySQL.

    I have a page on my site that displays a lot of information from
    various tables. Currently I have lots of small PHP wrapper functions
    around SQL queries to get each bit of information. This results in
    maybe 10 queries to display one page, but they are all very small and
    simple, and it keeps the PHP looking nice too as they are simple
    function calls to get each piece of info, which can be called from any
    page in my site.

    However, after reading up on SQL a bit, I am wondering whether I should
    be looking at just generating one large query at the start, and using
    joins etc to lump all the relevant info together. This complicates the
    PHP as this means a custom query for each page of my site depending on
    what needs to be displayed, but I am wondering if it is worth it if I
    get a fair performance gain.

    Can any gurus point me in the right direction? Is there a "proper" way
    to do this sort of thing?

    Many thanks.

    listerofsmeg01@hotmail.com Guest

  2. #2

    Default Re: Lots of small queries vs one big query

    com wrote: 

    That's what relational databases are all about - having related data in
    different tables and joining them together.

    Sure you'll have different queries on different pages. That's because
    you need different data on different pages, don't you? The rest of your
    code isn't the same - why should the queries be?

    Let the database do what it's best at!

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  3. #3

    Default Re: Lots of small queries vs one big query


    com schreef:
     

    Normally if you join tables, it's because they need to be joined. E.g.
    if you want to display the private details of different persons from
    PRIVATE_DETAILS_TABLE and all the messages that these persons have sent
    from MESSAGES_TABLE, these to tables have to be joined to know what
    messages belong to what person. That's logical.
    Of course u can first query the PRIVATE_DETAILS_TABLE and based on that
    data query the MESSAGES_TABLE with a loop, but that's not so clean and
    I guess much slower since you have to query the DB again and again,
    while u only need to query it once.

    Greets,

    Bart

    Bart Guest

  4. #4

    Default Re: Lots of small queries vs one big query

    You are generally going to get a performance gain from grouping smaller
    queries into one query if possible. Be careful though, because this is
    not always the case. There are situations where the cost of the joins
    and the possible locking of the tables may decrease performance. These
    cases are rare though.

    Now, just because you get better performance does not mean that you
    should go out and change your code immediately. You mentioned that
    your code is currently easy to maintain. This carries a lot of value.
    There are certain situations where speed is critical and must be
    achieved at any cost, even if it increases the difficulty of the code
    maintenance. There's always that balance.

    Of course, do not think that the best performance is only achieved
    through the most cryptic code. There might be a way to get the best of
    both worlds. Is there a way that you can use the functions you have
    right now to, rather than perform queries, aggregate onto a query. I
    have done this many times where a function call does not generate a
    query of its own but simply adds to a query that I am generating. Once
    I have called all my necessary methods, I then run the query and
    extract the data.

    Hopefully this has been somewhat helpful.

    jinxidoru Guest

  5. #5

    Default Re: Lots of small queries vs one big query

    On 21 Nov 2006 16:09:29 -0800, com wrote: 

    Premature optimization is the Root of Evil in programming. Do you
    foresee having enough load on your little ten queries to cause a
    problem? (And bear in mind, that's probably a much bigger load than you
    think, and that your actual usage will seldom reach your wildest
    expectations.) If the ten-query version works for any reasonably
    expectation of usage *AND* is clean and easier to read as a result of
    the design, then don't change it.

    --
    66. My security keypad will actually be a fingerprint scanner. Anyone who
    watches someone press a sequence of buttons or dusts the pad for
    fingerprints then subsequently tries to enter by repeating that sequence
    will trigger the alarm system. --Peter Anspach's Evil Overlord List
    Peter Guest

  6. #6

    Default Re: Lots of small queries vs one big query

    There is no "proper" way for all systems. You have to strike a balance
    that works for your system. The query cache stores the results of
    queries and returns the stored result rather than performing the query
    again, provided the tables underneath have not changed.

    So if all of your tables update infrequently then you would be able to
    get some performance gains out of combining all your queries into one.
    However, if one of those tables updates frequently that same combined
    query could potentially decrease performance depending on the
    complexity of the joins. If some update frequently and some don't then
    you could get some gain combining the queries that update infrequently,
    and leaving the others alone.

    See where I'm going? It's database specific. You need to yze how
    often things update to see whether or not you might gain something by
    combining queries. Like others have said, you also have to keep in mind
    maintenance.

    Dr.Zoidberb Guest

  7. #7

    Default Re: Lots of small queries vs one big query

    Just wanted to say thanks for everyone who replied. Much appreciated
    and have taken on board many of your comments.

    In the end I have grouped some similar queries together rather than the
    whole lot, and cached the results in a php class. I then access the
    values through that class which just does the one fetch initially, then
    feeds me similar values should I request them without doing a lookup.

    Thanks again.

    listerofsmeg01@hotmail.com Guest

Similar Threads

  1. Query of Queries
    By restlessmedia in forum Coldfusion Database Access
    Replies: 12
    Last Post: September 12th, 09:51 PM
  2. Need Help with Query of Queries
    By LyndonPatton in forum Coldfusion Database Access
    Replies: 6
    Last Post: April 18th, 09:02 PM
  3. Query of Queries with constructed query using QueryNew()and QueryAddRow()
    By LeadFoot in forum Coldfusion - Advanced Techniques
    Replies: 2
    Last Post: August 4th, 02:00 PM
  4. query of queries with avg()
    By gogl in forum Coldfusion - Getting Started
    Replies: 3
    Last Post: July 6th, 09:50 PM
  5. Query of Queries on query New type query
    By david_h in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 6th, 08:55 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139