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

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