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
[Firebird-net-provider] Abbility to use no garbage collect connection parameter
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 -- 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] Abbility to use no garbage collect connection parameter
Looks fine to me. -- 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