Icon View Incident Report

Serious Serious
Reported By: Clive Bennett
Reported On: 3/29/2004
For: Version 4.05 Build 1
# 1632 Using an Un-Optimized Condition in an SQL SELECT WHERE Clause Can Cause Slow Results with Joins

This query executes fine if the first field in the primary key is NOT scenario_code, If I push scenario_code to be the first field the query hangs.

  rpt_generic_detail r
  inner join rpt_generic_detail cash_sales on cash_sales.period = r.
and cash_sales.scenario_code = r.scenario_code
  inner join rpt_generic_detail purc on purc.period = r.period and
purc.scenario_code = r.scenario_code
  inner join rpt_generic_detail wag on wag.period = r.period and
wag.scenario_code = r.scenario_code
  inner join rpt_generic_detail int on int.period = r.period and
int.scenario_code = r.scenario_code
  inner join rpt_generic_detail e on e.period = r.period and e.
= r.scenario_code
  inner join periods p on p.period = r.period and p.project_code =
  r.scenario_code =-2 and
  r.rowid = 12 and
  p.project_code = 1 and
  int.rowid = 5 and
  wag.rowid = 15 and
  purc.rowid = 10 and
  e.rowid = 85 and
  cash_sales.rowid = 8

Comments Comments and Workarounds
Adding an index on the RowID field solves the problem. The problem was that the query was taking a very long time to execute due to the un-optimized RowID conditions. DBISAM was treating un-optimized WHERE conditions that are on the target table of an INNER JOIN condition in a way that was inefficient for this query.

Resolution Resolution
Fixed Problem on 3/29/2004 in version 4.06 build 1