Page 1 of 2

Submitting more than 8000 textfields

Posted: Wed Aug 27, 2008 2:56 pm
by emmbec
Hi everyone, I am having some problems with a small webpage I made. Let me explain first what it is about so that you get a better idea of what I'm doing.

There are supervisors in the company, each supervisor has about 20 employees at his/her command, and each supervisor must evaluate each of the employees that they have on different "Skills" that each employee has. In total, there are 205 skills to evaluate (In a scale of 0-5) for each employee. Now What I did was writte the 205 skills automatically generated from a database with a textfield so the supervisors can enter the evaluation for each employee, and also they must enter in another textfield the minimum evaluation that each employee should have.

So, in average we have a total of 8200 textfields inside a form.

When supervisors finally finish evaluating their employees, they click on a SAVE button and what I do is submit the form to another page and there I do the following:

- I get the list of skills from the database
- For each skill I get the list of employees that the supervisor has from the DB
- For each skill, I do a

Code: Select all

$_REQUEST['textfield_employee_Skill_ID']
to get the value that they typed in the textfield for each skill, the textfield name is composed of the ID of the skill and the employee number, to have 8200 unique textfield names.
- After getting the textfield value, I run a query to insert or update that value into the database, so in total there are 8200 queries to be executed to the DB (I know, it is a waste of memory...)

Now here is my problem, some times users are able to save their information correctly without any problems, but some times only Half of their evaluations is saved, and when that happens I get angry calls from them hahahha

I don't really know what may be the problem, do you think is the fact that I run a single query for each value from the form??

Any ideas on how to make my code more efficient or to avoid having loses of information when saving the information?

Thanks!

Re: Submitting more than 8000 textfields

Posted: Wed Aug 27, 2008 3:08 pm
by ghurtado
emmbec wrote: I don't really know what may be the problem...
The problem is right up there, in the title of your thread. The problem is that you have over 8000 form fields

Any time you have a form with that many fields, you are asking for trouble: in the front end, in the backend, and everywhere in between. Some browsers may not even be able to submit that much post data at once without using multipart. The issues you have ran into are only some of the problems you will face.

So if 8000 fields is the problem, it follows that the solution is to split them up. Make a page for each skill, so that the supervisor doesn't have to spend 3 hours filling out a form that isn't even guaranteed to save. I would be pretty upset too.
emmbec wrote: ... and when that happens I get angry calls from them hahahha
It does not make you seem very professional to laugh about your user's angry calls when you caused the problem in the first place.

Re: Submitting more than 8000 textfields

Posted: Wed Aug 27, 2008 3:15 pm
by onion2k
Having a huge number of fields isn't necessarily a problem, although doing thousands of queries to process the form definitely is a problem. That's very likely to be where it's falling down. You need to do something to minimise that. By the sounds of it the most obvious optimisation would be to have one form per employee rather than 20 on the same form. Second, if you're not doing it already, the 'current' value and the 'target' value should be stored in the same database record. Those two things will take it down to a couple of hundred queries each time you process the form. That's better, but still bad. Couldn't you put a number of these skills into the same record? It'd be better to have 200 columns in a table than 200 records if you're always going to be fetching and updating all the data at the same time.

Re: Submitting more than 8000 textfields

Posted: Wed Aug 27, 2008 3:38 pm
by emmbec
onion2k wrote:... Second, if you're not doing it already, the 'current' value and the 'target' value should be stored in the same database record.
Yes I am doing that.
onion2k wrote: ... Couldn't you put a number of these skills into the same record? It'd be better to have 200 columns in a table than 200 records
The thing is that skills change a lot, and each supervisor can customize the skills.

The thing is that supervisors want to see the page as if it were excel, they want to see all of the employees at once, and they also have an IMPORT option so they can import their evaluations from an excel spreadsheet, but sometimes they edit just one employee inside the form and that is when that edited employee doesn't get saved some times. This has happened to me only two times in a two months period which is why I didn't put that much attention to the problem.

What I was thinking is that I could process all of the evaluations with JavaScript and then just post a string with the SQL query sort of created to my other page, so that I don't waste too much DB memory executing the queries. I'll give it a try and let you know the results.

