Turbo Charge your 4D v11 Development
10/10/08 20:34 Filed in: 4D Summit
2008
Turbo
Charge your 4D v11 Database:
Thibaud Arguillère & Olivier Deschannels
Pat One:
Thibaud and Olivier gave lots of details about what I would call Micro Optimization of a databas, probably more useful for a project where you want to squeeze performance out of a well defined, mature database. This is not a discussion of how to grind out a dozen databases in under a week.
That said, this is the kind of stuff that I think is cool.
For instance, record structure.
Given a table containing 8 logint fields, create 100,00 records. The data file will be 40Mb
Now create a table containing 9 longint fields and create 100,000 records. The data file will now be 80 Mb.
Tha additional field caused each record to occupy an additional block of disk space. So be mindful of block size.
Some advise:
Limit variale size fields
Keep alpha fields under control
Store text & Blob data outside the record
Try to have fixed sized records.
Airbag Records -
JPR’s way of teaching about building fixed size records.
The concept is to pad the size of the largest possible record, using a Blob field containing a variable size blob (the airbag). Then force all of the records to reserve that number of blocks, whether it needs them or not. This will prevent fragmentation of records.
This is an old technique, which Olivier doesn’t use in v11.
Free Space:
If your max size of a record leaves free space within the block size of a record, It would cost nothing in terms of disk space to create an additional field for some type of calculated or meta data. For example, you could have a date field (i.e. birthday) that you wold like to search from. Sometimes you might want to do a search for “children over six years old”. This wold be a two argument search, but you could create another date field and ppulate it with a stored procedure that runs every night. You would then be able to search the calculated field, which would improve search speed and it wouldn’t require any additional disk space.
BLOBS:
Blobs are stored in the data file, but utside the record.
In the record, 4D Stores only a longint reference to the Blob. The idea of this slution is to allow in several fields, the same blob number.
Indexes:
One Major Optimizaation Rule:
Exchange space against time. Give up space in your record in order to save time.We have lots of new types of indexes avalable:
- Compound ndexes
- Expression indexes
Also B-tree and Cluster index types.
Thibaud Arguillère & Olivier Deschannels
Pat One:
Thibaud and Olivier gave lots of details about what I would call Micro Optimization of a databas, probably more useful for a project where you want to squeeze performance out of a well defined, mature database. This is not a discussion of how to grind out a dozen databases in under a week.
That said, this is the kind of stuff that I think is cool.
For instance, record structure.
Given a table containing 8 logint fields, create 100,00 records. The data file will be 40Mb
Now create a table containing 9 longint fields and create 100,000 records. The data file will now be 80 Mb.
Tha additional field caused each record to occupy an additional block of disk space. So be mindful of block size.
Some advise:
Limit variale size fields
Keep alpha fields under control
Store text & Blob data outside the record
Try to have fixed sized records.
Airbag Records -
JPR’s way of teaching about building fixed size records.
The concept is to pad the size of the largest possible record, using a Blob field containing a variable size blob (the airbag). Then force all of the records to reserve that number of blocks, whether it needs them or not. This will prevent fragmentation of records.
This is an old technique, which Olivier doesn’t use in v11.
Free Space:
If your max size of a record leaves free space within the block size of a record, It would cost nothing in terms of disk space to create an additional field for some type of calculated or meta data. For example, you could have a date field (i.e. birthday) that you wold like to search from. Sometimes you might want to do a search for “children over six years old”. This wold be a two argument search, but you could create another date field and ppulate it with a stored procedure that runs every night. You would then be able to search the calculated field, which would improve search speed and it wouldn’t require any additional disk space.
BLOBS:
Blobs are stored in the data file, but utside the record.
In the record, 4D Stores only a longint reference to the Blob. The idea of this slution is to allow in several fields, the same blob number.
Indexes:
One Major Optimizaation Rule:
Exchange space against time. Give up space in your record in order to save time.We have lots of new types of indexes avalable:
- Compound ndexes
- Expression indexes
Also B-tree and Cluster index types.