Icon View Incident Report

Serious Serious
Reported By: Joe Apperson
Reported On: 11/17/2006
For: Version 4.24 Build 1
# 2217 Executing an UPDATE Statement with a Self-Join Results in Bad Performance

We have an application currently using D6 & DBISAM 3.19 very successfully in a production environment. In moving the same app to BDS 2006, using DBISAM 4.24B1, some very noticeable SQL performance problems show themselves. An update query that took a few seconds before now takes almost 5 minutes. The update needs to run 10 times (the plan was the same for each run, only the elapsed times & records effected changed).

I can work around the problem by adding a new index on the effective_pc field, but why the drastic change in performance is the real question. I tried the NOJOINOPTIMIZE qualifier as well, without any noticeable change.

=================
SQL statement (Executed with 4.24 Build 1)
=================

update employee set effective_pc = e2.effective_pc
 from employee, employee e2 
 where employee.report_to = e2.emp_id
 and employee.effective_pc is null
 and e2.effective_pc is not null

Tables Involved
---------------

employee (employee) table opened shared, has 9356 rows
employee (e2) table opened shared, has 9356 rows

WHERE Clause Execution
----------------------

Join Ordering
-------------

The driver table is the employee table (employee)

The employee table (employee) is joined to the employee table (e2) with the 
INNER JOIN expression:

employee.report_to = e2.emp_id

Optimizer will attempt to re-order the joins to a more optimal order
Use the NOJOINOPTIMIZE clause at the end of the SQL statement to force the 
optimizer to leave the joins in their declared order

The joins are already in optimal order and cannot be optimized any further

The expression:

employee.effective_pc is null 

is UN-OPTIMIZED, covers 9356 rows or index keys, costs 3667552 bytes, and will 
be applied to the employee table (employee) before any joins

The expression:

e2.effective_pc is not null 

is UN-OPTIMIZED, covers 9356 rows or index keys, costs 3667552 bytes, and will 
be applied to the employee table (e2) before any joins

The expression:

employee.report_to = e2.emp_id

is OPTIMIZED

=================
>>>>> 5119 rows affected in 288.063 seconds [Run 1]
>>>>> 2189 rows affected in 121.187 seconds [Run 2]
>>>>> 496 rows affected in 29.5 seconds [Run 3]
>>>>> 15 rows affected in 2.61 seconds [Run 4]
>>>>> 0 rows affected in 1.843 seconds [Runs 5 thru 10]
=================



Resolution Resolution
Fixed Problem on 11/19/2006 in version 4.25 build 1


Products Affected Products Affected
DBISAM Additional Software and Utilities
DBISAM ODBC Client-Server
DBISAM ODBC Client-Server with Source
DBISAM ODBC Standard
DBISAM ODBC Standard with Source
DBISAM ODBC Trial
DBISAM VCL Client-Server
DBISAM VCL Client-Server with Source
DBISAM VCL Standard
DBISAM VCL Standard with Source
DBISAM VCL Trial

Image