title: Optimising a SQL Query with Union All
body: <p>I have a query with 4 Select Queries joined by Union All to return one resultset, they are pretty complex select queries but no joins are involved, they actually select from 2 different views. I am just looking into optimising it but not really sure where I should start. The "Actual Execution Plan" is saying the "Estimated subtree cost" is 2.12357, I always assumed if it was over 1 then it's a pretty slow query? is that a correct assumption? I have also checked the entire plan and there are no table scans, only clustered Index Scans and Clustered Index Seeks which again I am assuming are ok? There is however a lot of Hash Matches. Anyway this query will be getting ran a lot of times hence why i'd like it optimised as best as possible. Hope it makes some sort of sense.</p>
  
  <p>Ok, the SQL looks like this </p>
  
  <pre><code>CREATE PROCEDURE [dbo].[CompareFiles] -- Add the parameters for the stored procedure here
  @VersionID int, 
  @SyncRequestID int,     
  @RegionID int, 
  @LanguageID int 
  AS
  BEGIN
  
  SELECT 'Delete' AS Action, Type, SUBSTRING(FullPath,2,LEN(FullPath)-1) AS FullPath, FileNameOnServer, FileSize FROM View1
  WHERE NOT EXISTS(SELECT 1 FROM View2 where View2.FullPath = View1.FullPath 
  AND View2.VersionId = @VersionID
  AND View2.RegionID = @RegionID 
  AND View2.LanguageID = @LanguageID
  )
  AND View1.SyncRequestID = @SyncRequestID
  
  UNION ALL 
  
  SELECT 'Add' AS Action, Type, SUBSTRING(FullPath,2,LEN(FullPath)-1) AS FullPath, FileNameOnServer,FileSize FROM View2  
  WHERE NOT EXISTS(SELECT 1 FROM View1 where View1.FullPath = View2.FullPath AND View1.SyncRequestID = @SyncRequestID)
  AND 
  View2.VersionId = @VersionID
  AND View2.RegionID = @RegionID
  AND View2.LanguageID = @LanguageID
  
  UNION ALL 
  
  SELECT 'Delete' AS Action, Type, SUBSTRING(FullPath,2,LEN(FullPath)-1) AS FullPath, FileNameOnServer, FileSize FROM View2 
  WHERE EXISTS
  (
      SELECT 1 FROM View2 fd2 
      WHERE 
          View2.FullPath = fd2.FullPath AND fd2.VersionID = @VersionID
  AND 
      View2.FileNameOnServer &lt;&gt; fd2.FileNameOnServer 
  AND 
      fd2.RegionID = @RegionID
  AND 
      fd2.LanguageID = @LanguageID 
  )
  AND 
      View2.VersionId = (SELECT VersionID FROM SyncRequest WHERE SyncRequestID = @SyncRequestID)
  AND 
      View2.RegionID = @RegionID
  AND 
      View2.LanguageID = @LanguageID
  
  UNION ALL
  
  SELECT 'Add' AS Action, Type, SUBSTRING(FullPath,2,LEN(FullPath)-1) AS FullPath, FileNameOnServer, FileSize FROM View2 
  WHERE EXISTS
  (
  SELECT 1 FROM View2 fd2 
  WHERE 
      View2.FullPath = fd2.FullPath AND fd2.VersionID = (SELECT VersionID FROM SyncRequest WHERE SyncRequestID = @SyncRequestID)
  AND 
      View2.FileNameOnServer &lt;&gt; fd2.FileNameOnServer 
  AND 
      fd2.RegionID = @RegionID
  AND 
      fd2.LanguageID = @LanguageID 
  )
  AND 
      View2.VersionId = @VersionID
  AND 
      View2.RegionID = @RegionID
  AND 
      View2.LanguageID = @LanguageID
  
  END
  </code></pre>
  
  <p>View 1 Definition</p>
  
  <pre><code>SELECT DISTINCT fqp.Path + '/' + ISNULL(f.Name, '') + ISNULL(f.Extension, '') AS FullPath, fl.LanguageID, fr.RegionID, f.FileID, dbo.CIT_APD_Versions.ApID, f.FileNameOnServer, dbo.Versions.VersionID, (CASE WHEN FileNameOnServer IS NULL THEN 'Folder' ELSE 'File' END) AS Type, f.FileSize
  FROM dbo.FileRegions AS fr 
  RIGHT OUTER JOIN dbo.Files AS f 
      INNER JOIN dbo.FilePaths AS fp ON fp.FileID = f.FileID ON fr.FileID = f.FileID 
      LEFT OUTER JOIN dbo.FileLanguages AS fl ON fl.FileID = f.FileID 
      LEFT OUTER JOIN dbo.RoleFiles AS fro ON fro.FileID = f.FileID 
      RIGHT OUTER JOIN dbo.Versions 
          INNER JOIN dbo.View3 AS fqp ON dbo.VersionID = fqp.VersionID ON fqp.PathID = fp.PathID
  </code></pre>
  
  <p>View 2 Definition</p>
  
  <pre><code>SELECT     '//' + dbo.CIT_APD_SyncRequestFiles.FullPath AS FullPath, NULL AS LanguageID, NULL AS RegionID, NULL AS FileID, NULL AS ApID, 
                    dbo.SyncRequest.VersionID, '' AS FileNameOnServer, '' AS Type, NULL AS FileSize, dbo.SyncRequestFiles.SyncRequestID
  FROM dbo.SyncRequestFiles 
  INNER JOIN dbo.CIT_APD_SyncRequest ON dbo.SyncRequestFiles.SyncRequestID = dbo.SyncRequest.SyncRequestID
  </code></pre>
  
  <p>View 3 Definition</p>
  
  <pre><code>WITH Parent AS (SELECT PathID, ParentPathID, VersionID, CONVERT(varchar(128), Path) AS Path
  FROM dbo.Paths AS ParentPaths
  WHERE (ParentPathID = 0)
  UNION ALL
  SELECT ChildPaths.PathID, ChildPaths.ParentPathID, ChildPaths.VersionID, CONVERT(varchar(128), Parent.Path + '/' + ChildPaths.Path) AS Path
  FROM dbo.Paths AS ChildPaths 
  INNER JOIN Parent ON Parent.PathID = ChildPaths.ParentPathID)
  
  SELECT PathID, ParentPathID, VersionID, Path
  FROM Parent
  </code></pre>
  
  <p>Thanks</p>