I can think of 2 or 3 things that might account for the difference,
although I have no proof and am not sure how to verify this:

1. I think we make 3 or 4 calls to the db, I suspect postgres makes 1 or 2
with autocommit true. (ours are start tx, execute query, fetch results(??),
commit)

2. I wonder if firebird is writing (or reading) something to disk on each
transaction.

3. I wonder if firebird is parsing the sql anew for each query.

Anyone know if there are calls to execute a singleton query in autocommit
mode?

thanks
david jencks

On 2002.07.21 00:32:34 -0400 rbair23 wrote:
> Hey everyone,
>
> I made a first stab at a controlled performance test today. I
> compared firebird with postgreSQL (since that was the other DB I had
> set up). Here are the setups/params for this test
>
> -I ran both of these tests from the same machine as the database (so
> network issues shouldn't have been a factor).
> -I created the Employee table on the postgres database, and filled it
> with the exact same data as the employee.gdb employee table (except
> for one row I apparently missed).
> -The same query was run against both databases
> -Both databases were in their initial unoptimized condition (out of
> the box)
> -The exact same code was executed for both. I have command line
> arguments feed the database URL etc, so the same jar is used in both
> tests.
> -I used the `long startTime = System.currentTimeMillis()` method for
> timing these things - no overhead with gui's, etc.
> -Each test executed the SQL statement 10000 times.
> -I tested off of the version of the code in CVS as of this morning.
>
> Okay, so now for the results. Following is the output from the two
> tests:
> --------------------------------
> These were your parameters:
> SQL: select * from employee where emp_no = 141
> Host: localhost
> Port: 5432
> User: richard
> Password: bair
> dbURL: jdbc:postgresql://localhost/test
> Driver: org.postgresql.Driver
> Number of Times to Loop: 10000
> Connecting to the database
> Connection made
> Running Test (no hash - please wait)
> Test took a total of 33.0 seconds
> That is 0.0033 seconds per query
>
> Next Test:
>
> These were your parameters:
> SQL: select * from employee where emp_no = 141
> Host: localhost
> Port: 3050
> User: sysdba
> Password: masterkey
> dbURL:
> jdbc:firebirdsql:localhost/3050:/opt/interbase/examples/employee.gdb
> Driver: org.firebirdsql.jdbc.FBDriver
> Number of Times to Loop: 10000
> Connecting to the database
> Connection made
> Running Test (no hash - please wait)
> Test took a total of 1183.0 seconds
> That is 0.1183 seconds per query
>
> ----------------------------
>
> Now, the results are so staggering that I wondered if they did indeed
> execute the same number of times. I checked the scripts I used to
> start 'em up, and sure enough, they both executed the query 10,000
> times.
>
> There is another factor that may have some bearing - that of logging
> (since I know we log, but I don't know if postgres does). However,
> in my java command for running the firebird test, I had the following:
>
> -DFBLog4j=false
>
> So I would have expected logging to be turned off. I'm not sure if
> it was, since it errored out early on because I didn't have a `logs`
> directory from where I was running the jar. It should not be asking
> me that, but it was. Maybe somebody out there has a hint about this?
>
> Anyway, I'm going to profile next and see if I can find the hangup.
> I don't have a license for JProbe, which is too bad because I used
> that application to great advantage at work. I may have to just
> eyeball the code and do some mini time testing inside the driver
> itself.
>
> Any thoughts?
> Rich
>
>
>
> 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/
>
>
>
>
>