i m creating 100GB database on sybase . for inserting the data in the table m using bcp utility the schema of database was created using the script . on the tables of database primary keys are already defined before the insertion of data. the device in which database is present is of 200 GB . for insertion of data m using the file with extension .dat . for the database i have selected shutdown recovery type.
Problem :- data loading in this data base is too slow i m loading the first table in it, which is of 2 GB in size . it took more than 2 days to load this table with 2 GB of data.
plz can any body help me how can i get the insertion faster than this. because with this speed it will take more than 50 days to insert the data of 100 GB.
When you are trying to BCP in data in table with indexes..syabse uses slow BCP.
1. Drop indexes and then run BCP. When BCP completes, re-create indexes.
2. If doing remotely, increase the packet size in BCP command.
hi , thanks for the suggestion, well i haven't tried this because the previous running query is not finished yet.
i have got one more problem in this database is that after creating this database when i m restarting the services of sybase server from the start->services->SYbase SQL server. this restart is taking too much time nearly an hour or more.
i didn't understand why this much time is taken for starting the server.
is this time taking is normal or there is some problem exists.
I guess your problem is because of the non logged nature of bcp. actully bcp is a non logged operation in which data recovery is not guranteed after restart. So the solution is as below.
1. Drop all the indexes,constraints from all the tables.
2. do the bcp in of all the tables.
3. create the indexes and constraints on all the tables.
4. take a full dump of the database.
5. run checkpoint on the database
6. restart the server in no recovery mode for that database.
7. if data is not available load the dump what you had taken previously.
8. change the status value for the databse in sysdatabases table to 12(to change it from no recovery to recovery) and restart the server.
This time niether your restart will take much time nor the data will be lost.