Form Builder storing results as WDDX

[cf_matt]

A while back i wrote a form builder. To store the results of a form submission I decided to use wddx and save the xml string into the db, which seemed like a good idea at the time. Further down the line this form builder has been used for much larger forms and sheer amount of data being store as text is making the server crash when it has to be serialized or deserialized.
I was wondering if there is a better way to store this data, obviously storing the data in tables would be a bit of a pain as the form builder is dynamic and can have any number of fields.
I decided against storing the wddx data in a text file as i was worried about read/write locks on a busy form. Was i wrong to go down this route?

Hope someone can help

Matt

[Newsgroup_User]

cf_matt wrote:
> I decided against storing the wddx data in a text file as i was worried about
> read/write locks on a busy form. Was i wrong to go down this route?
>
> Hope someone can help
>
> Matt

How are you storing the data? In varChar fields? If so could you
consider a CLOB (Character Large OBject) field? Assuming your database
management system supports them. This is somewhat like storing as a
text file, but your database takes care of managing them for you. It
improves the performance of your database tables which can ignore this
large data when it is not needed at the slight cost of not being able to
do normal searches and filters on any data contained in a CLOB field.
But I presume you are not doing any matches or filters based on this
data, are you?

[cf_matt]

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?

[Newsgroup_User]

Oguz's suggestion is basically a form of you file solution. Create
these forms as files of HTML fragments and then include the desired file
whenever a form needs to be displayed.

To determine what the exact problem is and what maybe the best solution
will take some testing and experimentation. One would need to identify
which exact operations are taking the most time and what, if anything,
solution improves that performance.

[Oğuz_Demirkapı]

I had a same kind of issue and at the end I decided to generate forms in static HTML via admin. Whenever they finished editing forms I generated HTML for the forms with a "generate" button and saved the generated forms in a directory and embedded (included) these forms in the application pages with a simple parameters.

[Oğuz_Demirkapı]

You can save these forms in HTML format and include them whenever you need them.

How you generate ehse forms is also very basic logic. After deciding latest form status in your admin, you can push these latest forms as HTML into a directory etc. with a "Generate Forms" button. This only works once on admin interface and you do not have to do this work in realtime for every request.

I hope it is clear. :)

[cf_matt]

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.

[cf_matt]

Also if i stored the results as html, i wouldnt be able to ever change the output or easily show the output, it sounds messy.
the data is queried also (i convert the wddx to cfquery). I know its very hard to know without seeing the code but this wddx packet is definately the bottleneck and if there was an alternative or faster way of storing the information or dealing with this information then that would really help.
I've started to give the option to archive the information but on a form that has say 50 fields it doesnt take long for the wddx results to get huge!

I didnt originally design the form builder for forms of this size but it is being used this way and is so much faster than developing the forms with cfml.

[Oğuz_Demirkapı]

You can check the page politico.com as an example.

This site uses ColdFusion but you can not figure out easily.

Because of having huge load on servers, they prefer to generate news page as HTML from database and save as static pages. That does not means that these pages are not dynamic. They are but just not generated on fly.

This allows less load because of not generating all the pages in realtime. The admin just push a button and generate all the pages 4 times a day etc. I think your forms are not changing everyday and you may consider to generate these dynamic forms daily or on demand basis and use them in your application without having the load to generate them dynamically in every request.

[cf_matt]

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

[Newsgroup_User]

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

Is all the data from all the form submitted records in a single database
record? I would hope not.

[Oğuz_Demirkapı]

Why don't you save form results direct to the database? Do you really need WDDX?

OR you can try to use JSON or direct XML.

[cf_matt]

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!

[Newsgroup_User]

cf_matt wrote:
> Is there a wddx to db solution? that would be rather cool!
>

Well, there are XML based database management systems. I have never
used one, but I do know they exist, but that is about the extent of my
knowledge.

[cf_matt]

Ian

yes i think thats right

so i have:

form_id | form_name | form_recordset
1 enquiries <WDDX>

I did think about storing name and value pairs in a table but then that would be huge amounts of records

[Newsgroup_User]

cf_matt wrote:
> Ian
>
> yes i think thats right
>
> so i have:
>
> form_id | form_name | form_recordset
> 1 enquiries <WDDX>
>
> I did think about storing name and value pairs in a table but then that would
> be huge amounts of records
>


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.

[cf_matt]

Hmm i hadnt thought about breaking each record into a wddx packet, it would make it hard on cf to display a list of results though as you would have to parse each packet for a value.

I know what you mean about huge amounts of records not really being a problem.

The scenario I have is that clients are using the form builder for employment forms, so the forms have about 100 fields (i'm not joking). so that would be 100 records per individual entry.

I guess it will still be faster than using wddx.

I am starting to think that if i'm going to do the whole thing with name and value pairs then i would rather re-write the whole thing to create individual tables.

[Newsgroup_User]

cf_matt wrote:
> Hmm i hadnt thought about breaking each record into a wddx packet, it would
> make it hard on cf to display a list of results though as you would have to
> parse each packet for a value.

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.

> I am starting to think that if i'm going to do the whole thing with name and
> value pairs then i would rather re-write the whole thing to create individual
> tables.

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

[cf_matt]

I'm going to re-write it to create tables. I think the individual records as wddx packets could still be a problem. Mainly because forms can be edited after results have been collected which would mean the field list in each result could change. I can think of ways round this but It could still potentially be a lot of reworking.

Thanks Ian