|
Thanks for the responses, I decided to just answer all of the questions in one large reply instead of 5 separate ones.
I am using transactions, especially since you cannot do inserts without transactions I believe. All of the inserts for each table are done in their own transactions (5 transactions for 5 tables).
I am not using embedded Firebird on my development machine at this time, although the end user software will be using embedded Firebird.
I am doing the inserts using a standard FBCommand object. An example of the code used is:
newCommand = new FbCommand("INSERT INTO Artist (ArtistId, ArtistName, DateAdded, DateModified, DateDeleted) " + "VALUES (@ArtistId, @ArtistName, @DateAdded, @DateModified, @DateDeleted);", newConnection, transaction); newCommand.Parameters.Add("@ArtistId", FbDbType.Integer); newCommand.Parameters.Add("@ArtistName", FbDbType.VarChar, 160); newCommand.Parameters.Add("@DateAdded", FbDbType.TimeStamp); newCommand.Parameters.Add("@DateModified", FbDbType.TimeStamp); newCommand.Parameters.Add("@DateDeleted", FbDbType.TimeStamp);
I then use an OleDbDataReader that has the data from the old Access Database. I go through a “while (reader.Read())” loop to get the data, and at the end of each iteration use a “newCommand.ExecuteNonQuery();” to execute the statement. When I have got all of the data from that table, I do a “transaction.Commit();” and then move onto the next table.
I have isolated just the actual inserts in my time calculation. I know that if I include the time for the application to start up that it would take a little longer. I have a button that I click that creates the database from a script, and the timer only starts after that is finished and the connections to the Access database and Firebird database have already been established.
I do have a Firewall and Antivirus running, but it is the same firewall that is running when I do the same inserts with Access. Any time delays from accessing the file system should then be the same whether I am using Access or Firebird.
Nothing in this database is user data at the time of installation. All of it is either on their installation CD or downloadable installation file on our website. A 10 MB zipped FDB file is fine for a CD, but it is rather large for a 56k modem user. By downloading a compressed binary file we can get it to under 1 MB, just like we did with our old Access database. Extracting the data from this compressed file takes less than a minute, but unfortunately in Firebird the insert statements are what is taking the longest time.
Carlos, I do not know what you meant by using a prepared command, or creating a new one. By prepared command, do you mean do I have a all of the “INSERT INTO …” commands in a text file somewhere? If that is the case, then no I do not have that. I guess we could go that route if necessary though.
I have not tried to deactivate indexes, as I do not know how to do this. Ill check my firebird book and see if that can speed it up.
I guess my main question is why it would be so quick to insert into an old database engine like Access 97, but it requires tweaking to get Firebird to get the same performance. Both databases use the same indexes. Maybe DAO 3.51 was just a very fast engine, and all of the extra features of Firebird end up slowing down batch inserts.
Thanks for your help,
Kyle
-----Original Message-----
My company is upgrading an old piece of software that is written from within Access 97 (using its built in VBA), and completely rewriting it using C# and Firebird. The reason that we chose Firebird is its very small distributable footprint and our initial speed tests did show it to be a fast database engine. The problem we are having now is the speed of inserting data into a Firebird database.
The database for our software has about 5 main tables which have a sum of about 400,000 records in total. The database itself is about 43.5 MB in size, which is a little smaller than the 50 MB Access database we are converting it from. Our main concern is the download time for 56k modem users, since this file only compresses to just under 10MB using standard zipping.
To fix this in Access 97 we wrote our own compression for our data. Because the compression was built with our specific data in mind, the data file was 832 KB (down from 14.5 MB using Windows Zip). During the program’s installation we wound uncompress it; a process that added about 30 seconds to 1 minute onto the installation time. This used standard DAO to do the inserting of the data into the database.
With Firebird, using the .Net 2.0 provider, it takes much longer. Using FBCommand Insert statements, along with parameters for all inserted fields, it take over 5 minutes on my very fast computer. This could take possibly take up to 10 minutes on an average computer. While this would still save 56k users time compared to downloading an uncompressed file from the internet (saving about 40 minutes), it would not be acceptable for our broadband users. Also, reinstallation and upgrading would take an unacceptable amount of time. People usually do not like software upgrades to be slower.
Are there any techniques for inserting large numbers of records with Firebird? I just cant see how Access 97, a 9 year old piece of software, can outperform Firebird by such leaps and bounds.
Thanks you for any help you can give,
Kyle
|
------------------------------------------------------------------------- Using Tomcat but need to do more? Need to support web services, security? Get stuff done quickly with pre-integrated technology to make your job easier Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
_______________________________________________ Firebird-net-provider mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
