Wow, you were right the execute time dropped from ~40s to ~20s. I’m happy
with that! I have some other things I need to tweak before I get too hung
up on performance like this. This was great! Now could someone explain why
this worked the way it did?;) Also I have been trying to be very selective
in where I place my own indexes, I’ve found its only helped me in a couple
select places and I can justify them due to the performance benefit they’ve
given. I have assumed while building my DB that as I create foreign keys
between tables indices are created automatically. The indices you see in
use in the plan below are indices created from adding foreign keys, should I
take them out? (I’ve always assumed an index was good, with negative
performance if you went overboard with them) Thanks for the great tip,
Todd.

_____

From: Svein Erling Tysvær [mailto:svein.erling.tysvaer@...]
Sent: Friday, February 11, 2005 2:09 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Insertion speed

--- In firebird-support@yahoogroups.com, "Todd Pinel" wrote:
> SELECT BP.BUYER_NO
> FROM BUYERPUBLICATION BP
> WHERE BP.PUBLICATION_NO = :PUBLICATION_NO
> AND NOT EXISTS(SELECT IM.BUYER_NO
> FROM ISSUESMAILED IM
> WHERE IM.BUYER_NO = BP.BUYER_NO
> AND IM.ISSUE_NO = :ISSUE_NO)

Hmm, that looks pretty OK!

> PLAN (IM INDEX (INTEG_132,INTEG_177)) PLAN (BP INDEX
> (FK_BUYERPUBLICATIONPUBLICAT))

Ouch, I dislike using indexes for both buyer_no and issue_no. Assuming
that issue_no is far less selective than buyer_no (hopefully, you sell
100000 copies of each issue ;o), try changing one line to

AND IM.ISSUE_NO+0 = :ISSUE_NO

and you should observe a - hopefully dramatic - improvement (except
for issues being sold to a handful of buyers).

HTH,
Set







Yahoo! Groups Sponsor


ADVERTISEMENT

<http://us.ard.yahoo.com/SIG=1293si76b/M=298184.6018725.7038619.3001176/D=gr
oups/S=1705115386:HM/EXP=1108199369/A=2532114/R=2/SIG=12km4vtut/*http:/clk.a
tdmt.com/NFX/go/yhxxxnfx0020000014nfx/direct/01/&time=1108112969454483>


<http://view.atdmt.com/NFX/view/yhxxxnfx0020000014nfx/direct/01/&time=110811
2969454483>


<http://us.adserver.yahoo.com/l?M=298184.6018725.7038619.3001176/D=groups/S=
:HM/A=2532114/rand=521123210>

_____

Yahoo! Groups Links
* To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

* To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com
<mailto:firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe>

* Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
<http://docs.yahoo.com/info/terms/> .


[Non-text portions of this message have been removed]