This table isn't normal(ized)

[disruptivehair]

I'm new to DB design, and the guy who actually designed the DB doesn't know much about it either.

I am attaching a sanitized copy of 100 rows from our main data table, which currently contains about 530,000 rows. Our app is getting slower and slower the larger this table gets and I know it's probably badly designed but I figured I'd get to it later. Now I'm looking at it and I'm not even sure where to begin, since basically none of the data seems to be dependent on other parts of data on the same table.

It's not actually a zip file, but I can't zip files on this PC so I just stuck .zip on the end. It's an Excel file.

Any hints or advice=welcome.

[si_the_geek]

Under what sort of circumstances does it get slow? (I'm presuming queries.. but what is the kind of queries are used?)


I can see lots of repeated data (in Team/Section/Plcy_Type_Cd/...), so it would be better in terms of size (and quite possibly speed) to put the unique data from those columns into separate tables (with an ID field), and using the ID instead of the text each time.

[disruptivehair]

Under what sort of circumstances does it get slow? (I'm presuming queries.. but what is the kind of queries are used?)

Any kind really, but we've got some that take several minutes to run.

I can see lots of repeated data (in Team/Section/Plcy_Type_Cd/...), so it would be better in terms of size (and quite possibly speed) to put the unique data from those columns into separate tables (with an ID field), and using the ID instead of the text each time.

How would I break those up?

The ones I'm breaking out right now are the date pair columns, i.e. process_started/process_ended, at the advice of my supervisor.

[szlamany]

Show us some queries that you are running that take 2 minutes - that will help us understand what the root cause is and offer more clear advice.

btw - is this MS SQL Server?

[si_the_geek]

edit: make sure you check szlamany's post first - he's a bit of a pro at this sort of thing!


So are you just moving those columns (and a copy of the Pol_no column?) to another table? If so, that wont help much - in fact it will probably just slow things down, as all you are doing is adding an extra join (and no benefit).


Do you have any queries that have something like "...WHERE Team = 'Team11' ..."? If that is the case (and there is lots of duplication) then a separate table for those columns would improve things.

The table for Team would contain data like this:

TeamID   Team
1        Team1
2        Team2
3        Team7
4        Team11
...

(basically the unique values from the main table, and an Autonumber/Identity field for the ID).

The main table would then store just these ID's instead of the text (so 3 instead of "Team7"), enabling queries to be faster even with the extra join - as they will mainly be comparing numbers instead of text.

The query could then change to something like this: "...WHERE TeamID = (SELECT TeamID FROM Teams WHERE Team = 'Team11') ..."

Doing this does mean tho that you will need to use a few joins in your queries to return the full data (assuming you do the same thing to a few of the columns). I would suspect that it would still be faster tho.


I don't know how much work it would be for you to change the app (or even if you will be allowed the time!), or even if there will be obvious gains from doing this. What I would recommend is 'copying' the main table to start with, and make these changes to the copy - you can then run similar queries on each to see what sort of difference it makes.