Greetings,

any possibilites in ASA 9 and SA 11 finding out the exact size of a row in
e.g. bytes?

Thanks in advance,
Pavel

For an individual row, one might consider summing up the data_length( )'s of
all the individual column values. That would be quite close but would miss
some overhead details (like the bit overhead for null-valued columns, any
Prefix contribution, and other such data structure overhead per row or
per page).

The dbinifo type information available from sa_table_page_usage( )
as well as the newer sa_table_stats( ) system stored procedures
may provide the information you required for the entire table;
according the state of the table at the time of the runs, that is.

Of course 'exact size' begs a more exact definition as to what
one one wants to be included. Index overhead is another factor
as might be other things.

"Pavel Karady" <firstname.lastname (AT) nospam_kogerusa (DOT) com> wrote

Do you want the logical row size or the physical row size?

As Nick suggests the sum of the datalengths would give you the latter.
I wish there was an easier way, but... I've never had much use for it
in the first place.

The physical row size is more interesting IMO, but also virtually
impossible to calculate for an individual row, as Nick implies.

Here's how Foxhound interprets "row size": the number of pages divided
by the number of rows... that gives the actual disk storage usage for
the average row, and *that's* a number which is useful for calculating
storage requirments. It includes table pages, blob arena (extension)
pages and index pages. If the number of pages is really small,
Foxhound doesn't give a row size since overhead may grossly skew the
number (1 ten-byte row in one 16K page would give "16K row size").

Tell us what YOU want, maybe there's a product suggestion lurking
there.

Breck


On 12 Jan 2010 12:51:11 -0800, "Pavel Karady"
<firstname.lastname (AT) nospam_kogerusa (DOT) com> wrote:

--
Breck Carter - Blog: http://sqlanywhere.blogspot.com/

SQLA questions and answers: http://sqla.stackexchange.com

RisingRoad helps SQL Anywhere developers make better databases
http://www.risingroad.com/
Breck.Carter at gmail

Breck,

just to clarify...

Regardss
Volker



Breck Carter [TeamSybase] wrote:
Or "If the number of rows is really small,"?

(Would make more sense IMHO).

On 18 Jan 2010 04:57:12 -0800, Volker Barth
<No_VBarth (AT) Spam_GLOBAL-FINANZ (DOT) de> wrote:

You be the judge...

Here is what the Foxhound Help says: "The bytes per row amount is
based on the disk space used for table data, extension and index
pages. It doesn't include free pages in the database, but it does
count free space in pages allocated to this table. As such, this
number is usually larger, sometimes quite a bit larger, than the
average number of bytes a query might return. To avoid showing
inflated numbers for very small tables, the bytes per row is only
shown for tables with at least 2 data or 2 extension pages. "

Foxhound will show bytes per row for a table with one row that is
100,000 bytes in length, but not for a table with 100 rows that are 1
byte each, since the latter will take up one page for data and
(probably) one page for index, and 8K / 100 gives you 82 bytes per row
which is useless for predicting future disk space requirements.

Referring back to the Help excerpt above, there is "sometimes quite a
bit larger" and then there's "ridiculous"... anyway, that's the
philosophy behind Foxhound, tell you things you probably need to know,
if you want other information there's always dbisql and SELECT <g>.

(Don't you just hate that, when software gives you the answer some
stinking programmer thinks you SHOULD have asked, instead of the
question you are really asking?

Breck Is Clearly NOT A Salesperson

PS the part of Foxhound that shows bytes per row for all your tables
will always be free, since that part of Foxhound will continue to work
after the evaluation period is over. Just the monitor part will stop
working (and by implication, the alerts function).

PPS Foxhound also has a cool list, ranks "biggest tables" using both
row count and space usage... shows you all your Tables From Hell at a
glance. Foxhound is the consultant's friend



--
Breck Carter - Blog: http://sqlanywhere.blogspot.com/

SQLA questions and answers: http://sqla.stackexchange.com

RisingRoad helps SQL Anywhere developers make better databases
http://www.risingroad.com/
Breck.Carter at gmail

Breck Carter [TeamSybase] wrote:
Breck,

well, obviously you put a lot more thought and common sense on this
topic than I did - so I plead for acquintal


I hope my use of these legal terms is correct as

Volker Is NEITHER A Salesperson NOR A Laywer

Not thought... experience... Foxhound originally did what you said,
and the results were, well, less than helpful in the case of tables
with very few pages (as in one or two pages). In some databases, there
are HUGE numbers of tiny tables like that, and the row-size list was
cluttered with, well, stuff.

Breck Sometimes Learns After Making The Same Mistake Again And Again


On 19 Jan 2010 04:06:56 -0800, Volker Barth
<No_VBarth (AT) Spam_GLOBAL-FINANZ (DOT) de> wrote:

--
Breck Carter - Blog: http://sqlanywhere.blogspot.com/

SQLA questions and answers: http://sqla.stackexchange.com

RisingRoad helps SQL Anywhere developers make better databases
http://www.risingroad.com/
Breck.Carter at gmail