Hi All:

I am trying to speed up a search on a view and it is giving me fits.
Basically, I have three tables involved: 'Contacts' are customers,
'Orders' are orders they placed, and 'Products' are the specific
products they ordered (one product per order). The search is to
retrieve a set of 'contactid's representing customers who have ordered
certain types of products in the recent past; the view is a join of
these three tables with only certain fields exposed. (See DDL below)

The database has around 500,000 orders and 200,000 contacts divided into
4 accounts. Typical search times are 12 to 20 seconds. I have been
specifiying a plan because without one, searches were taking about a
minute. I am worried about these search times because a lot more
information is going to get thrown into the tables. (See searches with
plans below) Platform is Firebird 1.0 on a P3 Windows 98 box.

I am sure that there is a way to speed these searches up quite a bit
more. For example, why would search 2 be so faster than search 1 when
search 2 would seem to require a separate access to the 'products' table
with no index indicated in the plan. There must be something in the
plans or the indexing that I am missing; any ideas or pointers?

- Steve Peterson


Search 1
--------
SELECT distinct ORDERSEARCH.CONTACTID from ORDERSEARCH
where (ORDERSEARCH.ACCOUNTID = 330) and
(ORDERSEARCH.ORDERDATE >= '4/5/99') and
((ORDERSEARCH.PRODUCTID = 6360 OR ORDERSEARCH.PRODUCTID = 6364 OR
ORDERSEARCH.PRODUCTID = 5676 OR ORDERSEARCH.PRODUCTID = 5677 OR
ORDERSEARCH.PRODUCTID = 5678 OR ORDERSEARCH.PRODUCTID = 5679)) and
(ORDERSEARCH.VALIDUSPS = 'Yes') and
(ORDERSEARCH.OKSENDUSPS = 'Yes')
PLAN SORT (JOIN (ORDERSEARCH ORDERS INDEX (ORDERS_FIND),
ORDERSEARCH PRODUCTS NATURAL,
ORDERSEARCH CONTACTS NATURAL))
//found 11,138 contactids in 17 seconds.

Search 2
--------
select distinct ORDERSEARCH.CONTACTID
from ORDERSEARCH
where (ORDERSEARCH.ACCOUNTID = 330) AND
(ORDERSEARCH.ORDERDATE >= '4/5/00') AND
(ORDERSEARCH.ORDERAMOUNT >= -1000) AND
(ORDERSEARCH.SUBJECTID = 9 OR ORDERSEARCH.SUBJECTID = 16 OR
ORDERSEARCH.SUBJECTID = 10 OR ORDERSEARCH.SUBJECTID = 13 OR
ORDERSEARCH.SUBJECTID = 15 OR ORDERSEARCH.SUBJECTID = 65) AND
(ORDERSEARCH.VALIDUSPS = 'Yes') and
(ORDERSEARCH.OKSENDUSPS = 'Yes')
PLAN SORT (JOIN (ORDERSEARCH ORDERS INDEX (ORDERS_FIND),
ORDERSEARCH PRODUCTS NATURAL,
ORDERSEARCH CONTACTS NATURAL));
//found 18891 contactids in 12 seconds.

Search 3
--------
select distinct ORDERSEARCH.CONTACTID from ORDERSEARCH where
((ORDERSEARCH.COUNTRY = 'UNITED STATES')) and (( (ORDERSEARCH.ORDERDATE

>= '4/4/99') )) and ((ORDERSEARCH.SUBJECTID = 10 OR

ORDERSEARCH.SUBJECTID = 13 OR ORDERSEARCH.SUBJECTID = 17 OR
ORDERSEARCH.SUBJECTID = 18)) and (1=1) and
((title CONTAINING "coord") or
(title CONTAINING "lea") or
(title CONTAINING "man") or
(title CONTAINING "dir") or
(title CONTAINING "ceo") or
(title CONTAINING "offic") or
(title CONTAINING "adm")) and
((ORDERSEARCH.ACCOUNTID = 330)) and
(ORDERSEARCH.VALIDUSPS = 'Yes') and
(ORDERSEARCH.OKSENDUSPS = 'Yes')
group by ORDERSEARCH.CONTACTID having (( (SUM(ORDERSEARCH.ORDERAMOUNT)

>= 5)))

PLAN SORT (JOIN (ORDERSEARCH ORDERS INDEX (ORDERS_FIND),
ORDERSEARCH PRODUCTS NATURAL,
ORDERSEARCH CONTACTS NATURAL));
//found 470 contactids in 20 seconds.



--------- THE RELATED DDL -----------------------------------

CREATE TABLE CONTACTS (CONTACTID INTEGER NOT NULL,
ACCOUNTID INTEGER NOT NULL,
PREFIX VARCHAR(4),
FIRSTNAME VARCHAR(15),
MI VARCHAR(1),
LASTNAME VARCHAR(20),
SUFFIX VARCHAR(4),
COMPANY VARCHAR(32),
TITLE VARCHAR(32),
MAILSTOP VARCHAR(10),
FAX VARCHAR(12),
EMAIL VARCHAR(50),
GENDER CHAR(1) NOT NULL,
SOURCEID INTEGER NOT NULL,
UPDATEDBY VARCHAR(15),
DATEENTER DATE NOT NULL,
DATEUPDATE DATE NOT NULL,
BOUNCEFLG CHAR(3) NOT NULL,
BOUNCEDATE DATE NOT NULL,
USERNAME VARCHAR(20),
PASSWORDX VARCHAR(20),
ADDRESS VARCHAR(32),
ADDRESS2 VARCHAR(32),
CITY VARCHAR(32),
STATE VARCHAR(20),
ZIP VARCHAR(10),
COUNTRY VARCHAR(28) NOT NULL,
COUNTRYALT VARCHAR(28),
EXTERNALID VARCHAR(15),
PHONE VARCHAR(18),
PHONEEXT VARCHAR(5),
OKSENDEMAIL CHAR(3) NOT NULL,
OKSENDUSPS CHAR(3) NOT NULL,
OKSHARENAM CHAR(3) NOT NULL,
OKTEL CHAR(3) NOT NULL,
VALIDEMAIL CHAR(3) NOT NULL,
VALIDUSPS CHAR(3) NOT NULL,
VALIDUSTEL CHAR(3) NOT NULL,
ADDRESSKEY VARCHAR(50),
NAMEKEY VARCHAR(15),
FULLNAMEKY VARCHAR(25),
VERSION INTEGER NOT NULL,
PRIMARY KEY (CONTACTID));

CREATE TABLE ORDERS (ORDERID INTEGER NOT NULL,
ACCOUNTID INTEGER NOT NULL,
CONTACTID INTEGER NOT NULL,
ORDERDATE DATE NOT NULL,
ORDERAMOUNT INTEGER NOT NULL,
PRODUCTID INTEGER NOT NULL,
BILLSTATUS CHAR(1) NOT NULL,
PRIMARY KEY (ORDERID));

CREATE TABLE PRODUCTS (PRODUCTID INTEGER NOT NULL,
ACCOUNTID INTEGER NOT NULL,
PRODCODE VARCHAR(20) NOT NULL,
DESCRIPTION VARCHAR(60) NOT NULL,
LISTPRICE INTEGER NOT NULL,
COURSEDATE DATE NOT NULL,
SUBJECTID INTEGER NOT NULL,
PRODTYPE CHAR(1) NOT NULL,
PRIMARY KEY (PRODUCTID),
UNIQUE (PRODCODE,ACCOUNTID));


ALTER TABLE ORDERS ADD CONSTRAINT FK_ORDERS_PRODUCTS FOREIGN KEY
(PRODUCTID) REFERENCES PRODUCTS(PRODUCTID) ON DELETE CASCADE;

ALTER TABLE ORDERS ADD CONSTRAINT FK_ORDERS_CONTACTS FOREIGN KEY
(CONTACTID) REFERENCES CONTACTS(CONTACTID) ON DELETE CASCADE;

CREATE INDEX CONTACTS_ADDRESSKEY ON CONTACTS(ADDRESSKEY);
CREATE INDEX CONTACTS_COMPANY ON CONTACTS(COMPANY);
CREATE INDEX CONTACTS_TITLE ON CONTACTS(TITLE,ACCOUNTID);
CREATE INDEX CONTACTS_EMAIL ON CONTACTS(EMAIL);
CREATE INDEX CONTACTS_EXTERNALID ON CONTACTS(EXTERNALID);
CREATE INDEX CONTACTS_FIRSTNAME ON CONTACTS(FIRSTNAME);
CREATE INDEX CONTACTS_FULLNAMEKEY ON CONTACTS(FULLNAMEKY);
CREATE INDEX CONTACTS_LASTNAME ON CONTACTS(LASTNAME);
CREATE INDEX CONTACTS_NAMEKEY ON CONTACTS(NAMEKEY);
CREATE INDEX CONTACTS_PHONE ON CONTACTS(PHONE);
CREATE INDEX CONTACTS_ZIP ON CONTACTS(ZIP);
CREATE DESCENDING INDEX ORDERS_FIND ON ORDERS(ACCOUNTID, ORDERDATE,
ORDERAMOUNT, PRODUCTID);

CREATE VIEW ORDERSEARCH
(ACCOUNTID, CONTACTID,
COUNTRY, STATE, ZIP, GENDER, EMAIL, TITLE,
PRODUCTID, SUBJECTID, ORDERDATE, ORDERAMOUNT,
OKSENDEMAIL, OKSENDUSPS,
VALIDEMAIL, VALIDUSPS
) AS
select
ORDERS.ACCOUNTID, ORDERS.CONTACTID,
CONTACTS.COUNTRY, CONTACTS.STATE, CONTACTS.ZIP, CONTACTS.GENDER,
CONTACTS.EMAIL, CONTACTS.TITLE,
ORDERS.PRODUCTID, PRODUCTS.SUBJECTID, ORDERS.ORDERDATE,
ORDERS.ORDERAMOUNT,
CONTACTS.OKSENDEMAIL, CONTACTS.OKSENDUSPS,
CONTACTS.VALIDEMAIL, CONTACTS.VALIDUSPS
FROM ORDERS, PRODUCTS, CONTACTS
WHERE ORDERS.CONTACTID = CONTACTS.CONTACTID AND ORDERS.PRODUCTID =
PRODUCTS.PRODUCTID;


--
Steven Peterson, President
Frontier Productions, Inc.
310 Wesley Drive
Chapel Hill, NC 27516
http://www.frontierproductions.net
Tel: 919-942-1386
Fax: 919-933-2677