Icon View Incident Report

Serious Serious
Reported By: Paul Richardson
Reported On: 8/28/2012
For: Version 4.33 Build 1
# 3631 LEFT OUTER JOIN Conditions Can Cause Performance

We have noticed a decrease in performance with version 4.32.0.1. The following SQL script when run on the same data in version 4.21.0.11 returned no rows almost instantly. However in 4.32.0.1 takes seconds if not minutes to return no rows, with the dbsys program stopping responding. It also seems to take extremely large amounts of cpu usage and the DBServer application's memory usage grows rather large. The first SQL in the script puts no rows into "\\Memory\cwnjob" table, 4.21.0.11 seems to handle this and the subsequent SQL returns nothing very quickly.

select JB_JobNo as njob  
into "\\Memory\cwnjob" 
from Jobs 
join claimwatch v on jb_Jobno = v.CW_jobno and v.CW_send = True 
top 20;                                                         

CREATE INDEX IF NOT EXISTS "cjob" ON "\\Memory\cwnjob" ("njob"); 

select JB_JobNo,  
min(o.NT_LineNo) as "Onsite", 
max(f.NT_LineNo) as "Job complete", 
max(h.NT_LineNo) as "Handover", 
min(i.NT_LineNo) as "Invoiced", 
max(eb.NT_LineNo) as "estbook", 
min(es.NT_LineNo) as "estimate", 
max(ei.NT_LineNo) as "inspected", 
max(ea.NT_LineNo) as "authorised", 
max(en.NT_LineNo) as "incident", 
max(er.NT_LineNo) as "notif", 
max(ep.NT_LineNo) as "inspbook", 
max(el.NT_LineNo) as "tloss", 
min(p.NT_LineNo) as "parts", 
min(r.NT_LineNo) as "recv",   
max(ca.NT_LineNo) as "canc", 
min(TS_Unique) as "inprog" 
 
into "\\Memory\times" 
from Jobs 
join "\\Memory\cwnjob" on jb_jobno = njob 
left outer join notes h on jb_jobno = h.NT_Identifier and h.NT_Note like 'Set as Collected on%' 
left outer join notes o on jb_jobno = o.NT_Identifier and (o.NT_Note like 'Job flagged as On-Site%' or o.NT_Note like '%Assigned Key Tag number%') 
left outer join notes f on jb_jobno = f.NT_Identifier and Upper(f.NT_Note) like '%SET AS FINISHED%' 
left outer join notes i on jb_jobno = i.NT_Identifier and i.NT_Note like 'Invoiced by %' 
left outer join notes eb on jb_estno = eb.NT_Identifier and eb.NT_Note like 'Estimate Booking Date %' 
left outer join notes es on jb_estno = es.NT_Identifier and (es.NT_Note like 'Estimated Date %' or es.NT_Note like 'Inserted by import %' or es.NT_Note like 'Linked to Job %') 
left outer join notes ei on jb_estno = ei.NT_Identifier and ei.NT_Note like 'Inspected Date %' 
left outer join notes ea on jb_estno = ea.NT_Identifier and ea.NT_Note like 'Authorised Date %' 
left outer join notes en on jb_estno = en.NT_Identifier and en.NT_Note like 'Incident Date %' 
left outer join notes er on jb_estno = er.NT_Identifier and er.NT_Note like 'Repair Notification %' 
left outer join notes ep on jb_estno = ep.NT_Identifier and ep.NT_Note like 'Inspection Booking Date %' 
left outer join notes el on jb_jobno = el.NT_Identifier and el.NT_Note like 'Set as Total Loss on %' 
left outer join notes p on jb_jobno = p.NT_Identifier and p.NT_Note like 'Parts Ordered: %' 
left outer join notes r on jb_jobno = r.NT_Identifier and r.NT_Note like 'Parts Received: %' 
left outer join notes ca on jb_jobno = ca.NT_Identifier and ca.NT_Note like 'Job Cancelled - %' 
left outer join Timeshts on jb_jobno = TS_JobNo and TS_Date > '1991-01-01' 
group by 1;



Comments Comments
This is a very similar issue to incident #3077, and thanks to Paul for suggesting that this was the case. In this case, the problem was with the non-join LIKE conditions.


Resolution Resolution
Fixed Problem on 8/31/2012 in version 4.34 build 1


Products Affected Products Affected
DBISAM Additional Software and Utilities
DBISAM CLX Client-Server
DBISAM CLX Client-Server with Source
DBISAM CLX Standard
DBISAM CLX Standard with Source
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