SQL -游标用于我的查询需要一个小时(太长)

Question

我将发布代码，但首先我将解释我试图通过伪代码实现什么。

我在临时表中插入重复的记录。然后，我想单独检查该表中的每个记录，以执行与该记录匹配的选择，并在此基础上为所有记录('Y‘、'N’、'E')标记一个字段决赛。为了达到这个目的，我使用了两个游标和它的可怕。这很糟糕，因为它很难阅读，代码也很讨厌，而且我的查询也需要一个多小时的时间才能运行。是否有任何方法使它在选择/更新时运行得更好？

以下是守则：

declare @hic [nvarchar](255)
declare @oscar [float] 
declare @typecode [float] 
declare @fromdate [datetime] 
declare @thrudate [datetime] 
declare @claimcode [float] 
declare @claimeffdate [datetime] 

declare @id [int]
declare @finalhic [nvarchar](255)
declare @finaloscar [float] 
declare @finaltypecode [float] 
declare @finalfromdate [datetime] 
declare @finalthrudate [datetime] 
declare @finalclaimcode [float] 
declare @finalclaimeffdate [datetime]

declare cur1 CURSOR LOCAL for
select   
     [HIC #], 
     [Provider Oscar #],
     [Claim Type Code], 
     [Claim From Date], 
     [Claim Thru Date],
     [Claim Adjustment Type Code]  from #tmp_hic_cancels

open cur1
fetch next from cur1 into @hic, @oscar, @typecode, @fromdate, @thrudate,     @claimcode
while @@FETCH_STATUS = 0 BEGIN

--select @hic, @oscar, @typecode, @fromdate, @thrudate, @claimcode
begin

--typecode = 10
if @typecode = 10
BEGIN
    insert into  #tmp_hic_cancel_batch
    select 
        [ID],
    [HIC #], 
    [Provider Oscar #], 
    [Claim Type Code],
    [Claim From Date], 
    [Claim Thru Date],
    [Claim Adjustment Type Code],
    [Claim Effective Date]
    from [ACO].[dbo].['PA_Header_Temp']
    where [HIC #] = @hic
    and  [Claim Type Code] = @typecode
        and [Provider Oscar #] = @oscar
        and [Claim From Date] = @fromdate 


    --Mark final based on claimcode
    DECLARE Cur2 CURSOR FOR
     select 
     [ID],
    [HIC #], 
    [Provider Oscar #], 
    [Claim Type Code],
    [Claim From Date], 
    [Claim Thru Date],
    [Claim Adjustment Type Code],
    [Claim Effective Date]
         from #tmp_hic_cancel_batch
    OPEN Cur2;
    FETCH NEXT FROM Cur2 INTO @id, @finalhic, @finaloscar, @finaltypecode, @finalfromdate, @finalthrudate, @finalclaimcode, @finalclaimeffdate;
    WHILE @@FETCH_STATUS = 0
    --BEGIN CUR2
    BEGIN
    --IF A 2 EXISTS IN BATCH, perform these operations
        if (EXISTS (select 
            [Claim Adjustment Type Code] 
        from [ACO].[dbo].['PA_Header_Temp']
        where [HIC #] = @finalhic
            and [Provider Oscar #] = @finaloscar
            and [Claim Type Code] = @finaltypecode
            and [Claim From Date] = @finalfromdate
            and [Claim Adjustment Type Code] = @finalclaimcode
            and [Claim Adjustment Type Code]  = 2))
        BEGIN
        --MARK FINAL CODES BASED ON CLAIM CODES
            if @finalclaimcode = 2
            begin
                    insert into #tmp_hic_final
                    select @id, @finalhic, @finaloscar, @finaltypecode, @finalfromdate, @finalthrudate, @finalclaimcode, @finalclaimeffdate
                    , 'Y', DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
            end
            else
            begin
                    insert into #tmp_hic_final
                    select @id, @finalhic, @finaloscar, @finaltypecode, @finalfromdate, @finalthrudate, @finalclaimcode, @finalclaimeffdate
                    ,'N', DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
            end
        END
        --IF NO 2 EXISTS IN BATCH
        ELSE
        BEGIN
            if @finalclaimcode = 1
            begin
                    insert into #tmp_hic_final
                    select @id, @finalhic, @finaloscar, @finaltypecode, @finalfromdate, @finalthrudate, @finalclaimcode, @finalclaimeffdate
                    ,'N', DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
            end
        END 
        --END IF NO 2 EXISTS IN BATCH

        FETCH NEXT FROM Cur2 INTO @id, @finalhic, @finaloscar, @finaltypecode, @finalfromdate, @finalthrudate, @finalclaimcode, @finalclaimeffdate
    END;
    --END CUR2
    CLOSE Cur2;
    DEALLOCATE Cur2;

END--END typecode = 10

--else typecode = 40...
else
BEGIN
    insert into #tmp_hic_cancel_batch
    select 
        [ID],
    [HIC #], 
    [Provider Oscar #], 
    [Claim Type Code],
    [Claim From Date], 
    [Claim Thru Date],
    [Claim Adjustment Type Code],
    [Claim Effective Date]
    from [ACO].[dbo].['PA_Header_Temp']
    where [HIC #] = @hic
    and [Provider Oscar #] = @oscar
    and [Claim Type Code] = @typecode
    and [Claim From Date] = @fromdate 
    and [Claim Thru Date] = @thrudate
    and [Claim Adjustment Type Code] = @claimcode

--Mark final based on claimcode
    DECLARE Cur2 CURSOR FOR
     select
     [ID],
    [HIC #], 
    [Provider Oscar #], 
    [Claim Type Code],
    [Claim From Date], 
    [Claim Thru Date],
    [Claim Adjustment Type Code],
    [Claim Effective Date]
         from #tmp_hic_cancel_batch
    OPEN Cur2;
    FETCH NEXT FROM Cur2 INTO @id, @finalhic, @finaloscar, @finaltypecode, @finalfromdate, @finalthrudate, @finalclaimcode, @finalclaimeffdate
    WHILE @@FETCH_STATUS = 0
    --BEGIN CUR2
    BEGIN
    --IF A 2 EXISTS IN BATCH, perform these operations
        if (EXISTS (select 
            [Claim Adjustment Type Code] 
        from [ACO].[dbo].['PA_Header_Temp']
        where [HIC #] = @finalhic
            and [Claim Type Code] = @finaltypecode
            and [Provider Oscar #] = @finaloscar
            and [Claim From Date] = @finalfromdate
            and [Claim Thru Date] = @finalthrudate
            and [Claim Adjustment Type Code] = @finalclaimcode
            and [Claim Adjustment Type Code]  = 2))
        BEGIN
        --MARK FINAL CODES BASED ON CLAIM CODES
            if @finalclaimcode = 2
            begin
                    insert into #tmp_hic_final
                    select @id, @finalhic, @finaloscar, @finaltypecode, @finalfromdate, @finalthrudate, @finalclaimcode, @finalclaimeffdate
                    ,'Y', DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
            end
            else
            begin
                    insert into #tmp_hic_final
                    select @id, @finalhic, @finaloscar, @finaltypecode, @finalfromdate, @finalthrudate, @finalclaimcode, @finalclaimeffdate
                    ,'N', DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
            end
        END
        --IF NO 2 EXISTS IN BATCH
        ELSE
        BEGIN
            if @finalclaimcode = 1
            begin
                    insert into #tmp_hic_final
                    select @id, @finalhic, @finaloscar, @finaltypecode, @finalfromdate, @finalthrudate, @finalclaimcode, @finalclaimeffdate
                    ,'N', DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
            end
            else
            begin
                    insert into #tmp_hic_final
                    select @id, @finalhic, @finaloscar, @finaltypecode, @finalfromdate, @finalthrudate, @finalclaimcode, @finalclaimeffdate
                    ,'Y', DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
            end
        END 
        --END IF NO 2 EXISTS IN BATCH

        FETCH NEXT FROM Cur2 INTO @id, @finalhic, @finaloscar, @finaltypecode, @finalfromdate, @finalthrudate, @finalclaimcode, @finalclaimeffdate
    END;
    --END CUR2
    CLOSE Cur2;
    DEALLOCATE Cur2;

END

    --clears temp table for next batch
    delete from #tmp_hic_cancel_batch

fetch next from cur1 into @hic, @oscar, @typecode, @fromdate, @thrudate, @claimcode
END

close cur1
deallocate cur1

Answer 1

正如你所推测的那样，游标就是问题所在。它们效率极低，只有在非常有限的条件下才适用。对于像这样的大规模处理，即使是嵌套操作，游标也不是一个好的解决方案。

您需要完成整个过程，并将光标中的每一个位提取出来。我会给你第一个，这就是为什么我认为这是一个答案，而不仅仅是一个评论。

第一个插入发生在第一个游标内，可以在整个过程开始之前将其提取到，最终结果如下所示：

insert into  #tmp_hic_cancel_batch                     
select PAHT.[ID],
        PAHT.[HIC #], 
        PAHT.[Provider Oscar #], 
        PAHT.[Claim Type Code],
        PAHT.[Claim From Date], 
        PAHT.[Claim Thru Date],
        PAHT.[Claim Adjustment Type Code],
        PAHT.[Claim Effective Date],
        PAHT.[Current ClaimID],
        PAHT.[Claim Bill Facility Type Code],
        PAHT.[Claim Bill Classification Code],
        PAHT.[Principal Diagnosis Code],
        PAHT.[Admitting Diagnosis Code],
        PAHT.[Claim Medicare Non Payment Reason Code],
        PAHT.[Claim Payment Amount],
        PAHT.[Claim NCH Primary Payer Code],
        PAHT.[FIPS state Code],
        PAHT.[Bene Patient Status Code],
        PAHT.[Diagnosis Related Group Code],
        PAHT.[Claim Outpatient Service Type Code],
        PAHT.[Facility Provider NPI #],
        PAHT.[Operating Provider NPI #],
        PAHT.[Attending provider NPI #],
        PAHT.[Other Provider NPI #],
        PAHT.[Claim IDR Load Date],
        PAHT.[Bene Equitable BIC HICN #],
        PAHT.[Claim Admission Type Code],
        PAHT.[Claim Admission Source Code],
        PAHT.[Claim Bill Frequency Code],
        PAHT.[Claim Query Code],
from [ACO].[dbo].['PA_Header_Temp'] PAHT
        inner join #tmp_hic_cancels THC on PAHT.[HIC #] = THC.[HIC #] and
                                        PAHT.[Claim Type Code] = THC.[Claim Type Code] and 
                                        PAHT.[Provider Oscar #] = THC.[Provider Oscar #] and
                                        PAHT.[Claim From Date] = THC.[Claim From Date] 
where PAHT.[Claim Type Code] = 10

然后，您必须转移到下一个游标内的下一个位，并以同样的方式提取它，将游标操作更改为带有适当联接的selects组合。

您可能会发现，整个过程减少到了几个查询。或者，如果你对问题的细节有足够的了解，你就可以直接提出这些问题。但是整个过程要花几个小时才能完成，所以我不会尝试整个过程。而且，我确信上面的内容是正确的，但是我没有办法去测试它，所以.

是的，这可以使更快和更有效率。这可以通过仅使用查询(选择、插入、更新)来完成，这是一个NO游标。但你不可能让别人免费为你做这件事。

Answer 2

使用@Gilchrist代码作为基础，这就是我用来将代码表单游标修改为4实际sql插入到selects的代码.它检查类型编码= 20和a 2是否存在，然后类型编码= 20而不是2。第二组是类型编码<> 20和2存在，然后类型编码<> 20而不是2。

希望有一天有人能发现这个有用的东西：

insert into  #tmp_hic_final                     
select distinct PAHT.[ID],
    PAHT.[HIC #], 
    PAHT.[Provider Oscar #], 
    PAHT.[Claim Type Code],
    PAHT.[Claim From Date], 
    PAHT.[Claim Thru Date],
    PAHT.[Claim Adjustment Type Code],
    PAHT.[Claim Effective Date],
    PAHT.[Current ClaimID],
    PAHT.[Claim Bill Facility Type Code],
    PAHT.[Claim Bill Classification Code],
    PAHT.[Principal Diagnosis Code],
    PAHT.[Admitting Diagnosis Code],
    PAHT.[Claim Medicare Non Payment Reason Code],
    PAHT.[Claim Payment Amount],
    PAHT.[Claim NCH Primary Payer Code],
    PAHT.[FIPS state Code],
    PAHT.[Bene Patient Status Code],
    PAHT.[Diagnosis Related Group Code],
    PAHT.[Claim Outpatient Service Type Code],
    PAHT.[Facility Provider NPI #],
    PAHT.[Operating Provider NPI #],
    PAHT.[Attending provider NPI #],
    PAHT.[Other Provider NPI #],
    PAHT.[Claim IDR Load Date],
    PAHT.[Bene Equitable BIC HICN #],
    PAHT.[Claim Admission Type Code],
    PAHT.[Claim Admission Source Code],
    PAHT.[Claim Bill Frequency Code],
    PAHT.[Claim Query Code],
    CASE 
        WHEN PAHT.[Claim Adjustment Type Code] = '2' THEN 'Y'
        ELSE 'N' 
    END,
    DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
from [ACO].[dbo].['PA_Header_Temp'] PAHT
    inner join #tmp_hic_cancels THC on PAHT.[HIC #] = THC.[HIC #] and
                                    PAHT.[Claim Type Code] = THC.[Claim Type Code] and 
                                    PAHT.[Provider Oscar #] = THC.[Provider Oscar #] and
                                    PAHT.[Claim From Date] = THC.[Claim From Date] and
                                    PAHT.[Claim Adjustment Type Code] = THC.[Claim Adjustment Type Code]
where PAHT.[Claim Type Code] = 10
    and EXISTS (select 
                [Claim Adjustment Type Code] 
            from [ACO].[dbo].['PA_Header_Temp']
            where 
                [HIC #] = PAHT.[HIC #]
                and [Provider Oscar #] = PAHT.[Provider Oscar #]
                and [Claim Type Code] = PAHT.[Claim Type Code]
                and [Claim From Date] = PAHT.[Claim From Date]
                and [Claim Adjustment Type Code] = PAHT.[Claim Adjustment Type Code]
                and [Claim Adjustment Type Code]  = 2)

insert into  #tmp_hic_final  
select distinct PAHT.[ID],
    PAHT.[HIC #], 
    PAHT.[Provider Oscar #], 
    PAHT.[Claim Type Code],
    PAHT.[Claim From Date], 
    PAHT.[Claim Thru Date],
    PAHT.[Claim Adjustment Type Code],
    PAHT.[Claim Effective Date],
    PAHT.[Current ClaimID],
    PAHT.[Claim Bill Facility Type Code],
    PAHT.[Claim Bill Classification Code],
    PAHT.[Principal Diagnosis Code],
    PAHT.[Admitting Diagnosis Code],
    PAHT.[Claim Medicare Non Payment Reason Code],
    PAHT.[Claim Payment Amount],
    PAHT.[Claim NCH Primary Payer Code],
    PAHT.[FIPS state Code],
    PAHT.[Bene Patient Status Code],
    PAHT.[Diagnosis Related Group Code],
    PAHT.[Claim Outpatient Service Type Code],
    PAHT.[Facility Provider NPI #],
    PAHT.[Operating Provider NPI #],
    PAHT.[Attending provider NPI #],
    PAHT.[Other Provider NPI #],
    PAHT.[Claim IDR Load Date],
    PAHT.[Bene Equitable BIC HICN #],
    PAHT.[Claim Admission Type Code],
    PAHT.[Claim Admission Source Code],
    PAHT.[Claim Bill Frequency Code],
    PAHT.[Claim Query Code],
    'N',
    DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
from [ACO].[dbo].['PA_Header_Temp'] PAHT
    inner join #tmp_hic_cancels THC on PAHT.[HIC #] = THC.[HIC #] and
                                    PAHT.[Claim Type Code] = THC.[Claim Type Code] and 
                                    PAHT.[Provider Oscar #] = THC.[Provider Oscar #] and
                                    PAHT.[Claim From Date] = THC.[Claim From Date]  and
                                    PAHT.[Claim Adjustment Type Code] = THC.[Claim Adjustment Type Code]
where PAHT.[Claim Type Code] = 10
    and EXISTS (select 
                [Claim Adjustment Type Code] 
            from [ACO].[dbo].['PA_Header_Temp']
            where 
                [HIC #] = PAHT.[HIC #]
                and [Provider Oscar #] = PAHT.[Provider Oscar #]
                and [Claim Type Code] = PAHT.[Claim Type Code]
                and [Claim From Date] = PAHT.[Claim From Date]
                and [Claim Adjustment Type Code] = PAHT.[Claim Adjustment Type Code]
                and [Claim Adjustment Type Code]  <> 2)

insert into  #tmp_hic_final  
select distinct PAHT.[ID],
    PAHT.[HIC #], 
    PAHT.[Provider Oscar #], 
    PAHT.[Claim Type Code],
    PAHT.[Claim From Date], 
    PAHT.[Claim Thru Date],
    PAHT.[Claim Adjustment Type Code],
    PAHT.[Claim Effective Date],
    PAHT.[Current ClaimID],
    PAHT.[Claim Bill Facility Type Code],
    PAHT.[Claim Bill Classification Code],
    PAHT.[Principal Diagnosis Code],
    PAHT.[Admitting Diagnosis Code],
    PAHT.[Claim Medicare Non Payment Reason Code],
    PAHT.[Claim Payment Amount],
    PAHT.[Claim NCH Primary Payer Code],
    PAHT.[FIPS state Code],
    PAHT.[Bene Patient Status Code],
    PAHT.[Diagnosis Related Group Code],
    PAHT.[Claim Outpatient Service Type Code],
    PAHT.[Facility Provider NPI #],
    PAHT.[Operating Provider NPI #],
    PAHT.[Attending provider NPI #],
    PAHT.[Other Provider NPI #],
    PAHT.[Claim IDR Load Date],
    PAHT.[Bene Equitable BIC HICN #],
    PAHT.[Claim Admission Type Code],
    PAHT.[Claim Admission Source Code],
    PAHT.[Claim Bill Frequency Code],
    PAHT.[Claim Query Code],
    'N',
    DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
 from [ACO].[dbo].['PA_Header_Temp'] PAHT
    inner join #tmp_hic_cancels THC on PAHT.[HIC #] = THC.[HIC #] and
                                    PAHT.[Claim Type Code] = THC.[Claim Type Code] and 
                                    PAHT.[Provider Oscar #] = THC.[Provider Oscar #] and
                                    PAHT.[Claim From Date] = THC.[Claim From Date] and
                                     PAHT.[Claim Thru Date] = THC.[Claim Thru Date]
where PAHT.[Claim Type Code] <> 10
    and EXISTS (select 
                [Claim Adjustment Type Code] 
            from [ACO].[dbo].['PA_Header_Temp']
            where 
                [HIC #] = PAHT.[HIC #]
                and [Provider Oscar #] = PAHT.[Provider Oscar #]
                and [Claim Type Code] = PAHT.[Claim Type Code]
                and [Claim From Date] = PAHT.[Claim From Date]
                and [Claim Adjustment Type Code] = PAHT.[Claim Adjustment Type Code]
                and [Claim Adjustment Type Code]  = 2)

insert into  #tmp_hic_final  
select distinct PAHT.[ID],
    PAHT.[HIC #], 
    PAHT.[Provider Oscar #], 
    PAHT.[Claim Type Code],
    PAHT.[Claim From Date], 
    PAHT.[Claim Thru Date],
    PAHT.[Claim Adjustment Type Code],
    PAHT.[Claim Effective Date],
    PAHT.[Current ClaimID],
    PAHT.[Claim Bill Facility Type Code],
    PAHT.[Claim Bill Classification Code],
    PAHT.[Principal Diagnosis Code],
    PAHT.[Admitting Diagnosis Code],
    PAHT.[Claim Medicare Non Payment Reason Code],
    PAHT.[Claim Payment Amount],
    PAHT.[Claim NCH Primary Payer Code],
    PAHT.[FIPS state Code],
    PAHT.[Bene Patient Status Code],
    PAHT.[Diagnosis Related Group Code],
    PAHT.[Claim Outpatient Service Type Code],
    PAHT.[Facility Provider NPI #],
    PAHT.[Operating Provider NPI #],
    PAHT.[Attending provider NPI #],
    PAHT.[Other Provider NPI #],
    PAHT.[Claim IDR Load Date],
    PAHT.[Bene Equitable BIC HICN #],
    PAHT.[Claim Admission Type Code],
    PAHT.[Claim Admission Source Code],
    PAHT.[Claim Bill Frequency Code],
    PAHT.[Claim Query Code],
    'N',
    DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
from [ACO].[dbo].['PA_Header_Temp'] PAHT
    inner join #tmp_hic_cancels THC on PAHT.[HIC #] = THC.[HIC #] and
                                    PAHT.[Claim Type Code] = THC.[Claim Type Code] and 
                                    PAHT.[Provider Oscar #] = THC.[Provider Oscar #] and
                                    PAHT.[Claim From Date] = THC.[Claim From Date] and
                                     PAHT.[Claim Thru Date] = THC.[Claim Thru Date]
where PAHT.[Claim Type Code] <> 10
    and EXISTS (select 
                [Claim Adjustment Type Code] 
            from [ACO].[dbo].['PA_Header_Temp']
            where 
                [HIC #] = PAHT.[HIC #]
                and [Provider Oscar #] = PAHT.[Provider Oscar #]
                and [Claim Type Code] = PAHT.[Claim Type Code]
                and [Claim From Date] = PAHT.[Claim From Date]
                and [Claim Adjustment Type Code] = PAHT.[Claim Adjustment Type Code]
                and [Claim Adjustment Type Code]  <> 2)