Disclaimer: Outside of theory, I have very limited experience with DBMS. This question might be incredibly stupid.

I am currently starting a new project from scratch. It involves constantly appending data (measurements, mostly) to a local database. After I read ADS Is High Performance I quickly fell in love with Advantage Database. It looks like a strictly ISAM-based approach is exactly what I need: The natural order of records is strictly determined by the order I add them: If I need to find a handful of records from two years back, I don't have to crawl the entire set of data. In fact, I will always search, or at least limit, by date.

Because of that, I started off by using DBF tables. Random folks on the internet were starting to make fun of me. The Advantage Developer's Guide itself even states:

In short, except for legacy application support, the use of the DBF file formats is discouraged. In fact, if you are building a new application with no need for backward compatibility with existing Clipper or FoxPro applications, you are much better off using the Advantage proprietary format.

I'm puzzled. On the one hand, a DBF-based database looked like exactly what I wanted and not like another random relational DB operating at crawling speed. The concept of not actually deleting records looks appealing. I'm either horribly wrong, or an ADT-based database does not offer that charming guaranteed natural order and is much closer to your everyday relational DBMS.

On the other, I fear that working with such a dinosaur format is going to lead to problems with huge file sizes, Unicode or probably only offers limited referential integrity. Stuff I'll only find out about when I later run into it.

Summary: I am constantly appending data. From time to time, I'll be purging the database so it doesn't grow above some fixed size. Having data appear in the natural order it was written is an incredibly powerful performance boost. Therefore, I started off with DBF. In 2013. Am I wrong?

Many thanks in advance.

asked 07 Nov '13, 02:17

Overclock%20Your%20Breakfast's gravatar image

Overclock Yo...
accept rate: 0%

A few additional thoughts...

I would concur with Joachim that the ADT format will offer a few more features. Off the top of my head, here are some additional items to add to the list of things supported by ADTs but not by DBFs:

  • Rowversion field type
  • Modtime field type
  • Differential backups
  • Strong encryption. Well ... actually it is supported with the VFP format, but if this is used the table would be completely non-usable with any 3rd party application since it would not recognize the encrypted table, so that would seem to defeat the purpose of using the DBF format.
  • Encrypted indexes. When using encryption, CDX indexes (if I recall correctly) are never encrypted.

And it may be worth pointing out that with the Visual Foxpro style tables (DBF/VFP), the difference between ADT and DBF is a bit narrower. A few important things that VFP DBFs support over DBF/CDX are Auto-increment fields, primary keys (thus allowing referential integrity), true NULL support, and binary indexes. Note, though, that RI (referential integrity) on VFP DBF tables does take a little bit more thought. For example, you likely need !DELETED() conditions on primary and foreign keys otherwise it can be a little bit weird since delete operations do not really delete anything in DBFs. In other words, if you want RI, then ADT is probably a better choice.

Another thing to consider is future features. In general, if it makes sense new functionality is supported with both ADT and DBF formats, but it is not guaranteed. For example, a couple new field types that are being developed for v12 are long (64-bit) integers and a GUID field type. It is not yet clear if they will be supported in the DBF format. Also, memo compression being added for v12 I think will only be supported in the ADT format.

Having said that, if the DBF format is doing everything you need, then it may be a good choice. There can be a number of reasons to use them including the fact that because it has been around for a long time there are quite a few utilities that recognize the DBF format.


answered 07 Nov '13, 08:44

Mark%20Wilkins's gravatar image

Mark Wilkins
accept rate: 26%

Thank you, that's been most helpful! I am indeed using VFP style DBF tables, hence the confusion regarding an AutoInc type. ADT certainly offers some appealing features. However, I still can hardly imagine ADT being able to compete with DBF's performance because of it's true physical, natural order. I believe I need to learn more about what indexes actually are and whether they're able to bring ADT tables up to competetive results.

