Liran Zelkha has uploaded a new change for review. Change subject: core: Improve performance of GetStorageConnectionsByStorageTypeAndStatus ......................................................................
core: Improve performance of GetStorageConnectionsByStorageTypeAndStatus This Stored Procedure is slow because of distinct and UNION operators. The refactored implementation uses sub-selects to improve performance, Change-Id: I8048b87fa321086469c892c3650f7c5548cc13d8 Bug-Url: https://bugzilla.redhat.com/1186763 Signed-off-by: lzel...@redhat.com <lzel...@redhat.com> --- M packaging/dbscripts/storages_san_sp.sql 1 file changed, 9 insertions(+), 13 deletions(-) git pull ssh://gerrit.ovirt.org:29418/ovirt-engine refs/changes/28/37528/1 diff --git a/packaging/dbscripts/storages_san_sp.sql b/packaging/dbscripts/storages_san_sp.sql index e0f9241..75e43dc 100644 --- a/packaging/dbscripts/storages_san_sp.sql +++ b/packaging/dbscripts/storages_san_sp.sql @@ -536,19 +536,15 @@ statuses int[]; BEGIN statuses := string_to_array(v_statuses,',')::integer[]; -RETURN QUERY SELECT * FROM (SELECT distinct storage_server_connections.* - FROM - LUN_storage_server_connection_map LUN_storage_server_connection_map - INNER JOIN LUNs ON LUN_storage_server_connection_map.LUN_id = LUNs.LUN_id - INNER JOIN storage_domains ON LUNs.volume_group_id = storage_domains.storage - INNER JOIN storage_server_connections ON LUN_storage_server_connection_map.storage_server_connection = storage_server_connections.id - WHERE (storage_domains.storage_pool_id = v_storage_pool_id and storage_domains.status = any(statuses)) - UNION - SELECT distinct storage_server_connections.* - FROM storage_server_connections - INNER JOIN storage_domains ON storage_server_connections.id = storage_domains.storage - WHERE (storage_domains.storage_pool_id = v_storage_pool_id and storage_domains.status = any(statuses)) - ) connections WHERE (v_storage_type is NULL or connections.storage_type = v_storage_type); +RETURN QUERY SELECT * + FROM storage_server_connections + WHERE (v_storage_type is NULL or connections.storage_type = v_storage_type) + AND (id in (select storage from storage_domains where storage_domains.storage_pool_id = v_storage_pool_id and storage_domains.status = any(statuses)) + OR (id in (select lun_storage_server_connection_map.storage_server_connection + FROM lun_storage_server_connection_map + INNER JOIN luns ON lun_storage_server_connection_map.lun_id = luns.lun_id + INNER JOIN storage_domains ON luns.volume_group_id = storage_domains.storage + WHERE (storage_domains.storage_pool_id = v_storage_pool_id and storage_domains.status = any(statuses))))); END; $procedure$ LANGUAGE plpgsql; -- To view, visit http://gerrit.ovirt.org/37528 To unsubscribe, visit http://gerrit.ovirt.org/settings Gerrit-MessageType: newchange Gerrit-Change-Id: I8048b87fa321086469c892c3650f7c5548cc13d8 Gerrit-PatchSet: 1 Gerrit-Project: ovirt-engine Gerrit-Branch: master Gerrit-Owner: Liran Zelkha <lzel...@redhat.com> _______________________________________________ Engine-patches mailing list Engine-patches@ovirt.org http://lists.ovirt.org/mailman/listinfo/engine-patches
