Thoughts about text field lengths...

Hi all,

I was just recently browsing through the data descriptions of the files in my ERP's analysis and realized, that I have many fixed length text fields of 50, 80 or 100 etc. characters. Still, very few of them are very seldom filled even nearly to the full lengths of the fields. So I started to wonder, why not just use a memo type field for any string field that usually contains data longer than 8 characters (or approximately 8; of course there is some overhead when using memo type fields)?
That could cut the size of the database considerably, would't it?

Of course, the disk capacity is not expensive these days, so that is not a factor, but time is. For instance, how long does it take take to make a backup copy of the database.

So, any ideas? Why not use memo fields for any and all long text fields? What would be the drawbacks? Or other benefits than those already mentioned?

I would like to hear what you guys are thinking about this.

Best regards
Ola

Hello Ola

I came to Windev from Foxpro and because their memo fields were not 100% reliable I adopted a process of making my own memo files using a separate file with a memo field and keys to link it back to the source file. That one file provides memos for every other file. Turns out that Windev memo's are quite stable and I didn't need to do that but I was always worried about the memo links getting scrambled. I only use that separate memo process for "real" memos - mainly text notes and string data larger than 100 characters.

The fact that PCSoft allowed you to turn off the memos indicated to me that they saw performance issues when retrieving data I still use memo fields sparingly and never in a transaction file like payroll records or invoices etc.

I think it would be a nightmare to retro-fit an existing application with memo fields instead of text fields.

In terms of backup, I use the inbuilt Windev zip process and it is quick enough for me. It is really a function of the resources you can give the process and most of my clients back up in the middle of the night. One client has 13 GB of data in 1200 files and that takes 7 minutes to create a zip file of 1.5 GB on their server. I can speed that up considerably by dropping the ndx files.

Regards
Al.

Hi Al,

Thanks for sharing your experience and insights.

I came from Clarion Professional Developer and I used memos quite freely. Practically all my base files had a memo for free notes. No problems ever. I continued that practice with Windev, including files that are used in transactions, and in some files I even have more than one memo, because it is possible in WD. Like in a work order I have one memo field for a short description of the work and another one for internal notes only. In CPD I could only have one memo per file.

I actually had one or two hickups with WD memos in the very early days (version 5.5 or 7.5 or something like that) and lost some data in the memos. I also lost a lot of hair those days and the rest was colored grey, thanks to WD. But no memo problems since then.

I was quite happy with the one or two memos per file until recently, when I realized that there will be several very long string fields in my new email system, and there will be a lot of emails in several archive folders, so why not make each of them a memo field, just 8 strings long if there is no data, and otherwise occupying a space only for the actual length of the data. And memo fields can even be used as keys (which was not possible in CPD). I think I'll have a go with this approach.

Best regatds
Ola

Hi again

Just found another drawback when using memo fields instead of normal (very long) fixed length string fields.

In addition the the possible performance hit Al aready pointed out, there is a drawback when using HRetrieveRecord (for instance, to compare an original record to an edited record when saving a form): HRetrieveRecord does not care about memos at all, so the memo contents must be compared separately by extra code, if necessary.

However, there are cases when this is not an issue, for instance with email records, because email records are never modified.

Correction: I was too fast while RTFM: Obviously HRetrieveRecord DOES take care of normal text memos. What it does NOT take care of is BINARY memos! So this is not a drawback in the sense I mentioned!

Best regards
Ola



Edited 1 time(s). Last edit at 09/17/2020 03:35PM by Ola.

Hi all,

Just found one drawback in using memo-type unlimited-length items as "normal" one-line string items:
they cannot be a part of a composite key.

Best regards
Ola

Not sure if it's my code, tbh, but do 'empty' text memo columns actually contain null? I seem to have /0 in a column even though I populated it from an empty field?

Cheers
Reg

Apologies, I meant \0

Hi Norfolk

In C \0 equals null.
It is the termination character of a string in C language so yes, this is null or better: an empty string if nothing is in front of it.
Since C is the basis of about any programming language out there this is the same across systems in general when it comes to (binary) strings.

Cheers Peter H.



Edited 1 time(s). Last edit at 03/05/2021 08:22PM by PeHoBe.

Hi,

But strangely the field that contains this does not register if checked as null using, for example, the c# null construct. Might be a quirk with PCSoft's OLEDB software....