*
The moose likes Performance and the fly likes Performance issue for Storing data in database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Java » Performance
Bookmark "Performance issue for Storing data in database" Watch "Performance issue for Storing data in database" New topic
Author

Performance issue for Storing data in database

Bob Mathews
Ranch Hand

Joined: Feb 26, 2006
Posts: 43
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
Author and all-around good cowpoke
Rancher

Joined: Mar 22, 2000
Posts: 12761
    
    5
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
author
Sheriff

Joined: Jul 11, 2001
Posts: 14112
One way would be to have a new table for the attributes, along the lines of



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?


The soul is dyed the color of its thoughts. Think only on those things that are in line with your principles and can bear the light of day. The content of your character is your choice. Day by day, what you do is who you become. Your integrity is your destiny - it is the light that guides your way. - Heraclitus
Bob Mathews
Ranch Hand

Joined: Feb 26, 2006
Posts: 43
Thanks for the responses
any other solution will be highly appricieted
Nishant
William Brogden
Author and all-around good cowpoke
Rancher

Joined: Mar 22, 2000
Posts: 12761
    
    5
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
Ranch Hand

Joined: Feb 26, 2006
Posts: 43
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
author
Sheriff

Joined: Jul 11, 2001
Posts: 14112
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
Ranch Hand

Joined: Oct 05, 2001
Posts: 1170

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
Author and all-around good cowpoke
Rancher

Joined: Mar 22, 2000
Posts: 12761
    
    5
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
Ranch Hand

Joined: Nov 27, 2005
Posts: 71
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



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



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


<a href="http://www.auptyma.com" target="_blank" rel="nofollow">The Peak of Performance</a>
Ilja Preuss
author
Sheriff

Joined: Jul 11, 2001
Posts: 14112
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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Performance issue for Storing data in database
 
Similar Threads
Unable to test property value in <s:if> tag
Hibernate performance issues using huge databases
UML Doubt
HttpSession
Persisting a serialized object to a database