How can I obtain the size of the tables in a database?
Trevor Best
>I have a database which has 11 tables inside. Is there any utility that
>can let me display the size of each table (include data and index)?
>Please reply to me via e-mail.
>Thanks in advance.
It's difficult (nigh on impossible) to calculate as Access uses
variable length records, you could loop through the table in code, get
the length of each text field and add them to the known lengths of any
numeric field as they're always the same, i.e Interger=2, Long=4,
Single=4, Double=8, Currency=8 bytes.
That won't give you index sizes though, alternativly you could export
the table to variable length ASCII and look at the file size, but
again this doesn't give you the size of the indexes.
\|||/
/ \
C o o D
-----------------ooO--u--Ooo-------------------------------
It is better to have loved a short man than never to have
loved a tall.
Derek Kung
can let me display the size of each table (include data and index)?
Please reply to me via e-mail.
Thanks in advance.
Derek Kung
mailto:de...@hk.super.net
KIF
Maybe another way to do this is to make a copy of the *.mdb file;
check its size (using file manager, etc) then open the file;
delete the table; and check size again;
(might have to do some compacting)
...kif
Steven Drinovsky
I do not think this will work. Access increases the size of databases
in chunks. If you notice, a new db is 64k. you can add and add to
this db but it will still be 64k, then in one jump, it goes to 96k.
So, unless you want the size of a table within 32k you will have to
use another method.
Steven
--
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+ Steven Drinovsky sdrin...@wic.tdh.state.tx.us +
+ Texas Department of Health, home (512) 453-2317 +
+ WIC Automation work (512) 458-7111x3476 +
+ Linux=Real OS; Win95=A Joke Pager 800-624-7243 PIN#145-9112 +
+ My opinions are my own and by no means represent the state of Texas. +
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Copyright (C) 1995 by Steven Drinovsky.
Peter Miller
Ok, so I've only caught the last two posts on this thread, so if this is a
restatement of someone else's post, I'm sorry...
Other than OLE, memo and vairable length string fields, the answer is easy. You
know what the fields are, you know the length for each fieldtype and you know how
many records there are. Adding the field lengths and multiplying by the number
of records is your answer (plus minimal overhead).
OLE fields aren't the easiest to size, but memo and variable length strings can
be sized by using the len function (although you'd have to run through all
records).
But then maybe this was all covered somewhere else...
Peter Miller
PK Solutions
The Nephilim
PK...@gnn.com (Peter Miller) wrote:
>Other than OLE, memo and vairable length string fields, the answer is easy. You
>know what the fields are, you know the length for each fieldtype and you know how
>many records there are. Adding the field lengths and multiplying by the number
>of records is your answer (plus minimal overhead).
>Peter Miller
>PK Solutions
The only problem (!) is that Access saves records of variable length. If a 40 column field
empty, then it takes no space.
I'd like to see the answer to this too. I am a veteran Foxpro programmer, and I've gotten
used to the "clutter" of the individual databases (er, TABLES. Sorry) and programs. I've
begun to get used to the Access paradigm, but it still makes it difficult for me to get to
the kind of (low-level) information I like to check on (like table sizes). I miss being able
to use a command window to test things interactively and the "disp stru/stat" commands that
gave me detailed table information in an efficient and concise layout.
So far, IMHO, Access slows my development speed because I have to write a "module" for every-
thing I want to do. I cannot interactively test if I can manipulate certain commands in
undocumented ways. Anyway, I'm babbling. If anyone can difinitively tell us how to determine
table sizes (easilly), I'm interested in hearing it!
Dave
The Nephilim
Allen Browne <abr...@odyssey.apana.org.au> wrote:
>neph...@netaxis.com (The Nephilim) wrote:
>dBase/Fox use the *very* inefficient system of assigning a
>space for every character of a field. You may notice dBase
>files often compress 8:1 because of this inefficiency.
>
>Access uses variable length fields for storage, so you
>cannot calculate the table size by simply counting the
>bytes for each record. It depends how much data is actually
>in each record.
>
>The size of a fully expanded record (as loaded into the
>page for locking) can be calculated as:
Thanks.
>>I miss being able to use a command window to test things
>>interactively
>
>Haven't you discovered the Immediate Window? Open any
Yes, I had, but it cannot be used like the Fox command window.
(ie: you cannot assign variables or open tables interactively). I've
used it for some minor debugging, though.
>
>>and the "disp stru/stat" commands that gave me detailed
>>table information in an efficient and concise layout.
>
>This is very easy to simulate using DAO. E-mail me if you
>need an example.
Thanks. Been there, done that. I was just making the point
that it wasn't an already available feature, and even with
that, it isn't quite satisfactory. I just need(ed) to get
used to the differences. Thanks again for your reply and help.
I'll use the formuli you gave me to build some means of
(gu)estimating table sizes.
Dave
Allen Browne
>... but it still makes it difficult for me to get to the
>kind of (low-level) information I like to check on (like
>table sizes).
dBase/Fox use the *very* inefficient system of assigning a
space for every character of a field. You may notice dBase
files often compress 8:1 because of this inefficiency.
Access uses variable length fields for storage, so you
cannot calculate the table size by simply counting the
bytes for each record. It depends how much data is actually
in each record.
The size of a fully expanded record (as loaded into the
page for locking) can be calculated as:
- sum of field sizes (1 for Byte or Yes/No, 4 for Counter
or Long Integer, 8 for Double, Date/Time or Currency,
14 for Memo, 14 for OLE, 56 for text size 56, etc.);
- 7 bytes overhead for the record;
- 1 byte overhead per field;
- 1 extra byte overhead for every 256 bytes of variable
length fields (text, OLE, Memo).
>I miss being able to use a command window to test things
>interactively
Haven't you discovered the Immediate Window? Open any
module, and squash it down to a thin strip at the bottom
of your screen. Then when you click on it, the Immediate
Window pops up, and you can Ctrl+C, Ctrl+V between there
and an Event Procedure or a control's ControlSource etc.
>and the "disp stru/stat" commands that gave me detailed
>table information in an efficient and concise layout.
This is very easy to simulate using DAO. E-mail me if you
need an example.
,~,_/\
/ \
Allen Browne { }
Perth, WA, Australia => *_,~~\_/
http://odyssey.apana.org.au/~abrowne/ v
Trevor Best
>Yes, I had, but it cannot be used like the Fox command window.
>(ie: you cannot assign variables or open tables interactively). I've
>used it for some minor debugging, though.
You can assign local variables in the immediate while the code is
running and stopped in the debugger, and you can open forms & tables
from there using DoCmd, it's actually easier to just switch to the
database window and open objects from there.
\|||/
/ \
C o o D
-----------------ooO--u--Ooo-------------------------------
Charlie was a chemist, but Charlie is no more.
What Charlie thought was H2O was H2SO4.
tre...@microprism.com - Microprism (UK) Limited
Donald Ravey
neph...@netaxis.com (The Nephilim) wrote:
|I am a veteran Foxpro programmer, and I've gotten used to the
|"clutter" of the individual databases (er, TABLES. Sorry) and
|(low-level) information I like to check on (like table sizes).
|
[...]
Hi, Dave,
As an experienced Xbase programmer and teacher, as well as an
enthusiastic Access programmer (and soon-to-be teacher) and a novice
Delphi programmer, I can relate to your views; but I would like to give
you some advice: come to terms with the fact that there are more than
one way to interact with your development project, and some of the ways
are incompatible with other ways. Being very experienced with one way
can be a real impediment to learning another way! Some of us do,
indeed, find one way preferable to another way, but in the end, the
person who can adapt and take advantage of the newer paradigms without
agonizing over the differences is the person who will gain. Your last
sentence, above, begins with "So far, ..." I can guarantee you that if
you will allow yourself to adapt, without fighting it, you CAN improve
your development cycle by factors of 4 to 20 times!! Of course it won't
happen in a few weeks, probably not in a few months, but it WILL happen
if you let it. Is that worth setting aside those techniques that you've
become comfortable with and really trying to understand how to achieve
the same END RESULT in a new paradigm, using DIFFERENT approaches? If
you think it is, then you will be rewarded. If not, then I suggest you
shouldn't waste your time learning anything new.
|If anyone can difinitively tell us how to determine
|table sizes (easilly), I'm interested in hearing it!
I don't mean to be argumentive (and maybe you really DO have a
requirement, but I just have to ask), but WHY do you need to know the
table sizes? Of course, we always have to be able to ESTIMATE sizes for
storage planning, etc., but in my experience, the PRECISE size of one
table in a database isn't a significant number. In typical operations,
this is a dynamic value anyway, as data is added and modified. It may
'feel' uncomfortable, not being able to look at a FoxPro .DBF file and
see precisely how much storage it takes, but what does it really matter?
As I said, some particular applications might have special needs that I
can't quite visualize, but nothing that I've ever worked on required an
accurate measure of table size, as long as there was sufficient disk
storage, and that requires only a rough estimate, IMHO.
+======================================================================+
Don Ravey W6DBZ San Mateo, California dra...@aimnet.com
+----------------------------------------------------------------------+
"Life must be lived forwards, but can only be understood backwards."
- Soren Kierkegaard