OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Using very long text fields

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
anotherbigal
General User
General User


Joined: 17 Jul 2010
Posts: 29
Location: Sunny (?) West Wales in the UK

PostPosted: Fri Sep 02, 2011 1:52 am    Post subject: Using very long text fields Reply with quote

Hi

I am designing a new database but try as I might it seems that one of the tables will have 20 different fields that could contain textual data that is anywhere from zero to 3,000 characters long. Not all fields will contain data in any one record, indeed it might only be one or two, but the facility must be there. What is more queries must be able to search for random strings within each of these text fields and be able to output the results, together with a link to the full field entry, in a form.

The fields are, I believe, far, far too long for a database so I am considering using an external (external to the database that is) source to store the data, say tables in a Writer document but I am not sure if this is the correct, or a practical way to go about it. My worry about using an external storage is that it could be split up from the database - on the basis of if it could happen, it will happen; and at the most inconvenient time possible. Any comments, suggestions, observations or pointers will be of great help.

Cheers

Alan
Back to top
View user's profile Send private message
probe1
Moderator
Moderator


Joined: 18 Aug 2004
Posts: 2560
Location: Chonburi Thailand Asia

PostPosted: Sun Sep 04, 2011 12:53 am    Post subject: Re: Using very long text fields Reply with quote

anotherbigal wrote:
that one of the tables will have 20 different fields that could contain textual data that is anywhere from zero to 3,000 characters long.


Besides from the idea to create an extra table for all text entries ( relation: ID of entry in your 1st table, ID/category of text-entry, TEXT)...

Look out for column type VARCHAR
The max possible length of data varies, depending on which DB engine you use,
MySQL accepts 65535 chars, PostgreSQL claims "no limit" (which in fact is 1 GB), while the OOo-imbedded HSQLDB accepts a 1 million char definition (not tested in practice!)
Nevertheless the space occupied by the tables on disc depends only on the amount of content entered, not by the column length definition.

Does this help?
_________________
Cheers
Winfried
My Macros
DateTime2 extension: insert date, time or timestamp, formatted to your needs
Back to top
View user's profile Send private message Visit poster's website
anotherbigal
General User
General User


Joined: 17 Jul 2010
Posts: 29
Location: Sunny (?) West Wales in the UK

PostPosted: Sun Sep 04, 2011 8:08 am    Post subject: Re: Using very long text fields Reply with quote

Hi,

Thank you for the info about the table formats, char and varchar. That was very helpful. I was quite concerned about the 'on disk' size. varchar seems to solve that very tidily.

However, You said "depending on which DB engine you use". Sorry but that has confused me, so could you expand a little please. From within Open Office I select a new database from the file menu and am taken to the Database wizard. It will be a new DB with no connections to any pre existing one. I am not given any options to select which DB engine I want to use. Am I missing something? I have always understood that the OOo database engine used MySQL yet you comments seem to imply I could, if I wanted choose PostgreSQL. I confess that databases are still something I am getting my head around so I am probably barking up the wrong tree.

Cheers

Alan
_________________
I know a lot of things. I just wish I could remember them.
Back to top
View user's profile Send private message
probe1
Moderator
Moderator


Joined: 18 Aug 2004
Posts: 2560
Location: Chonburi Thailand Asia

PostPosted: Sun Sep 04, 2011 8:28 am    Post subject: Re: Using very long text fields Reply with quote

anotherbigal wrote:
I am not given any options to select which DB engine I want to use.


You can choose option "Connect to an existing database" within File>New>Database

But these are OOo-external data base engines, have to be set up first.


For a professional/productive task I strongly recommend not using the internal DB.
_________________
Cheers
Winfried
My Macros
DateTime2 extension: insert date, time or timestamp, formatted to your needs
Back to top
View user's profile Send private message Visit poster's website
anotherbigal
General User
General User


Joined: 17 Jul 2010
Posts: 29
Location: Sunny (?) West Wales in the UK

PostPosted: Sun Sep 04, 2011 10:49 am    Post subject: Reply with quote

Ah! I see now.

Why is it that these things are so obvious once someone has pointed them out to you?

Cheers

Alan
_________________
I know a lot of things. I just wish I could remember them.
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Mon Sep 05, 2011 10:27 am    Post subject: Reply with quote

anotherbigal wrote:
that one of the tables will have 20 different fields that could contain textual data that is anywhere from zero to 3,000 characters long.

Your textual data requirements are relatively modest but as probe1 mentioned, you'll run into problems with the "internal DB."

The problem is not so much with the built-in HSQL database engine as it is with the default configuration setup by Base. The "embedded database" configuration generated by the "new database" wizard is prone to data loss. And if that's not bad enough, the dubious configuration also reduces the default cache from 48MB to a mere 6MB. Large records don't play well in that environment, so you may experience performance drops with as few as 50 rows (assuming all 20 fields per row filled with 3,000 characters each).

You can utilize the the built-in HSQLDB engine as an "existing database" connection through the "JDBC" option of the database wizard. That effectively fixes both of the above drawbacks. See my signature links below for setup details.

But you should also be aware that the built-in version of HSQLDB (1.8.0.10) doesn't offer the CLOB data type. VARCHAR may suffice in your case, but CLOB is often suggested when VARCHAR fields exceed approximately 1024 characters/bytes (the first 256 unicode characters only use 1 byte per character when encoded by the UTF-8 spec).

I mention CLOB because some engines generate a separate Table for CLOB objects automatically, leaving only a placeholder in the original column to maximize performance. The notable one's are H2 and HSQLDB 2.x (much better than the built-in version). MySQL (perhaps others) will do the same with an add-on. Only H2 offers CLOB compression.

You also mentioned searching the fields for text strings. With many large text fields, you'll be best served by a DB engine or add-on with "full text search" capabilities, as it's much faster and more efficient than typical database searches involving text fields. H2, MySQL and PostgreSQL offer full-text search.

So on paper, it seems that H2 offers the best mix: automatic CLOB tables (without add-ons) combined with a "full-text search" engine. And H2 generally beats all other database engines hands-down in speed tests, with the exception of HSQLDB which is neck-and-neck. Here's a setup link for H2 with Base. Disclaimer: I haven't used H2 with CLOB/BLOB personally.
_________________
Soli Deo gloria
Tutorial: avoiding data loss with Base + Splitting 'Embedded databases'
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group