NoPublic, bureaucrat, administrator
1,068
edits
Changes
no edit summary
== Examples ==__NOEDITSECTION__
=== View my_view_hazardreductions in Afghanistan ===__NOEDITSECTION__
Version with a very poor performance(approx. 860 seconds):
<pre>
CREATE OR REPLACE VIEW my_view_hazardreductions AS
AND customdefinedfield.name = 'CDF-Clearance Type';
</pre>
Optimised version(approx. 160 seconds):
<pre>
WITH hazreduc_imsmaenum AS (
=== Problematic WHERE clause example ===__NOEDITSECTION__
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]
GROUP BY districtfree_view_hazardclosed.Region, districtfree_view_hazardclosed.Province, districtfree_view_hazardclosed.District, districtfree_view_hazardclosed.district_guid;
</pre>
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"))
GROUP BY districtfree_view_hazardclosed.Region, districtfree_view_hazardclosed.Province, districtfree_view_hazardclosed.District, districtfree_view_hazardclosed.district_guid;
</pre>
In this case, it seemed safe to make the according changes, because it is not very likely that another status will be added to the system (and thus the two where clauses are and will stay equivalent). However, this cannot be generalised to other cases.
== Summary of recommendations ==__NOEDITSECTION__