Icon View Incident Report

Serious Serious
Reported By: Ian Turner
Reported On: 12/12/2004
For: Version 4.15 Build 1
# 1924 Totally Un-Optimized WHERE Clause Conditions Can Cause Slower Than Expected Query Results

Previously I was using v4.08 (Delphi 7). On upgrading to 4.15 a large number of SQL queries are now running extremely slowly.

On a 30,000 record table, pulling off about 20 records, the example below, running on your Database System Utility takes 0.82 seconds to complete. Under v4.15 then same query takes ~ 10 mins.

SELECT History.DateStart, History.Duration, History.RoomNo,
       Patient.Title, Patient.ChristName, Patient.Surname,
Patient.PatientCode,  Patient.DOB, Patient.Retired, Patient.Sex,
Rooms.RoomName, Photo.Photo
FROM "History" History
LEFT OUTER JOIN "PatPhoto" Photo
ON (History.PatientCode = Photo.PatientCode),
"Patient" Patient, "Rooms" Rooms
WHERE (((History.DateStart >= '2004-12-06')  AND (History.DateStart <=
'2004-12-12')) OR
      (((History.DateStart+History.Duration-1) >= '2004-12-06')  AND
((History.DateStart+History.Duration-1) >= '2004-12-12'))
       AND ( History.LocCode = 'HARR' )
AND (Patient.PatientCode = History.PatientCode)
       AND (Rooms.LocCode = History.LocCode)
       AND (Rooms.RoomNo = History.RoomNo)

ORDER BY Patient.PatientCode

Comments Comments and Workarounds
The workaround is to index at least one condition in the WHERE clause, ideally the most selective. The problem was that DBISAM was not evaluating the cost of evaluating the un-optimized WHERE condition before or after the joins properly.

Resolution Resolution
Fixed Problem on 12/14/2004 in version 4.16 build 1