Professional Web Applications Themes

Query Performance Question - MySQL

Hi everybody, I am coding a application that helps people track their weight by date and then graphs it for them. My question is, would it be faster to store all of the weight/date combinations in one field in an xml format and pull it out as one row, or would it be faster to store each weight/date as an individual field, or is there no difference since it is the same amount of data? I would be using PHP to format the large xml string before I store it in the DB, so that would add to the scripts ...

  1. #1

    Default Query Performance Question

    Hi everybody,

    I am coding a application that helps people track their weight by date
    and then graphs it for them.

    My question is, would it be faster to store all of the weight/date
    combinations in one field in an xml format and pull it out as one row,
    or would it be faster to store each weight/date as an individual field,
    or is there no difference since it is the same amount of data?

    I would be using PHP to format the large xml string before I store it
    in the DB, so that would add to the scripts time.

    Any thoughts on which method would be faster?

    Thanks!

    arisser@gmail.com Guest

  2. #2

    Default Re: Query Performance Question

    >I am coding a application that helps people track their weight by date
    >and then graphs it for them.
    >
    >My question is, would it be faster to store all of the weight/date
    >combinations in one field in an xml format and pull it out as one row,
    >or would it be faster to store each weight/date as an individual field,
    >or is there no difference since it is the same amount of data?
    Storing data as one big mess in one field is usually a mistake. It
    makes the data much harder to use.

    In order to ADD a data point, you have to pull out all the data,
    add the data point, then put it all back. If you have a table of
    userid, date, weight, you only need to add a row. You also cannot
    ask MySQL to sort the data or select only, for example, the last
    year. Also, do you sort the data when it goes in, or when it
    comes out?

    It also makes it harder to generate summary data, like a graph
    of average weight of all participants in each month, or just
    a graph of number of users in each month.

    Any application can run infinitely fast and in zero space if it
    doesn't have to give the right answer. Are you sure this XML format
    stuffed into MySQL will accomodate 10 years worth of daily data?

    >I would be using PHP to format the large xml string before I store it
    >in the DB, so that would add to the scripts time.
    >
    >Any thoughts on which method would be faster?
    Maybe you should think about things other than "faster".

    Gordon L. Burditt
    Gordon Burditt Guest

  3. #3

    Default Re: Query Performance Question

    [email]arisser[/email] wrote:
    > My question is, would it be faster to store all of the weight/date
    > combinations in one field in an xml format and pull it out as one row,
    > or would it be faster to store each weight/date as an individual field,
    > or is there no difference since it is the same amount of data?
    I would store each weight & date in _two_ columns, one for weight and
    one for date. Plus a foreign key to the person table. You do need to
    store the weight & date in a separate table from your person table. So
    there will be a lot of rows, but with proper indexes it'll be very fast.
    A lot faster than doing a bunch of XML parsing and re-formatting in PHP.

    This design enables you to do lots of things more easily. Query for a
    range of dates, compute the min, max, and average weights, percentage
    weight loss over time, etc. directly in SQL.

    Regards,
    Bill K.
    Bill Karwin Guest

  4. #4

    Default Re: Query Performance Question

    Thank you for the response. Maybe I should explain why I wanted to do
    it with the xml field. The user gets an excel like chart, where they
    can add rows dynamically and edit every value(like excel). The problem
    that I was seeing was that if they can edit every value, then I will
    have to run a sql update for each row when the user submits. I figured
    that this would be very slow.

    With the data in an xml string, its easy to add, edit, update the xml
    using the xml dom functions in PHP. So it would be easy to reformat
    the xml then do one sql update to put it back in the database.

    You make an excellent point about sorting the data and getting summary
    data. My solution would not handle this. Thanks again for your help.

    Adam Risser Guest

  5. #5

    Default Re: Query Performance Question

    Adam Risser wrote:
    > With the data in an xml string, its easy to add, edit, update the xml
    > using the xml dom functions in PHP. So it would be easy to reformat
    > the xml then do one sql update to put it back in the database.
    You're assuming that something that takes less code to write will be
    quicker.

    In my experience, DOM prs are notoriously slow. A web app I
    developed spent 80% of its time parsing HTML templates, looking for form
    fields to auto-populate. The SQL code represented more code in my app,
    but a tiny fraction of the execution time.

    I don't have experience with PHP's DOM functions, so I can't say for
    certain that it's faster or slower than using SQL. But I'd want to do
    some tests to find out which is faster, using DOM or using UPDATE of
    potentially several rows, before making an assumption about which has
    better performance.

    Regards,
    Bill K.
    Bill Karwin Guest

Similar Threads

  1. Simple query, very low performance
    By Bernhard Kornberger in forum MySQL
    Replies: 3
    Last Post: July 6th, 12:27 PM
  2. Performance Question
    By Mark A in forum Coldfusion - Advanced Techniques
    Replies: 7
    Last Post: March 14th, 08:08 PM
  3. ADO Performance Question
    By MDW in forum ASP Database
    Replies: 2
    Last Post: December 7th, 04:10 AM
  4. performance question...
    By HomeBrewer webforumsuser@macromedia.com in forum Macromedia Director Lingo
    Replies: 2
    Last Post: September 25th, 06:02 PM
  5. query performance mystery
    By Lou Fiorino in forum Oracle Server
    Replies: 1
    Last Post: January 11th, 11:29 AM

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