|
During a migration of a database from version 9 to version 12 I performed the following: Opened Sybase Central 9. Connected to version 9 running DB. Unloaded the database to a file reload.sql using Unload and Reload statements as the options. Disconnected version 9 running DB and shut it down. Opened Sybase Central 12. Created new database using all defaults except "Ignore trailing spaces." Set the public option for our OEM connection code. Disconnected SC 12 and shut down db. Started db using shortcut to dbsrv12.exe followed only by the path to the database and a -n for the unique name I wanted to assign. Connected to the database with ISQL. Now here is where the behavior changes: If I open the entire reload.sql file in ISQL and execute, most statements take .3 or .4 seconds for execution time. The whole thing takes around 1 to 1.5 hours. If I open the reload file in a text editor and copy and paste into ISQL section by section, the same statements that were taking .3 or .4 take .02 to .03 seconds to execute. The whole thing takes about 15 minutes. First question - Is the above the correct procedure to move a version 9 database to version 12 or is there a better way? Second question - If the above is appropriate, why is there such a difference in speed and is there anything to be done about it other than copying and pasting by section? (My guess is that the optimizer is better able optimize when all the statements are similar as in the copy and paste scenario.) Part II Ran the upgrade again today using the suggestions below and all was well. I ended up with a mix of Bruce's and Karim's suggestions as I had to create the database fresh outside the process in order to set our connection option, then used Karim's process to unload to an existing database. This ran much better than copy and paste. |
|
If you are migrating a v9 db to a v12 db using Sybase Central, then you are probably better off using: Tools->SQL Anywhere->Unload Database click the "unload database running on an earlier version of the server...", fill in the uid, pwd and file location of your v9 db (making sure it is not currently running anywhere) and click next. On the next screen click on "unload and reload into a new database" and click next Fill in the next screen with the name and location of your new v12 database file along with setting any of the other settings that you want and click next On the last screen click on unload structure and data, set order data by primary key and click finish. You should then find that the wizard will unload your old v9 db, create your new v12 db and unload/reload your schema and data from v9 to v12 without any intermediate files or the need for dbisql. Try that and see if the performance of that process is more reasonable. |
|
The method I would suggest for rebuilding the database would be:
For example: dbunload -c "uid=DBA;pwd=XXX;dbf=v9.db" -ac "uid=DBA;pwd=sql;dbf=v12.db" |
|
In addition to Bruce's suggestion: Besides using the command line tools (which I would prefer, too, in order to get reproducable steps), I would further suggest to use ALTER DBSPACE to make the new SA12 db file(s) big enough that they don't have to be growing step by step during the reload. - A defragmentation tool like SysInternals's CONTIG may be helpful, too. I usually would do several runs in a test environment, and the first runs may give a feeling how big your database is going to be. Note: Newer versions may lead to smaller db files because of changes in the architecture (e.g. relating to indexing). I would possibly do the first run with Sybase Central (as Karim says) and use the command line the Wizard generates (in his final page) as a starting point - a nice improvement in SA12, by the way:). |
Prior to your 2nd test, did you erase the database and then initialize a new one, OR did you do something else?
@Chris - Yes I started over to make sure I did not miss anything.
Remember to set the cache size on your target database. The default will be pretty small for a blank database and it takes time for the cache size to adapt. I don't think this can explain the discrepancy your are seeing but it may improve the performance in either case.
Is there anything funky, er, interesting about the hardware and/or OS? SAN/NAS? VM?
@Breck: The first time I noticed it I was migrating a client that was using Win 2008 on VMWare, not sure what the storage back end was. I chalked it up to virtual performance, but then I tried it on our physical test server (win2008, internal drives on RAID) and then again on my laptop with the same results.