Query Performance Question

#1 (**permalink**) 02-28-2008, 07:38 AM

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!

#2 (**permalink**) 02-28-2008, 07:38 AM

>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

#3 (**permalink**) 02-28-2008, 07:39 AM

arisser@gmail.com 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.

#4 (**permalink**) 02-28-2008, 07:39 AM

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.

#5 (**permalink**) 02-28-2008, 07:39 AM

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 parsers 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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode