深圳智汇创想科技有限公司

在 System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)    在 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)    在 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)    在 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)    在 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)    在 System.Data.SqlClient.SqlCommand.ExecuteNonQuery()    在 RB.DAL.DBOperator.ExecuteSql(String SQLString, SqlCommand cmd, Int32 Times, SqlParameter[] cmdParms)   在 RB.DAL.DBOperator.toCommonTipException(SqlException ex, String sql, SqlParameter[] cmdParms)    在 RB.DAL.DBOperator.ExecuteSql(String SQLString, SqlCommand cmd, Int32 Times, SqlParameter[] cmdParms)    在 RB.DALExtendV2.RB_Amazon_Product_BaseExtendV2.DeleteListByIDList(List`1 idList, Int32 customerID, Int32 orderSourceID)    在 Biz.Irobotbox.Amazon.AutoPublish.Amazon_AutoPublish_PublishListing_Delete.AutoPublishOrderSourceProductExecute(CM_Amazon_AutoPublish_OrderSource_Product autoPublishOrderSource)    在 RB.Utility.TaskSmartManager`1.execute(Func`2 execFunction)    在 System.Threading.Tasks.Task.Execute()    在 System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)    在 System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)    在 System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot)    在 System.Threading.Tasks.Task.ExecuteEntry(Boolean bPreventDoubleExecution)    在 System.Threading.ThreadPoolWorkQueue.Dispatch()

优化建议：

DECLARE @OrderSourceSKUIDs VARCHAR(max)= '917483574,918266608,917208530,918379153,917605334,918515860,917202673,918266600,919181450,917582237,918529345,919193186',
        @CustomerID        INT= 1,
        @OrderSourceID     INT= 5630,
        @nMaxid int=0,
        @nRows int=1;
drop table if exists #tabIds;
select id into #tabIds from RB_Amazon_Product_Base nolock
WHERE  OrderSourceSKUID IN (SELECT CONVERT(INT, item)
                            FROM   Charsplitforin(@OrderSourceSKUIDs, ','))
       AND CustomerID = @CustomerID
       AND OrderSourceID = @OrderSourceID ;
create index idx_id on #tabIds(id);
while @nRows>0
begin
    select top 500 id into #tabA from #tabIds where id>@nMaxid order by id;
    set @nRows=@@ROWCOUNT;
    create index idx_id on #tabA(id);
    select @nMaxid=max(id) from #tabA;    
    delete p from RB_Amazon_Product_Base p inner join #tabA t on p.id=t.id;
    drop table if exists #tabA;
end;


drop table if exists #tabIds;