Add Records Very Slow ?

[Andreas Grosam]

Hi Eric,

It's not a "bad choice" it's a “choice”. I experienced in the C VDSK a performance penalty of a factor of greater than 15 compared to fixed length strings.

A Text type will be stored differently - like a BLOB object - which is more expensive regarding time - but on the other hand, you can store really big text. 

A VarChar is a variable length string which will be (guess so) stored directly in the base table. Since there are certain limitations which will affect the overall performance and the total size of the base table - you shall not use very large sized strings when using this type.
There is no exact value where it is advantageable to prefere other string types, but as a rule of thumb the average size of all strings should be smaller than 256 bytes and the total size of all strings in one record should be smaller than 4k. Of course, you can even store larger strings, this is not harmful - but this may affect the performance a bit.
The reason for this is how a database works internally.


A String type is a fixed length string. 
The fastes method is certainly a string with fixed length - but this is not the best choice when your string values vary from very small (<20) to very big (>>30k) sizes.
A fixed size string type will be appropriate if your strings values are a) small and b) about of the same size.


Some other general performance hints:

When you have a certain table with a lot of columns which contains large strings (>>2k) but if these strings will be rarely accessed - or which are rarely in the projection of a query statement or are rarely part of the order by and where clause etc. - then queries might be faster if you declare them as Text.

If you have small strings (<16 bytes) and if they do never exceed a certain max length, choose a fixed length string. This should a little bit faster than variable sized strings.

Do not include a Text (or a BLOB) column in a projection when you don´t need it.
Do not access a Text (or a BLOB) from a cursor when you don't need it.

For instance, in a GUI in a TableView do not access a Text column if it is currently not visible.
(Although is is beyond the current issue)


OK, I hope this helps. Note that these hints are general hints - not only for Valentina - some may help others not.


Your other question regarding the integer types:

A Long is a singed integral value with 32 bits. Range is LONG_MIN...LONG_MAX
A ULong is an unsigned integral value with 32 bits. Range is 0..ULONG_MAX
A LLong is a singed integral value with 64 bits. Range is LONGLONG_MIN... LONGLONG_NAX
A ULLong is an unsigned integral value with 64 bits. Range is 0...ULLONG_MAX

A signed integral has a range from negative to positive values - an unsigned integral starts from zero and has only positive values.



Andreas

On Dienstag, 25. Februar 2003, Wonder Fef <wonderfef at noos.fr> wrote:
>
>Hello all,
>
>Each time I had speed problem with Valentina, it was due to the bad choices
>I made concerning my fields format.
>For example, I used text instead of varchar.
>
>I think that Valentina documentation is not very clear about all fields
>types.
>By the way, what's the difference between long, ulong, llong
>and ullong?
>
>Eric 
>
>_______________________________________________
>Valentina mailing list
>Valentina at lists.macserve.net
>http://lists.macserve.net/mailman/listinfo/Valentina
>