August 13, 2002 at 7:10 am
HI ALL
I use about 20 combobox that their record sources follows the same exact structure (ID int Identify (1, 1), ItemDescription nvarchar(30)). Should I union all of them? Will it improve performance? I think it might make it easer for me on the application development…
Thanks!!!
Thanks!!!
August 13, 2002 at 9:26 am
I think one table makes sense. Or at least a view.
Andy
August 13, 2002 at 1:09 pm
I'd do one table as
create table(
id identity
, boxname varchar
, boxvalue varchar)
or with two tables, a lookup the the boxname and one for the values, FK to the boxname table.
Steve Jones
August 20, 2002 at 5:05 pm
Depending on the size of each table it may or may not improve performance. It should not hurt performance if indexed properly. However some may say this doesn't follow the normalization rules but that does not always need to be done and can offer bennifits when not done. Now you have also a single value for items in the combo boxes so if a column has a value you know eactly which combo box it came from so you also gain bennifit there.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
August 20, 2002 at 5:12 pm
We actually had an optimization project for an application here that dealt with a similar situation. We maintain most of our 'code' tables in a separate database. The tech lead decided to use a dts to load a single table similar to the one Steve suggests from all the sources into one table called on form load in the application. The combo boxes are then filled with one db call instead of multiple calls to the db. Another setting to be sure and use is set nocount on. This keeps the client from being updated with a number of rows processed per sql statement.
This works well with the .net forms as well.
Jody
August 21, 2002 at 6:24 am
Not sure I agree about the normalization part. The alternative is to keep adding tables every time you need a new list. I've got one app that uses 40 distinct lists, do I really want 40 tables?
Andy
August 21, 2002 at 7:32 am
I would opt for a single table, if its just some configuration or description stuff. E.g. filling a combobox for filtering the resultset.
But when you will store the value of the combobox back in a record (e.g. the users credit card type), I would opt for a table for each combo.
In the latter case, referential integrity is much clearer defined.
August 21, 2002 at 8:06 am
Totally agree. Separating picklist from resulting pick is only way to go.
Andy
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply