Union / Union All使查询速度变慢

Question

我想优化这个查询，以便在1秒内获取数据。目前，使用Union需要4-6秒，所有这些都需要大约4秒&对于Union，获取数据大约需要6秒。请帮我做一个更快的查询.Here我的查询如下：

 SELECT  smsFDGPatientOrder.ID
          ,smsFDGPatientOrder.[Order]
          ,[ExamDate]
          ,[ArrivalTime]
          ,[Activity]
          ,[PatientFirstName]
          ,[PatientMiddleName]
          ,[PatientLastName]
          ,[DOB]
          ,[Order]
          ,[ActualExamTimeIn]
          ,[ActualExamTimeOut]
          ,[ActualScannerID]
          ,[ActualExamDate]
          ,[ActualCustomer]
          ,[ActualPatientFirstName]
          ,[ActualPatientLastName]
          ,[ActualDOB]
          ,[InsuranceCoID]
          ,[InsuranceID]
          ,[StartedInPreAuth]
          ,[DateReceived]
          ,[TimeReceived]
          ,[FDGPatientOrder]
          ,smsFDGPatientOrder.[StatusID] AS PatientorderStatusID
          ,smsFDGOrder.[StatusID] AS FDGorderStatusID
          --,smsFDGPatientOrder.[TimeAdded]
          ,smsFDGOrder.[TimeAdded]
          ,smsFDGPatientOrder.[Notes]
          ,smsFDGPatientOrder.[cntID]
          ,smsFDGPatientOrder.[empID]
          ,[Isotope]
          ,[Weight]
          ,[Diabetic]
          ,[Indication]
          ,[Procedure]
          ,[Physician]
          ,[InjectionTime]
          ,smsCustomers.custID
          ,effectChildren
    FROM VW_PatientOrderByMaxTimeAddedProc smsFDGPatientOrder 
    INNER JOIN [dbo].[VW_FGDOrderByMaxTimeAddedProc] AS smsFDGOrder ON smsFDGPatientOrder.[Order]=smsFDGOrder.ID  
    LEFT JOIN smscustomers ON smsFDGOrder.Customer=smscustomers.custID WHERE
    (
     smsFDGPatientOrder.StatusID IN (2,5,13,14, 15, 16, 17, 18, 19, 23,8,25,39)
    AND
    smsFDGOrder.StatusID  IN(8,12,23,25)
    )
    UNION ALL
    (

      SELECT 
          smsFDGPatientOrder.ID
          ,smsFDGPatientOrder.[Order]
          ,[ExamDate]
          ,[ArrivalTime]
          ,[Activity]
          ,[PatientFirstName]
          ,[PatientMiddleName]
          ,[PatientLastName]
          ,[DOB]
          ,[Order]
          ,[ActualExamTimeIn]
          ,[ActualExamTimeOut]
          ,[ActualScannerID]
          ,[ActualExamDate]
          ,[ActualCustomer]
          ,[ActualPatientFirstName]
          ,[ActualPatientLastName]
          ,[ActualDOB]
          ,[InsuranceCoID]
          ,[InsuranceID]
          ,[StartedInPreAuth]
          ,[DateReceived]
          ,[TimeReceived]
          ,[FDGPatientOrder]
          ,smsFDGPatientOrder.[StatusID] AS PatientorderStatusID
          ,smsFDGOrder.[StatusID] AS FDGorderStatusID
          --,smsFDGPatientOrder.[TimeAdded]
          ,smsFDGOrder.[TimeAdded]
          ,smsFDGPatientOrder.[Notes]
          ,smsFDGPatientOrder.[cntID]
          ,smsFDGPatientOrder.[empID]
          ,[Isotope]
          ,[Weight]
          ,[Diabetic]
          ,[Indication]
          ,[Procedure]
          ,[Physician]
          ,[InjectionTime]
          ,smsCustomers.custID
          ,effectChildren
    FROM VW_PatientOrderByMaxTimeAddedProc smsFDGPatientOrder 
    INNER JOIN [dbo].[VW_FGDOrderByMaxTimeAddedProc] AS smsFDGOrder ON smsFDGPatientOrder.[Order]=smsFDGOrder.ID  
    LEFT JOIN smscustomers ON smsFDGOrder.Customer=smscustomers.custID WHERE [order] IN
    (
    SELECT DISTINCT [order] FROM  VW_PatientOrderByMaxTimeAddedProc smsFDGPatientOrder INNER JOIN [dbo].[VW_FGDOrderByMaxTimeAddedProc] AS smsFDGOrder ON smsFDGPatientOrder.[Order]=smsFDGOrder.ID WHERE smsFDGPatientOrder.StatusID IN(3) AND smsFDGOrder.StatusID IN (8,12)
    EXCEPT
    SELECT DISTINCT [order] FROM  VW_PatientOrderByMaxTimeAddedProc smsFDGPatientOrder INNER JOIN [dbo].[VW_FGDOrderByMaxTimeAddedProc] AS smsFDGOrder ON smsFDGPatientOrder.[Order]=smsFDGOrder.ID WHERE smsFDGPatientOrder.StatusID  IN(select id FROM smsStatus WHERE ID<>3) AND smsFDGOrder.StatusID IN (8,12)
    )

    ) 

    UNION 

    SELECT 
    smsFDGPatientOrder.ID
    ,smsFDGPatientOrder.[Order]
          ,[ExamDate]
          ,[ArrivalTime]
          ,[Activity]
          ,[PatientFirstName]
          ,[PatientMiddleName]
          ,[PatientLastName]
          ,[DOB]
          ,[Order]
          ,[ActualExamTimeIn]
          ,[ActualExamTimeOut]
          ,[ActualScannerID]
          ,[ActualExamDate]
          ,[ActualCustomer]
          ,[ActualPatientFirstName]
          ,[ActualPatientLastName]
          ,[ActualDOB]
          ,[InsuranceCoID]
          ,[InsuranceID]
          ,[StartedInPreAuth]
          ,[DateReceived]
          ,[TimeReceived]
          ,[FDGPatientOrder]
          ,smsFDGPatientOrderStatus.[StatusID] AS PatientorderStatusID
          ,smsFDGOrder.[StatusID] AS FDGorderStatusID
          ,smsFDGPatientOrderStatus.[TimeAdded]
          ,smsFDGPatientOrderStatus.[Notes]
          ,smsFDGPatientOrderStatus.[cntID]
          ,smsFDGPatientOrderStatus.[empID]
          ,[Isotope]
          ,[Weight]
          ,[Diabetic]
          ,[Indication]
          ,[Procedure]
          ,[Physician]
          ,[InjectionTime]
          ,smsCustomers.custID
          ,effectChildren
      FROM smsFDGPatientOrder LEFT JOIN smsFDGPatientOrderStatus ON smsFDGPatientOrder.ID= smsFDGPatientOrderStatus.FDGPatientOrder 
     INNER JOIN [dbo].[VW_FGDOrderByMaxTimeAddedProc] smsFDGOrder ON smsFDGPatientOrder.[Order]=smsFDGOrder.ID LEFT JOIN smscustomers ON smsFDGOrder.Customer=smscustomers.custID 
     WHERE smsFDGPatientOrderStatus.StatusID IS NULL AND  FDGPatientOrder IS NULL  AND smsFDGOrder.StatusID IN (8,12)  AND effectChildren=1 order by ID asc

Answer 1

与(NoLock)一起使用在每个表名后面，例如，

Select * FROM smsFDGPatientOrder With(NoLock)

或者，您可以在列上构建非聚集索引，这些列在联接中使用或在许多查询中选择。我们已经创建了索引和查询，使我们可以更快地获得结果。