--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> wrote:

>
> At 08:33 AM 6/02/2010, you wrote:
> >A program is running queries in production that take as long as 180 seconds. When I do these same queries against the same database using ISQL, they can take as little as 5 seconds. Why the difference?
> >
> >The production box is a RHEL 5 Linux running Firebird 2.0 using classic architecture.
> >
> >Thanks for any help you can offer.
>
> Make sure you are comparing apples with apples, e.g.
>
> 1. isql starts fetching output as soon as it is available. Some applications don't start fetching until the query completes. Some applications are not as efficient as they could be at populating local data buffers and activating them in the client UI....not to mention the extra layering that lies between the engine and a GUI client.
>
> 2. If you are running isql locally and don't specify a network string for it to connect, it uses the libfbembed.so client that connects directly to the database. That will always be faster than a tcp/ip client connection, even if the host is localhost. How much faster depends on the speed of the transport layer and the capacity of the client to handle the output: e.g., a network connection is as fast as the slower end of the connection; wired clients are faster than wireless; client machines may be under-resourced in terms of RAM available to buffer output; and so on.
>
> 3. ..and if you are indeed making the isql side of your test with the embedded client then the application will have exclusive access (no competition from tcp/ip clients, fewer resources in use for caches, locking, etc.)
>
> Also, you don't say what sub-release of Fb 2.0 you are using. The current s/r is 2.0.5. If you are using an old version, you might well get some more clues by studying the Bug Fixes chapter of the v.2.0.5 release notes to see if any of the fixes done since your s/r could be relevant. You can read or download them from the Doc Index at the Firebird website.
>
> No answers here, just clues.
>
> ./heLen
>

Helen,
Thanks for your response. I am guessing this has something to do with memory usage, but I am not sure how to isolate the problem. The DefaultDbCachePages is not set in firebird.conf, so my understanding that each fb_inet_server process should take up around (75 pages x 4k page size = 300k) But when I look at a "top" display, each of the fb_inet_server processes has 32,000 of VIRT memory, 28m of RES memory. Furthermore, when I run isql, its child fb_inet_server process entry in top shows it having 49,000 of VIRT memory and 42m of RES memory. So the connection through ISQL is getting more memory than the connections in the application. This may explain the perfomance difference, but I don't understand why they would not both be the default -- or am I reading this wrong?

The version of Firebird is 2.1.2. The application environment is Java under Tomcat w/Jaybird 2.1.6 JDBC driver. The server has 6Gig of memory with dual processors. Both ISQL and the Jaybird connections are done through localhost. The database size is about 23gig, page size is 4k.

- Steve