Hi Team,
We've been using Interbase for the last few weeks, and we've finally run
into a wall.
I'm trying to figure out why this query takes between 25 and 55 seconds to
run (and why there's no obvious reason for the discrepancy).
It's also pretty slow considering the speed we've seen from Interbase for
other queries. I figure we've misunderstood how Interbase uses indexes, and
we're missing one or two. I've got a description of the PLAN in both cases
(the second case simply adds an ORDER clause, and gets an extra SORT
operation on the PLAN), but I can't figure out what it means exactly.
We're running Interbase on a dedicated server (P200, 64mb RAM, 6gb hard
disk, running Windows NT Workstation) with a Xitami web-server (with
minimal load since we're using it only for development and testing) and a
few other programs doing odd jobs on it.
NB: The field lists in the SELECT clauses below have been dramatically
truncated to allow ISQL to display the PLAN description. We're actually
bringing back about 15 fields from the tables in the FROM clause.
Basically, what I need to know is:
1) What do these PLANs actually mean, and
2) Are there any new indexes I can create that should help speed things up
(and why)
If anyone can help, I'd be very grateful.
Cheers,
Simon Mahony,
System Creator,
MetService.
------------------------------------------------------------------------
------------------------------------------------------------------------
------
SELECT pki.display_rule, prd.prod_id, iss.data
FROM package_items pki, products prd LEFT OUTER JOIN issues iss ON
(prd.prod_id = iss.prod_id)
WHERE (( prd.prod_id = pki.prod_id) and ( pki.pkg_id = 197) )
ORDER BY pki.display_order, iss.validity_start DESCENDING, iss.issue_time
DESCENDING
PLAN SORT (MERGE (SORT (PKI INDEX (RDB$PRIMARY5)),SORT (JOIN (PRD
NATURAL,ISS INDEX (ISSUE_START)))))
{it looks to me as if the query is only using two of the available indexes,
which seems a bit under-resourced.}
DISPLAY_RULE PROD_ID
============ ===========
VALID_FOR 108
VALID_AT 159
LATEST 239
LATEST 239
LATEST 239
LATEST 239
LATEST 239
LATEST 239
<Pay no attention to the result set - I've truncated it severely>
------------------------------------------------------------------------
------------------------------------------------------------------------
------
SELECT pki.display_rule, prd.prod_id, iss.data
FROM package_items pki, products prd LEFT OUTER JOIN issues iss ON
(prd.prod_id = iss.prod_id)
WHERE (( prd.prod_id = pki.prod_id) and ( pki.pkg_id = 197) )
PLAN MERGE (SORT (PKI INDEX (RDB$PRIMARY5)),SORT (JOIN (PRD NATURAL,ISS
INDEX (ISSUE_START))))
DISPLAY_RULE PROD_ID
============ ===========
VALID_FOR 108
VALID_AT 159
LATEST 239
LATEST 239
LATEST 239
LATEST 239
LATEST 239
LATEST 239
<Pay no attention to the result set - I've truncated it severely>
------------------------------------------------------------------------
------------------------------------------------------------------------
------
SHOW INDEX (editied to show only indexes for the tables involved)
--------------------------
ISSUE_START UNIQUE DESCENDING INDEX ON ISSUES(PROD_ID, VALIDITY_START,
ISSUE_TIME)
RDB$PRIMARY22 UNIQUE INDEX ON ISSUES(ISSUE_ID)
PKG_NAME INDEX ON PACKAGES(PKG_NAME)
RDB$PRIMARY4 UNIQUE INDEX ON PACKAGES(PKG_ID)
RDB$FOREIGN6 INDEX ON PACKAGE_ITEMS(PKG_ID)
RDB$FOREIGN7 INDEX ON PACKAGE_ITEMS(PROD_ID)
RDB$PRIMARY5 UNIQUE INDEX ON PACKAGE_ITEMS(PKG_ID, PROD_ID)
PROD_CONTENTS INDEX ON PRODUCTS(PROD_CONTENT_TYPE)
PROD_DID INDEX ON PRODUCTS(PROD_DID)
RDB$PRIMARY1 UNIQUE INDEX ON PRODUCTS(PROD_ID)
<end>
---------------------------------------------------------------------------
New Zealand Delphi Users group - Database List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