Thanks for giving me an answer that IS useful, I guess a competent developer is that who can find the answer to a problem and not that who just tries to insult someone else because they don't know the answer...

Re: Submitting more than 8000 textfields

Posted: Wed Aug 27, 2008 3:42 pm
by ghurtado
onion2k wrote:Having a huge number of fields isn't necessarily a problem...
For the guy who just spent 3 hours filling out the form only to have his computer shutoff / accidentally close his browser / session timed out / - insert anything that can happen in 3 hours time - that could be a very big problem. To have that many fields on a form in the front end is, even ignoring all other technical issues associated with it, a tremendous usability issue. There are lots of reasons why professionals try to keep forms on the web short and sweet, and when forms get too long they get broken down into multiple pages or wizards. None of these reasons are arbitrary.
onion2k wrote: It'd be better to have 200 columns in a table than 200 records if you're always going to be fetching and updating all the data at the same time.
Better how? Faster? By what benchmark? In MySQL? Assuming InnoDB with row level locking? or MyISAM with table level locking? Would you want to be the user waiting for a half an hour to save your page because someone else is saving at the same approximate time as you?

Maybe what he needs to do is to serialize all the form data into one single field into one single table and this masterpiece of how not to design a database driven web application will be finally complete.

Re: Submitting more than 8000 textfields

Posted: Wed Aug 27, 2008 4:02 pm
by ghurtado
emmbec wrote: Thanks for giving me an answer that IS useful, I guess a competent developer is that who can find the answer to a problem and not that who just tries to insult someone else because they don't know the answer...
I apologize for my comment: it was uncalled for, so I removed it. It most certainly had nothing to do with "knowing the answer", since this is a systems design issue and there is no "right answer". I guess it just made my blood boil when you made it seem as if it was funny to you that the way you designed your application was making some users upset. Or at least that's how I interpreted "hahaha". I also assumed that the idea for all the fields in one form was yours and that you didn't really care what your users thought.

On the other hand, I still believe that you have way too many fields on a single form. Competent or not, this is still my advice: reduce the number of fields and break it down into several pages.

Now I also know that your requirement was to make the page excel-like. In that case, you can do one of two things:

- ask your users if the reason they want it to be like excel like for printing purposes. If so, you can just make them a separate read-only report page where they see all the data at once.
- If the front end needs to remain the way it is, use AJAX on a field per field basis. It will save you more headaches than any other solution to your issue: minimal data transfer to the server, and minimal queries in the backend.

Best of luck, please do come back to tell us how you solved your issue

Re: Submitting more than 8000 textfields

Posted: Wed Aug 27, 2008 4:44 pm
by onion2k
emmbec wrote:sometimes they edit just one employee inside the form and that is when that edited employee doesn't get saved some times
Ahhh.. the solution is obvious then. When the form arrives at the server with the 8000+ fields fetch the employee's current information from the database, compare it with what's been entered into the form, and only update/insert the fields that have changed. If they're only changing a couple of things it'll be really quick.

Of course, the problem would still exist so if they decide to edit all 8000 fields it'll still fail, but hopefully noone will notice. :)

Another option would be to submit each field when it's edited using a javascript onChange event and some AJAX to send the value to the server. That'd be really nice actually. It'd be like saving every change as it happens. Of course, I've never tried to put 8000 event listeners into a single page.. it might be a bit slow. It's something to try though.

Re: Submitting more than 8000 textfields

Posted: Wed Aug 27, 2008 4:48 pm
by onion2k
ghurtado wrote:
onion2k wrote: It'd be better to have 200 columns in a table than 200 records if you're always going to be fetching and updating all the data at the same time.
Better how? Faster? By what benchmark? In MySQL? Assuming InnoDB with row level locking? or MyISAM with table level locking? Would you want to be the user waiting for a half an hour to save your page because someone else is saving at the same approximate time as you?
Better because it'd be normalised and therefore a neater, more sensible design.

The supervisors can customise the skill fields though, so it's not a possible solution.

Re: Submitting more than 8000 textfields

