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. Any ideas?
Thanks - Joe


The plan:
=================
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]
=================

Joe,

<< 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.
Any ideas?  >>

What happens if you code the update as follows:

update employee set effective_pc = e2.effective_pc
from employee INNER JOIN employee e2 ON employee.report_to = e2.emp_id
WHERE employee.effective_pc is null and e2.effective_pc is not null

?

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim Young wrote:
> What happens if you code the update as follows:
>
> update employee set effective_pc = e2.effective_pc
> from employee INNER JOIN employee e2 ON employee.report_to = e2.emp_id
> WHERE employee.effective_pc is null and e2.effective_pc is not null

Thanks for the suggestion Tim. Unfortunately, changing the update to use a
join
had no performance effect.

If this helps, I tried the query using 3.30 (vs. 3.19) of the Database
System Utility & experienced significantly slowed response time:
3.19 stats: 5119 rows affected, elapsed time was 0.953 secs.
3.30 stats: 5119 rows affected, elapsed time was 269.047 secs.

It looks like the issue is from a behavior change from many versions ago,
perhaps related to how null values are handled? That seems to be the
bottleneck. If I remove the where clause, the 3.30 time goes back to a
little over 1 second.

Thanks - Joe

Joe,

<< Thanks for the suggestion Tim. Unfortunately, changing the update to use
a join had no performance effect. >>

Just so you know, your original query is using a join also, just phrased
differently.

<< If this helps, I tried the query using 3.30 (vs. 3.19) of the Database
System Utility & experienced significantly slowed response time:
3.19 stats: 5119 rows affected, elapsed time was 0.953 secs.
3.30 stats: 5119 rows affected, elapsed time was 269.047 secs.

It looks like the issue is from a behavior change from many versions ago,
perhaps related to how null values are handled? >>

It shouldn't.  NULL comparisons will only be an issue with LOJs, not inner
joins.

<< That seems to be the bottleneck. If I remove the where clause, the 3.30
time goes back to a little over 1 second. >>

Could you possibly send me the tables that you're using ?  This one is very
puzzling since the WHERE clause NULL comparisons shouldn't have any bearing
on the performance in relation to the join.  IOW, they should be a simple
comparison on the field values *after* the join produces the rows that will
go into the result set.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim Young wrote:
> Could you possibly send me the tables that you're using ?  This one is
> very puzzling since the WHERE clause NULL comparisons shouldn't have any
> bearing on the performance in relation to the join.  IOW, they should be a
> simple

Tim, I sent a sample file & sql script for you to look at in more detail
last Fri. afternoon.
Thanks - Joe

Joe,

<< Tim, I sent a sample file & sql script for you to look at in more detail
last Fri. afternoon. >>

Okay, it appears to be a bug caused by the UPDATE kicking in the change
detection on the second instance of the employee table.  I've got it
included with the fixes coming with the new 4.24 build.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim Young wrote:
> Okay, it appears to be a bug caused by the UPDATE kicking in the change
> detection on the second instance of the employee table.  I've got it
> included with the fixes coming with the new 4.24 build.

Thank you very much Tim for finding that. Of course, one last question that
begs to be asked is "Any ETA on the next 4.24 build?" Corporate pressure is
increasing our need to get the application running under the current Delphi
& component versions, so any estimated date you could provide would be
fantastic.

Thanks - Joe

Joe,

<< Thank you very much Tim for finding that. Of course, one last question
that begs to be asked is "Any ETA on the next 4.24 build?" Corporate
pressure is increasing our need to get the application running under the
current Delphi & component versions, so any estimated date you could provide
would be fantastic. >>

Hopefully it won't be much longer than this upcoming Monday.  We're in the
process of doing the final modifications on our internal build systems for
ElevateDB, and this has been preventing any new DBISAM build.

--
Tim Young
Elevate Software
www.elevatesoft.com