Subject Re: SELECT ONLY database query performance
Author thefourie2000
Hey Vlad

What exactly do you mean by "serialising isc_attach_database by some
mutex" What would be the advantage of this? Will that not cause the
exact sort of problem I'm experiencing now. Where one thread has to
wait for the next to complete?

I was hoping that by creating a seperate connection for each thread I
could avoid this sort of locking. Especially since the application
only reads from the database.

At the moment I'm also connecting to the database and then only
disconnecting once the user session ends. So a user can potentially
run a great number of requests per connection. I assumed this would
improve the performance due to caching. Maybe another mistake on my part.


--- In, "Vlad Horsun" <hvlad@...> wrote:
> > The basics of my application are as follows.
> >
> > - I'm using FB Embedded 2.0
> > - A TCP Server receives a connection, creates a new thread and
> > connects
> > to the DB via the Embedded Engine
> > - Each thread makes it's own connection to the database.
> You must serialise isc_attach_database calls by some mutex or
> critical section. As long as each thread operate within its own
> attachment you don't need to synchronise other API calls
> > - The system validates information, starting a transaction,
> > 10 -> 15 selects and commiting the transaction. As stated the
> > system executes ONLY SELECTS
> > - All queries run against a single table with approx 35k rows.
> > - Five columns in the table are used to form the WHERE clause and
> > indexes have been created on those fields as required by the
> > - The result is then transmitted back to the client via TCP
> >
> > When processing requests for a SINGLE thread the AVG time per request
> > is 7ms. If I increase the number of requesting applications and thus
> > threads connected to the database to 200. The AVG time per request
> > goes to 1400ms. So it appears that somewhere something is locking the
> > entire database and not allowing other threads read access. :(
> It seems your application have some problems with threads...
> Regards,
> Vlad