NoPublic, bureaucrat, administrator
1,068
edits
Changes
no edit summary
In Afghanistan, there is an Access-based reporting system that is building on views created in the IMSMA database. When upgrading to V6, some of the Access queries had a very poor performance. In particular, the following query was affected (it initially took about 30 minutes!):
<pre>
SELECT districtfree_view_hazardclosed.Region, districtfree_view_hazardclosed.Province, districtfree_view_hazardclosed.District, districtfree_view_hazardclosed.district_guid, Max(districtfree_view_hazardclosed.StatusChangedDate) AS [Last Hazard Closed]
FROM districtfree_view_hazardclosed
WHERE (((districtfree_view_hazardclosed.Status)="Expired"))
After investigation, it turned out that the WHERE clause (Status = "Expired") was the bottleneck. The following, equivalent query takes approx. 18 seconds:
<pre>
SELECT districtfree_view_hazardclosed.Region, districtfree_view_hazardclosed.Province, districtfree_view_hazardclosed.District, districtfree_view_hazardclosed.district_guid, Max(districtfree_view_hazardclosed.StatusChangedDate) AS [Last Hazard Closed]
FROM districtfree_view_hazardclosed
WHERE (((districtfree_view_hazardclosed.Status) <> "Active" AND (districtfree_view_hazardclosed.Status) <> "Transitional"))