Making FoxPro Ducky – Part 5 (DBFs – Duck Built Files)

To recap from Part 1, the goals of this rabbit hole project were to use DuckDB’s fast processing engine alongside an existing Visual FoxPro 9 (VFP) application, to take advantage of DuckDB’s import/query speed on large datasets (100M+ rows, ~2GB) while keeping VFP as the primary application layer. The primary technical obstacle was that DuckDB only ships a 64-bit ODBC driver, and VFP9 only supports 32-bit ODBC which ruled out a direct database connection.

At the end of Part 4, after a lot of trial and error, dead ends and wrong turns, my new wingman, Claude.AI, threw out a casual comment that got my adrenaline pumping.

To quote Claude:

Worth investigating: DuckDB’s spatial extension can write DBF directly: DuckDB’s spatial extension wraps GDAL/OGR, and GDAL has an OGR driver that writes plain DBF (independent of shapefiles).

It turns out that the DuckDB Spatial Extension (GDAL) can use an alternate export path to write plain .dbf files directly from within DuckDB. The only caveat is that we would be moving back into the classic DBF limitations. Essentially, the DBF format caps character fields at 254 bytes and tables at 255 fields total — same ceiling as VFP table files. The downside being that won’t solve width/field-count overflow; however, using these files would avoid SQLite’s memo-field issue and give me typed fields inside of Visual Foxpro. I would still need to index the DBF to take advantage of Rushmore technology to optimize queries or seeks inside of VFP, but that shouldn’t be a problem, right?

My command stack was now looking like this:

1. Run VFP program
2. Create a Job command file for DuckDB
3. Shell out to an instance of DuckDB and read the Job Command File to import files into a persistent database
4. Return to VFP
5. Loop through a series of new Jobs to shell out and perform queries exporting the results to a classic .DBF file
6. Return to VFP and process the results found in the .DBF file created.

Easy, right?

I also found that it would only take changing the export  format (I was using CSV before) to take advantage of the GDAL format shapefile AND loading the spatial extension in DuckDB. Adding the extension was easy enough because all I needed to do was add two lines to the beginning of my Query Job Command file:

install spatial;
load spatial;

The first line tells DuckDB to download and install the spatial extension if it hasn’t been installed already, and the second line tells DuckDB to load the spatial extension. Once loaded, I can then use any of the Vector drivers made available in GDAL for just about any file format out there. The one I was interested in most was the “ESRI Shapefile” which exports natively to DBF file format. Reading through the documentation for the ESRI Shapefile, I found it supported building DBF files up to 8GB, but immediately followed that specification the following statement:

However, for compatibility with other software implementation, it is not recommended to use a file size over 2GB for both .SHP and .DBF files.

That didn’t really surprise me, but it also didn’t worry me because my idea would be to house the largest quantity of imported data in a DuckDB database, issue queries for smaller result sets and only export what I needed to the DBF format. So, I revised the code from part 4 to export the results to the ESRI Shapefile DBF format.

And here’s what that looks like:

* Import Multiple CSV Files and save as DuckDB Persistent Database
* Then issue a SELECT statement with JOIN as second repeatable Job
* and save results to a standard DBF file suitable for use in VFP
******************************************************************
LOCAL loShell, lcCmdString, lnResult
SET SAFETY OFF

CLEAR
a = SECONDS()
loShell = CREATEOBJECT("WScript.Shell")

DELETE FILE "./SQLWork/datawork.db"
DELETE FILE "./SQLWork/datawork.db.wal"
DELETE FILE "./SQLWork/job01.sql"
DELETE FILE "./SQLWork/job02.sql"

lcImportPath = "T:/DataWorks/TestData/"

TEXT TO lcSQLCmd TEXTMERGE NOSHOW
CREATE TABLE empls AS (SELECT * FROM read_csv_auto('<<lcImportPath>>Employee Export 01.csv'));
INSERT INTO empls (SELECT * FROM read_csv_auto('<<lcImportPath>>Employee Export 02.csv'));

