Performance issue for Storing data in database

[Bob Mathews]

Hi all ranchers
I have an issue
Our current database scheman is like this
ID NAME Attribute
1 abc colur=red;visibility=false;bold=true; ......(and so on. all the attribute as semi colon seperated)

Parameters in the attribute column are configurable ie say for example visibility=false might not show up in some case and say some other attribute (Eg ;nesting=true may show up
But now the size of the attribute list is growing like anything and this approch need to be changed.

Can you please suggest some beter ways to store data in the table because this way even if one attribute changes i have to stilll store the complete string. And it shows something in this way ;;;;;;;;;;color=red;;;;;;; .....

And this is giving performance issues

Please do reply

Thanks in advance

[William Brogden]

But now the size of the attribute list is growing like anything and this approch need to be changed.

Exactly why does it need to be changed? Are you running into some database field size limit or what?

Bill

[Ilja Preuss]

One way would be to have a new table for the attributes, along the lines of

ID NAME
1  abc
 
ID NAME-ID ATTRIBUTE  VALUE
1  1       colour      red
2  1       visibility false
3  1       bold       true

Not sure whether that would be *faster*, but certainly easier to maintain...

What makes you think that the attributes are responsible for your performance problem?

[Bob Mathews]

Thanks for the responses
any other solution will be highly appricieted
Nishant

[William Brogden]

Your reply conspicuously does NOT contain an answer to our questions.

Why do you think this is the cause of your performance issue?

Fixating on the wrong issue instead of backing off and looking at all possibilities is guaranteed to waste time and effort.

Bill

[Bob Mathews]

Sorry Bill
Issue is
Suppose if i m updating only one value from the whole chunk of attributes
color=red;visibility=true;---- lets say i just changes color from red to blue.Then also i have to iterate through the whole lot of semi colon separated string and also think that just to change one attribute or if i addjust one for taht matter i ll be unneccesary be saving something of this sort color=red;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;; and so on
We actually are looking to normalize data to further one or two more levels.
One is what one gentleman has suggested and further can be break down to 2nd NF.
Apart from that is there any other way to store the data smartly or can it be taken to 3nf

Thanks in advance

[Ilja Preuss]

I'd bet some amount that the String processing you seem to be worrying about takes an amount of time that is virtually unnoticable. Especially when compared to the costs of accessing the database.

[Mr. C Lamont Gilbert]

If they are all in the same table, then the difference to the database is probably negligible. I used to isolate individual updates like that in my program, but I decided it was best to update the whole object in one fell swoop.


I don't see a problem with what you are doing now, performancewise. It does have some maintainability issues though.

[William Brogden]

I'd bet some amount that the String processing you seem to be worrying about takes an amount of time that is virtually unnoticable. Especially when compared to the costs of accessing the database.

I want in on that bet too - the overhead of a database call is substantial, especially if the database is on another machine.

You most emphatically don't have to iterate through the string to change a value when you can locate the "visibility=" substring in one simple call. Surely there is no need for place-holding semicolons when you can search for the attribute name.

Bill

[Virag Saksena]

When designing for performance, you need to consider not only the insert/update requirement but also the query requirements

What people often forget is that 99% of the time you'll be querying data, and less than 1% of the time you'll be querying the data.

The approach outlined by Ilja is a good normalized design which eliminates repeated groups and will work with any number of parameters.

However very often you'd want to run a query which says give me all widgets with colour = red, and visibility = true and nested = false

Now if you have a fully normalized schema, you'll have to use correlated subqueries (with EXISTS clause) or IN clauses and either way query optimization can be a challenge. If you have a limited number of attributes you can put them as columns in the same table so you'll end up with

ID, NAME, COLOUR, VISIBILITY, BOLD, ....

If you don't know what additional attributes you might have, create holder attributes ATTRIBUTE1, ATTRIBUTE2... (common practice in packaged apps where user defined attributes are needed), and create/update view which can map the attributes.

Now you can run a simple query

select ..
  from widgets
 where colour = red
  and visibility = normal
  and nested = false

With the right indexes on your common attribute choices, you can have fast queries.

[Ilja Preuss]

Originally posted by Virag Saksena:
However very often you'd want to run a query which says give me all widgets with colour = red, and visibility = true and nested = false

Quite possible, in which case your concern is valid.

There is no single best database design - it all depends on what you want to do with it. For some applications, flexible queries with good performance will be more important, for others a more flexible schema that allows the easy definition of new attributes.

If you don't know what additional attributes you might have, create holder attributes ATTRIBUTE1, ATTRIBUTE2... (common practice in packaged apps where user defined attributes are needed), and create/update view which can map the attributes.

Or learn how to apply database refactorings: http://www.agiledata.org/essays/databaseRefactoring.html