一次简单的sql性能比较

用普通的 while循环插入数据

执行5分钟（未执行完，并且已经报内存溢出的错误。）

declare @feesCount bigint;
declare @feesid bigint
set @feesCount=1
set @feesid=100201000000002422

while(@feesCount<=100000)
begin
INSERT INTO [dbo].[TF_Fees]
           ([FeesID]
           ,[CommID]
           ,[CustID]
           ,[RoomID]
           ,[CostID]
           ,[CorpStanID]
           ,[StanID]
           ,[HandID]
           ,[ParkID]
           ,[CarparkID]
           ,[MeterSign]
           ,[AccountsDueDate]
           ,[FeesDueDate]
           ,[FeesStateDate]
           ,[FeesEndDate]
           ,[DueAmount]
           ,[DebtsAmount]
           ,[WaivAmount]
           ,[PrecAmount]
           ,[PaidAmount]
           ,[RefundAmount]
           ,[ContID]
           ,[LeaseContID]
           ,[OwnerFeesID]
           ,[IsAudit]
           ,[IsBank]
           ,[IsCharge]
           ,[IsFreeze]
           ,[IsProperty]
           ,[IsPrec]
           ,[CalcAmount]
           ,[CalcAmount2]
           ,[IncidentID]
           ,[StanMemo]
           ,[CommisionCostID]
           ,[CommisionAmount]
           ,[WaivCommisAmount]
           ,[PerStanAmount]
           ,[AccountFlag]
           ,[FeesMemo]
           ,[MeterID]
           ,[PMeterID]
           ,[CsmTaskID])
    select 
            @feesid+@feesCount as [FeesID]
           ,600  as [CommID]
           ,[CustID]
           ,[RoomID]
           ,[CostID]
           ,[CorpStanID]
           ,[StanID]
           ,[HandID]
           ,[ParkID]
           ,[CarparkID]
           ,[MeterSign]
           ,[AccountsDueDate]
           ,[FeesDueDate]
           ,[FeesStateDate]
           ,[FeesEndDate]
           ,[DueAmount]
           ,[DebtsAmount]
           ,[WaivAmount]
           ,[PrecAmount]
           ,[PaidAmount]
           ,[RefundAmount]
           ,[ContID]
           ,[LeaseContID]
           ,[OwnerFeesID]
           ,[IsAudit]
           ,[IsBank]
           ,[IsCharge]
           ,[IsFreeze]
           ,[IsProperty]
           ,[IsPrec]
           ,[CalcAmount]
           ,[CalcAmount2]
           ,[IncidentID]
           ,[StanMemo]
           ,[CommisionCostID]
           ,[CommisionAmount]
           ,[WaivCommisAmount]
           ,[PerStanAmount]
           ,[AccountFlag]
           ,[FeesMemo]
           ,[MeterID]
           ,[PMeterID]
           ,[CsmTaskID]
    from TF_Fees
    where FeesID=@feesid
    
    set @feesCount=@feesCount+1;
end

 

改用CTE后

declare @feesCount bigint;
declare @feesid bigint
set @feesCount=1
select  @feesid=MAX(feesid) from TF_Fees