(07 Nov '13, 09:15) Overclock Yo...

ADTs and DBFs are both ISAM formats. So from a speed perspective, there should be no obvious difference. If you seek for a value in an index, the reference to the actual data in the table is a direct computation of record size and record number. There are of course differences with respect to record recycling and record deletion, but the records themselves (deleted or not) are layed out in fixed length sequentially on disk just like DBFs.

(07 Nov '13, 09:20) Mark Wilkins

ADT is an enhanced DBF/CDX format, which gives you additional features:

  • true NULL values (empty string and not defined string is different)
  • re-use of deleted records (this breaks the natural insert order)
  • many more field types (including autoinc, which then can be used to get the natural order back)
  • binary indexes - faster and less space
  • referential integrity
  • true unique indexes
  • and much more...

ADT gives you all you need - I'd go for ADT


answered 07 Nov '13, 02:30

Joachim%20Duerr's gravatar image

Joachim Duerr
accept rate: 18%

Thank you for the quick reply. Could you please elaborate if the "re-use of deleted records" can be turned off? I believe an AutoInc fieldtype (which also exists in DBF) is no sufficient replacement for a true natural order. However, I have not understood the concept of indexes so far. I fear that setting a (PKEY-)AutoInc field as an index doesn't prevent the DBMS from crawling the entire database just for retrieving a handful of records that would, with DBF, all be "next" to each other. Am I mistaken?

(07 Nov '13, 02:34) Overclock Yo...

AutoInc is not available in DBF. ReUsing the deleted records cannot be turned of. However, you can use a data dictionary and overwrite the delete operation. See my blog post on insert only: http://joachimduerr.blogspot.de/2013/09/insert-only.html

(07 Nov '13, 04:51) Joachim Duerr

These guys know what they are talking about: if you want advise: take .ADT, if you want to ignore all the advices: go for .dbf and convert your application to .adt in 1 year when you haev collected all the knowledge. I have build applications based on .DFB and am happy to use .ADTs now. And please use dictionary bound tables from the beginning, no free tables. It is so easy...


answered 07 Nov '13, 16:36

Stephan%20Leiwering's gravatar image

Stephan Leiw...
accept rate: 19%

A bit late for a reply but I just read this one ...We would probably have used ADT files if there was an option to keep deleted records. I find it highly irritating that ADS decides to overwrite deleted records and that this is not a selectable option.

Being able to see what information used to be in the database and in which order before it was deleted has helped us quite a few times with sorting out issues. I know that I can build in a bag of tricks to "simulate" deleted records but if I would have wanted to apply tricks, I was probably better of with converting everything to SQL, for which there are many free versions with less limitations than ADS local server. Other plus of dbf is that I occasionally use DBF tools to manipulate data; even the simplest one has so much more options than the Advantage Data Architect which is close to unusable. As soon as you have adopted the ADT format you are condemned to the Data Architect for any non programmed actions!

So I think Jens is right; if you want some direct insight in your data and the maximum flexibility, go for DBF. I do not consider most advantages of the adt format of much value and for those I do care about it is unfortunately outweighed by the way adt handles deleted records and the need to have the that dreadful Data Architect as a tool.



answered 03 Dec '13, 07:55

Dick%20IC2's gravatar image

Dick IC2
accept rate: 0%

edited 03 Dec '13, 09:13

Thank you for your reply. I really do look at it the same way. While ADT certainly has some advantages (no pun intended), most of them simply don't come into Play in my Scenario. I still have some time left for the final decision but I still consider sticking with DBF.

(03 Dec '13, 12:09) Overclock Yo...
Your answer to the original question.
If responding to a request for additional information, please edit the question or use the comment functionality.
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "Title")
  • image?![alt text](/path/img.jpg "Title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported



Asked: 07 Nov '13, 02:17

Seen: 13,818 times

Last updated: 03 Dec '13, 12:09

Advantage Developer Zone Contact Us Privacy Policy Copyright Info

Powered by Advantage Database Server and OSQA
Disclaimer: Opinions expressed here are those of the poster and do not necessarily reflect the views of the company.