Originally posted by ken39
Yes, I always did a compact after daily backup. I dont think normalization is a good idea in my case. Thanks for reminding me anyway!
I'm curious why not. It might be excessive to perfectly normalise the data but if you have any 'name' fields in there, pulling them out to a lookup table is going to help a whole lot.
As I imported the data through ODBC (Link table then use query to back it up), all column size are specific. Thanks for asking!
I have worked with databases where the data is imported by ODBC and it may well be that the field sizes are excessive, if that is the case in the source databases - (don't assume the other developer was perfect!)
have a look at the max(len(x)) values for the text fields and see if there's anything worth hitting on?
Seriously I spent a lot of time working with a 60k+ trade records per day database (imported via ODBC, in part fwiw) and pulling out the 'static' data (In my case 'Customer Name', 'Cust Country' etc and 'Stock Name' made a huge difference to the history database sizes and vastly speeded up historic querying.
Mind you, tidying up 5 years is going to be pretty painfull, but there will be benefits in the end.