Dynamically adding columns

Question

Dynamically adding columns

krishna.kumar

Right there with Babe

Points: 775
More actions
July 24, 2009 at 12:44 am

#212486

Hi,
I have a peculiar requirement. We have a table that has around 20 fields specifying the characteristics of a product. Our client has come up with a requirement that the number of columns will be increased up to 100 columns. The client will be giving us a file to import and we will be importing the data to the table. If a new column is found in the file, then we need to add the column to the table and then import the data. There is another catch here. We have a search option, where a keyword would be entered. It can be any of the 100 characteristics of the product. So we need to search through all the 100 columns to find the text and then the field name. I feel this would affect the performance. Say if i have 100 records, so a search will happen in hundred columns in 100 rows which is equivalent to 100 * 100. I have my indexes all in place but still not happy in this model.
What is the ideal way of building dynamic columns. Any help would be greatly appreciated.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150467 More actions · Answer 1

You might consider storing the characteristics in a separate table. This separate table would have a key and a value for each characteristic, linked back to the main table by a foreign key relationship. This approach also lends itself well to full-text indexing.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 2

I tend to agree with Paul. Adding columns to support different characteristics might be breaking a normal form and repeating data for no reason. It is also harder to search across columns.

If you provide more information about what you mean about characteristics and some examples, perhaps we can help you model this better.

krishna.kumar Right there with Babe Points: 775 More actions · Answer 3

Hi Steve and Paul,

Thanks for your prompt reply and sorry for replying back late.

The table that i have stores the details of automobiles. There are lots of qualifiers / details that needs to stored. Currently we have around 15 fields like make, model, color, year, etc. The extra fields that will get added will be cab size, bed size, fuel capacity, mileage, seat color, etc. Just giving you few names out of the whole lot. Our client says that the number of qualifiers might exceed to 100 to 130 in total.

The part where i feel the challenge lies is the adding of columns and also during the search section.

In the search section, the user might only type Mercedes Benz and the query should first parse through all the coulmns and find the Mercedes Benz string and then its coulmn name, which i feel will slow down the performance very much. I also dont feel that this is the right design.

Your suggestions would help me

regards

krishna

Paul White SSC Guru Points: 150467 More actions · Answer 4

Having read your reply, I'm not sure you read mine...?

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 5

I agree. It doesn't appear that you understand how SQL Server is optimized.

coding for:

ID Make Model Color

--- ------ ------- -----

1 MB 450 Black

2 Chevy 2500 White

and writing code to search those columns is not slower than.

ID Criteria Value

--- -------- -------

1 Make MB

2 Make Chevy

2 Model 2500

....

The code to search multiple values from the 2nd can be as quick, or quicker, than the first. And it's cleaner.

While there might be certain times that you add columns, or add other tables for lookup, name/value pairs can be very efficient in terms of storage and query speed. SQL Server's power is often in searching through lots of rows for matches.

krishna.kumar Right there with Babe Points: 775 More actions · Answer 6

Hi Paul & Steve,

Thanks again for your replies.

I have my table already designed as you have mentioned and I am following second example model that steve has mentioned.

I wanted to check if i am following the right model and a normalized one.

There is just one catch here. If the characteristics increase, then for every record, there will be as many inserts as the characteristics. Will this effect the performance ? Although most of the Insertions are dfone from an XLS file (import) and the insertions are not very frequent.

Thank you once again for your help and for wonderful explanation.

Paul --> hope you are not irritated by my previous reply. I just wnated to give an example so that you can help me understand better.