On Fri, 5 Oct 2001 15:30:17 +0000 (UTC), InterbaseSupport@...
("Leyne, Sean") wrote:

>Then, why are you also comparing the SHORTCUTs?
>
>(It would seem that the TMP.ID value + the TMP.NAME is the unique
>reference)
>

Sean,

TMP.ID and TMP.SHORTCUT are known, and he wants to know SA.SAID. So
he joins SA and TMP using (SA)NAME:

select SA.SAID from SALESAREAS SA inner join DOS_CONSTANTS TMP on
(TMP.NAME=SA.SANAME) where TMP.ID=3 and TMP.SHORTCUT=:REGION into
:SAID;

Guido,

On Fri, 5 Oct 2001 15:17:02 +0000 (UTC), guido.klapperich@...
wrote:

>> > Table DOS_CONSTANTS: Index on SHORTCUT and NAME
>>
>> Delete the SHORTCUT index and create a compound index on SHORTCUT + ID
>>
>
>This doesn't change anything

Is the new index used? Did you check the plan?

Half an hour can be ok or it can be disappointing. It just depends on
the number of rows processed. I didn't see any hint to the numbers in
this thread.

Can you tell us the count(*) for each table involved?
What's your CPU speed?
CPU utilization while the SP is executing?
How long does your SP run, when you eliminate all "subselects", i.e.
you leave all xxIDs null?
How does this compare to

insert into CUSTOMERS (
CSDANAME1,CSDANAME2,CSDANAME3,CSDASTREET,CSDAZIPCODE,

CSDACITY,CSDAPOSTBOXZIPCODE,CSDAPOSTBOX,CSDADIALLINGCODE,CSDATELEPHONE,
CSDAFAX,CSCLID,CSSAID,CSNRFIRST,CSNRLAST,
CSINID,CSBRID,CSCHID,CSBRANCHNR,CSGPID2,
CSGPID3,CSOUTLETA,CSOUTLETB,CSOUTLETC,CSTURNOVER)
select
NAME1,NAME2,NAME3,DASTREET,DAPOSTBOXZIPCODE,
DACITY,DAZIPCODE,DAPOSTBOX,DADIALLINGCODE,DATELEPHONE,
DAFAX,null,null,NRFIRST,NRLAST,
null,null,null,OUTLET_NR,null,
null,OUTLETS_A,OUTLETS_B,OUTLETS_C,TURNOVER
from DOS_CUSTOMERS
where NRLAST>=0;

I know this cannot be your final solution, it's just to give us an
impression ...

BTW, your SP selects DOS_CUSTOMERS.SALES_REP into :SALES_REP and does
not use it.

Kind regards
Carl van Tast