NoPublic, bureaucrat, administrator
1,068
edits
Changes
no edit summary
=== 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!):
{{Note|These two queries are in MS Access syntax, not pgSQL syntax. In order to get a working pgSQL syntax, the [] (square brackets) need to be replaced by "" (double quotes) and the "" (double quotes) in the WHERE clause by single quotes}}
<pre>
SELECT districtfree_view_hazardclosed.Region,
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.