Unfortunately I don't know whether you could benefit from a temporary table, they're simply not available on the Firebird version I normally use. Since it has a static structure and isn't heavily used, I would be surprised if the advantage would be more than maximum the time it takes to run DELETE FROM MYREQUESTS followed by a SELECT COUNT(*) FROM MYREQUESTS, but I do not know.

Hopefully others will answer this question,
Set

Michael D. Spence wrote:

>Do you think there would be any advantage to using a temporary table for MYREQUESTS?

Set wrote (the MYREQUEST table definition etc):

> > First, some DDL:
> >
> > CREATE GENERATOR MYREQUESTGEN;
> >
> > CREATE TABLE MYREQUESTS
> > (
> > MYREQUESTKEY INTEGER NOT NULL,
> > PTYROLE Varchar(6) NOT NULL,
> > PARTIESKEY Bigint NOT NULL
> > );
> >
> > Now, each time you want to check for an identical set, start by
> > getting the next value of MYREQUESTGEN from your Java application,
> > and then insert your list of values into MYREQUESTS, all with the
> > same value as you got from MYREQUESTGEN.
> >
> > Then you can issue this SELECT, again using the value you got from
> > MYREQUESTGEN as your parameter:
> >
> > SELECT DISTINCT pg.PTYGROUPKEY
> > FROM PARTYGROUP pg
> > JOIN MYREQUESTS mr ON pg.PTYROLE = mr.PTYROLE
> > AND pg.PARTIESKEY = mr.PARTIESKEY WHERE
> > mr.MYREQUESTKEY = :MyRequestKey
> > AND NOT EXISTS(
> > SELECT null
> > FROM PARTYGROUP pg2
> > LEFT JOIN MYREQUESTS mr2 ON pg2.PTYROLE = mr2.PTYROLE
> > AND pg2.PARTIESKEY = mr2.PARTIESKEY
> > AND mr.MYREQUESTKEY = mr2.MYREQUESTKEY
> > WHERE pg.PTYGROUPKEY = pg2.PTYGROUPKEY
> > AND mr2.MYREQUESTKEY is null)
> > AND NOT EXISTS(
> > SELECT null
> > FROM MYREQUESTS mr3
> > LEFT JOIN PARTYGROUP pg3 ON pg3.PTYROLE = mr3.PTYROLE
> > AND pg3.PARTIESKEY = mr3.PARTIESKEY
> > AND pg.PTYGROUPKEY = pg3.PTYGROUPKEY WHERE
> > mr.MYREQUESTKEY = mr3.MYREQUESTKEY
> > AND pg3.PTYGROUPKEY is null)