I have forwarded it to list to hear comments on the changes the use proposes :) as i have doubs on some of them and seconds opinions are really welcome !!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Thanks in advance-------------------------------------------- xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx ---------------------------------------------
As I have mailed to the mailinglist:I have improved the speed of the DataReader about 3x I can get it to compete with the GDS server connection if but I will have to discuss this with you. I know my PInvoke and .Net optimalizations, however I need somebody to review the use of the API.
These are the performances I get when using my modified version to read 8600 records with 60 columns
using (IDataReader reader = cmd.ExecuteReader()) {
while (reader.Read()) {
for (int idx = 0; idx < reader.FieldCount; idx++) {
reader.GetValue(idx);
}
}
Embedded:
Before: 9688 ms
After: 3047 ms
Server:
Unmodified: 1141 ms
I think I can get the embedded version to the same performance level of
the Server version.
The modifications were done for 1.7, but can be easily reproduced for the 2.0 branch.
I attached a Speedup.txt explaining the steps I have taken. Also I attached a patch for the 1.7 version (patched against 18 oct. 2006)I hope to hear from you soon, because I need some more speedup in my application. I have a few questions at the end of Speedup.txt.
Also I would like to hear what you think of my modifications. Regards, Jelle Hissink -- Carlos Guzmán Álvarez Vigo-Spain http://carlosga.wordpress.com
Embedded database query took almost 10 seconds while the server connected
version took
about 1.1 seconds. The ant profiler pointed in the direction of the
XsqldaMarshaler
(more then 90 % of the time was spent in the marshaller class
% of time of IDataReader.Read()
MarshalNativeToManaged - 48 %
MarshalManagedToNative - 33 %
CleanUpNativeData - 10 %
next was Descriptor.getItem() with almost 4 % and DBValue.ctor with around 2 %)
Test situation:
interating over a select query that returns about 8600 rows and containing
about 60 columns.
Basicly doing a:
int startTicks = Environment.TickCount;
using (IDbCommand cmd = conn.CreateCommand()) {
cmd.CommandText =
"SELECT
HelpdeskRegistration.\"Key\", HelpdeskRegistration.TemplateName,
HelpdeskRegistration.MonitorSLA, HelpdeskRegistration.TimeSpendString,
HelpdeskRegistration.BTimeSpendString, HelpdeskRegistration.Summary,
HelpdeskRegistration.Description, HelpdeskRegistration.ExternalReference,
HelpdeskRegistration.Solution, HelpdeskRegistration.Notes,
HelpdeskRegistration.Publish, HelpdeskRegistration.Categorie_K,
HelpdeskRegistration.Categorie_T, HelpdeskRegistration.RegistrationType_K,
HelpdeskRegistration.RegistrationType_T, HelpdeskRegistration.Impact_K,
HelpdeskRegistration.Impact_T, HelpdeskRegistration.Priority_K,
HelpdeskRegistration.Priority_T, HelpdeskRegistration.Status_K,
HelpdeskRegistration.Status_T, HelpdeskRegistration.Assignee_K,
HelpdeskRegistration.Assignee_T, HelpdeskRegistration.Requestor_K,
HelpdeskRegistration.Requestor_T, HelpdeskRegistration.AffectedEndUser_K,
HelpdeskRegistration.AffectedEndUser_T, HelpdeskRegistration.RegistrationNr,
HelpdeskRegistration.OpenDate, HelpdeskRegistration.CloseDate,
HelpdeskRegistration.NeedByDate, HelpdeskRegistration.FreeBool,
HelpdeskRegistration.FreeInt, HelpdeskRegistration.FreeString,
HelpdeskRegistration.FreeString1, HelpdeskRegistration.FreeDate,
HelpdeskRegistration.FreeNumber, HelpdeskRegistration.Exported,
HelpdeskRegistration.Export, HelpdeskRegistration.RegisteredBy_K,
HelpdeskRegistration.RegisteredBy_T, HelpdeskRegistration.lfFreeBool_K,
HelpdeskRegistration.lfFreeBool_T, HelpdeskRegistration.lfFreeString_K, " +
"HelpdeskRegistration.lfFreeString_T, HelpdeskRegistration.lfFreeString1_K,
HelpdeskRegistration.lfFreeString1_T, HelpdeskRegistration.lfFreeInt_K,
HelpdeskRegistration.lfFreeInt_T, HelpdeskRegistration.lfFreeNumber_K,
HelpdeskRegistration.lfFreeNumber_T, HelpdeskRegistration.lfFreeDate_K,
HelpdeskRegistration.lfFreeDate_T, HelpdeskRegistration.Asset1_K,
HelpdeskRegistration.Asset1_T, HelpdeskRegistration.Asset2_K,
HelpdeskRegistration.Asset2_T, HelpdeskRegistration.Asset3_K,
HelpdeskRegistration.Asset3_T, HelpdeskRegistration.OrderNr" +
Environment.NewLine +
"FROM HelpdeskRegistration" +
Environment.NewLine +
" WHERE
HelpdeskRegistration.TypeNameId=37;";
using (IDataReader reader =
cmd.ExecuteReader()) {
while (reader.Read()) {
// not reading data, just
iterating for this test...
}
}
}
Console.WriteLine("{0} ms",
Environment.TickCount - startTicks);
Changes I made:
1) Charset.GetEncoding() cached
It is called frequently and costs relatively much. Caching provides some easy
to get speedup.
2) Try to optimize memory allocation/release
Memory allocation is done in a lot of small blocks in
XsqldaMarshaler.MarshalManagedToNative allocates a
large number of small memory blocks. This can be optimized if the data is
placed after the structure.
Cleanup can also be simplified
- Marshal.DestroyStructure is never needed, it only releases things like
COM-BStrings,
as all of the data is contained within the structure it is a redundant
call...
- When we only need one Marshal.FreeHGlobal to release all the memory we no
longer need to
call Marshal.PtrToStructure
- So the only call we need to make one call to Marshal.FreeHGlobal to
release all the memory.
3) more then 50 % of the remaining 6656 ms seems to origionate from
XsqldaMarshaler.GetString and XsqldaMarshaler.GetStringBuffer
Im going to reference times in profiler seconds (ps) as it runs slower in
profiler then in real life...
First we analyse XsqldaMarshaler.GetString():
a) Profiling shows:
- 3.62 ps is spent in Charset.GetString(byte[])
- 2.92 ps is spent on value.Replace('\0', ' ').Trim();
Replacing value.Replace('\0', ' ').Trim(); with value.TrimEnd('\0', ' ',
'\t', '\n', '\r'); shifts the balance
The TrimEnd() should not change the operations (as it seems it is only used
to remove padding at the end of the string).
- 3.81 ps is spent in Charset.GetString(byte[])
- 1.27 ps is spent on value.TrimEnd('\0', ' ', '\t', '\n', '\r');
b) Pre-trimming the byte array in XsqldaMarshaler.GetString()
(getting the length of the array and then decreasing it while it is ending
in '\0' or whitespace)
This should ease the task of Charset.GetString() and also should reduce the
TrimEnd to do nothing, thus I removed it.
Off course these optimizations only can be applied when
charset.BytesPerCharacter <= 1
Results:
- 3.39 ps for Charset.GetString(byte[], int offset, int count); (less
characters so somewhat faster)
- 0.85 ps TrimEnd() removed but got an extra loop removing trailing '\0'
and ' ' from the byte array
c) I feel we cannot improve much upon XsqldaMarshaler.GetStringBuffer()
except I feel a hashtable would allow some caching at higher speed.
So let's try that...
- 5.71 ps for charset.GetBytes(string, idx, count, buffer, startoffset)
Testing with a static hashtable to confirm...
- 5.19 ps now it is for the total routine
This doesn't seem worth the fact that you will have to pass around a
Hashtable for caching, so dumping this change.
4) A large change seems to be to not call
XsqldaMarshaler.MarshalManagedToNative and XsqldaMarshaler.CleanUpNativeData
on every call to Fetch(). So caching the IntPtr and releasing it in
Release() (overriden that) and calling CleanUpNativeData on finish (status ==
100)
5) Some minor optimalizations (caching Marshal.SizeOf calls)
Results (version 1.7):
percentage - time - details
100.0 % - 9390 ms - origional unmodified code
83.9 % - 7875 ms - (1) modification for Charset class (caching result of
Charset.GetEncoding())
70.1 % - 6656 ms - (2) allocating one continuous block of memory for
MarshalManagedToNative
58.6 % - 5500 ms - (3a) Changed trimming of strings in
XsqldaMarshaler.GetString()
49.6 % - 4656 ms - (3b) Pre-trimming the byte array in
XsqldaMarshaler.GetString()
32.8 % - 3078 ms - (4) Caching IntPtr sqlda within Fetch()
31.1 % - 2922 ms - (5) caching Marshal.SizeOf calls
The percentage can be dropped to about 10 % as the remaining time is largely
used for
XsqldaMarshaler.GetStringBuffer() however I'm unsure wether the Fetch() really
uses this.
Maybe we could come up with a version XsqldaMarshaler.MarshalNativeToManaged()
that reuses the last Descriptor.
source-1.7.18-10-2006.patch
Description: Binary data
------------------------------------------------------------------------- Using Tomcat but need to do more? Need to support web services, security? Get stuff done quickly with pre-integrated technology to make your job easier Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
_______________________________________________ Firebird-net-provider mailing list Firebird-net-provider@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
