使用多个表的慢SQL查询

Question

我是一个刚出生在SQL问题上的人，真的很感谢你的帮助。我得到了一个查询(不是我写的)，它几乎需要15分钟才能运行550,000行，所以我想改进它，但我看不到如何改进。

Follow query，我试着只过滤2021个数据，但它不起作用……

感谢大家的支持！！谨致问候!

Select 
    
    d.*, --BD dbo.Documents (23 496 430)
    case when d.DateStartStaging >0 then d.DateStartStaging else d.DateArchived end as CreatedDate,
    year(DateArchived) as Year,
    month(DateArchived) as Month,
    cast(DateArchived as date) as DateArchivedAux
From (select distinct  
        
        c.Name as InvestorName,
        dc.ClientId,
        lp.Judicialfile,
        d.id as DocumentId, 
        lkp.Name + isnull('/'+lk2.name,'') as DocumentType,
        lkp.Name AS DocumentName, 
        lk2.Name AS DocumentSubCode, 
        lda.Description AS ArchiveStatusCodeName, 
        case when h.ArchiveStatusCode = '0' then isnull(uc.DelegatorDisplayName,uc.DisplayName) else isnull(ud.DelegatorDisplayName,ud.DisplayName) end as Nome,
        di.CreatedDate as DocumentDate,
        case when h.ArchiveStatusCode = '0' then h.StatusDate else null end as DateStartStaging,
        h.ActivityDate,
        case when h.ArchiveStatusCode = '0' then he.StatusDate else h.StatusDate end as DateArchived
        
from HST_Documents h with (NOLOCK)
    inner join vwSessions       as ud with (NOLOCK) on h.SessionId = ud.Id 
    left join  HST_Documents    as he with (NOLOCK) on h.DocumentId = he.DocumentId and he.StatusDate >= h.StatusDate   and (he.PreviousArchiveStatusCode = '0' and he.ArchiveStatusCode = '1') and he.IsActive=1
    inner join Documents        as d with (NOLOCK) on h.DocumentId = d.Id and d.IsActive=1
    left join  LKP_Documents    as lkp with (NOLOCK) on d.DocumentCode = lkp.Code and lkp.IsActive=1
    left join  LKP_Documents    as lk2 with (NOLOCK) on d.DocumentSubCode = lk2.Code and lk2.IsActive=1
    left join  DocumentsClients as dc with (NOLOCK) on d.id = dc.DocId 
    left join  vwContacts       as c with (NOLOCK) on d.InvestorId = c.Id
    left join  vwSessions       as uc with (NOLOCK) on he.SessionId = uc.Id 
    left  join (select 
                llp.LegalProcessId,
                lp.JudicialFile,
                c.InvestorId
                from LegalProcesses AS lp WITH(NOLOCK)
                 Inner Join dbo.LoanLegalProcesses AS llp WITH(NOLOCK) ON llp.LegalProcessId = lp.Id  and llp.isactive=1
                 Inner Join dbo.Loans AS l WITH(NOLOCK) ON llp.LoanId = l.Id
                 Inner Join dbo.Clients AS c WITH(NOLOCK) ON l.ClientId = c.Id 
                 where  lp.isactive=1
                ) as lp on lp.LegalProcessId = d.LegalProcessId and lp.InvestorId=d.investorid
    left join  LKP_DocumentArchiveStatus as lda WITH (NOLOCK) ON h.ArchiveStatusCode = lda.Code 
    left join  DocumentInfo di WITH(NOLOCK) ON h.DocumentId=di.id
where   (he.StatusDate >= '2021-01-01' or h.StatusDate >= '2021-01-01') 
        and h.IsActive = 1 
        and ((h.ArchiveStatusCode = '0' AND ISNULL(h.PreviousArchiveStatusCode,'-') <> '0') or (h.ArchiveStatusCode = '1' 
        and h.PreviousArchiveStatusCode is null)) 
) as d
where d.ActivityDate >= '2021-01-01' -- filtro de data 2021
and DocumentName in ('AM DOC' ,'BORROWER DOC' ,'LEGAL DOC' , 'ARQUIVO','Notificação' ,'Requerimento') 
and Nome in (   'Ligia Pacheco',
                'Margarida Silva',
                'Claudia Matos',
                'Silvia Pinto',
                'Vanessa Carvalho',
                'Nidea Pires',
                'Ana Cristina Louro',
                'Soraia Frias',
                'Maria Isabel Eusebio',
                'Ines Melo',
                'Sergio Xavier',
                'Natacha Natu',
                'Sandra Goncalves',
                'Mariana Silva',
                'Andreia Filipa Paiva') --filtro por users

Answer 1

提高性能的一个快速修改是在子查询中移动筛选器。

Distinct操作对性能的要求非常高。在您的例子中，Distinct在第二个where之前启动。

