Serializing data vs Storing it in a seperate table

[Kyle Teague]

What would give better performance, serializing a multidimensiona l array
and storing it in a single entry in a table or storing each element of
the array in a separate table and associating the entries with the entry
in the other table?

Having a separate table would result in two queries instead of one, but
you wouldn't have to deal with the overhead of serializing and
unserializing data.

-- Kyle

[Jerry Stuckle]

Re: Serializing data vs Storing it in a seperate table

Kyle Teague wrote:[color=blue]
> What would give better performance, serializing a multidimensiona l array
> and storing it in a single entry in a table or storing each element of
> the array in a separate table and associating the entries with the entry
> in the other table?
>
> Having a separate table would result in two queries instead of one, but
> you wouldn't have to deal with the overhead of serializing and
> unserializing data.
>
> -- Kyle[/color]

That depends. Is the multidimensiona l array a single entity - or is it a
collection of entities?

Do some research on "database normalization". It's almost never a good idea to
store multiple entities in a single field in a RDB.

BTW you can still do it with one query by joining tables.

One other thing - this isn't a PHP question - you should be asking in a group
related to your database, such as comp.databases. mysql.

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===

[Colin McKinnon]

Re: Serializing data vs Storing it in a seperate table

Jerry Stuckle wrote:
[color=blue]
> Kyle Teague wrote:[color=green]
>> What would give better performance, serializing a multidimensiona l array
>> and storing it in a single entry in a table or storing each element of
>> the array in a separate table and associating the entries with the entry
>> in the other table?
>>[/color]
>
> That depends. Is the multidimensiona l array a single entity - or is it a
> collection of entities?
>
> Do some research on "database normalization". It's almost never a good
> idea to store multiple entities in a single field in a RDB.
>
> BTW you can still do it with one query by joining tables.
>
> One other thing - this isn't a PHP question - you should be asking in a
> group related to your database, such as comp.databases. mysql.
>[/color]

I don't think its OT. It really depends how big the array is and whether you
need all of it in memory every time you access it. If you only need a
specific element, I was surprised to find that even for quite small sets of
values the database was faster.

Build a test rig and try it out for yourself.

HTH

C.

[Jerry Stuckle]

Re: Serializing data vs Storing it in a seperate table

Colin McKinnon wrote:[color=blue]
> Jerry Stuckle wrote:
>
>[color=green]
>>Kyle Teague wrote:
>>[color=darkred]
>>>What would give better performance, serializing a multidimensiona l array
>>>and storing it in a single entry in a table or storing each element of
>>>the array in a separate table and associating the entries with the entry
>>>in the other table?
>>>[/color]
>>
>>That depends. Is the multidimensiona l array a single entity - or is it a
>>collection of entities?
>>
>>Do some research on "database normalization". It's almost never a good
>>idea to store multiple entities in a single field in a RDB.
>>
>>BTW you can still do it with one query by joining tables.
>>
>>One other thing - this isn't a PHP question - you should be asking in a
>>group related to your database, such as comp.databases. mysql.
>>[/color]
>
>
> I don't think its OT. It really depends how big the array is and whether you
> need all of it in memory every time you access it. If you only need a
> specific element, I was surprised to find that even for quite small sets of
> values the database was faster.
>
> Build a test rig and try it out for yourself.
>
> HTH
>
> C.[/color]

Colin,

Look at the question again. He's asking about the method of storing data in a
database (database structure), not array handling.

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===

[Malcolm Dew-Jones]

Re: Serializing data vs Storing it in a seperate table

Jerry Stuckle (jstucklex@attg lobal.net) wrote:
: Colin McKinnon wrote:
: > Jerry Stuckle wrote:
: >
: >
: >>Kyle Teague wrote:
: >>
: >>>What would give better performance, serializing a multidimensiona l array
: >>>and storing it in a single entry in a table or storing each element of
: >>>the array in a separate table and associating the entries with the entry
: >>>in the other table?
: >>>
: >>
: >>That depends. Is the multidimensiona l array a single entity - or is it a
: >>collection of entities?
: >>
: >>Do some research on "database normalization". It's almost never a good
: >>idea to store multiple entities in a single field in a RDB.
: >>
: >>BTW you can still do it with one query by joining tables.
: >>
: >>One other thing - this isn't a PHP question - you should be asking in a
: >>group related to your database, such as comp.databases. mysql.
: >>
: >
: >
: > I don't think its OT. It really depends how big the array is and whether you
: > need all of it in memory every time you access it. If you only need a
: > specific element, I was surprised to find that even for quite small sets of
: > values the database was faster.
: >
: > Build a test rig and try it out for yourself.
: >
: > HTH
: >
: > C.

: Colin,

: Look at the question again. He's asking about the method of storing data in a
: database (database structure), not array handling.

Look at the question again. He's asking about "serializin g a
multidimensiona l array", which is php array handling.

In other words, he is asking whether it is better to implement his data
structure in php and use the database just as a blob-like storage medium,
or is it better to implement his data structure in the database and use
php just as an access method to get at the (already structured) data.

I would normally tend to towards defining the correct database structure
to hold the data. It will be more flexible in the long run, and you still
have the option of loading the data into a php data structure if you
really need to do that for speed in some situations.

[R. Rajesh Jeba Anbiah]

Re: Serializing data vs Storing it in a seperate table

Kyle Teague wrote:[color=blue]
> What would give better performance, serializing a multidimensiona l array
> and storing it in a single entry in a table or storing each element of
> the array in a separate table and associating the entries with the entry
> in the other table?[/color]
<snip>

That depends. If you store multi-dimensional array in a single
field by serializing, querying data (if based on the values present in
the array) will be difficult; so in that case, storing them in tables
will be a better choice.

--
<?php echo 'Just another PHP saint'; ?>
Email: rrjanbiah-at-Y!com Blog: http://rajeshanbiah.blogspot.com/

[Jerry Stuckle]

Re: Serializing data vs Storing it in a seperate table

Malcolm Dew-Jones wrote:[color=blue]
> Jerry Stuckle (jstucklex@attg lobal.net) wrote:
> : Colin McKinnon wrote:
> : > Jerry Stuckle wrote:
> : >
> : >
> : >>Kyle Teague wrote:
> : >>
> : >>>What would give better performance, serializing a multidimensiona l array
> : >>>and storing it in a single entry in a table or storing each element of
> : >>>the array in a separate table and associating the entries with the entry
> : >>>in the other table?
> : >>>
> : >>
> : >>That depends. Is the multidimensiona l array a single entity - or is it a
> : >>collection of entities?
> : >>
> : >>Do some research on "database normalization". It's almost never a good
> : >>idea to store multiple entities in a single field in a RDB.
> : >>
> : >>BTW you can still do it with one query by joining tables.
> : >>
> : >>One other thing - this isn't a PHP question - you should be asking in a
> : >>group related to your database, such as comp.databases. mysql.
> : >>
> : >
> : >
> : > I don't think its OT. It really depends how big the array is and whether you
> : > need all of it in memory every time you access it. If you only need a
> : > specific element, I was surprised to find that even for quite small sets of
> : > values the database was faster.
> : >
> : > Build a test rig and try it out for yourself.
> : >
> : > HTH
> : >
> : > C.
>
> : Colin,
>
> : Look at the question again. He's asking about the method of storing data in a
> : database (database structure), not array handling.
>
> Look at the question again. He's asking about "serializin g a
> multidimensiona l array", which is php array handling.
>[/color]

Yes - but only as it affects how to store it in the database. Without the
database the serialization is meaningless.
[color=blue]
> In other words, he is asking whether it is better to implement his data
> structure in php and use the database just as a blob-like storage medium,
> or is it better to implement his data structure in the database and use
> php just as an access method to get at the (already structured) data.
>[/color]

Which is why he needs to be asking in the database newsgroup.
[color=blue]
> I would normally tend to towards defining the correct database structure
> to hold the data. It will be more flexible in the long run, and you still
> have the option of loading the data into a php data structure if you
> really need to do that for speed in some situations.
>[/color]

It depends entirely on how he's going to use the data. Does he need to retrieve
all the data all the time? Or just bits and pieces of it?

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===