firebird-net-provider — All about development of the MS .Net provider

[Firebird-net-provider] Error when reading from connection

[Marco C. <mc...@mc...>]

Hello,

I have an application that opens a Firebird connection and try to star a 
loop. In one computer the code runs fine and in another computer I get this 
message when this connection is in another server (Linux server). I tryed 
connections with and without pools with no difference. Firebird is 4.5, EDMX 
runs over ER6.1:

System.Data.Entity.Core.EntityCommandExecutionException: An error occurred 
while executing the command definition. See the inner exception for details. 
---> FirebirdSql.Data.FirebirdClient.FbException: Error reading data from 
the connection. 
---> FirebirdSql.Data.Common.IscException: Error reading data from the 
connection.
   em FirebirdSql.Data.Client.Managed.Version11.GdsStatement.Prepare(String 
commandText)
   em FirebirdSql.Data.FirebirdClient.FbCommand.Prepare(Boolean returnsSet)
   em 
FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteCommand(CommandBehavior b
ehavior, Boolean returnsSet)
   em 
FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteReader(CommandBehavior be
havior)
   --- Fim do rastreamento de pilha de exceções internas ---
   em 
FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteReader(CommandBehavior be
havior)
   em 
FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteDbDataReader(CommandBehav
ior behavior)
   em System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   em System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.
<Reader>b__c(DbCommand t, DbCommandInterceptionContext`1 c)
   em 
System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispat
ch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, 
TInterceptionContext interceptionContext, Action`3 executing, Action`3 
executed)
   em 
System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(
DbCommand command, DbCommandInterceptionContext interceptionContext)
   em 
System.Data.Entity.Internal.InterceptableDbCommand.ExecuteDbDataReader(Com
mandBehavior behavior)
   em System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   em 
System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.Exec
uteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
   --- Fim do rastreamento de pilha de exceções internas ---
   em 
System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.Exec
uteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
   em 
System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlan.Execute[
TResultType](ObjectContext context, ObjectParameterCollection 
parameterValues)
   em System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.
<GetResul
ts>b__6()
   em System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T]
(Func`1 func, IDbExecutionStrategy executionStrategy, Boolean 
startLocalTransaction, Boolean releaseConnectionOnSuccess)
   em System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.
<GetResults>b__5()
   em 
System.Data.Entity.Infrastructure.DefaultExecutionStrategy.Execute[TResult
](Func`1 operation)
   em System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 
forMergeOption)
   em System.Data.Entity.Core.Objects.ObjectQuery`1.
<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b__0()
   em System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
   em System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   em System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   em McSoft.Regras.BI.ConsultasOLAP.Consulta(Int32 _filial, DateTime _from, 
DateTime _to, Nullable`1 _produto, String _marca)

****************

Well, where can I start the issue solve?

Thanks,
Marco Casrto

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

[Jiří Č. <di...@ci...>]

There is still some room for improvement on network level. The packets are not batches as much as it could be for server to still understand it. Dmitri Y. reported it to me some months ago.


It just needs a lot of time with Wireshark and tweaking the socket until it's same as with fbclient.dll.


Maybe somebody could help me with that as well.

--
Mgr. Jiří Činčura
Independent IT Specialist

________________________________________
From: Jiří Činčura <di...@ci...>
Sent: Sunday, August 10, 2014 3:23:14 PM
To: For users and developers of the Firebird .NET providers
Subject: Re: [Firebird-net-provider] Questions with     performance     metrics doing large inserts into DB

It's roundtrip per some records (depends on size of data), not each record.

Putting the records into DataTable will not be faster in any way, as it uses the DataReader internally.

--
Mgr. Jiří Činčura
Independent IT Specialist

________________________________________
From: Paulo Gomes <pau...@sa...>
Sent: Saturday, August 09, 2014 4:30:41 PM
To: For users and developers of the Firebird .NET providers
Subject: Re: [Firebird-net-provider] Questions with performance metrics doing large inserts into DB

Hi Edward,

I think you answered yourself, the enumerator uses a trip to the database for
each record.

You should try to break the data in smaller packages (100000 for example) and
use a DataAdapter to get the data into DataTable.
If possible also use several transactions (1 per Package) to keep the memory
usage in control.
If performance is a must you can always use deal with different Threads(1 for
read and 1 for write) , though you will probably add a wanted dead or alive
sign  on my name for mentioning it.

Note: the nº of records should depend on their average size

Regards
Paulo Gomes


Em 09-08-2014 06:56, Edward Mendez escreveu:
>
>> You should start transaction explicitly. Else it's one transaction per command
>> and in a batch processing that goes out of hand really quickly.
> I agree with you 100%. The part that I hadn't given much thought was the Isolation levels to pass as the parameter into the transaction object.
>
>
>> I would rather write correct algorithm than spent hours on micro-optimizing
>> loops. BTW did you know asm loops are faster than Delphi and .NET loops?
> Ah ASM, that is definitely a blast from the past.
>
> In trying to eliminate certain bottlenecks, I moved a small sample of the source data to a local FB db and ran the .NET test and there it was practically instantaneous reading the data. I think the Network Latency on my corporate network is another factor.
> Over the weekend, I will move a more sizable sampling to my local DB and attempt the test with the Reads and Write. I downloaded Red-gate (trial) and will profile the App. I will keep everyone posted on the any findings.
>
> Thanks,
>
> Edward Mendez
>
>
> ------------------------------------------------------------------------------
> _______________________________________________
> Firebird-net-provider mailing list
> Fir...@li...
> https://lists.sourceforge.net/lists/listinfo/firebird-net-provider


------------------------------------------------------------------------------
_______________________________________________
Firebird-net-provider mailing list
Fir...@li...
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
------------------------------------------------------------------------------
_______________________________________________
Firebird-net-provider mailing list
Fir...@li...
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider

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

[Jiří Č. <di...@ci...>]

There is still some room for improvement on network level. The packets are not batches as much as it could be for server to still understand it. Dmitri Y. reported it to me some months ago.

It just needs a lot of time with Wireshark and tweaking the socket until it's same as with fbclient.dll.

Maybe somebody could help me with that as well.

--
Mgr. Jiří Činčura
Independent IT Specialist
________________________________
From: Alexander Muylaert-Gelein <amu...@ho...>
Sent: Friday, August 08, 2014 11:10:43 PM
To: fir...@li...urceforge.
Subject: Re: [Firebird-net-provider] Questions with performance metrics doing large inserts into DB

Hi

Attached some examples of transactions properly set.

If you can put the reader in "readonly"  and the writer in "WriteNoUndo", you gain some speed.  But euh, no magic to expect here.  It might be interesting to profile it though and post the results back.


public static class Transaction
 {
     private static FbTransactionOptions s_WriteTransaction = new FbTransactionOptions()
     {
         TransactionBehavior = FbTransactionBehavior.Write |
                               FbTransactionBehavior.ReadCommitted |
                               FbTransactionBehavior.RecVersion |
                               FbTransactionBehavior.Wait,
         WaitTimeout = new TimeSpan(0, 0, 0, 10) //seconds
     };

     public static FbTransactionOptions WriteTransactions
     {
         get
         {
             return s_WriteTransaction;
         }
     }

     private static FbTransactionOptions s_WriteNoUndoTransaction = new FbTransactionOptions()
     {
         TransactionBehavior = FbTransactionBehavior.Write |
                               FbTransactionBehavior.ReadCommitted |
                               FbTransactionBehavior.RecVersion |
                               FbTransactionBehavior.Wait |
                               FbTransactionBehavior.NoAutoUndo,
         WaitTimeout = new TimeSpan(0, 0, 0, 10) //seconds
     };

     public static FbTransactionOptions WriteNoUndoTransactions
     {
         get
         {
             return s_WriteTransaction;
         }
     }

     private static FbTransactionOptions s_ReadTransaction = new FbTransactionOptions()
     {
         TransactionBehavior = FbTransactionBehavior.Read |
                               FbTransactionBehavior.ReadCommitted |
                               FbTransactionBehavior.RecVersion |
                               FbTransactionBehavior.NoWait
     };

     public static FbTransactionOptions ReadTransactions
     {
         get
         {
             return s_ReadTransaction;
         }
     }


     public static FbTransaction BeginReadTransaction(this FbConnection aConnection)
     {
         return aConnection.BeginTransaction(s_ReadTransaction);
     }

     public static FbTransaction BeginWriteTransaction(this FbConnection aConnection)
     {
         return aConnection.BeginTransaction(s_WriteTransaction);
     }

     public static FbTransaction BeginWriteNoUndoTransaction(this FbConnection aConnection)
     {
         return aConnection.BeginTransaction(s_WriteTransaction);
     }
 }

I read somewhere that Looping in .NET is somewhat slower than in Delphi, but there are things you can do optimize the .NET loops.

Agreed, some tricks might be applied.  But that will bring you down from 18.7 seconds to 18.69998 seconds.  Optimizing loops is actually not the way to go.

What you could do is make a small test project.  with a few simple tables.  Add your simulations to it and make sure it compiles.  Then, if you have these minimal projects building, I would like to receive a copy and I can profile for you.

Maybe I can already see what is wrong.  Like Jiri mentioned.  .Net is slower then this FIB/FBClient (c++) dll.  But let us assume that is 10% overhead.  So 4 seconds delphi => 5 seconds .net is fine for me.  18 seconds...  I'm interested in speeding things up then  :-)

thanks

a

________________________________
From: Eme...@nc...
To: fir...@li...
Date: Fri, 8 Aug 2014 09:41:03 -0400
Subject: Re: [Firebird-net-provider] Questions with performance metrics doing large inserts into DB


Alexander,



Thank you for the feedback.



>>Two more things...

>>

>>1.  Your transaction parameters please.  How do you create them, what settings?

To be honest I’ve never really thought about the transaction settings other than the default. Are there a specific settings I should be using for just reading “stale” data?

>>2.  Did you check your "source-read" logic.  Cound you maybe fake data, so we know if it is the "insert" that is slow.  Maybe the materializing of the source record is slow or fetching it?

I was doing further testing last night and removed the insert logic to see if it was the reading of the data that was slowing it down. with the removal of the Insert Logic and just reading and looping for 5000 iterations, it still slower than Delphi’s results. For the read Logic I was using Dapper dot net and retrieving the results un-buffered (one row at a time). I then eliminated Dapper and it still was slower than Delphi. I then tried an OleDB Provider and that was a little faster than the .Net provider, but still slower than Delphi.



In retrospect I might have jumped the gun in blaming the Write performance of the .Net Provider. I think I need to try to optimize the read logic, then move on to the Write Logic.



I read somewhere that Looping in .NET is somewhat slower than in Delphi, but there are things you can do optimize the .NET loops.



Thank you,



Edward Mendez



From: Alexander Muylaert-Gelein [mailto:amu...@ho...]
Sent: Friday, August 8, 2014 1:54 AM
To: fir...@li...urceforge.
Subject: Re: [Firebird-net-provider] Questions with performance metrics doing large inserts into DB



Hi Edward



I'm also coming from a delphi background (using FIB) and we have ported/are already porting for 5 years our applications to .net.  I have noticed indeed that Delphi/fib is faster then .net provider.  But never in the magnitude of 500%.  It looked acceptable slower.



Your "write" code seems to be correct and pretty optimal.  Usually people recreate a command each time.  I've also done some profiling in the past and I've noticed that keeping a reference to the parameter doesn't help much.  A slightly slower method, but way less code is to "clear" the parameters and recreate them.   Once again, It is slightly slower, neglectible, but in your scenario 170 lines of code less.



using (command = new command){

  var par = command.Parameters

   while (! Eof){

       par.Clear();

       par.Add(Id);

       par.Add(Value);

       ...

   }

}



On the other hand Firebird is an open source database and also the .net provider.  Jiri (the guy supporting this) is getting a few bucks per month to support this.  We, as a company, sometimes sponsor these things by testing, benchmarking or lazy picking up the bill.  Since you have a testing environment up and running, you might walk the extra mile and help everybody by profiling a bit deeper?  This would benefit you, me and everybody.



Two more things...



1.  Your transaction parameters please.  How do you create them, what settings?

2.  Did you check your "source-read" logic.  Cound you maybe fake data, so we know if it is the "insert" that is slow.  Maybe the materializing of the source record is slow or fetching it?



Looking forward for tackling this thing.



thanks



a











________________________________

From: Eme...@nc...<mailto:Eme...@nc...>
To: fir...@li...<mailto:fir...@li...>
Date: Thu, 7 Aug 2014 18:14:43 -0400
Subject: Re: [Firebird-net-provider] Questions with performance metrics doing large inserts into DB

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



------------------------------------------------------------------------------ Want fast and easy access to all the code in your enterprise? Index and search up to 200,000 lines of code with a free copy of Black Duck Code Sight - the same software that powers the world's largest code search on Ohloh, the Black Duck Open Hub! Try it now. http://p.sf.net/sfu/bds
_______________________________________________ Firebird-net-provider mailing list Fir...@li... https://lists.sourceforge.net/lists/listinfo/firebird-net-provider

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

[Jiří Č. <di...@ci...>]

It's roundtrip per some records (depends on size of data), not each record.

Putting the records into DataTable will not be faster in any way, as it uses the DataReader internally.

--
Mgr. Jiří Činčura
Independent IT Specialist

________________________________________
From: Paulo Gomes <pau...@sa...>
Sent: Saturday, August 09, 2014 4:30:41 PM
To: For users and developers of the Firebird .NET providers
Subject: Re: [Firebird-net-provider] Questions with performance metrics doing large inserts into DB

Hi Edward,

I think you answered yourself, the enumerator uses a trip to the database for
each record.

You should try to break the data in smaller packages (100000 for example) and
use a DataAdapter to get the data into DataTable.
If possible also use several transactions (1 per Package) to keep the memory
usage in control.
If performance is a must you can always use deal with different Threads(1 for
read and 1 for write) , though you will probably add a wanted dead or alive
sign  on my name for mentioning it.

Note: the nº of records should depend on their average size

Regards
Paulo Gomes


Em 09-08-2014 06:56, Edward Mendez escreveu:
>
>> You should start transaction explicitly. Else it's one transaction per command
>> and in a batch processing that goes out of hand really quickly.
> I agree with you 100%. The part that I hadn't given much thought was the Isolation levels to pass as the parameter into the transaction object.
>
>
>> I would rather write correct algorithm than spent hours on micro-optimizing
>> loops. BTW did you know asm loops are faster than Delphi and .NET loops?
> Ah ASM, that is definitely a blast from the past.
>
> In trying to eliminate certain bottlenecks, I moved a small sample of the source data to a local FB db and ran the .NET test and there it was practically instantaneous reading the data. I think the Network Latency on my corporate network is another factor.
> Over the weekend, I will move a more sizable sampling to my local DB and attempt the test with the Reads and Write. I downloaded Red-gate (trial) and will profile the App. I will keep everyone posted on the any findings.
>
> Thanks,
>
> Edward Mendez
>
>
> ------------------------------------------------------------------------------
> _______________________________________________
> Firebird-net-provider mailing list
> Fir...@li...
> https://lists.sourceforge.net/lists/listinfo/firebird-net-provider


------------------------------------------------------------------------------
_______________________________________________
Firebird-net-provider mailing list
Fir...@li...
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider

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

[Paulo G. <pau...@sa...>]

Hi Edward,

I think you answered yourself, the enumerator uses a trip to the database for 
each record.

You should try to break the data in smaller packages (100000 for example) and 
use a DataAdapter to get the data into DataTable.
If possible also use several transactions (1 per Package) to keep the memory 
usage in control.
If performance is a must you can always use deal with different Threads(1 for 
read and 1 for write) , though you will probably add a wanted dead or alive 
sign  on my name for mentioning it.

Note: the nº of records should depend on their average size

Regards
Paulo Gomes


Em 09-08-2014 06:56, Edward Mendez escreveu:
>   
>> You should start transaction explicitly. Else it's one transaction per command
>> and in a batch processing that goes out of hand really quickly.
> I agree with you 100%. The part that I hadn't given much thought was the Isolation levels to pass as the parameter into the transaction object.
>
>   
>> I would rather write correct algorithm than spent hours on micro-optimizing
>> loops. BTW did you know asm loops are faster than Delphi and .NET loops?
> Ah ASM, that is definitely a blast from the past.
>
> In trying to eliminate certain bottlenecks, I moved a small sample of the source data to a local FB db and ran the .NET test and there it was practically instantaneous reading the data. I think the Network Latency on my corporate network is another factor.
> Over the weekend, I will move a more sizable sampling to my local DB and attempt the test with the Reads and Write. I downloaded Red-gate (trial) and will profile the App. I will keep everyone posted on the any findings.
>
> Thanks,
>
> Edward Mendez
>
>
> ------------------------------------------------------------------------------
> _______________________________________________
> Firebird-net-provider mailing list
> Fir...@li...
> https://lists.sourceforge.net/lists/listinfo/firebird-net-provider

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

[Norbert S. G. <ns...@te...>]

Hello there, 
can you send me your delphi dotnet project and I can understand what takes 18 seconds-) 
I D1, D3, D5, D7 & VS2005-> VS2013. 
I develop mainly in DotNet (RemObjects) and just so happens that, in normal use, dotnet is better than an application server delphi

 

 

   Norbert Saint Georges

 

   TetraSys Oy

   Bergantie 69, FI-02540 Kylmälä

   Tel. : +358 (0) 400 27 25 18

   E- mail : ns...@te... <mailto:tar...@te...> 

 

De : Alexander Muylaert-Gelein [mailto:amu...@ho...] 
Envoyé : samedi 9 août 2014 00:11
À : fir...@li...urceforge.
Objet : Re: [Firebird-net-provider] Questions with performance metrics doing large inserts into DB

 

Hi 

 

Attached some examples of transactions properly set.

 

If you can put the reader in "readonly"  and the writer in "WriteNoUndo", you gain some speed.  But euh, no magic to expect here.  It might be interesting to profile it though and post the results back.

 

public static class Transaction
 {
     private static FbTransactionOptions s_WriteTransaction = new FbTransactionOptions()
     {
         TransactionBehavior = FbTransactionBehavior.Write |
                               FbTransactionBehavior.ReadCommitted |
                               FbTransactionBehavior.RecVersion |
                               FbTransactionBehavior.Wait,
         WaitTimeout = new TimeSpan(0, 0, 0, 10) //seconds
     };
 
     public static FbTransactionOptions WriteTransactions
     {
         get
         {
             return s_WriteTransaction;
         }
     }
 
     private static FbTransactionOptions s_WriteNoUndoTransaction = new FbTransactionOptions()
     {
         TransactionBehavior = FbTransactionBehavior.Write |
                               FbTransactionBehavior.ReadCommitted |
                               FbTransactionBehavior.RecVersion |
                               FbTransactionBehavior.Wait |
                               FbTransactionBehavior.NoAutoUndo,
         WaitTimeout = new TimeSpan(0, 0, 0, 10) //seconds
     };
 
     public static FbTransactionOptions WriteNoUndoTransactions
     {
         get
         {
             return s_WriteTransaction;
         }
     }
 
     private static FbTransactionOptions s_ReadTransaction = new FbTransactionOptions()
     {
         TransactionBehavior = FbTransactionBehavior.Read |
                               FbTransactionBehavior.ReadCommitted |
                               FbTransactionBehavior.RecVersion |
                               FbTransactionBehavior.NoWait
     };
 
     public static FbTransactionOptions ReadTransactions
     {
         get
         {
             return s_ReadTransaction;
         }
     }
 
 
     public static FbTransaction BeginReadTransaction(this FbConnection aConnection)
     {
         return aConnection.BeginTransaction(s_ReadTransaction);
     }
 
     public static FbTransaction BeginWriteTransaction(this FbConnection aConnection)
     {
         return aConnection.BeginTransaction(s_WriteTransaction);
     }
 
     public static FbTransaction BeginWriteNoUndoTransaction(this FbConnection aConnection)
     {
         return aConnection.BeginTransaction(s_WriteTransaction);
     }
 }

 

I read somewhere that Looping in .NET is somewhat slower than in Delphi, but there are things you can do optimize the .NET loops.

 

Agreed, some tricks might be applied.  But that will bring you down from 18.7 seconds to 18.69998 seconds.  Optimizing loops is actually not the way to go.  

 

What you could do is make a small test project.  with a few simple tables.  Add your simulations to it and make sure it compiles.  Then, if you have these minimal projects building, I would like to receive a copy and I can profile for you.

 

Maybe I can already see what is wrong.  Like Jiri mentioned.  .Net is slower then this FIB/FBClient (c++) dll.  But let us assume that is 10% overhead.  So 4 seconds delphi => 5 seconds .net is fine for me.  18 seconds...  I'm interested in speeding things up then  :-)

 

thanks

 

a

 

________________________________

From: Eme...@nc...
To: fir...@li...
Date: Fri, 8 Aug 2014 09:41:03 -0400
Subject: Re: [Firebird-net-provider] Questions with performance metrics doing large inserts into DB

Alexander,

 

Thank you for the feedback.

 

>>Two more things...  

>> 

>>1.  Your transaction parameters please.  How do you create them, what settings?

To be honest I've never really thought about the transaction settings other than the default. Are there a specific settings I should be using for just reading "stale" data?

>>2.  Did you check your "source-read" logic.  Cound you maybe fake data, so we know if it is the "insert" that is slow.  Maybe the materializing of the source record is slow or fetching it?

I was doing further testing last night and removed the insert logic to see if it was the reading of the data that was slowing it down. with the removal of the Insert Logic and just reading and looping for 5000 iterations, it still slower than Delphi's results. For the read Logic I was using Dapper dot net and retrieving the results un-buffered (one row at a time). I then eliminated Dapper and it still was slower than Delphi. I then tried an OleDB Provider and that was a little faster than the .Net provider, but still slower than Delphi. 

 

In retrospect I might have jumped the gun in blaming the Write performance of the .Net Provider. I think I need to try to optimize the read logic, then move on to the Write Logic.

 

I read somewhere that Looping in .NET is somewhat slower than in Delphi, but there are things you can do optimize the .NET loops. 

 

Thank you,

 

Edward Mendez

 

From: Alexander Muylaert-Gelein [mailto:amu...@ho...] 
Sent: Friday, August 8, 2014 1:54 AM
To: fir...@li...urceforge.
Subject: Re: [Firebird-net-provider] Questions with performance metrics doing large inserts into DB

 

Hi Edward

 

I'm also coming from a delphi background (using FIB) and we have ported/are already porting for 5 years our applications to .net.  I have noticed indeed that Delphi/fib is faster then .net provider.  But never in the magnitude of 500%.  It looked acceptable slower.

 

Your "write" code seems to be correct and pretty optimal.  Usually people recreate a command each time.  I've also done some profiling in the past and I've noticed that keeping a reference to the parameter doesn't help much.  A slightly slower method, but way less code is to "clear" the parameters and recreate them.   Once again, It is slightly slower, neglectible, but in your scenario 170 lines of code less.  

 

using (command = new command){

  var par = command.Parameters

   while (! Eof){

       par.Clear();

       par.Add(Id);

       par.Add(Value);

       ...

   }

}

 

On the other hand Firebird is an open source database and also the .net provider.  Jiri (the guy supporting this) is getting a few bucks per month to support this.  We, as a company, sometimes sponsor these things by testing, benchmarking or lazy picking up the bill.  Since you have a testing environment up and running, you might walk the extra mile and help everybody by profiling a bit deeper?  This would benefit you, me and everybody.  

 

Two more things...  

 

1.  Your transaction parameters please.  How do you create them, what settings?

2.  Did you check your "source-read" logic.  Cound you maybe fake data, so we know if it is the "insert" that is slow.  Maybe the materializing of the source record is slow or fetching it?

 

Looking forward for tackling this thing.

 

thanks

 

a

 

 

 

 

 

________________________________

From: Eme...@nc...
To: fir...@li...
Date: Thu, 7 Aug 2014 18:14:43 -0400
Subject: Re: [Firebird-net-provider] Questions with performance metrics doing large inserts into DB

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 

 


------------------------------------------------------------------------------ Want fast and easy access to all the code in your enterprise? Index and search up to 200,000 lines of code with a free copy of Black Duck Code Sight - the same software that powers the world's largest code search on Ohloh, the Black Duck Open Hub! Try it now. http://p.sf.net/sfu/bds
_______________________________________________ Firebird-net-provider mailing list Fir...@li... https://lists.sourceforge.net/lists/listinfo/firebird-net-provider

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

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

 
> You should start transaction explicitly. Else it's one transaction per command
> and in a batch processing that goes out of hand really quickly.

I agree with you 100%. The part that I hadn't given much thought was the Isolation levels to pass as the parameter into the transaction object.

 
> I would rather write correct algorithm than spent hours on micro-optimizing
> loops. BTW did you know asm loops are faster than Delphi and .NET loops?

Ah ASM, that is definitely a blast from the past. 

In trying to eliminate certain bottlenecks, I moved a small sample of the source data to a local FB db and ran the .NET test and there it was practically instantaneous reading the data. I think the Network Latency on my corporate network is another factor. 
Over the weekend, I will move a more sizable sampling to my local DB and attempt the test with the Reads and Write. I downloaded Red-gate (trial) and will profile the App. I will keep everyone posted on the any findings. 

Thanks,

Edward Mendez

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

[Jiří Č. <di...@ci...>]

> To be honest I’ve never really thought about the transaction settings other 
> than the default. Are there a specific settings I should be using for just reading “stale” data?

You should start transaction explicitly. Else it's one transaction per command and in a batch processing that goes out of hand really quickly.

> I read somewhere that Looping in .NET is somewhat slower than in Delphi, 
> but there are things you can do optimize the .NET loops. 

I would rather write correct algorithm than spent hours on micro-optimizing loops. BTW did you know asm loops are faster than Delphi and .NET loops?

-- 
Mgr. Jiří Činčura
Independent IT Specialist

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

[Alexander Muylaert-G. <amu...@ho...>]

Hi 
Attached some examples of transactions properly set.
If you can put the reader in "readonly"  and the writer in "WriteNoUndo", you gain some speed.  But euh, no magic to expect here.  It might be interesting to profile it though and post the results back.
public static class Transaction
 {
     private static FbTransactionOptions s_WriteTransaction = new FbTransactionOptions()
     {
         TransactionBehavior = FbTransactionBehavior.Write |
                               FbTransactionBehavior.ReadCommitted |
                               FbTransactionBehavior.RecVersion |
                               FbTransactionBehavior.Wait,
         WaitTimeout = new TimeSpan(0, 0, 0, 10) //seconds
     };
 
     public static FbTransactionOptions WriteTransactions
     {
         get
         {
             return s_WriteTransaction;
         }
     }
 
     private static FbTransactionOptions s_WriteNoUndoTransaction = new FbTransactionOptions()
     {
         TransactionBehavior = FbTransactionBehavior.Write |
                               FbTransactionBehavior.ReadCommitted |
                               FbTransactionBehavior.RecVersion |
                               FbTransactionBehavior.Wait |
                               FbTransactionBehavior.NoAutoUndo,
         WaitTimeout = new TimeSpan(0, 0, 0, 10) //seconds
     };
 
     public static FbTransactionOptions WriteNoUndoTransactions
     {
         get
         {
             return s_WriteTransaction;
         }
     }
 
     private static FbTransactionOptions s_ReadTransaction = new FbTransactionOptions()
     {
         TransactionBehavior = FbTransactionBehavior.Read |
                               FbTransactionBehavior.ReadCommitted |
                               FbTransactionBehavior.RecVersion |
                               FbTransactionBehavior.NoWait
     };
 
     public static FbTransactionOptions ReadTransactions
     {
         get
         {
             return s_ReadTransaction;
         }
     }
 
 
     public static FbTransaction BeginReadTransaction(this FbConnection aConnection)
     {
         return aConnection.BeginTransaction(s_ReadTransaction);
     }
 
     public static FbTransaction BeginWriteTransaction(this FbConnection aConnection)
     {
         return aConnection.BeginTransaction(s_WriteTransaction);
     }
 
     public static FbTransaction BeginWriteNoUndoTransaction(this FbConnection aConnection)
     {
         return aConnection.BeginTransaction(s_WriteTransaction);
     }
 }
I read somewhere that Looping in .NET is somewhat slower than in Delphi, but there are things you can do optimize the .NET loops.
Agreed, some tricks might be applied.  But that will bring you down from 18.7 seconds to 18.69998 seconds.  Optimizing loops is actually not the way to go.  
What you could do is make a small test project.  with a few simple tables.  Add your simulations to it and make sure it compiles.  Then, if you have these minimal projects building, I would like to receive a copy and I can profile for you.
Maybe I can already see what is wrong.  Like Jiri mentioned.  .Net is slower then this FIB/FBClient (c++) dll.  But let us assume that is 10% overhead.  So 4 seconds delphi => 5 seconds .net is fine for me.  18 seconds...  I'm interested in speeding things up then  :-)
thanks
a
From: Eme...@nc...
To: fir...@li...
Date: Fri, 8 Aug 2014 09:41:03 -0400
Subject: Re: [Firebird-net-provider] Questions with performance metrics	doing large inserts into DB

Alexander, Thank you for the feedback. >>Two more things...  >> >>1.  Your transaction parameters please.  How do you create them, what settings?To be honest I’ve never really thought about the transaction settings other than the default. Are there a specific settings I should be using for just reading “stale” data?>>2.  Did you check your "source-read" logic.  Cound you maybe fake data, so we know if it is the "insert" that is slow.  Maybe the materializing of the source record is slow or fetching it?I was doing further testing last night and removed the insert logic to see if it was the reading of the data that was slowing it down. with the removal of the Insert Logic and just reading and looping for 5000 iterations, it still slower than Delphi’s results. For the read Logic I was using Dapper dot net and retrieving the results un-buffered (one row at a time). I then eliminated Dapper and it still was slower than Delphi. I then tried an OleDB Provider and that was a little faster than the .Net provider, but still slower than Delphi.  In retrospect I might have jumped the gun in blaming the Write performance of the .Net Provider. I think I need to try to optimize the read logic, then move on to the Write Logic. I read somewhere that Looping in .NET is somewhat slower than in Delphi, but there are things you can do optimize the .NET loops.  Thank you, Edward Mendez From: Alexander Muylaert-Gelein [mailto:amu...@ho...] 
Sent: Friday, August 8, 2014 1:54 AM
To: fir...@li...urceforge.
Subject: Re: [Firebird-net-provider] Questions with performance metrics doing large inserts into DB Hi Edward I'm also coming from a delphi background (using FIB) and we have ported/are already porting for 5 years our applications to .net.  I have noticed indeed that Delphi/fib is faster then .net provider.  But never in the magnitude of 500%.  It looked acceptable slower. Your "write" code seems to be correct and pretty optimal.  Usually people recreate a command each time.  I've also done some profiling in the past and I've noticed that keeping a reference to the parameter doesn't help much.  A slightly slower method, but way less code is to "clear" the parameters and recreate them.   Once again, It is slightly slower, neglectible, but in your scenario 170 lines of code less.   using (command = new command){  var par = command.Parameters   while (! Eof){       par.Clear();       par.Add(Id);       par.Add(Value);       ...   }} On the other hand Firebird is an open source database and also the .net provider.  Jiri (the guy supporting this) is getting a few bucks per month to support this.  We, as a company, sometimes sponsor these things by testing, benchmarking or lazy picking up the bill.  Since you have a testing environment up and running, you might walk the extra mile and help everybody by profiling a bit deeper?  This would benefit you, me and everybody.   Two more things...   1.  Your transaction parameters please.  How do you create them, what settings?2.  Did you check your "source-read" logic.  Cound you maybe fake data, so we know if it is the "insert" that is slow.  Maybe the materializing of the source record is slow or fetching it? Looking forward for tackling this thing. thanks a     From: Eme...@nc...
To: fir...@li...
Date: Thu, 7 Aug 2014 18:14:43 -0400
Subject: Re: [Firebird-net-provider] Questions with performance metrics doing large inserts into DBHello 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  
------------------------------------------------------------------------------
Want fast and easy access to all the code in your enterprise? Index and
search up to 200,000 lines of code with a free copy of Black Duck
Code Sight - the same software that powers the world's largest code
search on Ohloh, the Black Duck Open Hub! Try it now.
http://p.sf.net/sfu/bds
_______________________________________________
Firebird-net-provider mailing list
Fir...@li...
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider 		 	   		  

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

[Jiří Č. <di...@ci...>]

Yep, same for me.

--
Mgr. Jiří Činčura
Independent IT Specialist

From: Alexander Muylaert-Gelein [mailto:amu...@ho...]
Sent: Friday, August 8, 2014 8:55 PM
To: fir...@li...urceforge.
Subject: Re: [Firebird-net-provider] Questions with performance metrics doing large inserts into DB

I personally really like the one from red-gate

but the visual studio profiler is also good.

a
> From: Eme...@nc...<mailto:Eme...@nc...>
> To: fir...@li...<mailto:fir...@li...>
> Date: Fri, 8 Aug 2014 09:51:52 -0400
> Subject: Re: [Firebird-net-provider] Questions with performance metrics doing large inserts into DB
>
> Jiri,
>
> I am not familiar with Profiling. Is there one that you can recommend?
>
> Thank you,
>
> Edward Mendez
>
> Also, are there any tests that I can
> > -----Original Message-----
> > From: Jiří Činčura [mailto:di...@ci...]
> > Sent: Friday, August 8, 2014 1:59 AM
> > To: For users and developers of the Firebird .NET providers
> > Subject: Re: [Firebird-net-provider] Questions with performance metrics
> > doing large inserts into DB
> >
> > Hi,
> >
> > Any chance to do profiling? I would expect .NET be slightly slower that .NET,
> > but this is x-times slower. That's weird. But finding the bottleneck might be
> > helpful.
> >
> >
> > --
> > Mgr. Jiří Činčura
> > Independent IT Specialist
> >
> >
> > ------------------------------------------------------------------------------
> > Want fast and easy access to all the code in your enterprise? Index and
> > search up to 200,000 lines of code with a free copy of Black Duck
> > Code Sight - the same software that powers the world's largest code
> > search on Ohloh, the Black Duck Open Hub! Try it now.
> > http://p.sf.net/sfu/bds
> > _______________________________________________
> > Firebird-net-provider mailing list
> > Fir...@li...<mailto:Fir...@li...>
> > https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
>
>
> ------------------------------------------------------------------------------
> Want fast and easy access to all the code in your enterprise? Index and
> search up to 200,000 lines of code with a free copy of Black Duck
> Code Sight - the same software that powers the world's largest code
> search on Ohloh, the Black Duck Open Hub! Try it now.
> http://p.sf.net/sfu/bds
> _______________________________________________
> Firebird-net-provider mailing list
> Fir...@li...<mailto:Fir...@li...>
> https://lists.sourceforge.net/lists/listinfo/firebird-net-provider

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

[Alexander Muylaert-G. <amu...@ho...>]

I personally really like the one from red-gate
but the visual studio profiler is also good.
a

> From: Eme...@nc...
> To: fir...@li...
> Date: Fri, 8 Aug 2014 09:51:52 -0400
> Subject: Re: [Firebird-net-provider] Questions with	performance	metrics	doing large inserts into DB
> 
> Jiri,
> 
> I am not familiar with Profiling. Is there one that you can recommend? 
> 
> Thank you,
> 
> Edward Mendez
> 
> Also, are there any tests that I can 
> > -----Original Message-----
> > From: Jiří Činčura [mailto:di...@ci...]
> > Sent: Friday, August 8, 2014 1:59 AM
> > To: For users and developers of the Firebird .NET providers
> > Subject: Re: [Firebird-net-provider] Questions with performance metrics
> > doing large inserts into DB
> > 
> > Hi,
> > 
> > Any chance to do profiling? I would expect .NET be slightly slower that .NET,
> > but this is x-times slower. That's weird. But finding the bottleneck might be
> > helpful.
> > 
> > 
> > --
> > Mgr. Jiří Činčura
> > Independent IT Specialist
> > 
> > 
> > ------------------------------------------------------------------------------
> > Want fast and easy access to all the code in your enterprise? Index and
> > search up to 200,000 lines of code with a free copy of Black Duck
> > Code Sight - the same software that powers the world's largest code
> > search on Ohloh, the Black Duck Open Hub! Try it now.
> > http://p.sf.net/sfu/bds
> > _______________________________________________
> > Firebird-net-provider mailing list
> > Fir...@li...
> > https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
> 
> 
> ------------------------------------------------------------------------------
> Want fast and easy access to all the code in your enterprise? Index and
> search up to 200,000 lines of code with a free copy of Black Duck
> Code Sight - the same software that powers the world's largest code
> search on Ohloh, the Black Duck Open Hub! Try it now.
> http://p.sf.net/sfu/bds
> _______________________________________________
> Firebird-net-provider mailing list
> Fir...@li...
> https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
 		 	   		  

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

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

Jiri,

I am not familiar with Profiling. Is there one that you can recommend? 

Thank you,

Edward Mendez

Also, are there any tests that I can 
> -----Original Message-----
> From: Jiří Činčura [mailto:di...@ci...]
> Sent: Friday, August 8, 2014 1:59 AM
> To: For users and developers of the Firebird .NET providers
> Subject: Re: [Firebird-net-provider] Questions with performance metrics
> doing large inserts into DB
> 
> Hi,
> 
> Any chance to do profiling? I would expect .NET be slightly slower that .NET,
> but this is x-times slower. That's weird. But finding the bottleneck might be
> helpful.
> 
> 
> --
> Mgr. Jiří Činčura
> Independent IT Specialist
> 
> 
> ------------------------------------------------------------------------------
> Want fast and easy access to all the code in your enterprise? Index and
> search up to 200,000 lines of code with a free copy of Black Duck
> Code Sight - the same software that powers the world's largest code
> search on Ohloh, the Black Duck Open Hub! Try it now.
> http://p.sf.net/sfu/bds
> _______________________________________________
> Firebird-net-provider mailing list
> Fir...@li...
> https://lists.sourceforge.net/lists/listinfo/firebird-net-provider

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

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

Alexander,

 

Thank you for the feedback.

 

>>Two more things...  

>> 

>>1.  Your transaction parameters please.  How do you create them, what
settings?

To be honest I've never really thought about the transaction settings other
than the default. Are there a specific settings I should be using for just
reading "stale" data?

>>2.  Did you check your "source-read" logic.  Cound you maybe fake data, so
we know if it is the "insert" that is slow.  Maybe the materializing of the
source record is slow or fetching it?

I was doing further testing last night and removed the insert logic to see
if it was the reading of the data that was slowing it down. with the removal
of the Insert Logic and just reading and looping for 5000 iterations, it
still slower than Delphi's results. For the read Logic I was using Dapper
dot net and retrieving the results un-buffered (one row at a time). I then
eliminated Dapper and it still was slower than Delphi. I then tried an OleDB
Provider and that was a little faster than the .Net provider, but still
slower than Delphi. 

 

In retrospect I might have jumped the gun in blaming the Write performance
of the .Net Provider. I think I need to try to optimize the read logic, then
move on to the Write Logic.

 

I read somewhere that Looping in .NET is somewhat slower than in Delphi, but
there are things you can do optimize the .NET loops. 

 

Thank you,

 

Edward Mendez

 

From: Alexander Muylaert-Gelein [mailto:amu...@ho...] 
Sent: Friday, August 8, 2014 1:54 AM
To: fir...@li...urceforge.
Subject: Re: [Firebird-net-provider] Questions with performance metrics
doing large inserts into DB

 

Hi Edward

 

I'm also coming from a delphi background (using FIB) and we have ported/are
already porting for 5 years our applications to .net.  I have noticed indeed
that Delphi/fib is faster then .net provider.  But never in the magnitude of
500%.  It looked acceptable slower.

 

Your "write" code seems to be correct and pretty optimal.  Usually people
recreate a command each time.  I've also done some profiling in the past and
I've noticed that keeping a reference to the parameter doesn't help much.  A
slightly slower method, but way less code is to "clear" the parameters and
recreate them.   Once again, It is slightly slower, neglectible, but in your
scenario 170 lines of code less.  

 

using (command = new command){

  var par = command.Parameters

   while (! Eof){

       par.Clear();

       par.Add(Id);

       par.Add(Value);

       ...

   }

}

 

On the other hand Firebird is an open source database and also the .net
provider.  Jiri (the guy supporting this) is getting a few bucks per month
to support this.  We, as a company, sometimes sponsor these things by
testing, benchmarking or lazy picking up the bill.  Since you have a testing
environment up and running, you might walk the extra mile and help everybody
by profiling a bit deeper?  This would benefit you, me and everybody.  

 

Two more things...  

 

1.  Your transaction parameters please.  How do you create them, what
settings?

2.  Did you check your "source-read" logic.  Cound you maybe fake data, so
we know if it is the "insert" that is slow.  Maybe the materializing of the
source record is slow or fetching it?

 

Looking forward for tackling this thing.

 

thanks

 

a

 

 

 

 

 

  _____  

From: Eme...@nc...
To: fir...@li...
Date: Thu, 7 Aug 2014 18:14:43 -0400
Subject: Re: [Firebird-net-provider] Questions with performance metrics
doing large inserts into DB

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 

 

Re: [Firebird-net-provider] Abbility to use "no garbage collect" connection parameter

[Jiří Č. <di...@ci...>]

Looks fine to me.

-- 
Mgr. Jiří Činčura
Independent IT Specialist

[Firebird-net-provider] Abbility to use "no garbage collect" connection parameter

[Lukáš V. <l.v...@gm...>]

Hi

i d'like to add support for option NoGarbageCollect to connection 
parameters.

I purpouse this implementation:
     - Connection String will be extended with parameter "no garbage 
collect" (synonym: "nogarbagecollect")
     - Connection String Builder will be extended eigher (.NET property 
- bool name NoGarbageCollect)
     - FbConnectionInternal will add isc_dpb_no_garbace_collect into 
Database Parameter Buffer during BuildDpb method

We use Firebird database for web application, and we run backups every 
night. So we d'like to keep garbage collect for backup and speed-up 
normal operation of web.

Please let me know what you think about it.

Lukáš Vykydal

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

[Jiří Č. <di...@ci...>]

Hi,

Any chance to do profiling? I would expect .NET be slightly slower that .NET, but this is x-times slower. That's weird. But finding the bottleneck might be helpful.


-- 
Mgr. Jiří Činčura
Independent IT Specialist

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

[Alexander Muylaert-G. <amu...@ho...>]

Hi Edward
I'm also coming from a delphi background (using FIB) and we have ported/are already porting for 5 years our applications to .net.  I have noticed indeed that Delphi/fib is faster then .net provider.  But never in the magnitude of 500%.  It looked acceptable slower.
Your "write" code seems to be correct and pretty optimal.  Usually people recreate a command each time.  I've also done some profiling in the past and I've noticed that keeping a reference to the parameter doesn't help much.  A slightly slower method, but way less code is to "clear" the parameters and recreate them.   Once again, It is slightly slower, neglectible, but in your scenario 170 lines of code less.  
using (command = new command){  var par = command.Parameters   while (! Eof){       par.Clear();       par.Add(Id);       par.Add(Value);       ...   }}
On the other hand Firebird is an open source database and also the .net provider.  Jiri (the guy supporting this) is getting a few bucks per month to support this.  We, as a company, sometimes sponsor these things by testing, benchmarking or lazy picking up the bill.  Since you have a testing environment up and running, you might walk the extra mile and help everybody by profiling a bit deeper?  This would benefit you, me and everybody.  
Two more things...  
1.  Your transaction parameters please.  How do you create them, what settings?2.  Did you check your "source-read" logic.  Cound you maybe fake data, so we know if it is the "insert" that is slow.  Maybe the materializing of the source record is slow or fetching it?
Looking forward for tackling this thing.
thanks
a





From: Eme...@nc...
To: fir...@li...
Date: Thu, 7 Aug 2014 18:14:43 -0400
Subject: Re: [Firebird-net-provider] Questions with performance metrics	doing large inserts into DB

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  
------------------------------------------------------------------------------
Infragistics Professional
Build stunning WinForms apps today!
Reboot your WinForms applications with our WinForms controls. 
Build a bridge from your legacy apps to the future.
http://pubads.g.doubleclick.net/gampad/clk?id=153845071&iu=/4140/ostg.clktrk
_______________________________________________
Firebird-net-provider mailing list
Fir...@li...
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider 		 	   		  

Re: [Firebird-net-provider] Firebird and Generate Views in Visual Studio 2013

[Jiří Č. <di...@ci...>]

In EF6 you might not need pre-generate views. In EF6 this was greatly improved.

Anyway, you can fire up ProcMon and look where the T4 template is running.

-- 
Mgr. Jiří Činčura
Independent IT Specialist

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 

 

[Firebird-net-provider] [FB-Tracker] Created: (DNET-563) Abbility to use "no garbage collect" connection parameter

[Lukáš V. (J. <tr...@fi...>]

Abbility to use "no garbage collect" connection parameter
---------------------------------------------------------

                 Key: DNET-563
                 URL: http://tracker.firebirdsql.org/browse/DNET-563
             Project: .NET Data provider
          Issue Type: New Feature
          Components: ADO.NET Provider
    Affects Versions: 4.5.0.0
            Reporter: Lukáš Vykydal
            Assignee: Jiri Cincura
            Priority: Minor


Hi,

we d'like to add support for no garbage collect option to .net provider.

Parameter in connection string will be called "No Garbage Collect".

Can we implement it and send patch ?

Bye

Lukáš Vykydal

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

       

Re: [Firebird-net-provider] Firebird and Generate Views in Visual Studio 2013

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

Marco,

 

Can you try to put in the following code in your web.config or app.config

 

<system.data>

    <DbProviderFactories>

      <remove invariant="FirebirdSql.Data.FirebirdClient" />

      <add name="FirebirdClient Data Provider" invariant="FirebirdSql.Data.FirebirdClient" description=".NET Framework Data Provider for Firebird" type="FirebirdSql.Data.FirebirdClient.FirebirdClientFactory, FirebirdSql.Data.FirebirdClient" />

    </DbProviderFactories>

  </system.data>

 

Thanks,

 

Edward Mendez

 

From: Marco Castro - McSoft [mailto:mc...@mc...] 
Sent: Wednesday, August 6, 2014 8:47 AM
To: Fir...@li...
Subject: [Firebird-net-provider] Firebird and Generate Views in Visual Studio 2013

 

I already have runnning EF6 (Firebird provider 4.5) in VS 2013 and there is a single fix to solve. Generate Views of Power Tools Beta 4 shows this message when called to run T4 process:

System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Data.Entity.Core.MetadataException: Schema specified is not valid. Errors: 
Framework.edmx(7,6) : **error 0152: No Entity Framework provider found for the ADO.NET provider with invariant name 'FirebirdSql.Data.FirebirdClient'. Make sure the provider is registered in the 'entityFramework' section of the application config file. See http://go.microsoft.com/fwlink/?LinkId=260882 for more information.**
   at System.Data.Entity.Core.Metadata.Edm.StoreItemCollection.Loader.ThrowOnNonWarningErrors()
   at System.Data.Entity.Core.Metadata.Edm.StoreItemCollection.Loader.LoadItems(IEnumerable`1 xmlReaders, IEnumerable`1 sourceFilePaths)
   at System.Data.Entity.Core.Metadata.Edm.StoreItemCollection.Loader..ctor(IEnumerable`1 xmlReaders, IEnumerable`1 sourceFilePaths, Boolean throwOnError, IDbDependencyResolver resolver)
   at System.Data.Entity.Core.Metadata.Edm.StoreItemCollection.Init(IEnumerable`1 xmlReaders, IEnumerable`1 filePaths, Boolean throwOnError, IDbDependencyResolver resolver, DbProviderManifest& providerManifest, DbProviderFactory& providerFactory, String& providerInvariantName, String& providerManifestToken, Memoizer`2& cachedCTypeFunction)
   at System.Data.Entity.Core.Metadata.Edm.StoreItemCollection..ctor(IEnumerable`1 xmlReaders)
   --- End of inner exception stack trace ---
   at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
   at System.Reflection.RuntimeConstructorInfo.Invoke(BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   at System.RuntimeType.CreateInstanceImpl(BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes, StackCrawlMark& stackMark)
   at System.Activator.CreateInstance(Type type, BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes)
   at System.Activator.CreateInstance(Type type, Object[] args)
   at Microsoft.DbContextPackage.Utilities.EdmxUtility.GetMappingCollectionEF6(Assembly ef6Assembly, String& containerName)
   at Microsoft.DbContextPackage.Handlers.OptimizeContextHandler.OptimizeEdmx(String inputPath)

My guess is: EntityFramework.Firebird must be placed where Generate Views runs, where it is installed. What is this path? Where is Generate Views running? EntityFramework.Firebird is addressed at entityFramework but I guess that Power Tools look at machine.config but EntityFramework.Firebird can't be there.

<entityFramework>
  <defaultConnectionFactory type="FirebirdSql.Data.EntityFramework6.FbConnectionFactory, EntityFramework.Firebird" />
  <providers>
    <provider invariantName="FirebirdSql.Data.FirebirdClient" 
            type="FirebirdSql.Data.EntityFramework6.FbProviderServices, EntityFramework.Firebird" />
  </providers>
</entityFramework>

Thanks,

Marco Castro

[Firebird-net-provider] Firebird and Generate Views in Visual Studio 2013

[Marco C. - M. <mc...@mc...>]

<p style="margin: 0px 0px 1em; padding: 0px; border: 0px; font-size: 14px; vertical-align: baseline; clear: both; color: rgb(0, 0, 0); font-family: Arial, 'Liberation Sans', 'DejaVu Sans', sans-serif; line-height: 17.804800033569336px; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">I already have runnning EF6 (Firebird provider 4.5) in VS 2013 and there is a single fix to solve. Generate Views of Power Tools Beta 4 shows this message when called to run T4 process:</p><pre>System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---&gt; System.Data.Entity.Core.MetadataException: Schema specified is not valid. Errors: 
Framework.edmx(7,6) : **error 0152: No Entity Framework provider found for the ADO.NET provider with invariant name 'FirebirdSql.Data.FirebirdClient'. Make sure the provider is registered in the 'entityFramework' section of the application config file. See http://go.microsoft.com/fwlink/?LinkId=260882 for more information.**
   at System.Data.Entity.Core.Metadata.Edm.StoreItemCollection.Loader.ThrowOnNonWarningErrors()
   at System.Data.Entity.Core.Metadata.Edm.StoreItemCollection.Loader.LoadItems(IEnumerable`1 xmlReaders, IEnumerable`1 sourceFilePaths)
   at System.Data.Entity.Core.Metadata.Edm.StoreItemCollection.Loader..ctor(IEnumerable`1 xmlReaders, IEnumerable`1 sourceFilePaths, Boolean throwOnError, IDbDependencyResolver resolver)
   at System.Data.Entity.Core.Metadata.Edm.StoreItemCollection.Init(IEnumerable`1 xmlReaders, IEnumerable`1 filePaths, Boolean throwOnError, IDbDependencyResolver resolver, DbProviderManifest&amp; providerManifest, DbProviderFactory&amp; providerFactory, String&amp; providerInvariantName, String&amp; providerManifestToken, Memoizer`2&amp; cachedCTypeFunction)
   at System.Data.Entity.Core.Metadata.Edm.StoreItemCollection..ctor(IEnumerable`1 xmlReaders)
   --- End of inner exception stack trace ---
   at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
   at System.Reflection.RuntimeConstructorInfo.Invoke(BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   at System.RuntimeType.CreateInstanceImpl(BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes, StackCrawlMark&amp; stackMark)
   at System.Activator.CreateInstance(Type type, BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes)
   at System.Activator.CreateInstance(Type type, Object[] args)
   at Microsoft.DbContextPackage.Utilities.EdmxUtility.GetMappingCollectionEF6(Assembly ef6Assembly, String&amp; containerName)
   at Microsoft.DbContextPackage.Handlers.OptimizeContextHandler.OptimizeEdmx(String inputPath)
</pre><p style="margin: 0px 0px 1em; padding: 0px; border: 0px; font-size: 14px; vertical-align: baseline; clear: both; color: rgb(0, 0, 0); font-family: Arial, 'Liberation Sans', 'DejaVu Sans', sans-serif; line-height: 17.804800033569336px; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">My guess is: EntityFramework.Firebird must be placed where Generate Views runs, where it is installed. What is this path? Where is Generate Views running? EntityFramework.Firebird is addressed at entityFramework but I guess that Power Tools look at machine.config but EntityFramework.Firebird can't be there.</p><pre><span class="str" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; color: rgb(128, 0, 0); background: transparent;">&lt;entityFramework&gt;</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background: transparent;">
  </span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background: transparent;">&lt;</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background: transparent;">defaultConnectionFactory type</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background: transparent;">=</span><span class="str" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; color: rgb(128, 0, 0); background: transparent;">"FirebirdSql.Data.EntityFramework6.FbConnectionFactory, EntityFramework.Firebird"</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background: transparent;"> </span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background: transparent;">/&gt;</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background: transparent;">
  </span><span class="str" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; color: rgb(128, 0, 0); background: transparent;">&lt;providers&gt;</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background: transparent;">
    </span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background: transparent;">&lt;</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background: transparent;">provider invariantName</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background: transparent;">=</span><span class="str" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; color: rgb(128, 0, 0); background: transparent;">"FirebirdSql.Data.FirebirdClient"</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background: transparent;"> 
            type</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background: transparent;">=</span><span class="str" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; color: rgb(128, 0, 0); background: transparent;">"FirebirdSql.Data.EntityFramework6.FbProviderServices, EntityFramework.Firebird"</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background: transparent;"> </span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background: transparent;">/&gt;</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background: transparent;">
  </span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background: transparent;">&lt;/</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background: transparent;">providers</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background: transparent;">&gt;</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background: transparent;">
</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background: transparent;">&lt;/</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background: transparent;">entityFramework</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background: transparent;">&gt;</span></code></pre><p style="margin: 0px 0px 1em; padding: 0px; border: 0px; font-size: 14px; vertical-align: baseline; clear: both; color: rgb(0, 0, 0); font-family: Arial, 'Liberation Sans', 'DejaVu Sans', sans-serif; line-height: 17.804800033569336px; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">Thanks,</p><p style="margin: 0px 0px 1em; padding: 0px; border: 0px; font-size: 14px; vertical-align: baseline; clear: both; color: rgb(0, 0, 0); font-family: Arial, 'Liberation Sans', 'DejaVu Sans', sans-serif; line-height: 17.804800033569336px; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">Marco Castro</p>

Re: [Firebird-net-provider] Problem reading resources on linux

[diskuze <di...@ci...>]

Can you try running that under Mono 3.4.x?

--
Mgr. Jiri Cincura
Independent IT Specialist

From: Ernesto Cullen [mailto:ern...@gm...]
Sent: Tuesday, July 29, 2014 3:28 PM
To: fir...@li...
Subject: [Firebird-net-provider] Problem reading resources on linux

I've created a small program to test firebird access with an embedded server on linux (with mono), but I am getting errors related to resources.

This is the program:
namespace ConsoleFBTest
{
    class MainClass
    {
        public static void Main (string[] args)
        {
            Console.WriteLine ("Creating database...");
            FbConnection.CreateDatabase("ServerType=1;client library=libfbembed;Dialect=3;Database=test.fdb;User=sysdba;password=masterkey");
        }
    }
}

easy enough, I just want to create a database. I've copied all firebird lib/* files into target dir, along with firebird.msg, security2.fdb, firebird.conf, intl/*. I tested that the embedded server works copying also isql and executing from command line.
When I run previous program, both from MonoDevelop and from command line, I get the output below. I think it may have to do with resources formatting or embedding in the firebird client dll? maybe eol characters? old mono version? running out of ideas here, please help!
Ernesto Cullen

Mono version:
-------------------------------------
mono --version
Mono JIT compiler version 2.10.8 (tarball Mon Apr  7 03:54:27 IST 2014)
Copyright (C) 2002-2011 Novell, Inc, Xamarin, Inc and Contributors. www.mono-project.com<http://www.mono-project.com>
    TLS:           __thread
    SIGSEGV:       altstack
    Notifications: epoll
    Architecture:  amd64
    Disabled:      none
    Misc:          debugger softdebug
    LLVM:          supported, not enabled.
    GC:            Included Boehm (with typed GC and Parallel Mark)
NETProvider: NETProvider-4.2.0.0-MONO_LINUX
Firebird: FirebirdCS-2.5.3.26778-0.amd64

program output
-------------------------------------------------------------------
Creating database...

Unhandled Exception: System.ObjectDisposedException: The object was used after being disposed.
  at System.IO.UnmanagedMemoryStream.Seek (Int64 offset, SeekOrigin loc) [0x00000] in <filename unknown>:0
  at System.Resources.ResourceReader.LoadResourceValues (.ResourceCacheItem[] store) [0x00000] in <filename unknown>:0
  at System.Resources.ResourceReader+ResourceEnumerator.FillCache () [0x00000] in <filename unknown>:0
  at System.Resources.ResourceReader+ResourceEnumerator..ctor (System.Resources.ResourceReader readerToEnumerate) [0x00000] in <filename unknown>:0
  at System.Resources.ResourceReader.GetEnumerator () [0x00000] in <filename unknown>:0
  at System.Resources.ResourceSet.ReadResources () [0x00000] in <filename unknown>:0
  at System.Resources.ResourceSet.GetObjectInternal (System.String name, Boolean ignoreCase) [0x00000] in <filename unknown>:0
  at System.Resources.ResourceSet.GetObject (System.String name, Boolean ignoreCase) [0x00000] in <filename unknown>:0
  at System.Resources.ResourceSet.GetStringInternal (System.String name, Boolean ignoreCase) [0x00000] in <filename unknown>:0
  at System.Resources.ResourceSet.GetString (System.String name) [0x00000] in <filename unknown>:0
  at FirebirdSql.Data.Common.IscException.BuildSqlState () [0x00000] in <filename unknown>:0
  at FirebirdSql.Data.Common.IscException.BuildExceptionData () [0x00000] in <filename unknown>:0
  at FirebirdSql.Data.Client.Native.FesConnection.ParseStatusVector (System.IntPtr[] statusVector, FirebirdSql.Data.Common.Charset charset) [0x00000] in <filename unknown>:0
  at FirebirdSql.Data.Client.Native.FesDatabase.ParseStatusVector (System.IntPtr[] statusVector) [0x00000] in <filename unknown>:0
  at FirebirdSql.Data.Client.Native.FesDatabase.CreateDatabase (FirebirdSql.Data.Common.DatabaseParameterBuffer dpb, System.String dataSource, Int32 port, System.String database) [0x00000] in <filename unknown>:0
  at FirebirdSql.Data.FirebirdClient.FbConnectionInternal.CreateDatabase (FirebirdSql.Data.Common.DatabaseParameterBuffer dpb) [0x00000] in <filename unknown>:0
  at (wrapper remoting-invoke-with-check) FirebirdSql.Data.FirebirdClient.FbConnectionInternal:CreateDatabase (FirebirdSql.Data.Common.DatabaseParameterBuffer)
  at FirebirdSql.Data.FirebirdClient.FbConnection.CreateDatabase (System.String connectionString, Int32 pageSize, Boolean forcedWrites, Boolean overwrite) [0x00000] in <filename unknown>:0
  at FirebirdSql.Data.FirebirdClient.FbConnection.CreateDatabase (System.String connectionString) [0x00000] in <filename unknown>:0
  at ConsoleFBTest.MainClass.Main (System.String[] args) [0x00000] in <filename unknown>:0
[ERROR] FATAL UNHANDLED EXCEPTION: System.ObjectDisposedException: The object was used after being disposed.
  at System.IO.UnmanagedMemoryStream.Seek (Int64 offset, SeekOrigin loc) [0x00000] in <filename unknown>:0
  at System.Resources.ResourceReader.LoadResourceValues (.ResourceCacheItem[] store) [0x00000] in <filename unknown>:0
  at System.Resources.ResourceReader+ResourceEnumerator.FillCache () [0x00000] in <filename unknown>:0
  at System.Resources.ResourceReader+ResourceEnumerator..ctor (System.Resources.ResourceReader readerToEnumerate) [0x00000] in <filename unknown>:0
  at System.Resources.ResourceReader.GetEnumerator () [0x00000] in <filename unknown>:0
  at System.Resources.ResourceSet.ReadResources () [0x00000] in <filename unknown>:0
  at System.Resources.ResourceSet.GetObjectInternal (System.String name, Boolean ignoreCase) [0x00000] in <filename unknown>:0
  at System.Resources.ResourceSet.GetObject (System.String name, Boolean ignoreCase) [0x00000] in <filename unknown>:0
  at System.Resources.ResourceSet.GetStringInternal (System.String name, Boolean ignoreCase) [0x00000] in <filename unknown>:0
  at System.Resources.ResourceSet.GetString (System.String name) [0x00000] in <filename unknown>:0
  at FirebirdSql.Data.Common.IscException.BuildSqlState () [0x00000] in <filename unknown>:0
  at FirebirdSql.Data.Common.IscException.BuildExceptionData () [0x00000] in <filename unknown>:0
  at FirebirdSql.Data.Client.Native.FesConnection.ParseStatusVector (System.IntPtr[] statusVector, FirebirdSql.Data.Common.Charset charset) [0x00000] in <filename unknown>:0
  at FirebirdSql.Data.Client.Native.FesDatabase.ParseStatusVector (System.IntPtr[] statusVector) [0x00000] in <filename unknown>:0
  at FirebirdSql.Data.Client.Native.FesDatabase.CreateDatabase (FirebirdSql.Data.Common.DatabaseParameterBuffer dpb, System.String dataSource, Int32 port, System.String database) [0x00000] in <filename unknown>:0
  at FirebirdSql.Data.FirebirdClient.FbConnectionInternal.CreateDatabase (FirebirdSql.Data.Common.DatabaseParameterBuffer dpb) [0x00000] in <filename unknown>:0
  at (wrapper remoting-invoke-with-check) FirebirdSql.Data.FirebirdClient.FbConnectionInternal:CreateDatabase (FirebirdSql.Data.Common.DatabaseParameterBuffer)
  at FirebirdSql.Data.FirebirdClient.FbConnection.CreateDatabase (System.String connectionString, Int32 pageSize, Boolean forcedWrites, Boolean overwrite) [0x00000] in <filename unknown>:0
  at FirebirdSql.Data.FirebirdClient.FbConnection.CreateDatabase (System.String connectionString) [0x00000] in <filename unknown>:0
  at ConsoleFBTest.MainClass.Main (System.String[] args) [0x00000] in <filename unknown>:0

--
"Either write something worth reading or do something worth writing" B. Franklin

Re: [Firebird-net-provider] Compiling for mono

[diskuze <di...@ci...>]

Hi, probably best idea is to use the build for Mono/Linux from firebirdsql.org. Or you can build it yourself, there’s a configuration with same name in project.

--
Mgr. Jiri Cincura
Independent IT Specialist

Re: [Firebird-net-provider] Compiling for mono

[Jiri C. <ji...@ci...>]

Hi, probably best idea is to use the build for Mono/Linux from firebirdsql.org. Or you can build it yourself, there’s a configuration with same name in project.

--
Mgr. Jiri Cincura
Independent IT Specialist