View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0001948 | JEDI VCS | Server | public | 2004-07-07 11:03 | 2005-06-13 17:02 |
| Reporter | USchuster | Assigned To | THuber | ||
| Priority | normal | Severity | tweak | Reproducibility | always |
| Status | closed | Resolution | fixed | ||
| Product Version | |||||
| Target Version | Fixed in Version | 2.40 RC2 | |||
| Summary | 0001948: Speedup of GET_DESERTED_MODULES | ||||
| Description | Speedup of GET_DESERTED_MODULES by - using the following statement to retrieve the deserted modules select modules.moduleid, modules.name from modules where modules.moduleid not in (select pjmodule.moduleid from pjmodule) confirmed for DBISAM3, ORACLE, Firebird - setting ModuleIDs.Sorted := True for every other DBMS (at least MySQL 3.23 which doesn't support subselect) | ||||
| Tags | No tags attached. | ||||
| Fix in JVCS version | 2.40 RC2 | ||||
| Releasedocumentation | |||||
|
|
use a tstringlist here is very slow. better is using a tlist and typecast the integers (the IDs) to pointers. if you would keep tstringlist: set stored should be done after the tstringlist is filled, this will sort only once (and not after every add). |
|
|
same on GET_BLANK_MODULE_LIST |
|
|
Of course the memory footprint would be lower and the integer compare faster with TList, but it has no build in binary search. If TStringList is Sorted it uses binary search for IndexOf. You mean "Sorted" and not "stored" but it doesn't really matter if you set Sorted before (forces insertion by binary search) or after (forces quicksort) adding all items. Using JVCSClasses.TSortedIntegerList would be a solution with low memory footprint and fast compare. This requires some changes in JVCSClasses first to reduce the dependencies. |
|
|
Instead of subselect there is also a solution over join which should work (not tested) SELECT modules.moduleid, modules.name FROM modules LEFT JOIN pjmodule ON modules.moduleid=pjmodule.moduleid WHERE pjmodule.moduleid IS NULL; Not sure if this will run with same result without changes on all JVCS DBMS. |
|
|
wilf from the newsgroup here, Just tested the use of the IN operator in DBISAM 3, on an average developers box (PIII 1.? Ghz, 512MB RAM) With 49 895 modules and an OPTIMIZED table it takes about 11 seconds. If the modules and pjmodules table are badly optimized (for example, by deleting about 33 000 modules, then the results are very slow (202 seconds). Reoptimizing the tables brings it down to 2 seconds for 16 989 modules. With 199 580 modules at 91% optimization (ie. 9% deleted records) it takes about 186 seconds. Reoptimizing brings it down to 110 seconds. Query was: select modules.moduleid, modules.name from modules where modules.moduleid not in (select pjmodule.moduleid from pjmodule) SUMMARY: It will work well if there are not too many deleted pjmodule or modules entries. If there are many, optimization will help. OBSERVATION: On the same tests, Interbase never takes longer than 10 seconds. Definitely more scalable. |
|
|
@Wilf: Could you also give the join solution a try with DBISAM & Firebird? Thanks for your time! Btw: join solution tested on a huger MySQL archive runs fast and fine, so I can confirm join solution to be ok for: MySQL 3.23, Firebird 1.5 edited on: 07-08-04 16:57 Comment on Flashfiler tests: both, IN and JOIN working but IN ~3times faster edited on: 07-09-04 11:39 |
|
|
The LEFT JOIN (and LEFT OUTER JOIN) statement does work with Oracle 9 but not with 8. I can't recognize any performance difference between the both statements with around 5000 modules. |
|
|
For Oracle 8, the following statement works: SELECT modules.moduleid, modules.name FROM modules, pjmodule WHERE pjmodule.moduleid IS NULL and pjmodule.moduleid (+)= modules.moduleid; However, this is slower then the previous suggested select modules.moduleid, modules.name from modules where modules.moduleid not in (select pjmodule.moduleid from pjmodule); 3.4s vs. 0.2s (Both SQLs result in 9147 hits with a total of 30625 modules) |
|
|
Suggestion in summary (Please edit for another approach): DBI: IN FFR: IN FIB: IN IBX: JOIN ORA: IN MYS: JOIN MSQ: IN edited on: 07-09-04 13:13 |
|
|
Wilf again, Wow! The JEDI-VCS team sure is fast/smart! LEFT OUTER JOIN worked in about the same times on DBISAM 3 which surprised me. No faster or slower, which suggests it has a very simple optimizer/join engine. More importantly, At this point each engine has some sort of "single-statement" SQL that works. Using IFDEF or some kind of "driver" class to produce SQL for each engine is straightforward as long as the difference is simply the SQL string. We no longer have the problem of a different sequence of steps depending on the engine. Simply set the correct string for the engine and let the engine do all the work. If you are going to have different SQL for different engines, I would recommend using the IN solution whenever possible, simply because it is very straightforward to read and understand. Use the harder to read LEFT OUTER join just for MYSQL. |
|
|
Wilf one more time, Sorry I don't have edit rights, but it looks like the summary is now (* on changed items): DBI: IN FFR: IN *FIB: IN IBX: IN ORA: IN *MYS: LEFT JOIN (LEFT OUTER JOIN?) MSQ: IN |
|
|
@Wilf Change is alread commited to demos.href, I kept the Join for IBX as I had something in mind that Interbase to 6.x had problems with subselects, but I might be wrong. But as I see IB6 deprecated for next JVCS release and performance difference in Firebird between Join and subselect are not too much, I think we can keep this. |
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2004-07-07 11:03 | USchuster | New Issue | |
| 2004-07-07 13:19 | schuettecarsten | Note Added: 0004710 | |
| 2004-07-07 13:22 | schuettecarsten | Note Added: 0004711 | |
| 2004-07-07 13:50 | USchuster | Note Added: 0004712 | |
| 2004-07-08 04:20 | USchuster | Description Updated | |
| 2004-07-08 13:14 | THuber | Status | new => confirmed |
| 2004-07-08 13:14 | THuber | Description Updated | |
| 2004-07-08 15:32 | THuber | Note Added: 0004719 | |
| 2004-07-08 15:50 | anonymous | Note Added: 0004720 | |
| 2004-07-08 15:58 | THuber | Note Added: 0004721 | |
| 2004-07-08 16:57 | THuber | Note Edited: 0004721 | |
| 2004-07-09 02:01 | USchuster | Note Added: 0004722 | |
| 2004-07-09 04:44 | dors | Note Added: 0004724 | |
| 2004-07-09 08:43 | THuber | Note Added: 0004725 | |
| 2004-07-09 11:39 | THuber | Note Edited: 0004721 | |
| 2004-07-09 11:39 | THuber | Note Edited: 0004725 | |
| 2004-07-09 13:13 | THuber | Note Edited: 0004725 | |
| 2004-07-09 14:28 | anonymous | Note Added: 0004726 | |
| 2004-07-09 14:34 | anonymous | Note Added: 0004727 | |
| 2004-07-09 15:19 | THuber | Note Added: 0004728 | |
| 2004-07-09 15:20 | THuber | Status | confirmed => resolved |
| 2004-07-09 15:20 | THuber | Resolution | open => fixed |
| 2004-07-09 15:20 | THuber | Assigned To | => THuber |
| 2004-12-10 15:57 | THuber | Fix in JVCS version | => 2.40 RC2 |
| 2004-12-10 15:57 | THuber | Fixed in Version | => 2.40 RC2 |
| 2005-01-15 05:54 | THuber | Relationship added | child of 0002361 |
| 2005-06-13 17:02 | THuber | Status | resolved => closed |
