Webmaster Forum / ColdFusion / Advanced Techniques / March 2008

Ian
I am storing using CLOB.
Basically when a person on the site fills in a form i have to append the row
to the wddx packet, also when deleting rows i have to a cfquery and exclude the
row that is to be deleted. There is a lot of going back and forth serializing
and deserializing which seems to be creating a bottleneck on large amounts of
data.

Oguz
Its storing the results from forms i'm having trouble with, i dont think i
understand your solution or we have our wires crossed?

Hmm, i'm still a bit puzzled, the actual form itself isnt too bad
processing-wise its dealing with the data posted from the form and storing the
results, the results needs to be exportable to csv so storing them as html
woulnt really work.
At the moment when the form is posted it goes to a cfcase and appends a row to
the wddx packet, the problem is this packet is huge and when i deserialize or
serialize it, the server hangs for quite a while dealing with the amount of
data.
You have to remember the forms are dynamic so the results are dynamic also.

All the previous replies (by the look of it) were related to the case
when form STRUCTURE is stored as WDDX, so they are quite irrelevant...

If you are trying to store the results of forms submissions (multiple
different forms) in some generic place then you are limited in
options:
1) [as you did already] you can store the data in serialized format,
you can play with various better functions for serializing/
deserializing, but it will give you only limited gains in
preformance...
2) I would NOT suggest storing results of forms submissions in
separate files - locking on edit/read is going to cause you
problems...
3) you can generate separate DB table to store the form data when you
create a form, again it has it's own drawbacks...

If the number of different forms is NOT too big, I would try option
3), if number of different forms is approaching DB limits on number of
tables/performance, I would even try to reuse tables for similar
forms...

Oguz
but the problem occurs when the form is submitted i dont have any problems
generating the code for the forms.
The process is as follows:

- coldfusion dynamically generates form (no speed issues)
- user submits form
- data submitted gets entered into database as wddx <----bottleneck server
hangs for a while serializing and deserializing
- Administrator logs into admin to look at results < -- more serialization /
deserialization - server is hanging

I opted for using wddx because the results were dynamic. A form could have many
fields and i would need to use create table SQL statement and I would also need
to create relationship tables for form fields that could have multiple options.
This is the route i'm trying to avoid, but it looks like its inevitable.
A different XML format other than WDDX would probably half the size of the
data i'm storing so i guess that might be an option.
Is there a wddx to db solution? that would be rather cool!
:grin;

First of all, don't be afraid of a huge amount of records.  That is what
databases are designed to deal with.  A great many very smart people
have spend a great many hours making database management systems very
efficient at processing huge amounts of records for the past fifty years
or so.  Personally, I trust their work to be more complete then anything
I can cobble together on my own.

Secondly, I would at least break it up so that each complete form submit
is one record in the database.  Then at least you are dealing with a
constant sized WDDX packet, not one that is growing with every new
record append to the data.

You more or less have to do that now.  You have to parse each record
node of the larger WDDX packet.  I don't think it would be that large a
change to parse a group of WDDX packets for the list.

My instinct says this is probably a more robust and scalable solution.

Put your CREATE/ALTER table functionality in your form generation
function - I would still suggest to generate static html from your CF
structure and use that even if right now your CF server can handle the
load, there is no point in repeating the same actions over and over
and over and over and over and over and over if you can do it once.
If you change your form AFTER some results were already collected it
is NOT going to change the existing results, just add new fields with
sensible default values...
If you allow to remove existing fields I would suggest NOT to remove
them from the tables (just do not use them), you never know when the
customer will realise that it was a mistake to remove that particular
field...