CREATE TABLE earns AS (SELECT * FROM read_csv_auto('<<lcImportPath>>Q1 Earnings.csv'));
INSERT INTO earns (SELECT * FROM read_csv_auto('<<lcImportPath>>Q2 Earnings.csv'));

CREATE TABLE deducts AS (SELECT * FROM read_csv_auto('<<lcImportPath>>Q1 Deductions.csv', types={'Document Identifier': 'VARCHAR'}));
INSERT INTO deducts (SELECT * FROM read_csv_auto('<<lcImportPath>>Q2 Deductions.csv', types={'Document Identifier': 'VARCHAR'}));

CREATE TABLE taxes AS (SELECT * FROM read_csv_auto('<<lcImportPath>>Q1 Taxes.csv', types={'Document Identifier': 'VARCHAR', 'Hours Worked': 'VARCHAR'}));
INSERT INTO taxes (SELECT * FROM read_csv_auto('<<lcImportPath>>Q2 Taxes.csv', types={'Document Identifier': 'VARCHAR', 'Hours Worked': 'VARCHAR'}));
ENDTEXT

STRTOFILE(lcSQLCmd,"./SQLWork/job01.sql")

lcShellCmd = '.\duckdb.exe ".\SQLWork\datawork.db" -bail -c ".read ./SQLWork/job01.sql"'

lnResult = loShell.Run(lcShellCmd, 0, .T.) && 0 = hide window; .T. = wait until the command finishes

IF lnResult <> 0
    MESSAGEBOX("DuckDB returned error code " + TRANSFORM(lnResult))

ELSE

	b = SECONDS()
	? "Job #1 Time: " + ALLTRIM(TRANSFORM(b-a)) + " seconds"
ENDIF

TEXT TO lcSQLCmd TEXTMERGE NOSHOW
install spatial;
load spatial;

COPY (SELECT 
"t"."Company Name",
"t"."Employee Number",
"e"."Employee SSN/SIN",
"e"."Employee First Name",
"e"."Employee Middle Name",
"e"."Employee Last Name",
"e"."Employee Name Suffix",
"e"."Pay Class Name",
"e"."Pay Type Name",
"e"."Employment Status Name",
"t"."Check Date",
"t"."Pay Type Description",
"t"."Payroll Tax Name",
"t"."Payroll Tax Description",
"t"."Tax Authority Name",
"t"."Is Employee Tax",
"t"."Is Employer Tax",
"t"."Tax Amount",
"t"."Curr Wage Basis",
"t"."Subject Wages",
"t"."Gross Wages",
"t"."Work State Code",
"t"."Is Voided"
FROM taxes t
LEFT JOIN empls e
ON "t"."Employee Number" = "e"."Employee Number"
WHERE "t"."Employee Number" = 35183
) TO './SQLWork/queryresults.csv' (FORMAT GDAL, DRIVER 'ESRI Shapefile');
ENDTEXT

STRTOFILE(lcSQLCmd,".\SQLWork\job02.sql")

lcShellCmd = '.\duckdb.exe ".\SQLWork\datawork.db" -bail -c ".read ./SQLWork/job02.sql"'

lnResult = loShell.Run(lcShellCmd, 0, .T.) && 0 = hide window; .T. = wait until the command finishes

IF lnResult <> 0
    MESSAGEBOX("DuckDB returned error code " + TRANSFORM(lnResult))

ELSE

	c = SECONDS()
	? "Job #2 Time: " + ALLTRIM(TRANSFORM(c-b)) + " seconds"
ENDIF

Just like before, the import in Job01 command file took 19.025 seconds. The Job02 command file took 3.218 seconds to load DuckDB, install/load the spatial extension, issue the query and export the results to a DBF containing 410 records and 23 fields. The fields were typed as Character fields of 80 characters each and numeric fields (14,2). I was sure I could use CAST to tailor the characters fields and minimize the amount of blank space used in the DBF and perhaps even shave a micro-second or two off the DBF file creation time but that could come later. However as it stood, when I averaged the time per record to create the DBF file I came up with 0.00784 seconds per record. Of course, some of that is the overhead of opening an instance of DuckDB, but as good at that sounds, it gets worse as the result files get bigger.

For the next test, I decided to go for a bigger result set so I added an additional command to the Job02 command file which simply instructed DuckDB to copy the entire taxes table comprised of 881,139 records with 40 fields per record to a single DBF file. My theory was that by only opening a single instance of DuckDB and issuing the same query as before + the table dump, that would minimize the impact of the overhead of opening DuckDB, loading the instance, etc. This would also give me a more accurate measure of the exact per record time cost of writing data from DuckDB into a DBF file.

This time the Job02 command file ran for 165.50 seconds and created a DBF file of 1,953,486,477 bytes, dangerously close to the 2Gb limit! As noted the tax file contained 881,139 records and the first query had 410 records which when combined gives a total of 881,549 records exported. That means the average time to export a record to a DBF file was 0.0001877 seconds. Again, that sounds pretty respectable from a pure import/export point of view. However, that is a lot of data to chew through and it means that just to get a bunch of CSV files imported, issue a couple of queries with large result sets it took about 3 minutes with nothing on the screen and no way to give a user any feedback on how the process was progressing. Plus, as I mentioned earlier, these exported DBF files are raw exports with no indexing so there could be no Rushmore technology until the indexes were built in VFP for each result set file. Having built a compound index file for a large DBF previously, I knew that this would probably take even longer than the 3 minutes already expended just getting the data into the DBF file.

But let’s stick with thinking about just the first query without muddying the waters with the table dump and indexing large DBF files. And, let’s not consider the import time of 20 seconds to create the initial database.

If we only consider a process which looks like this:

1. Create the DuckDB database and import all of the data.
2. Issue some query to create a DBF file as a batch loop.
3. Loop through the batch in Foxpro.
4. With each batch record, fire off an instance of DuckDB with a new query.
5. Open the returned DBF file(s) in VFP.
6. Perform additional actions with the returned DBF data.
7. Got back to Step #4 and repeat until the batch loop is complete.

While the initial average numbers were actually pretty decent considering what I was doing at the time, it became quite clear while I was doing the math there a problem that was only going to get bigger as the files got bigger. The test results showed that for a simple query with a single join and exporting the results to a DBF file, I was averaging 0.00784 seconds per record (which included the overhead cost of opening a new instance of DuckDB and loading the spatial extension each time). That is awesome in my simple test with a single query. But in an effort to simulate many of the projects I work on, I populated the employee file with more than 30,000 obfuscated employee records and if I used just the DISTINCT list of Employee IDs  as the batch loop, that would mean firing of a new instance of DuckDb for each one of those employees in the batch loop. Multiply the 3.218 seconds we got in the single employee tax query test by 30,000 times and you get a grand total of 96,540 seconds or 1,609 minutes or 26.82 hours to complete just one large batch loop with just one query per record in the loop.

So, I called up my new buddy, Claude.AI, and presented my less than exciting findings. Claude agreed the most likely place for us to make significant gains might be in figuring out how to minimize the number of instances of DuckDB we really needed. After all, if there was a way to open DuckDB in the background, create the database, import the data and then push new commands directly into the CLI then we would only ever need one instance of DuckDB running.

So far, I’d been using the WScript.Shell object to fire off an hidden window instance of DuckDB whenever I needed it and it worked pretty well. I would even go so far as to say if I would doing a project in-house, I could probably live with some of the limitations I was finding in this particular stack, but since I’m build distributed applications and clients are far less willing to wait for a result when their desk is full of other responsibilities vying for their attention. It is even worse if there is not some kind of progress bar to at least let them know that work is being done, even if they can’t see it.

Claude and I agreed our next step needed to be figuring out how to keep our Duck alive during a complete process and pick up as much time as we could by not having to reopen the database over and over again.

We definitely were not there yet, but I was starting to feel like together, Claude & I had definitely made some progress and I had learned a lot along the way. After all, while timing was a problem, we had a command stack that actually worked and I could do what I set out to do. Now, I just had to find a way to make it faster.

Nothing runs like a fox, and now I just needed to get the duck to keep up.

[Continue Reading Part 6]