Michael,

<< The 1st i guess. Let me ask you, what's better and faster for the
Operating System or TDataset ? To cache 10 mb of data or 50mb ?
>>

Even a table with true VarChar support still has to divide the
database/table into pages of 4k or more, meaning that there is wasted slack
space in some pages after deletions, etc. that unnecessarily consume cache
space.  Things just aren't cut-and-dry when it comes to space savings using
variable-length columns.  There are trade-offs with both.

Also, to answer your question - it doesn't matter as long as you're not
hitting the page swap file in the OS.

<< Also with VarChar we can avoid using Memo fields for storing small string
data. >>

But you couldn't possibly come up with a high-end value <= 512 characters
for such data ?  Frankly, if you think that there's going to be more than
512 characters in the column, then a Memo/CLOB column is a much better
choice anyways.  Also, indexing anything larger than 512 characters is a
mistake anyways, so the indexing is a non-issue.

<<  The only thing that worrys me about VarChar is fragmentation of the
database which i guess it's the only think that it can slow down. (But hey!
Thats why OptimizeTable exists!) >>

It's more than just fragmentation.  The whole organization of the
database/table takes a big performance hit.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Guys,

Been watching this Varchar discussion with interest.

If I understand the definitions of a VarChar v Char correctly then you couldn't
use a Varchar as an index because of the variability of its length.  Yes/no?

Regards,

Ian

Hello Tim,

> Even a table with true VarChar support still has to divide the
> database/table into pages of 4k or more, meaning that there is wasted slack
> space in some pages after deletions, etc. that unnecessarily consume cache
> space.  Things just aren't cut-and-dry when it comes to space savings using
> variable-length columns.  There are trade-offs with both.

You know better =)

> Also, to answer your question - it doesn't matter as long as you're not
> hitting the page swap file in the OS.

Correct

> << Also with VarChar we can avoid using Memo fields for storing small string
> data. >>
>
> But you couldn't possibly come up with a high-end value <= 512 characters
> for such data ?  Frankly, if you think that there's going to be more than
> 512 characters in the column, then a Memo/CLOB column is a much better
> choice anyways.  Also, indexing anything larger than 512 characters is a
> mistake anyways, so the indexing is a non-issue.

You're suggesting to store for eg. an e-mail subject into a memo field
rather on varchar ? How about "search" speed ? It would be faster
searching a indexed varchar or memo ?

> It's more than just fragmentation.  The whole organization of the
> database/table takes a big performance hit.

Well, i'll have an answer about this after finish my e-mail server.

--
Charalabos Michael - [Creation Power] - http://www.creationpower.com -
http://www.creationpower.gr

Charalabos


An email subject of 512 characters - why bother with the email body

Roy Lambert

Michael,

<< You know better =) >>

Not really - I just know for a fact that most of the time I don't know for
sure.  As for this subject, a good way to think about it is in terms of
working with only a portion of the database/table at any given time.  As
long as the database engine is efficient at quickly locating the proper data
and then efficiently working with it, it really doesn't matter how it is
stored other than how much disk space it consumes.  Occasionally the entire
database/table needs to be dealt with, but that is usually fairly rare.

<< You're suggesting to store for eg. an e-mail subject into a memo field
rather on varchar ? How about "search" speed ? It would be faster searching
a indexed varchar or memo ? >>

Well, I would think that you could get away with using a 100-character or so
column for an email subject in most cases.  We do with our mail server.  As
for searching, it really depends upon the nature of how you wish to search
the column and whether you wish to sort by the column at all.

<< Well, i'll have an answer about this after finish my e-mail server.
>>

Interesting.  Are you using DBISAM or something else ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Ian,

<< If I understand the definitions of a VarChar v Char correctly then you
couldn't use a Varchar as an index because of the variability of its length.
Yes/no? >>

Yes and no, actually.  You could still index it provided that it is less
than the index page size.  The problem is that you can't index something
larger than that.  However, I doubt that you would want to in most cases
anyways.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hello Tim,

> Yes and no, actually.  You could still index it provided that it is less
> than the index page size.  The problem is that you can't index something
> larger than that.  However, I doubt that you would want to in most cases
> anyways.

You must also define the maximum size of VarChar.

--
Charalabos Michael - [Creation Power] - http://www.creationpower.com -
http://www.creationpower.gr

Hello Roy,

> An email subject of 512 characters - why bother with the email body

Well i want to cover ever the rare cases.
You can use email body for "HTML" and Attachements and etc.

--
Charalabos Michael - [Creation Power] - http://www.creationpower.com -
http://www.creationpower.gr

Hello Tim,

> << You know better =) >>
>
> Not really - I just know for a fact that most of the time I don't know for
> sure.  As for this subject, a good way to think about it is in terms of
> working with only a portion of the database/table at any given time.  As
> long as the database engine is efficient at quickly locating the proper data
> and then efficiently working with it, it really doesn't matter how it is
> stored other than how much disk space it consumes.  Occasionally the entire
> database/table needs to be dealt with, but that is usually fairly rare.

Ok

> << You're suggesting to store for eg. an e-mail subject into a memo field
> rather on varchar ? How about "search" speed ? It would be faster searching
> a indexed varchar or memo ? >>
>
> Well, I would think that you could get away with using a 100-character or so
> column for an email subject in most cases.  We do with our mail server.  As
> for searching, it really depends upon the nature of how you wish to search
> the column and whether you wish to sort by the column at all.

Currently i use an 128 char field. But in order to make a fast IMAP4
server you need to index many properties of the e-mail body and e-mail
header. (see RFC of IMAP4Rev1) So there's where varchar goes. You can
save up a lot of space and since VarChar is indexed your search speed
will be much better than the current brute force scanning of memo
fields.

> << Well, i'll have an answer about this after finish my e-mail server.
>  >>
>
> Interesting.  Are you using DBISAM or something else ?

What a silly question! Of course i'm using DBISAM v4 (as i always do
in a few applications that i've made) that's why i'm ticking you to
send me an alpha of v5 <bg>. I love this product!

To be serious, i'm just curious to see if VarChar can actually save
me a great amount of disk space but keeping the perfomance at good
levels so i'll try out MSDE 2005 (MSSQL) using Chars and VarChars and
i'll post the results. Currently i have a couple customers having
some thousand e-mails in their outlook.(which from them i'll get the
test data) Of course this will take a couple months ... =)

--
Charalabos Michael - [Creation Power] - http://www.creationpower.com -
http://www.creationpower.gr

Hi Tim,

Tim Young [Elevate Software] wrote:

>
> Yes and no, actually.  You could still index it provided that it is less
> than the index page size.  The problem is that you can't index something
> larger than that.  However, I doubt that you would want to in most cases
> anyways.

Maybe I'm still thinking old school rather than the SQL type world but I
thought that inherently indexs (what is the official plural of index?), or more
specifically the fields that are indexed, could not be variable in length.  A
VarChar, by my understanding, varies its physical length up to the defined
length, therefore the effective field length is varying which index's don't
like.

Or do I have this all screwed up?

Regards,

Ian


--