Thursday, January 24, 2019

Update to VFPX Upsizing Wizard

I released an update to the VFPX Upsizing Wizard today. It has a couple of changes:

  • It handles large tables better. The bulk XML load process, which is by far the fastest way to upsize the records in a table, creates an XML file using CURSORTOXML and then uses the SQLXML COM object to process that file. With a large table, the XML file could be more than 2 GB, which causes CURSORTOXML to fail, so the Upsizing Wizard reverts to using a slower mechanism. In this update, the bulk XML load routine now processes large tables in batches so the XML file never gets bigger than about 1 GB.
  • It renames the built-in tables Keywords.dbf, ExprMap.dbf, and TypeMap.dbf to have an underscore prefix to avoid conflict with tables using those names in the database to be upsized (this was an issue someone ran into).

Note: since SQL Server 2008, the SQLXML module that supports bulk XML load is no longer automatically installed, so if you find bulk XML load isn’t being used, it may be because the COM object doesn’t exist. In that case, download it from https://docs.microsoft.com/en-us/sql/relational-databases/sqlxml/what-s-new-in-sqlxml-4-0-sp1?view=sql-server-2017.

at

5 comments:

  1. Unknown3:36 AM

    Hi Doug,

    Good stuff!
    After reading the handling and the issue regarding SQLXML I was wondering whether it could be an alternative approach to use sqlbulkcopy instead?

    I've been using it in several projects - VFP and C# alike - to transfer large sets of data into SQL Server. The transfer operates at approximately 12,000 records / second depending on hardware and network.

    Regards, JoKi

    ReplyDelete
  2. Doug Hennig9:30 AM

    Thanks for the suggestion. I'll look into that. It should be easy to create a C# wrapper and call it from VFP using wwDotNetBridge.

    Doug

    ReplyDelete
  3. Doug Hennig1:15 PM

    FYI, I created a C# DLL using SqlBulkCopy. On a large table (over 2 million records), it was about 2.5 minutes faster than bulk XML load (12:45 vs 10:16). So, I'll implement that in a future release of the Upsizing Wizard.

    ReplyDelete
  4. Michael Hogan9:38 AM

    Thanks, Doug. Any chance you'll be updating your https://doughennig.blogspot.com/2006/07/sqlxmlbulkload-rocks.html article with the improvements you've made?

    ReplyDelete
  5. Doug Hennig3:01 PM

    Hi Michael. You can download an updated version of BulkXMLLoad.prg by downloading or cloning the VFPX Upsizing Wizard (https://github.com/VFPX/UpsizingWizard) or downloading https://github.com/VFPX/UpsizingWizard/blob/master/PROGRAM/bulkxmlload.prg

    ReplyDelete