Hi,

>my app appears to be spending about 20% of my time within

FBConnection.prepareFixedStatement

I presume you mean FBStatement.prepareFixedStatement and does that 20%
include time spent in methods called from prepareFixedStatement(which I
should think it would do) - and if so can you determin what percentage of
the time spent in prepareFixedStatement is actually spent in methods from
the jgds - specifically isc_dsql_allocate_statement and isc_dsql_prepare and
possibly isc_dsql_describe_bind. You may find that the vast majority of the
time in prepareFixedStatement is actually spent in calls to these methods.

These methods do the actual communications with the server - and the time
spent communicating with the server(and it doing its stuff) should dwalf the
time spent in the java code when preparing statements. It would be
interesting to see if this is the case with your app.

There is most likley(IMO) room for quite a huge saving in object allocation
rates in much of the code - and if savings are made extensivly this could
lead to a mesurable performance boost in applications which use the driver
heavily - saving a few allocations here and there (Again IMO) though is
unlikeley to lead to a messurable difference - it would really need to be
done across the board to see a real difference.

>Is it possible to reuse statements across
>connections? Or maybe cache common statements with a custom database
>pool?

I have beleive I have seen talk of such things but dont know myself about
the possibilies. Its an interesting idea that may lead to a good performance
gain(I also see a bit over 20% of time preparing statements in my app).
Perhaps search this and other firebird groups to see previous discussions on
the subject.

If you want to try and squeeze maximum performance from firebird then using
the embeded server then - depending on your circumstances it may be usable
and lead to quite drastic improvements in your applications performance - as
it has done with mine.

I have made some modifications to the driver that allows the embeded version
to be used from a java application. If you are running on Windows I could
send you my latest stable(but still experimental) version that I am using so
you could try it out. This uses the Firebird 1.5 RC1 embeded server
version(maybe theres a newer one available now, and it should work equally
well with any). I've been using this for a number of months and it seems
stable - the JCA functionality we dont use and so this hasent been well
tested - I may well have broken somthing here, but it passes the test cases
in the CVS tree OK.

Other platforms and an offical release maybe are on there way, but I
wouldent hold your breath just yet.

Thanks
Ryan


-----Original Message-----
From: Ken Richard [mailto:kenr@...]
Sent: 28 May 2003 14:18
To: Firebird-Java@yahoogroups.com
Subject: RE: [Firebird-Java] Performance Tweak


I also noticed that FBConnection.nativeSQL appears to be called twice
for each query. Once from:
executeQuery
internalExecute
isExecuteProcedureStatement

once from:
executeQuery
internalExecute
prepareFixedStatement

I realize that these may be insignificant but my app appears to be
spending about 20% of my time within FBConnection.prepareFixedStatement.
The environment is tomcat with firebird backend. Any suggestions?
There are some statements (stored procs) that are called often but only
once per servlet request. Is it possible to reuse statements across
connections? Or maybe cache common statements with a custom database
pool?

-----Original Message-----
From: Ken Richard [mailto:kenr@...]
Sent: Tuesday, May 27, 2003 1:38 PM
To: Firebird-Java@yahoogroups.com
Subject: [Firebird-Java] Performance Tweak


I am running a profiler (numega) against my code (tomcat servlets with
jaybird 1.0) and found something that may be a small benefit for
performance but is easy to fix.

getJavaEncodings in FBConnectionHelper is called often through the stack
FBResultSet constructor
prepareVars
FBField.setConnection
getJavaEncodings

and through a similar stack involving FBPreparedStatement constructor.

The getJavaEncodings calls getDefaultJavaEncodings which looks like:

public static String getDefaultJavaEncoding() {
InputStreamReader reader = new InputStreamReader(
new ByteArrayInputStream(new byte[2]));
return reader.getEncoding();
}

There is a lot being allocated/freed there just to find the default
encodings. Would it be possible to move the code in
getDefaultJavaEncodings into loadEncodings and store the result as a
static?

For my test (which may be atypical) 1% of my code's execution time was
spent inside getDefaultJavaEncoding().

I am working with JayBird 1.0 and can supply a patch if you are
interested.

I can also give some additional performance data from the profiler if
you are interested. For example - the biggest hits I am taking from
JayBird are in GDS_Impl.parseTruncSqlInfo and GDS_Impl.isc_vax_integer
but that would be expected.

-Ken




To unsubscribe from this group, send an email to:
Firebird-Java-unsubscribe@yahoogroups.com



Your use of Yahoo! Groups is subject to
http://docs.yahoo.com/info/terms/





To unsubscribe from this group, send an email to:
Firebird-Java-unsubscribe@yahoogroups.com



Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/