First, make sure the selectivity of your index is correct by using
SET STATISTICS INDEX name;

I have had situations in the past like yours where I have changed the BT JOIN clause to
JOIN CLIENTBILLTO BT ON SO.CLIENTBILLTOID = BT.CLIENTBILLTOID AND (BT.ClientBillToID > 0)
or use a similar clause involving fields in the desired index where the condition is always true.

HTH

Louis Kleiman
SSTMS, Inc.

----- Original Message -----
From: GreatDayDan
To: ib-support@yahoogroups.com
Sent: Wednesday, April 03, 2002 4:39 PM
Subject: [ib-support] Indexes and Joins


Good Morning!

I have a query that looks like this:

SELECT SO.SERVICEORDERID, SO.STATUS,SO.CLIENTBILLTOID,
BT.ATTN,BT.ADDRESS1, BT.ADDRESS2, BT.ADDRESS3,
BT.CITY, BT.ST, BT.ZIP,
CL.NAME,
CL.ACCOUNTNUMBER,SO.VERBALPO,SO.BLANKETPOID,
SUM((PD.QTY*PD.SALEPRICE)+PD.SALESTAX) AS
PARTTOTAL
FROM SERVICEORDER SO
JOIN CLIENTBILLTO BT ON SO.CLIENTBILLTOID =
BT.CLIENTBILLTOID
JOIN CLIENT CL ON BT.CLIENTID = CL.CLIENTID
LEFT OUTER JOIN PARTDETAIL PD ON SO.SERVICEORDERID =
PD.SERVICEORDERID
WHERE SO.STATUS = 10
AND SO.DATECOMPLETED < '04/03/2002'
AND SO.INVOICENUMBER IS NULL
GROUP BY SO.SERVICEORDERID,
SO.STATUS,SO.CLIENTBILLTOID,
BT.ATTN,BT.ADDRESS1, BT.ADDRESS2, BT.ADDRESS3,
BT.CITY, BT.ST, BT.ZIP,
CL.NAME,
CL.ACCOUNTNUMBER,SO.VERBALPO,SO.BLANKETPOID


This is the plan that IB comes up with:

PLAN
SORT
(JOIN
(JOIN
(BT Natral,

CL INDEX (RDB$PRIMARY13),
SO INDEX (SERVICEORDER_COMPLETEDATE,
SERVICEORDER_CLIENTBILLTOID,
SERVICEORDER_STATUS
)
),
PD INDEX (PARTDETAIL_SO)
)
)

This makes for a rather slow result set.
If I try to use **BT INDEX (RDB$PRIMARY14)**, IB
complains that that index cannot be used. That is the
primary key for that table.

Any ideas why it cannot be used?

Thanks...Dan'l



=====
____________________________________
Every day is a Great day, but
some days are GREATER than others!

__________________________________________________
Do You Yahoo!?
Yahoo! Tax Center - online filing with TurboTax
http://taxes.yahoo.com/

Yahoo! Groups Sponsor
ADVERTISEMENT




To unsubscribe from this group, send an email to:
ib-support-unsubscribe@egroups.com



Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.


[Non-text portions of this message have been removed]