Create TABLE #temp (
    [FeesID] [bigint] NOT NULL,
    [CommID] [int] NULL,
    [CustID] [bigint] NULL,
    [RoomID] [bigint] NULL,
    [CostID] [bigint] NULL,
    [CorpStanID] [bigint] NULL,
    [StanID] [bigint] NULL,
    [HandID] [bigint] NULL,
    [ParkID] [bigint] NULL,
    [CarparkID] [bigint] NULL,
    [MeterSign] [nvarchar](10) NULL,
    [AccountsDueDate] [datetime] NULL,
    [FeesDueDate] [datetime] NULL,
    [FeesStateDate] [datetime] NULL,
    [FeesEndDate] [datetime] NULL,
    [DueAmount] [numeric](18, 2) NULL,
    [DebtsAmount] [numeric](18, 2) NULL,
    [WaivAmount] [numeric](18, 2) NULL,
    [PrecAmount] [numeric](18, 2) NULL,
    [PaidAmount] [numeric](18, 2) NULL,
    [RefundAmount] [numeric](18, 2) NULL,
    [ContID] [bigint] NULL,
    [LeaseContID] [bigint] NULL,
    [OwnerFeesID] [bigint] NULL,
    [IsAudit] [smallint] NULL,
    [IsBank] [smallint] NULL,
    [IsCharge] [smallint] NULL,
    [IsFreeze] [smallint] NULL,
    [IsProperty] [smallint] NULL,
    [IsPrec] [smallint] NULL,
    [CalcAmount] [numeric](18, 2) NULL,
    [CalcAmount2] [numeric](18, 2) NULL,
    [IncidentID] [bigint] NULL,
    [StanMemo] [nvarchar](200) NULL,
    [CommisionCostID] [bigint] NULL,
    [CommisionAmount] [numeric](18, 2) NULL,
    [WaivCommisAmount] [numeric](18, 2) NULL,
    [PerStanAmount] [numeric](18, 2) NULL,
    [AccountFlag] [int] NULL,
    [FeesMemo] [nvarchar](255) NULL,
    [MeterID] [bigint] NULL,
    [PMeterID] [bigint] NULL,
    [CsmTaskID] [uniqueidentifier] NULL)

print @feesCount
while(@feesCount<=100000)
begin
INSERT INTO #temp
           ([FeesID]
           ,[CommID]
           ,[CustID]
           ,[RoomID]
           ,[CostID]
           ,[CorpStanID]
           ,[StanID]
           ,[HandID]
           ,[ParkID]
           ,[CarparkID]
           ,[MeterSign]
           ,[AccountsDueDate]
           ,[FeesDueDate]
           ,[FeesStateDate]
           ,[FeesEndDate]
           ,[DueAmount]
           ,[DebtsAmount]
           ,[WaivAmount]
           ,[PrecAmount]
           ,[PaidAmount]
           ,[RefundAmount]
           ,[ContID]
           ,[LeaseContID]
           ,[OwnerFeesID]
           ,[IsAudit]
           ,[IsBank]
           ,[IsCharge]
           ,[IsFreeze]
           ,[IsProperty]
           ,[IsPrec]
           ,[CalcAmount]
           ,[CalcAmount2]
           ,[IncidentID]
           ,[StanMemo]
           ,[CommisionCostID]
           ,[CommisionAmount]
           ,[WaivCommisAmount]
           ,[PerStanAmount]
           ,[AccountFlag]
           ,[FeesMemo]
           ,[MeterID]
           ,[PMeterID]
           ,[CsmTaskID])
    select 
            @feesid+@feesCount as [FeesID]
           ,600  as [CommID]
           ,[CustID]
           ,[RoomID]
           ,[CostID]
           ,[CorpStanID]
           ,[StanID]
           ,[HandID]
           ,[ParkID]
           ,[CarparkID]
           ,[MeterSign]
           ,[AccountsDueDate]
           ,[FeesDueDate]
           ,[FeesStateDate]
           ,[FeesEndDate]
           ,[DueAmount]
           ,[DebtsAmount]
           ,[WaivAmount]
           ,[PrecAmount]
           ,[PaidAmount]
           ,[RefundAmount]
           ,[ContID]
           ,[LeaseContID]
           ,[OwnerFeesID]
           ,[IsAudit]
           ,[IsBank]
           ,[IsCharge]
           ,[IsFreeze]
           ,[IsProperty]
           ,[IsPrec]
           ,[CalcAmount]
           ,[CalcAmount2]
           ,[IncidentID]
           ,[StanMemo]
           ,[CommisionCostID]
           ,[CommisionAmount]
           ,[WaivCommisAmount]
           ,[PerStanAmount]
           ,[AccountFlag]
           ,[FeesMemo]
           ,[MeterID]
           ,[PMeterID]
           ,[CsmTaskID]
    from TF_Fees
    where FeesID=@feesid

    set @feesCount=@feesCount+1;
end

select * from #temp;

disable trigger [TRG_TF_Fees_GetMaxFeesID] on TF_Fees
insert into TF_Fees select * from #temp ;
enable trigger [TRG_TF_Fees_GetMaxFeesID] on TF_Fees
drop table #temp

执行只用13秒。。。。。