What was that, Paul? You’re going to do a session on this at VFF? Kewl! 😉
Likewise, I hope to virtually see folks from Ed's Profox list representing at VFF. If you're still working in our wonderful but aged dev environment, it's well worth the cost to attend. - https://virtualfoxfest.com/sessions.aspx -- rk From: [email protected] <[email protected]> Sent: Tuesday, September 22, 2026 6:25 PM To: [email protected] Subject: Ducky Lite Foxpro... About a month ago in this forum, I posted I was doing testing with DuckDb because I wanted to take advantage of some of the import speed from flat delimited files, JSON files and other file types. I was very impressed with the data import and export speeds but I was trying to work around the fact that DuckDb only provides a 64bit ODBC driver which is problematic because native VFP only works with 32bit ODB Drivers. Yes, I could us VFP Advanced 64bit, but I work every day in the native development environment. And I wanted to leverage my library of VFP data conversion tools and expand it to enable the fastest start to finish speeds I can possibly achieve for my clients. I won’t bore you with all of the testing I did, the wild goose changes and rabbit holes I went down, but ultimately, I did develop a way for VFP to run DuckDB in the background, send SQL commands to the DuckDb instance via the stdin pipe and get results exported to files which I could easily import back into VFP. However, having established that much, I still wanted a faster way of getting results back into VFP and avoid all of the file creation timing issues and workarounds that were functional but annoying. So, after some work and some theoretical conversions with Claude AI (I could not ignore Kurt and Ed’s recommendations on the matter), I have come up with what some might refer to as a Rube Goldberg process stack that works surprisingly well and is incredibly fast. Here are the basics: I have a flat delimited file comprised of two fields and approximately 190 million records. I used SQLite’s 32Bit ODBC Driver to create an empty database and destination table for the data with a primary key defined comprised of both the fields. Next, behind the scenes, I fire off DuckDb with the SQLite extension installed and read in the 190 million records directly into the SQLite database table created in the first step. Once the import completes, I close the DuckDb instance and create a new ODBC DSNLess connection to the SQLite db populated by DuckDb and issue a query using SQL Passthrough which returns over 660,000 records to a temporary cursor within VFP with typed fields and the entire chain completes in approximately 3.5 minutes. The final query itself takes less than 3 seconds to return 660,000+ records, so the majority of the time is used importing and creating the indexed SQLite tables, but based on my test results that works out to about 1,055,556 records per second read, saved to an indexed SQLite suitable for SQL passthrough queries using the SQLite 32bit ODBC driver. The stack requires only the DuckDB CLI and the SQLite 3 ODBC driver. Not sure if anyone is interested, but since I brought it up before, I thought I’d share my results which I think are pretty substantial when compared to the same process working solely within VFP. In that scenario, I could barely keep the files under 2GB and the best import and index time I was able to achieve was about 25-30 minutes start to finish just for the import process. As always, your mileage may vary, always wear your safety goggles when testing and please, no wagering. See you at Virtual FoxFest! Paul H. Tarver Tarver Program Consultants, Inc. _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: https://lists.leafe.com/postorius/lists/profox.leafe.com/ To unsubscribe send an email to [email protected] OT-free version of this list: https://lists.leafe.com/postorius/lists/profoxtech.leafe.com/ Searchable Archive: https://leafe.com/archives This message: https://leafe.com/archives/byMID/ <mw4pr10mb65849df8b335d988dc2c02f1d2...@mw4pr10mb6584.namprd10.prod.outlook.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious
