temp table db size

[aspnetuser]

I am performing some action queries that grab SQL server 2000 and create temp tables in a local DB. Aprox 100,000 records. When I do this and append it into a live table then clean up the temp table my DB size is @ 100mb even after compacting... How can I reduce this, Is that the size for 100,000 records? All my tables are indexed...

 

[SimonDavis]

I guess it depends on how big the records are - that's only 1k per record, or 1000 characters or so. Doesn't sound unlikely to me.

 

[BNPMike]

The size of the table will be 100,000 times the size of the record plus an overhead for indexes - whether this is significant or not depends on how many indexes you have, how unique they are and how long each index value is.

If the record size or some data items are big, then you may get spilling over into new pages or large amounts of BLOB storage.

You need to provide details.

I have never seen Jet to be space-hungry.

 

[aspnetuser]

I have never seen Jet to be space-hungry." What does this mean?

Before I provide more detail?

 

[BNPMike]

When I have loaded large amounts of data eg 1 million rows, Jet (the name of the default database for Access - mdb format) takes up the amount of space you would expect eg 100,000 rows times 80 bytes per record equals not much more that 8mb.

You need to tell us the length of your record and how you have calculated it eg 4 integer fields (4 bytes), 5 text fields with data averaging 27 bytes (135 bytes) etc.

 

[JeremyNYC]

Just put the temp table in a temp database and then it won't matter. You can have a template database sitting around. Make a copy of it before copying the records over. Blow away the copy before closing the main database. And, just for good measure, make sure you've blown it away when you open the main database. Then the bloat won't matter, because it won't be happening in a database that does anything.

Jeremy

---
Jeremy Wallace
METRIX Project Coordinator
Fund for the City of New York