Hello,

I have a procedure that is using an index in one select statement, but not
the another identical one. The index for the select statement defined in
lines 63 through 77 is used and as indicated with the following plan:

Plan:
----------------------------------------------------------------------------
----
SORT (JOIN (S INDEX (SPECS_IDX1),JOIN (CR INDEX (CROSS_REFERENCE_IDX1),P
INDEX (PARTS_IDX2))))

The odd thing is that the select statement defined in lines 122 through 136
(which is identical) does not use an index as indicated with this plan:

Plan:
----------------------------------------------------------------------------
----
SORT (JOIN (S INDEX (SPECS_IDX1),JOIN (CR NATURAL,P INDEX (PARTS_IDX2))))

Can someone explain why that is and how I can force FB to use the correct
index? Thanks a million.

Kevin Lingofelter

Procedure:

CREATE PROCEDURE SP_GETPARTS (
PARTTYPEID INTEGER,
MAKE VARCHAR(50),
MODEL VARCHAR(50),
APPYEAR VARCHAR(10),
WAC INTEGER)
RETURNS (
PARTTYPE VARCHAR(50),
SKU VARCHAR(10),
BASEPRICE NUMERIC(15,2),
SALEPRICE NUMERIC(15,2),
ENGINE VARCHAR(50),
ONSALE INTEGER,
NOTES VARCHAR(255),
ENABLED INTEGER,
INSTOCK INTEGER,
APPREMARKS VARCHAR(255),
SHOWSPECS INTEGER,
SPECLINE VARCHAR(50),
PICNAME VARCHAR(10),
HEIGHT NUMERIC(15,4),
WIDTH NUMERIC(15,4),
DEPTH NUMERIC(15,4),
ROWS INTEGER,
USERPICSMALL VARCHAR(50),
USERPICLARGE VARCHAR(50),
USERTOC VARCHAR(50),
USERHEIGHT DECIMAL(15,4),
USERROWS INTEGER,
USERDEPTH DECIMAL(15,4),
USERWIDTH DECIMAL(15,4),
USERPARTTYPE VARCHAR(50),
USEREOC VARCHAR(50),
USERREMARKS VARCHAR(255))
AS
DECLARE VARIABLE LTR VARCHAR(50);
DECLARE VARIABLE CD VARCHAR(50);
DECLARE VARIABLE ENG VARCHAR(10);
DECLARE VARIABLE DEBUGMODE INTEGER;
DECLARE VARIABLE EOC VARCHAR(50);
DECLARE VARIABLE TOC VARCHAR(50);
DECLARE VARIABLE DPI INTEGER;
DECLARE VARIABLE D1 INTEGER;
DECLARE VARIABLE D2 INTEGER;
DECLARE VARIABLE D3 INTEGER;
begin

-- Determine of the store is in Debug mode. This is used to determine if the
NUMBERSEARCHED field gets updated
select STOREOPTIONS.DEBUGMODE FROM STOREOPTIONS into :DEBUGMODE;

if (PARTTYPEID <> 18) then begin
for select distinct
(case when A.ENG_TYPE = 'N/A' then '' else A.ENG_TYPE || ' ' end) ||
(case when A.LITERS = 'N/A' then '' else A.LITERS || 'L ' end) ||
(case when A.CID = 'N/A' then '' else A.CID || ' CID' end) as
ENGINE,
A.DPI1, A.REMARKS,
P.SKU, P.BASEPRICE, P.SALEPRICE, P.ONSALE, P.NOTES, P.USER$PICLARGE,
P.USER$PICSMALL, P.USER$PARTTYPE, P.USER$REMARKS,
C.PARTTYPE
from APPLICATIONS A
inner join BASEAPPLICATIONS BA on (A.BASEAPPLICATIONID =
BA.BASEAPPLICATIONID)
inner join APPLICATION_DICTIONARY AD on (BA.APPLICATION_DICTIONARYID
= AD.APPLICATION_DICTIONARYID)
inner join CROSS_REFERENCE CR on (A.DPI1 = CR.DPINUMBER)
inner join PARTS P on (CR.SKU = P.SKU)
inner join CATAGORIES C on (A.PARTTYPEID = C.PID)
where
AD.MAKE = :MAKE and
AD.MODEL = :MODEL and
BA.APPYEAR = :APPYEAR and
A.PARTTYPEID = :PARTTYPEID and
P.ENABLED = 1 and
P.INSTOCK = 1
order by
A.ENG_TYPE, A.LITERS, A.REMARKS, P.SKU
into
:ENGINE, :PICNAME, :APPREMARKS,
:SKU, :BASEPRICE, :SALEPRICE, :ONSALE, :NOTES, :USERPICLARGE,
:USERPICSMALL, :USERPARTTYPE, :USERREMARKS,
:PARTTYPE
do begin
-- update the amount of times this part number has been searched if
not in debug mode
if (DEBUGMODE <> 1) then
update PARTS set NUMBERSEARCHED = NUMBERSEARCHED + 1 where SKU =
:SKU;
suspend;
end
end else begin
-- this is a temp table used to filter duplicate part numbers
delete from TEMP$PARTS;
for select distinct
C.PARTTYPE,
A.LITERS, A.CID, A.ENG_TYPE, A.DPI1, A.DPI2, A.DPI3, A.REMARKS
from APPLICATIONS A
inner join BASEAPPLICATIONS BA on (A.BASEAPPLICATIONID =
BA.BASEAPPLICATIONID)
inner join APPLICATION_DICTIONARY AD on (BA.APPLICATION_DICTIONARYID
= AD.APPLICATION_DICTIONARYID)
inner join CATAGORIES C on (A.PARTTYPEID = C.PID)
where
AD.MAKE = :MAKE and
AD.MODEL = :MODEL and
BA.APPYEAR = :APPYEAR and
A.PARTTYPEID = 18
order by
A.ENG_TYPE, A.LITERS, A.REMARKS
into
:PARTTYPE, :LTR, :CD, :ENG, :D1, :D2, :D3, :APPREMARKS
do begin
-- determine which DPI number field to use
DPI = (case :WAC when 0 then :D1 when 1 then :D2 end);

-- Get the part number(s) for the STD/AC DPI number
for select distinct
P.SHOWSPECS, P.SKU, P.BASEPRICE, P.SALEPRICE, P.ONSALE, P.NOTES,
P.ENABLED, P.INSTOCK,
P.USER$PARTTYPE, P.USER$REMARKS, P.USER$HEIGHT, P.USER$WIDTH,
P.USER$DEPTH,
P.USER$ROWS, P.USER$PICLARGE, P.USER$PICSMALL, P.USER$TOC,
P.USER$EOC,
S.ROWS, S.HEIGHT, S.WIDTH, S.DEPTH, S.EOC, S.TOC
from PARTS P
right outer join CROSS_REFERENCE CR on (P.SKU = CR.SKU)
right outer join SPECS S on (CR.DPINUMBER = S.DPI)
where
S.DPI = :DPI
into
:SHOWSPECS, :SKU, :BASEPRICE, :SALEPRICE, :ONSALE, :NOTES,
:ENABLED, :INSTOCK,
:USERPARTTYPE, :USERREMARKS, :USERHEIGHT, :USERWIDTH,
:USERDEPTH,
:USERROWS, :USERPICLARGE, :USERPICSMALL, :USERTOC, :USEREOC,
:ROWS, :HEIGHT, :WIDTH, :DEPTH, :EOC, :TOC
do begin
-- see if the part number has already been displayed
if (not exists(select RECID from TEMP$PARTS where RECID = (:SKU
|| :ENGINE))) then begin
-- update the amount of times this part number has been
searched if we are not in debug mode
if (DEBUGMODE <> 1) then
update PARTS set NUMBERSEARCHED = NUMBERSEARCHED + 1
where SKU = :SKU;

-- build SPECLINE
if (SHOWSPECS is null) then SHOWSPECS = 1;
if (SHOWSPECS = 1) then begin
if ((case when EOC = 'NO' then null else EOC end) is
null) then
SPECLINE = 'w/o EOC';
else begin
if (EOC = 'YES') then
SPECLINE = 'w/ EOC';
else
SPECLINE = 'w/ ' || EOC || ' EOC';
end
if ((case when TOC = 'NO' then null else TOC end) is
null) then
SPECLINE = :SPECLINE || '<br>w/o TOC';
else begin
if (TOC = 'YES') then
SPECLINE = :SPECLINE || '<br>w/ TOC';
else
SPECLINE = :SPECLINE || '<br>w/ ' || TOC || '
TOC';
end
end

-- Build the description
ENGINE = (case when ENG = 'N/A' then '' else ENG || ' ' end)
||
(case when LTR = 'N/A' then '' else LTR || 'L '
end) ||
(case when CD = 'N/A' then '' else CD || ' CID'
end);

if (SKU is not null) then
insert into TEMP$PARTS (RECID) values ((:SKU ||
:ENGINE));
PICNAME = :DPI;
if (ENABLED is null) then ENABLED = 0;
if (INSTOCK is null) then INSTOCK = 0;
suspend;
end

-- See if we need to send the HD record
if (DPI <> :D3) then begin
-- Get the part number(s) for the STD/AC DPI number
for select distinct
P.SHOWSPECS, P.SKU, P.BASEPRICE, P.SALEPRICE, P.ONSALE,
P.NOTES, P.ENABLED, P.INSTOCK,
P.USER$PARTTYPE, P.USER$REMARKS, P.USER$HEIGHT,
P.USER$WIDTH, P.USER$DEPTH,
P.USER$ROWS, P.USER$PICLARGE, P.USER$PICSMALL,
P.USER$TOC, P.USER$EOC,
S.ROWS, S.HEIGHT, S.WIDTH, S.DEPTH, S.EOC, S.TOC
from PARTS P
right outer join CROSS_REFERENCE CR on (P.SKU = CR.SKU)
right outer join SPECS S on (CR.DPINUMBER = S.DPI)
where
S.DPI = :D3
into
:SHOWSPECS, :SKU, :BASEPRICE, :SALEPRICE, :ONSALE,
:NOTES, :ENABLED, :INSTOCK,
:USERPARTTYPE, :USERREMARKS, :USERHEIGHT, :USERWIDTH,
:USERDEPTH,
:USERROWS, :USERPICLARGE, :USERPICSMALL, :USERTOC,
:USEREOC,
:ROWS, :HEIGHT, :WIDTH, :DEPTH, :EOC, :TOC
do begin
-- see if the part number has already been displayed
if (not exists(select RECID from TEMP$PARTS where RECID
= (:SKU || :ENGINE))) then begin
-- update the amount of times this part number has
been searched if we are not in debug mode
if (DEBUGMODE <> 1) then
update PARTS set NUMBERSEARCHED = NUMBERSEARCHED
+ 1 where SKU = :SKU;

-- build SPECLINE
if (SHOWSPECS is null) then SHOWSPECS = 1;
if (SHOWSPECS = 1) then begin
if ((case when EOC = 'NO' then null else EOC
end) is null) then
SPECLINE = 'w/o EOC';
else begin
if (EOC = 'YES') then
SPECLINE = 'w/ EOC';
else
SPECLINE = 'w/ ' || EOC || ' EOC';
end
if ((case when TOC = 'NO' then null else TOC
end) is null) then
SPECLINE = :SPECLINE || '<br>w/o TOC';
else begin
if (TOC = 'YES') then
SPECLINE = :SPECLINE || '<br>w/ TOC';
else
SPECLINE = :SPECLINE || '<br>w/ ' || TOC
|| ' TOC';
end
end

-- Build the description
ENGINE = (case when ENG = 'N/A' then '' else ENG ||
' ' end) ||
(case when LTR = 'N/A' then '' else LTR ||
'L ' end) ||
(case when CD = 'N/A' then '' else CD || '
CID' end);

if (SKU is not null) then
insert into TEMP$PARTS (RECID) values ((:SKU ||
:ENGINE));
PICNAME = :D3;
if (ENABLED is null) then ENABLED = 0;
if (INSTOCK is null) then INSTOCK = 0;

suspend;
end
end
end
end
end
end
end