Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 8 of 8 total |
Big performance decrease from 3.19 to 4.24 |
Tue, Nov 14 2006 8:44 PM | Permanent Link |
Joe Apperson | 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] ================= |
Wed, Nov 15 2006 4:22 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Fri, Nov 17 2006 8:01 AM | Permanent Link |
"Joe Apperson" | 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 |
Fri, Nov 17 2006 11:32 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Mon, Nov 20 2006 7:09 AM | Permanent Link |
"Joe Apperson" | 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 |
Mon, Nov 20 2006 5:57 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Tue, Nov 21 2006 9:57 AM | Permanent Link |
"Joe Apperson" | 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 |
Tue, Nov 21 2006 1:30 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Tuesday, April 30, 2024 at 03:55 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |