Thanks for the quick response Gary,

Tried it out this way and shaved off ~5secs, its now running at ~35s from
~40s. I'm not 100% sure what that gives better performance either;) maybe
the DB doesn't have to pull IM.BUYER_NO it just evaluates the where clause
to determine the final bool value of the subselect? Also I do have indices
on both Issue_no and Buyer_no in the ISSUESMAILED table, these came from
adding the foreign keys to this table.

I am interested in the transaction aspect of this, as I've started following
another thread to do with transactions and cpu usage. Helen even suggested
doing the full operation in 2 separate transactions. What kind of
transactions settings should I be using in a case like this? Also I am
using IBX as well in my app. Thanks Todd.

_____

From: Gary Benade [mailto:hobbit@...]
Sent: Thursday, February 10, 2005 12:30 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Insertion speed

> Thanks for all the help and tips. I have to admit that my simple query
> wasn't quite as simple as originally stated. What it was trying to
> achieve
> was something like this
> FOR
> 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)
> INTO :BUYER_NO
> DO
> BEGIN
> //INSERT BUYER_NO INTO OTHER TABLE
> END

change to this and see if there is an improvement. Helen pointed it out to
me once and it made a huge difference (still not 100% sure why). BTW. is
there an index on ISSUESMAILED( BUYER_NO) and/or ISSUESMAILED( ISSUE_NO)

FOR
SELECT BP.BUYER_NO
FROM BUYERPUBLICATION BP
WHERE BP.PUBLICATION_NO = :PUBLICATION_NO
AND NOT EXISTS(SELECT 1
FROM ISSUESMAILED IM
WHERE IM.BUYER_NO = BP.BUYER_NO
AND IM.ISSUE_NO = :ISSUE_NO)
INTO :BUYER_NO
DO
BEGIN
//INSERT BUYER_NO INTO OTHER TABLE
END






Yahoo! Groups Sponsor


ADVERTISEMENT

<http://us.ard.yahoo.com/SIG=129a42ovr/M=298184.6018725.7038619.3001176/D=gr
oups/S=1705115386:HM/EXP=1108150082/A=2532114/R=2/SIG=12koen71l/*http:/clk.a
tdmt.com/NFX/go/yhxxxnfx0020000014nfx/direct/01/&time=1108063682078140>


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


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

_____

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]