(第一个where -> distinct ->第二个where)

通过组合两个条件where的示例

Select 
    
    d.*, --BD dbo.Documents (23 496 430)
    case when d.DateStartStaging >0 then d.DateStartStaging else d.DateArchived end as CreatedDate,
    year(DateArchived) as Year,
    month(DateArchived) as Month,
    cast(DateArchived as date) as DateArchivedAux
From (select distinct  
        
        c.Name as InvestorName,
        dc.ClientId,
        lp.Judicialfile,
        d.id as DocumentId, 
        lkp.Name + isnull('/'+lk2.name,'') as DocumentType,
        lkp.Name AS DocumentName, 
        lk2.Name AS DocumentSubCode, 
        lda.Description AS ArchiveStatusCodeName, 
        case when h.ArchiveStatusCode = '0' then isnull(uc.DelegatorDisplayName,uc.DisplayName) else isnull(ud.DelegatorDisplayName,ud.DisplayName) end as Nome,
        di.CreatedDate as DocumentDate,
        case when h.ArchiveStatusCode = '0' then h.StatusDate else null end as DateStartStaging,
        h.ActivityDate,
        case when h.ArchiveStatusCode = '0' then he.StatusDate else h.StatusDate end as DateArchived
        
from HST_Documents h with (NOLOCK)
    inner join vwSessions       as ud with (NOLOCK) on h.SessionId = ud.Id 
    left join  HST_Documents    as he with (NOLOCK) on h.DocumentId = he.DocumentId and he.StatusDate >= h.StatusDate   and (he.PreviousArchiveStatusCode = '0' and he.ArchiveStatusCode = '1') and he.IsActive=1
    inner join Documents        as d with (NOLOCK) on h.DocumentId = d.Id and d.IsActive=1
    left join  LKP_Documents    as lkp with (NOLOCK) on d.DocumentCode = lkp.Code and lkp.IsActive=1
    left join  LKP_Documents    as lk2 with (NOLOCK) on d.DocumentSubCode = lk2.Code and lk2.IsActive=1
    left join  DocumentsClients as dc with (NOLOCK) on d.id = dc.DocId 
    left join  vwContacts       as c with (NOLOCK) on d.InvestorId = c.Id
    left join  vwSessions       as uc with (NOLOCK) on he.SessionId = uc.Id 
    left  join (select 
                llp.LegalProcessId,
                lp.JudicialFile,
                c.InvestorId
                from LegalProcesses AS lp WITH(NOLOCK)
                 Inner Join dbo.LoanLegalProcesses AS llp WITH(NOLOCK) ON llp.LegalProcessId = lp.Id  and llp.isactive=1
                 Inner Join dbo.Loans AS l WITH(NOLOCK) ON llp.LoanId = l.Id
                 Inner Join dbo.Clients AS c WITH(NOLOCK) ON l.ClientId = c.Id 
                 where  lp.isactive=1
                ) as lp on lp.LegalProcessId = d.LegalProcessId and lp.InvestorId=d.investorid
    left join  LKP_DocumentArchiveStatus as lda WITH (NOLOCK) ON h.ArchiveStatusCode = lda.Code 
    left join  DocumentInfo di WITH(NOLOCK) ON h.DocumentId=di.id
where   (he.StatusDate >= '2021-01-01' or h.StatusDate >= '2021-01-01') 
        and h.IsActive = 1 
        and ((h.ArchiveStatusCode = '0' AND ISNULL(h.PreviousArchiveStatusCode,'-') <> '0') or (h.ArchiveStatusCode = '1' 
        and h.PreviousArchiveStatusCode is null)) 
        
        and h.ActivityDate  >= '2021-01-01' -- filtro de data 2021
        and lkp.Name in ('AM DOC' ,'BORROWER DOC' ,'LEGAL DOC' , 'ARQUIVO','Notificação' ,'Requerimento') 
        and ((h.ArchiveStatusCode = '0' and  isnull(uc.DelegatorDisplayName,uc.DisplayName)in (   'Ligia Pacheco',
                'Margarida Silva',
                'Claudia Matos',
                'Silvia Pinto',
                'Vanessa Carvalho',
                'Nidea Pires',
                'Ana Cristina Louro',
                'Soraia Frias',
                'Maria Isabel Eusebio',
                'Ines Melo',
                'Sergio Xavier',
                'Natacha Natu',
                'Sandra Goncalves',
                'Mariana Silva',
                'Andreia Filipa Paiva')) 
            OR (h.ArchiveStatusCode != '0' and  isnull(ud.DelegatorDisplayName,ud.DisplayName)in (   'Ligia Pacheco',
                'Margarida Silva',
                'Claudia Matos',
                'Silvia Pinto',
                'Vanessa Carvalho',
                'Nidea Pires',
                'Ana Cristina Louro',
                'Soraia Frias',
                'Maria Isabel Eusebio',
                'Ines Melo',
                'Sergio Xavier',
                'Natacha Natu',
                'Sandra Goncalves',
                'Mariana Silva',
                'Andreia Filipa Paiva')))  
) as d

您还可以在第二个where条件之后应用distinct。

在两个条件where之后启动distinct的示例

Select distinct
    
    d.*, --BD dbo.Documents (23 496 430)
    case when d.DateStartStaging >0 then d.DateStartStaging else d.DateArchived end as CreatedDate,
    year(DateArchived) as Year,
    month(DateArchived) as Month,
    cast(DateArchived as date) as DateArchivedAux
From (select           
        c.Name as InvestorName,
        dc.ClientId,
        lp.Judicialfile,
        d.id as DocumentId, 
        lkp.Name + isnull('/'+lk2.name,'') as DocumentType,
        lkp.Name AS DocumentName, 
        lk2.Name AS DocumentSubCode, 
        lda.Description AS ArchiveStatusCodeName, 
        case when h.ArchiveStatusCode = '0' then isnull(uc.DelegatorDisplayName,uc.DisplayName) else isnull(ud.DelegatorDisplayName,ud.DisplayName) end as Nome,
        di.CreatedDate as DocumentDate,
        case when h.ArchiveStatusCode = '0' then h.StatusDate else null end as DateStartStaging,
        h.ActivityDate,
        case when h.ArchiveStatusCode = '0' then he.StatusDate else h.StatusDate end as DateArchived
        
from HST_Documents h with (NOLOCK)
    inner join vwSessions       as ud with (NOLOCK) on h.SessionId = ud.Id 
    left join  HST_Documents    as he with (NOLOCK) on h.DocumentId = he.DocumentId and he.StatusDate >= h.StatusDate   and (he.PreviousArchiveStatusCode = '0' and he.ArchiveStatusCode = '1') and he.IsActive=1
    inner join Documents        as d with (NOLOCK) on h.DocumentId = d.Id and d.IsActive=1
    left join  LKP_Documents    as lkp with (NOLOCK) on d.DocumentCode = lkp.Code and lkp.IsActive=1
    left join  LKP_Documents    as lk2 with (NOLOCK) on d.DocumentSubCode = lk2.Code and lk2.IsActive=1
    left join  DocumentsClients as dc with (NOLOCK) on d.id = dc.DocId 
    left join  vwContacts       as c with (NOLOCK) on d.InvestorId = c.Id
    left join  vwSessions       as uc with (NOLOCK) on he.SessionId = uc.Id 
    left  join (select 
                llp.LegalProcessId,
                lp.JudicialFile,
                c.InvestorId
                from LegalProcesses AS lp WITH(NOLOCK)
                 Inner Join dbo.LoanLegalProcesses AS llp WITH(NOLOCK) ON llp.LegalProcessId = lp.Id  and llp.isactive=1
                 Inner Join dbo.Loans AS l WITH(NOLOCK) ON llp.LoanId = l.Id
                 Inner Join dbo.Clients AS c WITH(NOLOCK) ON l.ClientId = c.Id 
                 where  lp.isactive=1
                ) as lp on lp.LegalProcessId = d.LegalProcessId and lp.InvestorId=d.investorid
    left join  LKP_DocumentArchiveStatus as lda WITH (NOLOCK) ON h.ArchiveStatusCode = lda.Code 
    left join  DocumentInfo di WITH(NOLOCK) ON h.DocumentId=di.id
where   (he.StatusDate >= '2021-01-01' or h.StatusDate >= '2021-01-01') 
        and h.IsActive = 1 
        and ((h.ArchiveStatusCode = '0' AND ISNULL(h.PreviousArchiveStatusCode,'-') <> '0') or (h.ArchiveStatusCode = '1' 
        and h.PreviousArchiveStatusCode is null)) 
) as d
where d.ActivityDate >= '2021-01-01' -- filtro de data 2021
and DocumentName in ('AM DOC' ,'BORROWER DOC' ,'LEGAL DOC' , 'ARQUIVO','Notificação' ,'Requerimento') 
and Nome in (   'Ligia Pacheco',
                'Margarida Silva',
                'Claudia Matos',
                'Silvia Pinto',
                'Vanessa Carvalho',
                'Nidea Pires',
                'Ana Cristina Louro',
                'Soraia Frias',
                'Maria Isabel Eusebio',
                'Ines Melo',
                'Sergio Xavier',
                'Natacha Natu',
                'Sandra Goncalves',
                'Mariana Silva',
                'Andreia Filipa Paiva') --filtro por users

您还可以通过定义事务级别(在查询之前)来避免对每个表使用with (NOLOCK)

set transaction level read uncommitted