The query is:
select distinct c2.contactid
from contacts c1, contacts c2
where c1.oksendemail='N'
and c1.accountid=540
and c1.accountid=c2.accountid
and c1.email=c2.email
and c2.validemail='Y'
and c2.todelete='N';

The plan shown in ISQL is:
PLAN SORT (JOIN (C1 INDEX (RDB$FOREIGN13), C2 INDEX (CONTACTS_EXEMAIL_XC1)))

The index used is: CREATE INDEX CONTACTS_EXEMAIL_XC1 INDEX ON CONTACTS(EMAIL, ACCOUNTID, VALIDEMAIL, TODELETE)

--- In Firebird-Java@yahoogroups.com, Steve Wiser <steve@...> wrote:
>
> What is the query in question?
>
> -steve
>
> On 2/8/2010 11:07 AM, peterson77 wrote:
> >
> > Thanks for your reply. I guess my question is changing as my
> > understanding evolves. What prompted this question was the large
> > difference in performance time between a query processed via JDBC and
> > one processed through ISQL. JDBC time was 180 seconds while ISQL time
> > was 5 seconds. As the result set was quite small, I was assuming that
> > the difference in performance time was related to memory differences.
> >
> > As reported by 'top', the VIRT memory of an fb_inet_server process
> > created through jdbc is 32meg while isql's fb_inet_server process size
> > is larger at 49meg. I am hoping to track down why the size of an
> > fb_inet_server process created through Jaybird/JDBC would be so
> > different than the size of the an fb_inet_server process created
> > through ISQL. The size of the each process does not change
> > significantly while queries are being performed. How can I make the
> > memory footprint of the JDBC connections more like the ISQL connection?
> >
> > p.s. While I am using the classic server architecture, there are only
> > 8 connections in a connection pool, so my understanding was that 2048
> > buffers was not too much -- 8 meg/connection * 8 connections = 64meg
> > devoted to buffers. Is there some other reason besides memory to keep
> > the number of buffers low in this set-up?
> >
> > - Steve
> >
> > --- In Firebird-Java@yahoogroups.com
> > <mailto:Firebird-Java%40yahoogroups.com>, "Mark Rotteveel"
> > <Avalanche1979@> wrote:
> > >
> > > > I need to tune an application using Jaybird 2.1.6/Firebird 2.1
> > classic in
> > > > a tomcat application on a 6gig RHEL 5 box.
> > > >
> > > > When I run 'top' on the server, each fb_inet_server process
> > created via
> > > > Jaybird takes around 32meg of VIRT memory and I would like to
> > understand what
> > > > goes into this memory footprint.
> > > >
> > > > 1) The buffers are set to 2048 (gfix -buffers 2048). This means each
> > > > connection should take 8 meg (2048 x 4k page = 8meg).
> > > >
> > > > 2) The TempCacheLimit (formerly SortMemCacheLimit) is not set in
> > > > firebird.conf, so the default should be 8meg. This means each
> > connection should
> > > > take an additional 8meg.
> > > >
> > > > I am not specifying any connection parameters in the Jaybird
> > connection
> > > > string. So all told, it looks like each connection should take
> > 16meg. Why
> > > > then is each connection actually taking 32meg? What am I missing?
> > >
> > > This is a totally different question than the one you asked on
> > Firebird-support. This question is one that can better be directed to
> > Firebird-support as it is about the general behaviour of Firebird and
> > has nothing to do with the Java/Jaybird side of things.
> > >
> > > To answer your question:
> > > 1) In general 2048 pages of buffer is considered too much for a
> > classic server. The default is 75 and more pages should only be used
> > if you really need it, otherwise it is better to let the OS filesystem
> > cache handle the caching.
> > >
> > > The cache and sortspace is not the only things that take memory in a
> > Firebird process. Think about the data needed to process the
> > statement, connection details, the data actually materialized from the
> > database to send to the client, etc.
> > >
> > > Mark
> > > --
> > > GRATIS für alle GMX-Mitglieder: Die maxdome Movie-FLAT!
> > > Jetzt freischalten unter http://portal.gmx.net/de/go/maxdome01
> > <http://portal.gmx.net/de/go/maxdome01>
> > >
> >
> >
>
> --
> Steve Wiser
> President
> Specialized Business Software
> 6325 Cochran Road, Unit 1
> Solon, OH 44139
>
> www.specializedbusinesssoftware.com
> (440) 542-9145 - fax (440) 542-9143
> Toll Free: (866) 328-4936
>
>
>
>
> This message and any files transmitted with it may contain information that is privileged, confidential, and exempt from disclosure under applicable law. They are intended solely for the use of the intended recipient. If you are not the intended recipient, distributing, copying, disclosing, or reliance on the contents of this communication is strictly prohibited. If this has reached you in error, kindly destroy this message and notify the sender immediately. Thank you for your assistance.
>
> We attempt to sweep harmful content (e.g. viruses) from e-mail and attachments, however we cannot guarantee their safety and can accept no liability for any resulting damage. The recipient is responsible to verify the safety of this message and any attachments before accepting them.
>