WFA Select only volume where there is no CIFS share

[masson]

Hello Team,

I'm trying to create a workflow where i have to select in a particular SVM only volume without any CIFS share.

I've create the following SQL Query:

SELECT
        volume.name as volname
FROM
        cm_storage.volume as volume,
        cm_storage.vserver as vserver,
        cm_storage.cluster as cluster
WHERE
        cluster.name='${ClusterName}'
    AND vserver.name='${VserverName}'
    AND volume.vserver_id=vserver.id
    AND volume.name LIKE '%vol_${VserverName}_w%'
    AND volume.name NOT LIKE '%w00%'
    AND volume.name NOT IN
(SELECT DISTINCT
    substr(cifs_share.path,2,17) as volumeName
FROM
    cm_storage.cifs_share as cifs_share,
    cm_storage.cluster as cluster,
    cm_storage.vserver as vserver
WHERE
    cluster.name='${ClusterName}'
    AND vserver.name='${VserverName}'
    AND cifs_share.path LIKE '%vol_${VserverName}_w%')
   

I know that using NOT IN is not recommended and indeed this query is a bit slow.

Do you have any idea to rewrite this query with something more efficient.

Regards.

[coreywanless · Accepted solution]

I love doing joins. Here is my rendition:

select
cluster.name as cluster,
vserver.name as vserver,
volume.name as volume
from
cm_storage.volume
left join cm_storage.vserver on vserver.id = volume.vserver_id
left join cm_storage.cluster on cluster.id = vserver.cluster_id
left join cm_storage.cifs_share on cifs_share.path like concat(volume.junction_path,'%')
where cifs_share.id is null

NOTE: Append any where statements you need

[masson]

Thanks Coreywanless,

It works very quicky

Best regards.