Not enough temporary space. Large table!! 1

[krizma5683]

I have a database that imports daily records from our meter reading department. The system our meter reading data is loaded to is a mainframe system and the only way to extract data is through Crystal Reports & SAS. I'm using the extracted data to load into the database (approx. 42,000 records daily) and then through queries I'm performing additional calcs & lookups to generate monthly reports.

However, January - mid-April has already created 3 million records in my database and now none of my queries will run. They all time out saying that I don't have enough temporary space. I'm afraid this issue will only get worse throughout the year.

Can someone give me suggestions or point me to some discussion forums that may help me use these millions of records to still create my excel pivot table reports???

 

[Golom]

If you are still on Access 97 then upgrade. 97 has a 1GB limitation while 2K+ has 2GB. If you are already on 2K+ then you sound like a candidate for the industrial strength stuff like SQL Server or Oracle.

The other thing to look at is ... "Do you really need all that history for current reporting requirements?"

There may be some opportunities to archive historical data out of your active database and regain some room. I don't know exactly what you're doing so I can't say how practical that might be.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[gmmastros]

Golom's suggestions are good. Just to add a little...

What you are describing sounds like a perfect candidate for a data warehouse. Data warehouses are very similar to databases, and are, in fact, implemented through a database. Generally speaking, you want to normalize a production database to 3rd normal form (at least). This optimizes performance and storage requirements.

With a data warehouse, normalization rules go out the window. You typically have 1 table per 'report' that you wish to print. You stated that you load approximately 42,000 records daily, so that you can generate monthly reports. In a data warehouse environment, you would generate the summary data for each day, and store that in a table. Then, when you run your reports, you calculate values based on the daily summary information. This will likely reduce your database from a gigabyte or 2, down to a couple megabytes, and your reports will likely run much faster.

I hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[krizma5683]

Thanks to both of you.
Unfortunately I need to keep 12 to 16 months of data active for reports and can't use the archive option due to the nature of the data. I agree this should be in an Oracle type environment, but we don't have the resources to get there (it would take to long through our IT channels)

I'll investigate the data warehouse method some. Hopefully this will do the trick. My reports don't change very often so this should work.

Thanks for the speedy response.