优化代码

Question

我已经为小型数据库写了这个代码，但知道数据库大小已经增加了，它显示了timeout error.plz在优化它方面的帮助，下面是代码：

IF OBJECT_ID('Temp_expo') is not null
begin 
    drop table Temp_expo
end

set @query3 =  'SELECT SPCT_ID_REL_LOW,SPCT_ID_REL_HIGH,ROW_NUMBER() over (order by PDBC_PFX) as TempId  

INTO Temp_expo  
FROM ['+ @FCTServer +'].['+@FCTDBName+'].dbo.CMC_SPCT_SUPP_CONV  
where  SPCT_ID_REL_LOW <> '''' and SPCT_ID_REL_HIGH <> '''''

exec (@query3)

Select @minCount= min(TempId) from Temp_expo   
Select @maxCount= max(TempId) from Temp_expo  

create table #ICD9SPCT
(
    ICD9Code varchar(200)
} 

while @minCount<=@maxCount 
begin
    select @low=SPCT_ID_REL_LOW,@high=SPCT_ID_REL_HIGH 
    from Temp_expo 
    where TempId=@minCount 
    group by SPCT_ID_REL_LOW,SPCT_ID_REL_HIGH

    set  @loworder = (select ISNULL(OrderId,0) from FCT_ICD9_Diag_ORDER where ICD9=@low)
    set  @highorder = (select ISNULL(OrderId,0) from FCT_ICD9_Diag_ORDER where ICD9=@high)


    insert into #ICD9SPCT 
    select ICD9 from FCT_ICD9_Diag_ORDER ordert
    left join #ICD9SPCT icdorder on ordert.ICD9 = icdorder.ICD9Code
    where OrderId between @loworder and @highorder and icdorder.ICD9Code is null

    set @minCount = @minCount+1;
end

Answer 1

如果这是针对SQL SERVER，那么您可以尝试以下一些基本技巧:在每次使用select之后使用: WITH (NOLOCK)。即select ICD9 from loworder WITH (NOLOCK) left join #ICD9SPCT icdorder on ordert.ICD9 = icdorder.ICD9Code，其中@loworder和@highorder之间的OrderId和icdorder.ICD9Code为null

您还可以尝试将临时表更改为变量表，只需更改@ like的#即可：

create table @ICD9SPCT ( ICD9Code varchar(200) }

尽管如此，您正在使用的WHILE循环可能是问题的主要原因。