Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Zath
Constraint Violating Yak Guru
298 Posts |
 Posted - 2014-09-13 : 13:49:24
|
| I inherited a database (don't we all just love that) and a table has grown out of control.The table is over 50 GB.I did a defrag on it as well as re-indexing but that did not help.The problem is that the table has 236 columns and about 23000 rows.And no, that is not a typo!The only solution I can see is to break up the table.The .Net app is on our intranet and I have optimized every piece of code it contains as well as the stored procedures.The table contains information such as TempHigh, TempLow, and TempMed. The High, Low, and Med repeat throughout the table for other factors. So each High, Low, and Med will become its own table with a foreign key pointing to the parent table.This will create a lot of JOINs when accessing the data and updating.This is the only way I can see that may fix the problem.My question is, am I overlooking a better way to fix this problem?Any and all suggestions are welcome.Thanks!!! |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2014-09-14 : 02:20:13
|
| Hi Zath, 23000 rows is not very much. Is the response slow?A few things :1)If by changing the table design , what is the impact on the application? What changes will need to be made to the app?2)The solution you are proposing is more relational , the access paths will change and require different sql statements. You'll need to design an indexing strategy 3)I'm unclear about you mean by "each High, Low, and Med will become its own table" ? could you explain.Jack Vamvas--------------------http://www.sqlserver-dba.com |
 |
|
|
Zath
Constraint Violating Yak Guru
298 Posts |
Posted - 2014-09-15 : 08:16:55
|
| The changes that will need to be made are to a select sql and an update sql. No changes to the app itself will be needed.Each High, Med, and Low are just columns such as TempHigh, TempMed, and TempLow. There are other columns such as EndHigh, EndMed, and EndLow. Many columns that are in a structure like <someVar>High. These are in groups of three and the table needs to be split apart due to the fact there are 236 columns and it is finally effecting performance.Other columns I was able to change some time ago with a foreign key such as a Reason column. Most information repeated so I created a separate table and replaced that column with ReasonID. There is no constraint on these new column ID's as some are NULL but I can add a NULL entry to the Reason table then add a constraint for indexes.Basically, would breaking up this large column table into smaller tables help the performance? |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2014-09-16 : 01:44:15
|
| Do you have diferent types of queries hitting this table?or is it just the one query?it might help performance as 1)Breaking the table up into smaller tables with Foreign Keys , will allow you to eradicate repeat rows.2)Exploit set theory via more efficient queriesFocus on a proper and consistent model, following a set of rules that will allow you to progress from conceptual to physical.Read more on : http://www.sqlserver-dba.com/2011/05/iso-11179-naming-conventions-and-sql-ddl.htmlJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
|
|
|
|
|
Extra Large Table Effects Performance