I am trying to transfer several tables from a Sybase IQ database on one machine, to the same database on another machine (exact same schema and table layout etc).
To do this I'm using an insert from location statement:
insert into <local table> location <other machine> select * from mytablex
This works fine, but the problem is that it is desperately slow. I have a 1 gigabit connection between both machines, but the transfer rate is nowhere near that.
With a 1 gigabyte test file, it takes only 1 or 2 minutes to transfer it via ftp (just as a file, nothing to do with IQ). But I am only managing 100 gigabytes over 24 hours in IQ. That means that the transfer rate is more like 14 or 15 minutes for 1 gigabyte for the data going through Sybase IQ.
Is there any way I can speed this up?
I saw there is an option to change the packet size, but would that make a difference? Surely if the transfer is 7 times faster for a file the packet size can't be that much of a factor?
Thanks! :)
It appears from the documentation here and here that using insert into is a row by row operation, and not a bulk operation. This could explain the performance issues that you are seeing.
You may want to look at the bulk loading LOAD TABLE operation instead.