Posted: Wed Aug 27, 2008 5:51 pm
by ghurtado
onion2k wrote:It'd be better to have 200 columns in a table than 200 records if you're always going to be fetching and updating all the data at the same time.
onion2k wrote: Better because it'd be normalised and therefore a neater, more sensible design.
No, actually, you got it backwards. That would be exactly the opposite of normalizing. In an *extremely* simplified way, when you normalize you tend to end up with less columns per table. Which was exactly my point: how can denormalizing the database be a good idea if you aren't even sure you will gain a performance improvement? Denormalizing the database (which is exactly what turning 200 records into 200 columns is) is only a good idea in extreme cases and only when you can guarantee that the performance improvements will outweigh the mess that you will end up with.

To be crystal clear about this:

Turning 200 columns into 200 records: normalizing a database (good)
Turning 200 records into 200 columns: denormalizing a database (bad)

Re: Submitting more than 8000 textfields

Posted: Thu Aug 28, 2008 2:57 am
by Stryks
ghurtado wrote: Turning 200 columns into 200 records: normalizing a database (good)
Turning 200 records into 200 columns: denormalizing a database (bad)
I'd really have to disagree with this simplification. Normalization (to my mind anyhow) is to store the information you have with as little repetition as possible. So instead of saving user details with every user record, you move all the details into another table and give it an ID, which is then carried into the user records in all that redundant data's place. User data stored once and referred to many times.

By your logic, taking all data about a specific data entity and breaking that information into a bunch of 1 to 1 linked tables would be 'normalized'. There is nothing wrong with a large number of columns where that data uniquely describes an entity without redundancy. 1 row containing 1 entity IS normalized.

In this instance, of course, I would tend to agree that storing the data in columns instead of rows is more suitable, but primarily because of the dynamic nature of the items more than anything else. A fact already acknowledged by onion2k.

And I don't think anyone is arguing that that number of fields on a single form is not the best practice, but at the end of the day, you code to the clients requirements. All you can do is inform them of the reasons why it shouldn't be done and then leave the decision with them. There's not much point arguing the point with them ... or here with the original poster.

Let's get back on thread and try and be productive instead of argumentative. All we can do is present our case and let the OP decide the best course of action.

Cheers

Re: Submitting more than 8000 textfields

Posted: Thu Aug 28, 2008 3:43 am
by onion2k
ghurtado wrote:No, actually, you got it backwards. That would be exactly the opposite of normalizing. In an *extremely* simplified way, when you normalize you tend to end up with less columns per table. Which was exactly my point: how can denormalizing the database be a good idea if you aren't even sure you will gain a performance improvement? Denormalizing the database (which is exactly what turning 200 records into 200 columns is) is only a good idea in extreme cases and only when you can guarantee that the performance improvements will outweigh the mess that you will end up with.

To be crystal clear about this:

Turning 200 columns into 200 records: normalizing a database (good)
Turning 200 records into 200 columns: denormalizing a database (bad)
Nope. If you have 200 bits of information about 1 item it's better to store them as columns in a single table. Are you seriously suggesting that, for example, in a User table it's better to store the username in one row, the password in another row, the name in another, the date of birth in another, and so on? That's appalling an idea. If the data has a 1 to 1 relationship with what it represents it should all go on the same row in different columns. That is normalisation.

It's only better to store them as rows if the structure is flexible, eg people can add or change what each item is meant to represent. As it happens that's the case here but we only discovered that after my recommendation.

Re: Submitting more than 8000 textfields

Posted: Thu Aug 28, 2008 8:19 am
by ghurtado
onion2k wrote:It'd be better to have 200 columns in a table than 200 records if you're always going to be fetching and updating all the data at the same time.
This is what you said in your first post: you think that normalized data which is currently stored as 200 separate rows should be denormalized to a 200 column schema instead, in the name of performance. The reason you give is "fetching and updating all the data at the same time". This can be easily verified as bad advice, plan and simple. In the world of IT, this is called "premature optimization" and it is the "root of all evil" for a very good reason.

In your last post you are still sticking by your "turn the 200 records into 200 columns advice" mostly all throughout, then at the last minute you offer this:
onion2k wrote: It's only better to store them as rows if the structure is flexible, eg people can add or change what each item is meant to represent. As it happens that's the case here but we only discovered that after my recommendation.
So are you saying that all of a sudden you changed your mind and what you offered as good advice earlier has turned into bad advice now? What made you change your mind?
onion2k wrote:but we only discovered that after my recommendation
I think by "we" you must mean "you", because to anyone who read the original post with any level of attention it became obvious that his schema was already normalized. Certainly that must be the case for his page to require 8200 inserts and for you to propose reducing those inserts by way of sacrificing his (currently normalized) schema. So at some level you must have realized what was the original state of affairs before you proposed that change.

Pay special attention to where the OP mentions "the ID of the skill": that means there is a "skills" table, which means there probably is also a "skill_ratings" table, which is the one that gets 8200 inserts as of right now. That one is the table you are proposing to add 200 columns to (just so you understand the implications of your advice), that's the one that would have one new column for every skill in your proposed solution. Ask anyone with any serious experience with database design and they will tell you that's called "denormalizing a schema".

It sounds to me you are beginning to see the problems with your proposal, but you cant quite find a way to say "I think I maybe have been wrong". Fine by me, but at least make sure that the OP understands that your original advice was not sound advice, for his own sake.

You don't need to make up contrived examples about users and passwords since we have a perfectly good example to refer to: the original post. That was the context of your advice.
Stryks wrote:I'd really have to disagree with this simplification...
I was counting on your intelligence to realize that this simplification is only valid in the context of the original problem, but it seems to me you haven't taken the time to read or understand said original problem, given that you make no mention of it in your post. You seem pretty confident with your "of course" statements and calling your opinion a "fact already acknowledged", so confident indeed that you should have no trouble explaining to everyone reading this thread why in the context of the original problem and schema it is "better" to turn what currently is stored as rows into more columns on the same table.

Will you please explain that for the benefit of both the OP, myself, and anyone else out there who hasn't yet seen why this could ever be a good idea? They say if you can't explain something clearly and in simple terms it means you don't really understand it. Let your clarity shine through.
Stryks wrote:Let's get back on thread and try and be productive instead of argumentative.
I agree. Start with yourself by reading the original post and proposing some solution to it, rather than stating "facts already acknolwedged".

Re: Submitting more than 8000 textfields

Posted: Thu Aug 28, 2008 8:34 am
by Stryks
Actually ... I have added what I came here to add.

Simply: That you have have stated your advice and it is up to the original poster to draw conclusions.

As for your childish attempt to draw me into an argument ... really ... grow up. If you want compare brain pans you're really at the wrong place. This is a place for *friendly* discussion .... an open exchange. Not a place for a rant from someone with an apparent case of small-man syndrome.

If YOU read back over the original post, it wasn't clearly stated that the 'skills' were entirely dynamic. The suggestion put forth by onion2k was based on a 'static skills' set. When it was clarified that this was not the case, the suggestion was withdrawn.

As for me, I never implies that your advice was wrong. I in fact agreed with you. I did however state that your two line oversimplification of 'what normalization is' is incorrect. And I stand by that.

Either way, this childishness does NOT help the OP at all. You have for some reason just decided to get all upset that someone might dare offer a counter-opinion to your own and have launched this idiotic tirade. The advice has been given, grow up and move on.

Cheers.

Re: Submitting more than 8000 textfields

Posted: Thu Aug 28, 2008 9:08 am
by ghurtado
Stryks wrote: ... your childish attempt to draw me into an argument ... really ... grow up.
Stryks wrote:... Not a place for a rant from someone with an apparent case of small-man syndrome.
Stryks wrote:... childishness does NOT help the OP at all.
Stryks wrote: ... and have launched this idiotic tirade. The advice has been given, grow up and move on.
Let's see: all you have done in your post is call me childish (twice), a small-man, an idiot and told me to grow up (twice). Any more insults you would like to add, or is your ego satisfied now?

Re: Submitting more than 8000 textfields

Posted: Thu Aug 28, 2008 9:15 am
by Stryks
Listen, I'm not the one with the ego. I'm attempting to draw your attention to your behavior in the hopes that you might chill out a little.

I'm not at all responsible for how you portray yourself.

Can we move on now.