tsql - 优化 SQL Server 2012 的 SQL 查询

Question

我有一个包含 8 个表的数据库，我需要能够在其中 6 个表中搜索特定值。

简而言之，查询应该执行以下操作：

SELECT i.InActivity,
   i.ReceivedTime,
   i.ReceivedFrom,
   i.DataSize,
   o.FileName,
   o.FileSize,
   o.Status,
   s1.UniqueReference,
   s1.SenderCode,
   s1.ReceiverCode,
   s2.DetailId,
   s3.UnitDescription,
   t1.SenderCode,
   t1.ReceiverCode,
   t2.TaskDescription,
   t3.TaskStatus
FROM InData i
LEFT JOIN OutData o WITH(NOLOCK) ON i.ActivityID = o.InActivity
LEFT JOIN SupplyDataLevel1 s1 WITH(NOLOCK) ON s1.InActivity = i.ActivityID
LEFT JOIN SupplyDataLevel2 s2 WITH(NOLOCK) ON s2.ParentId = s1.ActivityID
LEFT JOIN SupplyDataLevel3 s3 WITH(NOLOCK) ON s3.ParentId = s2.ActivityID
LEFT JOIN TasksDataLevel1 t1 WITH(NOLOCK) ON t1.InActivity = i.ActivityID
LEFT JOIN TasksDataLevel2 t2 WITH(NOLOCK) ON t2.ParentId = t1.ActivityID
LEFT JOIN TasksDataLevel3 t3 WITH(NOLOCK) ON t3.ParentId = t2.ActivityID
WHERE s1.IdSender = @searchParam OR
      s1.IdReceiver = @searchParam OR
  s2.StackNumber = @searchParam OR
  s2.StackOrderNumber = @searchParam OR
  s3.Reference = @searchParam OR
  t1.ProcessReference = @searchParam OR
  t1.ProcessStage = @searchParam OR
  t1.MasterNumber = @searchParam OR
  t2.ProcessStep = @searchParam OR
  t3.SubProcessStep = @searchParam

所以问题是，必须有比这更好的方法。这些表包含数百万行，执行此查询无疑将花费数小时和数小时来执行，这与将具有搜索页面的网站押韵很糟糕。

我应该创建一个视图并查询那个视图，这会是一个更好的选择，性能明智吗？

欢迎任何帮助。

标签： tsqlsql-server-2012