Re: [Firebird-net-provider] Questions with performance metrics doing large inserts into DB

[Edward M. <Eme...@nc...>]

Hello All,

 

I have to develop an application that will move "old/stale" data from certain tables to another FB DB. We already have an existing application that did something similar to this, but his application is written using Delphi 5 and we are a .NET shop and wanted to develop newer applications using .NET technologies so we can reuse our developer resources. Little by Little we have been migrating off from Delphi5 to .NET.

 

In our shop we are running various instances of FB on 2.14 Classic on CentOS 5.6. Our Database is larger than 250GB. 

 

In past .NET projects I have used Dapper dot Net and thought that this might fit the requirements. 

 

We developed a working prototype of what we wanted I had our testers run the application to see what they thought. To my dismay, they informed me that the performance was terrible.

In some cases we need to archive millions of rows to the other Database. And it seemed using dapper was not giving us acceptable results. The users said that using the old Delphi applications was quicker when archiving data ( that Delphi application has functionality that also archives different data to other Databases).

 

I wanted to have a baseline test so we can compare apples to apples. 

 

I trimmed down the prototype to eliminate Dapper dot net and use straight ADO.NET  for the Inserts using the latest Firebird.NET provider on .NET 4.5.1. 

                In the application once the data was retrieved I read it one row at a time, because trying to read in over a million rows into memory would cause Out of Memory issues. A sample of the code I am using is shown below. As I looped over the results, I reported on every 1000 rows and calculated elapsed times every 5000 rows. Running this application from various computers in our infrastructure to various target DBs the best performance I got was 19 seconds for 5000 rows. 

 

I then put on my Delphi Hat On and created a small app that did similar to what the .NET app is doing and the Delphi app’s performance blew away the .NET performance with a consistent 5000 rows @ 4 seconds. This is Delphi5! From the year 1999. I also have to add that I am using the FIBPlus data components from devrace. 

 

Below is an edited version of my .NET code.

 

query = @"Select *** Here is my Select Query from the Source DB *** ";

 

// This is using dapper.net to retrieve the rows I need.

var en = db_source.Query<TABLE_A_DTO>(query, new { ARCHIVE_SET_ID = m_archiveSetId, ARCHIVE_DATA_TYPE = _tableName }, srcTransaction, buffered: false);

int totalUpdated = 0;

JobStartTime = DateTime.Now;

try

      {

            using (FbConnection db_target = ((FbConnection)GetConnection(targetDB)))

            {

                  FbTransaction transaction = db_target.BeginTransaction();

                  m_isInTransaction = true;

 

                  using (FbCommand command = new FbCommand(_updateSQL, db_target, transaction))

                  {

                  

                        command.Parameters.Add("TABLE_A_ID", FbDbType.Integer);

                        

                              // There are 86 fields in the result set so i do this 85 more times

                     

                        command.Prepare();

 

                        StepStartTime = DateTime.Now;

                        _logger.Write("ARCHIVING of " + _tableName + " Started", 2);

                        // I get the Enumerator of the results so I can iterate over them

                        _iEnumerator = en.GetEnumerator(); 

                        while (_iEnumerator.MoveNext())

                        {

                              rowsRead++;

                              var rec = ((TABLE_A_DTO)_iEnumerator.Current);

                              command.Parameters[0].Value = rec.TABLE_A_ID;

                                    

                                    // I do this 85 more times for each parameter

 

                              rowsAffected = command.ExecuteNonQuery();

                              totalUpdated = rowsAffected + totalUpdated;

 

                              if (rowsRead % 1000 == 0)

                                    Console.Write(totalUpdated.ToString() + "\r");

 

                              if (rowsRead % m_recordBufferCount == 0)

                              {

                                    _logger.Write(string.Format("Archived... Rows Archived = {0}, Elasped Time {1}", totalUpdated.ToString("N0"), Utils.GetElapsedTime(DateTime.Now - StepStartTime)), 3);

                                    StepStartTime = DateTime.Now;

                              }

                        }

 

Is the .NET Provider that slow? I am sure that the Database Configuration has something to do with the performance, but If that is true I expected that the Delphi Application show performance results on par with the .NET performance.

 

Am I doing something blatantly wrong code? I would hate to go back to my Boss and tell them that if performance is a factor we need to continue with Delphi.

 

If this open source .NET Provider is not the “fastest on the Block”, are there any other 3rd party Libraries that I can use with .NET that you would recommend? Even if it a commercial product?

 

 

Thanks,

 

Edward Mendez