September 17, 2004 at 11:53 am
Hi,
I have a table that has about 90 columns. These are all values that relate to different types of print finishing. Each print category may have multiple columns that describe the process, some may have six columns, some are just a single value.
However, often, when a finishing option is selected, there may only be a single column used and therefore a finishing record is created where 90% of the record space is "unused". So, I have grouped closely related finishing options and have come up with about 12 smaller tables that represent all of the columns in the original finishing table.
The client side process has to interogate every finishing column to see if there is data so a finishing "description" string can be built. So whether there is one largely empty 90 column record or 12 tables that mostly have no data, all the tables would still need to be queried in order to build the finshing string. Whilst the latter approach would definately save space, I just wonder how much overhead would be created in having to query 12 small tables as opposed to 1 big table? Many of the columns are varchar type so, the record length(s) could vary considerably.
Do you have any thoughts as to whether it would be as quick querying 12 small tables as opposed to a single table with a generally large, mostly empty record. One advantage to the set of smaller tables is that in the parent record, I could record which of the smaller tables hold data (say a bit column for each smaller table). So, on the client side, I could have logic that only queries the smaller table(s) that have a true value in their corresponding bit column in the parent record. So, each time the parent record changes, whilst there are potentially 12 child tables, I may only end up having to requery 1 or 2 of the child tables.
Is 80-90 columns big for SQL server or am I worrying over nothing? Whilst this design is not optimal, my dev tool (VFP) could easily handle a 90 column table in its own local data engine with no performance issues whatsoever.
This one is really confusing me - any suggestions would be *very* much appreciated.
Thanks.
September 18, 2004 at 11:26 am
Interesting one this. Do you have any indexes on the 90 column "big" table?
Personally I would have the 12 smaller tables, each with their own indexes.
As for dynamically building the query, why not just join to all the 12 tables anyway? Surely you will only get the results if they exist? This may help optimise the performance of the client application, as it will no longer have to work out where/what it needs to join.
Is 80-90 columns big for SQL server or am I worrying over nothing?
Not really, the question I would ask is: Does correct database design allow for so many columns?
Hope this is of some help?
September 18, 2004 at 11:44 am
Hi Journeyman
Thanks for replying.
>Do you have any indexes on the 90 column "big" table?
There is the PK and an FK that points back to the parent table.
>Personally I would have the 12 smaller tables, each with their own indexes.
I have more or less arrived at your conclusion.
>As for dynamically building the query
Just so you know, I am not creating "dynamic sql" in a sproc or on the server side. The dynamic sql would be created on the client side and executed via sql-passthru.
>why not just join to all the 12 tables
Because SQL server will still have to hunt for data that I may know ahead of time doesn't exist (if a created a record in one of the smaller tables, I would update its corresponding bit column in the parent record).
>This may help optimise the performance of the client application, as it will no longer have to work out where/what it needs to join.
As far as I can see, the client (visual foxpro) is waiting on SQL server to do its stuff so this is not too much of an issue.
>the question I would ask is: Does correct database design allow for so many columns?
You have hit it right on the nose. It just doesn't stack up creating a record in a table that will generally be 90+% empty.
>Hope this is of some help?
It sure is. You have confirmed my un-easiness about the table/design. In the absence of any replies to my "plea", I had decided to go for the smaller tables.
Thanks again for the heads up !
Regards.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply