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 paulo_...@sapo.pt 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 (10 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 Firebird-net-provider@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/firebird-net-provider -- ___ Firebird-net-provider mailing list Firebird-net-provider@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/firebird-net-provider -- ___ Firebird-net-provider mailing list Firebird-net-provider@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
Re: [Firebird-net-provider] Questions with performance metrics doing large inserts into DB
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 disk...@cincura.net 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 paulo_...@sapo.pt 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 (10 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 Firebird-net-provider@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/firebird-net-provider -- ___ Firebird-net-provider mailing list Firebird-net-provider@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/firebird-net-provider -- ___ Firebird-net-provider mailing list Firebird-net-provider@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/firebird-net-provider -- ___ Firebird-net-provider mailing list Firebird-net-provider@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
Re: [Firebird-net-provider] Questions with performance metrics doing large inserts into DB
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 amuylaert_gel...@hotmail.com
Sent: Friday, August 08, 2014 11:10:43 PM
To: firebird-net-provider@lists.sourceforge.
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: emendez...@nc.rr.com
To: firebird-net-provider@lists.sourceforge.net
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
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: emendez...@nc.rr.com
To: firebird-net-provider@lists.sourceforge.net
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
Re: [Firebird-net-provider] Questions with performance metrics doing large inserts into DB
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 : n...@tetrasys.eu mailto:taru.kaukova...@tetrasys.eu
De : Alexander Muylaert-Gelein [mailto:amuylaert_gel...@hotmail.com]
Envoyé : samedi 9 août 2014 00:11
À : firebird-net-provider@lists.sourceforge.
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: emendez...@nc.rr.com
To: firebird-net-provider@lists.sourceforge.net
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
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 (10 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 Firebird-net-provider@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/firebird-net-provider -- ___ Firebird-net-provider mailing list Firebird-net-provider@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
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 Firebird-net-provider@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
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:disk...@cincura.net] 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 Firebird-net-provider@lists.sourceforge.net 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 Firebird-net-provider@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
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: emendez...@nc.rr.com To: firebird-net-provider@lists.sourceforge.net 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:disk...@cincura.net] 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 Firebird-net-provider@lists.sourceforge.net 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 Firebird-net-provider@lists.sourceforge.net 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 Firebird-net-provider@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
Re: [Firebird-net-provider] Questions with performance metrics doing large inserts into DB
Yep, same for me. -- Mgr. Jiří Činčura Independent IT Specialist From: Alexander Muylaert-Gelein [mailto:amuylaert_gel...@hotmail.com] Sent: Friday, August 8, 2014 8:55 PM To: firebird-net-provider@lists.sourceforge. 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: emendez...@nc.rr.commailto:emendez...@nc.rr.com To: firebird-net-provider@lists.sourceforge.netmailto:firebird-net-provider@lists.sourceforge.net 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:disk...@cincura.net] 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 Firebird-net-provider@lists.sourceforge.netmailto:Firebird-net-provider@lists.sourceforge.net 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 Firebird-net-provider@lists.sourceforge.netmailto:Firebird-net-provider@lists.sourceforge.net 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 Firebird-net-provider@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
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:amuylaert_gel...@hotmail.com]
Sent: Friday, August 8, 2014 1:54 AM
To: firebird-net-provider@lists.sourceforge.
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: emendez...@nc.rr.com
To: firebird-net-provider@lists.sourceforge.net
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
Re: [Firebird-net-provider] Questions with performance metrics doing large inserts into DB
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 -- ___ Firebird-net-provider mailing list Firebird-net-provider@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
Re: [Firebird-net-provider] Questions with performance metrics doing large inserts into DB
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 Firebird-net-provider@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
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.QueryTABLE_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
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: emendez...@nc.rr.com
To: firebird-net-provider@lists.sourceforge.net
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.QueryTABLE_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
