New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
performance: round-trip/sql invocation cost [DNET280] #291
Comments
Modified by: Vasily Kabanov (vaso)description: I tried populating simple test table with parameterized insert statement. It was very slow (~270 rows/second). Processor utilization was 60-70% in kernel (which to me is a sign of something unproductive going on). => I tried populating simple test table with parameterized insert statement. It was very slow (~270 rows/second). Processor utilization was 15-40%, 60-70% of which - in kernel (which to me was a sign of something unproductive going on). |
Commented by: @cincuranet How is you code looking? |
Commented by: Vasily Kabanov (vaso) The way the code is looking is pretty much irrelevant here I guess. I mean even absolutely empty command is slow to invoke. Example: create or alter procedure sp_test_empty ---------------
} I am using embedded Firebird (which should probably be faster than superserver), but I also tried superserver, it was slow too. Same code via .NET over ODBC ({call sp_test_empty}): |
Modified by: @cincuranetpriority: Major [ 3 ] => Minor [ 4 ] |
Commented by: F.D.Castel (fdcastel) Benchmarks for Firebird http://ADO.NET Data Provider , ODBC and Delphi/IBX |
Modified by: F.D.Castel (fdcastel)Attachment: Program.cs [ 12277 ] Attachment: IbxSpeedTest.dpr [ 12278 ] |
Commented by: F.D.Castel (fdcastel) New benchmarks. Using http://FB.Net Data Provider 3.0.2, Firebird 2.5.2 x64 SuperServer running on local machine I also made a Delphi 2007/IBX test to compare. Source code attached, The results are really something: ----- |
Commented by: F.D.Castel (fdcastel) Profiling the code, I found several calls to
So... Changing the sample code to use explicit transactions made a huge gain: http://FB.Net: 100000 commands in 12,643 seconds; 7909,51514672151 commands per second but yet, 4x slower than ODBC and 10x slower than Delphi: IBX: 100000 commands in 1,37200020253658 seconds; 72886,2866165164 commands per second (*) (*) IBX obliges you to use explicit transactions, so the Delphi code was already using them. I'll continue to profile the code trying to find where the bottleneck is. Meanwhile, it is worthy of notice that ODBC code doesn't suffer from using implicit transactions. Maybe that call to FbCommand.CommitImplicitTransaction() should be rethought |
Commented by: F.D.Castel (fdcastel) Another cause for large slowdowns is a call to LogCommand() in FbCommand.Prepare(bool): * Official Build * /trunk build without LogCommand() call However, this function is marked with a conditional TRACE attribute. * /trunk build without TRACE compiler directive Bottom line: the current distribution (3.0.2) were built with TRACE directive turned on. |
Commented by: @cincuranet Did you had any listener attached/defined? |
Commented by: F.D.Castel (fdcastel) No. It was a simple program (source code attached) built as Release (not a Debug build) and running outside the IDE (without a debugger attached) This program, running with the Official build, took 12.5 seconds. Just replacing the FirebirdSql.Data.FirebirdClient.dll (with the one built without TRACE directive) it took 7.2 seconds. The TRACE directive should not be removed on Release builds? |
Commented by: @cincuranet With the help of DNET895 the performance of ODBC is on par with FirebirdClient. For the Delphi part I'm getting about 8200 op/s (I modified the source to explicitly use localhost to prevent direct file usage), while .NET and ODBC is about half of that. But that's going to need some deeper investigation, because it's not exactly apples-to-apples comparison. I'll leave this open, in case somebody would want to start profiling sooner than me. |
Modified by: @cincuranetstatus: Open [ 1 ] => In Progress [ 3 ] |
Modified by: @cincuranetstatus: In Progress [ 3 ] => Open [ 1 ] |
Closing now in favor of specific performance tasks. |
Submitted by: Vasily Kabanov (vaso)
Attachments:
Program.cs
IbxSpeedTest.dpr
Votes: 1
I tried populating simple test table with parameterized insert statement. It was very slow (~270 rows/second). Processor utilization was 15-40%, 60-70% of which - in kernel (which to me was a sign of something unproductive going on).
1st, I suspected query re-parsing to be a problem and packaged insert into a SP, no luck.
2nd, suspected character sets convertions for strings and left only integer parameters, again slow.
finally, suspected some marshaling or reflection issues and tried .NET over ODBC.
Parameterized insert was slow again, but when using stored proc performance went up: 6500 rows/second which is excellent.
The text was updated successfully, but these errors were encountered: