November 11, 2015 at 9:41 am
Ok, so right up front I realize the best way to avoid performance problems with a key/value pair table...is to NOT use a key/value pair table.
We are currently in the process of updating our old content delivery system including the database. I'm trying to avoid the pitfalls of my predecessors and ensure this DB is properly designed and thus increase performance. Some of the requirements have changed but some will be similar. In this vain I'm referring to aforementioned nasty table. In reality the table is very simple, it has 4 columns (ID, KEY, VALUE, DESCRIPTION). So I looked at the data in the existing table and discovered 137 unique keys with a wide range of frequency. Some keys are only used a handful of times while the most common key is associated with 89% of the ~2 Million unique ID's with everything else in between.
It doesn't make sense to create a 137 column table with tons of empty fields, which would likely be requiring new columns frequently just to normalize the data.
I would like to hear from anybody that has had to deal with this kind of mess and how they handled it. Any ideas on design or querying strategies that could make a significant difference to performance?
November 11, 2015 at 9:51 am
I'm not 100% sure of exactly what your situation is, but it looks like you should keep on using that key values pair table. Just make sure you have a proper link between the 2 tables and that you have an index on the key column(s).
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
November 11, 2015 at 9:54 am
You might not be able to get rid of that table completely, but with some analysis, you could create several tables once you identify common attributes to define entities correctly.
November 11, 2015 at 10:00 am
Quick suggestion, look into sparse columns for this one, found it to be one of the more efficient mechanisms for handling this.
😎
November 11, 2015 at 10:53 am
yb751 (11/11/2015)
It doesn't make sense to create a 137 column table with tons of empty fields
It would to me if you were OUTER JOIN'ing the KeyValue table 137 times in a single query/VIEW :hehe: but if you are JOIN'ing it once, e.g. to just display a list of "Associated Attributes", then I reckon you are fine as you are.
November 11, 2015 at 11:00 am
Kristen-173977 (11/11/2015)
yb751 (11/11/2015)
It doesn't make sense to create a 137 column table with tons of empty fieldsIt would to me if you were OUTER JOIN'ing the KeyValue table 137 times in a single query/VIEW :hehe: but if you are JOIN'ing it once, e.g. to just display a list of "Associated Attributes", then I reckon you are fine as you are.
Why would you join the table 137 times when you can join it a single time and use cross tabs to generate the columns?
November 11, 2015 at 11:07 am
Sorry, I was being facetious 🙂
November 11, 2015 at 11:13 am
Alvin Ramard (11/11/2015)
I'm not 100% sure of exactly what your situation is, but it looks like you should keep on using that key values pair table. Just make sure you have a proper link between the 2 tables and that you have an index on the key column(s).
Yeah, as I mentioned I don't think there is anyway around using that table. Also, the key column is indexed as it should be. I just wasn't sure if there was some clever implementation or strategy I wasn't aware of. If nothing else can be done so be it...just trying to be proactive.
Luis Cazares (11/11/2015)
You might not be able to get rid of that table completely, but with some analysis, you could create several tables once you identify common attributes to define entities correctly.
Hmmm...that's not a bad idea. The only problem is the application would still need to query the key/value table in order to get the lesser used attributes albeit the table should be significantly smaller.
Eirikur Eiriksson (11/11/2015)
Quick suggestion, look into sparse columns for this one, found it to be one of the more efficient mechanisms for handling this.😎
That would help me save space if I normalized the table. Not sure how it would help if the business decides to add new attributes every week or so. Oh boy that would be a wide table. lol
November 11, 2015 at 1:18 pm
Well I just finished talking with our Dev team and I realized that the attributes do not need to be updated individually. Also any searches would be offloaded by our search appliance. So I just suggested we just store the pairs as XML in one column. 😀 Any updates would always include all of the pairs and thus just replace the old XML string.
Better yet, they loved the idea too so I didn't have try hard to sell it. 😎
November 11, 2015 at 1:21 pm
yb751 (11/11/2015)
Well I just finished talking with our Dev team and I realized that the attributes do not need to be updated individually. Also any searches would be offloaded by our search appliance. So I just suggested we just store the pairs as XML in one column. 😀 Any updates would always include all of the pairs and thus just replace the old XML string.Better yet, they loved the idea too so I didn't have try hard to sell it. 😎
Developers love XML. If you propose to get rid of the database and keep all as XML files, they might buy the idea as well. Seriously, don't even mention the possibility. 😀
November 11, 2015 at 1:25 pm
Luis Cazares (11/11/2015)
yb751 (11/11/2015)
Well I just finished talking with our Dev team and I realized that the attributes do not need to be updated individually. Also any searches would be offloaded by our search appliance. So I just suggested we just store the pairs as XML in one column. 😀 Any updates would always include all of the pairs and thus just replace the old XML string.Better yet, they loved the idea too so I didn't have try hard to sell it. 😎
Developers love XML. If you propose to get rid of the database and keep all as XML files, they might buy the idea as well. Seriously, don't even mention the possibility. 😀
LOL...that's probably not far from the truth. Except they'd rather have it in JSON. 🙂
November 17, 2015 at 6:25 pm
yb751 (11/11/2015)
Well I just finished talking with our Dev team and I realized that the attributes do not need to be updated individually. Also any searches would be offloaded by our search appliance. So I just suggested we just store the pairs as XML in one column. 😀 Any updates would always include all of the pairs and thus just replace the old XML string.Better yet, they loved the idea too so I didn't have try hard to sell it. 😎
There ya go! That's a much more efficient way to waste space and still make it look like you know what you're doing. 😛 And, it's a wonderful double header because you'll probably need to use TYPE when reading the column to deentitize XML reserved characters which also makes the code run roughly twice as slow still. :w00t:
If you really want to go for the hat trick, add an XML index! Yeah... that's the ticket! :hehe:
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply