Forum

General discussion on using the Kognitio Analytical Platform.
Contributor
Offline
User avatar
Posts: 14
Joined: Wed Feb 11, 2015 3:57 pm

view image size with varchar datatype

by jagan.kumar » Mon Jan 22, 2018 8:52 am

hi,

This is just to confirm the approach for estimating size of a view image

View has 3 columns :
id1 int not null
name1 varchar(20) not null ---with avg length 10
address1 varchar(100) not null --with avg length 50

Which of the below method is correct for calculating size for one row please?

a) 4 + (10 + 8) + (50 + 8) + 8 (header offset) = 88 bytes, where (Average field length* + 8) rule is applied for each varchar
b) 4 + 10 + 50 + 8 (header offset) = 72 bytes

thanks,
kumar
Reply with quote Top
Contributor
Offline
User avatar
Posts: 384
Joined: Thu May 23, 2013 4:48 pm

Re: view image size with varchar datatype

by markc » Mon Jan 22, 2018 10:59 am

The easiest way to check something like this is to do a quick test:

>set schema test;
Query 3 Complete ---- 0:00.0 0:00.0 0:00.0
>create table vctest(id1 int not null, name1 varchar(20) not null, address1 varchar(100) not null);
Query 4 Complete ---- 0:00.0 0:00.0 0:00.0
>insert into vctest select 1, '1234567890', '12345678901234567890123456789012345678901234567890' from values between 1 and 1000000;
Query 5 1000000 Rows Inserted ---- 0:01.5 0:01.5 0:01.5
>create view vctestv as select * from vctest;
Query 6 Complete ---- 0:00.0 0:00.0 0:00.0
>create view image vctestv;
Query 7 Complete ---- 0:00.1 0:00.1 0:00.1
>explain vctestv;
...
RAM Usage:-
80.4 MiB (84347552 bytes)
...

So we can see here that for a million rows matching your description, the view image takes up just over 84 million bytes, so each row is 84 bytes.

This is made up of
1) 4 bytes of row header (a long time ago this used to be 8 bytes, for a size word and a link to next record, but all software in use now removes that duplication of information and just has a 4 byte value for the header). It looks like the explain command above still erroneously reports an 8 byte row header, but that will be fixed in a later version.
2) 4 bytes for the integer field.
3) for each varchar, 8 bytes for the offset and length, then the data itself, so 18 + 58 = 76 bytes here

So a total of 4 + 4 + 76 = 84 bytes.

Note that rows are generally word-aligned, so in this case the average varchar lengths mean no alignment is needed, but if e.g. name1 was always 11 characters rather than 10, that would take the row length up from 84 bytes to 88 rather than 85.
Reply with quote Top
Contributor
Offline
Posts: 183
Joined: Wed May 29, 2013 2:10 pm
Location: Bracknell

Re: view image size with varchar datatype

by MikeAtkinson » Mon Jan 22, 2018 2:35 pm

Alternatively you can use Kognitio Console.

Connect to the database, open the schema and double-click on the view. Various tabs tabs in the View object view will show information about the view, the one we want is "RAM", this will show an estimate of how the RAM is being used.

Image
Reply with quote Top
Contributor
Offline
User avatar
Posts: 14
Joined: Wed Feb 11, 2015 3:57 pm

Re: view image size with varchar datatype

by jagan.kumar » Tue Jan 23, 2018 6:22 am

Thanks Marc and Mike
Reply with quote Top

Who is online

Users browsing this forum: No registered users and 1 guest

cron