dbunload for SA16 runs VERY slow

[dhkom]

I have run dbunload.exe for external unloads (both -xx and -xi) on a test 20 GB SQL Anywhere 16 database. The "-xx" unload completed in 29 hours 32 minutes. I aborted the "-xi" unload after 21 hours 30 minutes. The only thing remarkable about this database is a single table with blob data that exported into a 32.5 GB ".dat" file. However, unloads of large, conventional data tables seem to run at a slow proportional speed.

I am running the unload on a powerful, dedicated Windows 10 PC with 32 GB of RAM and a SSD with lots of free space.

Why should the unload run so slowly? Is there anything I can do to speed this up?

This long elapsed time presents a problem as I want to rebuild a customer database, splitting the blob data into a separate dataspace. To do this - I need to do an external unload, modify the reload.sql, and then run the modified reload.sql. The customer database will be even larger than my 20 GB test database, and a 48 hour (or more) production downtime will probably be unacceptable.

Interestingly, the "-xi" unload ran about twice as fast as the "-xx" unload up to the point of the large blob table, and then bogged right down to similar performance.

I'm using SA 16.0.0.1324, however I have searched "SQL Anywhere Bug Fix Readme for Version 16.0.0, build 2798" and see no evidence of this being a problem that fixed by a maintenance release or EBF.

Thanks your your help and consideration!

[Breck_Carter]

You are correct that UNDER NO CIRCUMSTANCES should it take 20 hours to unload a 20G database... not on any computer capable of running SQL Anywhere 16.

Please show us the exact full command line for dbunload.exe... copy and paste, not typed in.

Do you have any reason to run dbunload -xi instead of dbunload -ii? The default is -ii for a very very very very very good reason.

Are you unloading the *.dat files across a slow network to a different computer?

Try dbunload.exe -ii on the same computer as dbsrv16.exe, with the output files going to a locally attached hard drive.

Definitely reconsider usin -ix or -xx because that will cause the reload to be slow.

[dhkom]

Thanks, Breck! "-ii" unloaded in 7 minutes versus 29.5 hours for "-xx"! I had tried "-xx" and "-xi" because of a mistaken assumption vis a vis "-ix" and "-ii", however "-i*" should do the trick. I will keep far, far away from "-xi" and "-xx" going forward.

[Breck_Carter]

> "-ii" unloaded in 7 minutes versus 29.5 hours for "-xx"

[VolkerBarth]

Just to understand: Did you run the dbunload command from the machine the database is located on? And where did the external reload go to? (Because the difference between internal and external unload should not at all be that huge unless the external unload uses a very slow connection, as Breck has suggested...)

[Breck_Carter]

I am guessing this happens when dbunload.exe is used to create *.dat files on the same computer as dbsrv16.exe.

The dbunload.exe -i. command tells dbsrv16.exe to unload the data directly to the *.dat files, with no client-server traffic from dbsrv16.exe to dbunload.exe .

(Note: the dot in -i. means "doesn't matter if it is -ii or -ix, we're only talking about the unload to *.dat")

The dbunload.exe -x. command is sets up a client-server relationship where dbsrv16.exe retrieves the data and returns it to dbunload.exe via client-server traffic, and then dbunload.exe writes the data onward to the *.dat file as a second stream.

So, even if the target data files reside on the same computer, if dbunload.exe is running on a client workstation (for example) the data traverses the network two times (back and forth) for dbunload.exe -x. but zero times for dbunload.exe -i.

It doesn't matter much, how fast or slow the network is, if you don't have to use the network at all 🙂

I think that's why -ii was invented, so dbunload.exe could be executed on a workstation anywhere in the world and it would still be fast.