I would try an explicit join and see how it handles it. I know way back
when we used Interclient it was handling queries different with implicit
joins.

select distinct c2.contactid
from contacts c1
join contacts c2 on (c1.email = c2.email AND c1.accountid = c2.accountid)
where c1.oksendemail='N'
and c1.accountid=540
and c2.validemail='Y'
and c2.todelete='N';

-steve

On 2/8/2010 11:29 AM, peterson77 wrote:
>
> 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
> <mailto:Firebird-Java%40yahoogroups.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>
> > > <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>
> > > <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.
> >
>
>

--
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.