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
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
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
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=153845071iu=/4140/ostg.clktrk___ Firebird-net-provider mailing list Firebird-net-provider@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
Re: [Firebird-net-provider] Firebird and Generate Views in Visual Studio 2013
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:mcas...@mcsoft.com.br] Sent: Wednesday, August 6, 2014 8:47 AM To: Firebird-net-provider@lists.sourceforge.net 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 -- 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=153845071iu=/4140/ostg.clktrk___ Firebird-net-provider mailing list Firebird-net-provider@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
Re: [Firebird-net-provider] Problem reading resources on linux
Ernesto,
In my connection string for Embedded Server (on Windows) I have the following;
data source=localhost;Client Library=|DataDirectory|fbembed.dll;initial
catalog=pathToDatabase\DataBaseName.FDB;user
id=sysdba;password=masterkey;server type=1
I needed to specify the path to the Database File, along with the Path to the
fbembed.dll and also the localhost. I also needed to include the following
files in my output folder…
firebird.conf, firebird.msg, ib_util.dll, icudt30.dll, icuin30.dll, icuuc30.dll
Hope this helps,
Ed Mendez
From: Ernesto Cullen [mailto:ernestocul...@gmail.com]
Sent: Tuesday, July 29, 2014 9:28 AM
To: firebird-net-provider@lists.sourceforge.net
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
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)
[0x0] in filename unknown:0
at System.Resources.ResourceReader.LoadResourceValues (.ResourceCacheItem[]
store) [0x0] in filename unknown:0
at System.Resources.ResourceReader+ResourceEnumerator.FillCache () [0x0]
in filename unknown:0
at System.Resources.ResourceReader+ResourceEnumerator..ctor
(System.Resources.ResourceReader readerToEnumerate) [0x0] in filename
unknown:0
at System.Resources.ResourceReader.GetEnumerator () [0x0] in filename
unknown:0
at System.Resources.ResourceSet.ReadResources () [0x0] in filename
unknown:0
at System.Resources.ResourceSet.GetObjectInternal (System.String name,
Boolean ignoreCase) [0x0] in filename unknown:0
at System.Resources.ResourceSet.GetObject (System.String name, Boolean
ignoreCase) [0x0] in filename unknown:0
at System.Resources.ResourceSet.GetStringInternal (System.String name,
Boolean ignoreCase) [0x0] in filename unknown:0
at System.Resources.ResourceSet.GetString (System.String name) [0x0] in
filename unknown:0
at FirebirdSql.Data.Common.IscException.BuildSqlState () [0x0] in
filename unknown:0
at FirebirdSql.Data.Common.IscException.BuildExceptionData () [0x0] in
filename unknown:0
at FirebirdSql.Data.Client.Native.FesConnection.ParseStatusVector
(System.IntPtr[] statusVector, FirebirdSql.Data.Common.Charset charset)
[0x0] in filename unknown:0
at FirebirdSql.Data.Client.Native.FesDatabase.ParseStatusVector
(System.IntPtr[] statusVector) [0x0] in filename unknown:0
at FirebirdSql.Data.Client.Native.FesDatabase.CreateDatabase
(FirebirdSql.Data.Common.DatabaseParameterBuffer dpb, System.String dataSource,
Int32 port, System.String database) [0x0] in filename unknown:0
at FirebirdSql.Data.FirebirdClient.FbConnectionInternal.CreateDatabase
(FirebirdSql.Data.Common.DatabaseParameterBuffer dpb) [0x0] 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)
[0x0] in filename unknown:0
at
[Firebird-net-provider] Firebird-net 2.5.2 and microsoft report services
Hello, Our scenario is as follows. We have a Delphi front end which uses the FIBPlus library to connect to Firebird. Some users run many reports in groups. And most of the reports in the groups use a common stored procedure with one set of parameters for each group of reports generated. Currently in production these reports run sequentially and the common stored procedure can take many hours to complete. Potentially it could take up to [many hours] X # of Reports. So we implemented a Report Cache where the first report in the group would insert the output of the common stored proc into this report Cache. Any subsequent report from that group would just select from the report cache. This significantly decreased the time spent running these reports. We are now trying to take this another level and leverage Microsoft Reporting Services for the report processing. We are using the .NET Firebird data provider to connect to Firebird from Microsoft Reporting Services. Our problem is that when the multiple reports are being run, the first one would run for a long time, and the output of the report would contain data, which means that the report cache is populated on the firebird side. But when the other reports are run, they return back with no results. If I do a select in IBEXPERT to the report cache for the groups data, I return no rows, but the first report ran successfully. By best guess is that Microsoft Reporting Services is running the report and after the report is run, the transaction is rolled back. My question, is if there is a way to set on an auto commit feature on the Firebird .net data provider? I know some data providers use an auto_commit ( or some other variation ) to turn on/off the auto commit feature from the connectionstring? Regards, Edward Mendez -- Increase Visibility of Your 3D Game App Earn a Chance To Win $500! Tap into the largest installed PC base get more eyes on your game by optimizing for Intel(R) Graphics Technology. Get started today with the Intel(R) Software Partner Program. Five $500 cash prizes are up for grabs. http://p.sf.net/sfu/intelisp-dev2dev___ Firebird-net-provider mailing list Firebird-net-provider@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
