[步骤]
(@tb_Amazon_OSIDs [Tb_Ids] READONLY,@CustomerId int,@StartTimeValue nvarchar(19),@EndTimeValue nvarchar(19)) SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY AddTime DESC,Sku asc) AS [PageIndex] ,* FROM ( select pp.Id as Id ,p.ProductNameCN as ProductNameCN ,p.ProductName as ProductName ,pp.Sku as Sku ,p.ProductGroupSKU as ProductGroupSKU ,p.ClientSKU as ClientSKU ,p.GrossWeight as GrossWeight ,p.Pack_Length as Pack_Length ,p.Pack_Width as Pack_Width ,p.Pack_Height as Pack_Height ,p.Length as Length ,p.Width as Width ,p.Height as Height ,p.ImageUrl as ImageUrl ,pp.PublishStatus as PublishStatus ,pp.AccountGroupId as AccountGroupId ,pp.BusinessTeamIds ,pp.PublishBusinessTeamId as PublishBusinessTeamId ,p.ClassID as ClassID ,p.ClassID1 as ClassID1 ,p.ClassID2 as ClassID2 ,pp.PublishUserId as PublishUserId ,pp.AuditAccountGroupTime as AuditAccountGroupTime ,pp.AuditBusinessTeamTime as AuditBusinessTeamTime ,pp.AuditPublisherTime as AuditPublisherTime ,pp.PublishTime as PublishTime ,pp.AddTime as AddTime ,pp.UpdateTime as UpdateTime ,pp.OrderSourceIds as OrderSourceIds ,pp.PublishOrderSourceIds as PublishOrderSourceIds ,pa.OnlineTime ,pp.DataSource ,pa.CheckTime ,p.ProductState as ProductState ,p.MinSaleNum ,p.ProductProperty ,pa.NoSalePlatform ,pa.NoSaleCountry ,p.PlugRelate ,p.PlugRelateType ,p.BrandID ,p.WithBattery ,p.DevelopType ,pp.QueueOrderSourceIds ,pp.AddUserName ,pp.BusinessApprovalResult ,pp.ExpirationDay ,pp.DataSourceDetail ,pp.TeamPublishType ,pp.SkuSizeWeightRuleLimitSites ,pp.IsSkuSizeWeightRuleLimit ,ISNULL(pp.GenerateStatus, 0) GenerateStatus ,ISNULL(pp.TranslationStatus, 0) TranslationStatus from RB_Amazon_ProductPublish(nolock) pp inner join RB_Product(nolock) p on pp.CustomerID = p.CustomerID and pp.Sku = p.SKU inner join RB_Product_Admin(nolock) pa on pp.CustomerId = pa.CustomerID and pp.Sku = pa.SKU INNER JOIN @tb_Amazon_OSIDs IDS on pp.OrderSourceId=IDS.Id where pp.CustomerId = @CustomerId and pp.PublishStatus in (1,0,2,3,4,5) AND DATEDIFF(DAY,@StartTimeValue,pp.AddTime)>=0 AND DATEDIFF(DAY,@EndTimeValue,pp.AddTime)<=0 ) AS TempTable ) AS PagerTable WHERE PagerTable.[PageIndex] BETWEEN 1 AND 20
[结果]
[16:11:15] -------------------------------------------------------------
CODE:N16111540
Error:11_-2_0_-2146232060
SQL:
select o.ProductPublishId,o.PublishOrderSourceId,os.OrderSourceName,p.Sku
from RB_Amazon_ProductPublish p (nolock)
inner join RB_Amazon_ProductPublish_OrderSource o (nolock) on p.id=o.ProductPublishId and p.CustomerId=o.CustomerId
inner join RB_Ordersource os (nolock) on os.id=o.PublishOrderSourceId
where p.sku in ( select CONVERT(varchar(50),item) from CharSplitForIn(@SKUs,',' )) and p.CustomerId=@CustomerID
and o.PublishOrderSourceId in(select CONVERT(int,item)from CharSplitForIn(@PublishOrderSourceIds,',' ))
PARAMS:@CustomerID = 1 ,@SKUs = 18620514,19639838,6894449,11479902,19009291,19048069,7651211,16708736,18624748,18754165,20081778,10006071,17269513,17933453,20684050,20995140,17090610,20393624,9319330,12359261,16060472,18790031,19649044,20335070,9520345,17527523,13494303,15370299,11837240,19614555,16930651,19759511,18807963,19537157,9294035,11660567,16470860,19479011,11873377,18053154,19633472,15136784,15391650,10404313,16374918,10343870,14309669,17692056,14877765,19499575,13369733,10003315,10402459,13299987,16559951,17925786,9260691,19583581,20729740,10492818,18797237,10106821,16143370,20372851,13306936,8126529,17528238,19002420,10071814,15457924,7941684,16449258,21371660,7880528,12581213,20579929,14008123,22229683,10370435,10687861,14930763,18714987,10393201,13432651,16751983,20979695,13346640,21821369,11770992,19035300,21799835,8862740,10296958,17849444,7755073,12101173,17039531,9744643,16735757,19246805,21623543,16278965,15112862,9911554,19751399,21756957,20358213,17951578,9393612,7424542,17852393,10260215,7431334,10355602,14877548,6903442,11268738,13055804,14370282,18679360,10852498,17277427,17749507,8787433,14315029,22476569,10402110,13212754,13518944,15001065,9097006,10402216,14730991,21139303,7220122,20865405,18674156,20262120,8935046,10119857,14397007,15870531,17159478,9445440,18591525,10888299,9671829,13634297,16030309,15563552,7224599,20083156,13241862,14507814,16439611,11228420,8698975,13275142,8300941,6971721,8463715,9847342,11527528,13614629,19383481,20343429,20952991,11765812,15438134,17407686,14280844,17811032,20066659,20090639,20555162,13086700,19461835,11660551,7327747,7499216,16336236,7133152,11024392,19298757,8226140,10230126,14369347,7724459,9956607,13604050,14878544,17307052,13704396,15004732,14617713,21800696,8865150,11175419,22446058,18946097,21160909,16454287,17557237,21027335,8185899,8816658,8855405,11592044,8444393,19951533,7979770,8742183,17772927,19617228,11003442,13352169,14784392,8849869,10488685,8455813,17830532,7323928,18951434,20640883,8185833,8222781,15733979,16583035,18771324,19754925,17559144,18835925,8873220,17584730,18831219,20109332,8379877,11306488,13089022,17885909,9194037,13501606,9446502,10027769,13017120,10300643,11322662,11498182,8688695,8818337,15610164,18043171,7348852,11427657,20402671,9241062,14309670,7615558,14021306,20928819,9293924,9854191,17905939,18620142,13329209,13627611,15902065,20772842,10402773,9988493,10045276,20682165,7976077,9830425,12046381,17351549,19527103,13642045,21000824,9327180,14056808,19120843,10122624,18664034,21655462,20050891,11765940,19750971,8936728,17968333,19453785,11418935,16000639,19333044,20102436,8324430,16264288,17830846,18807971,14380061,10590444,14877819,8379824,13168384,15730862,15911199,20081856,6847641,14297809,20730489,7416905,7508794,7697964,9915814,13329614,13932902,21106483,9007947,14877816,20382508,21625468,8003140,12024958,14021723,20155272,7080684,13611387,18052428,22087826,11003415,11591750,20187470,8456511,9898151,18625213,19599348,9781886,14331169,16547231,11691795,19951581,14490619,6969353,9773780,16452636,17259193,19670907,16627346,17906375,9096944,19154183,21694522,13510578,15726840,14877818,6886544,9398034,20643458,19279982,20308213,14179376,15978827,6950412,19344694,12339654,21637294,15617636,19124836,7697838,16408952,9830426,15903419,16050257,17050571,13078733,18113682,12581212,13369783,14472155,18978479,20811846,15237212,21882688,15439223,17634634,18091007,15638311,19750174,10928164,14003948,9911665,14171714,20302137,22123016,7508793,7786718,7508691,16439653,11402679,20114441,15522132,16697521,18045991,19073755,11395843,11592102,12125059,16299128,8379902,9772275,16581329,21154722,8851182,14428677,21430248,6969539,8690221,16704908,11291806,17294888,13275178,17886483,19141215,10163222,10402857,13150038,10234232,15659857,19040962,19144388,19451412,15444811,16486146,8432380,20440965,21948949,22211840,16225364,16947976,17038221,22255483,10006019,9605440,10708398,9614678,18100267,9394007,13412409,10633372,10177436,14990648,9249065,13194383,16650066,17985105,11954220,20941413,15453429,15781149,19500775,18920091,20672553,21837654,11511282,19384512,20449895,21404358,20838086,7338398,16937263,16984014,22279089,18569629,15698092,20168136,17004071,20315616,20138974,18902145,20401909,19118681,20440905,13675793,7735475,20708530,7526609,10878235,20283148,7337304,13443275,20092058,7404495,10259488,11202533,10979652,14026657,19224651,14376016,18451346,21949470,10048194,8180615,11001726,23067635,7412362,17828085,19170026,21108059,23086287,16490808,19628263,22225709,19379888,21948040,22320683,9983260,12193460,19245827,20159878,20779825,7218368,19417124,21012345,17090496,20868785,22170730,17068158,19157549,19491290,17672301,20083771,20412094,16724453,22075854,12642177,8087310,22201205,20283170,22537482,10454677,17759544,15386159,21159610,16006865,20895169,21127329,23400507,15648787,18703548,18638969,21795690,20926224,11001955,20184458,16349438,20186619,20116520,20943232,21121842,13787289,17090634,8264907,14446364,19516471,14632920,18023989,19156429,22191259,18765444,21012249,22526749,10901935,11328191,16472928,15045359,16944946,19523856,20151125,21498025,22796946,8836491,11001994,13279704,19154096,10017902,10017930,20394538,21801359,13582254,6894399,20099745,20183851,16166612,10351869,16205224,21378864,13017103,13511434,8724981,13760510,20115724,13278254,15058365,17160513,17046670,18065690,7826829,8970364,13014698,14841781,17509235,17656888,18946144,7600393,21544802,14394133,20090126,10980189,11955958,14646913,16144532,7243863,9465260,13342269,13502003,18020580,22419387,7536075,15921092,19103432,19245148,21415740,15960347,19129251,19353026,19576339,21062589,10152155,10202402,13443513,16735238,18773715,7539680,7787933,15593322,13689645,19100632,8659023,9246329,9368299,14151811,16906780,18579300,19772100,20627778,21760871,14478528,10281303,10569609,16952874,22158365,17388190,21765149,21889765,14784446,20123432,20717220,9671729,17170534,7915526,17583842,15908044,16707317,17756949,17777152,18769958,7684823,8387499,16782745,17759816,17897397,18704679,20141964,9294162,17729493,19281287,22317656,20859200,16394860,19353888,7990714,15007496,17032444,20421737,19152503,9409487,11667705,17451813,18582198,18745298,19108437,13704564,21946572,9868217,13064560,16329268,19597199,10606419,12327307,20994119,7215348,10048191,18780624,15943733,19514152,9794548,17817015,12423346,13058012,14387232,18569752,16660239,14087906,20771407,16928683,21119918,14907802,20736107,21667373,9308139,18984839,20691012,8925049,13778079,20409699,16872856,17749925,7594923,9799733,13264227,13346634,17349309,20286219,22087873,9794355,11419438,13349664,14658043,21031532,21369331,21851306,22457860,13148945,13614490,18769956,19745164,18032628,15751652,17990744,13450973,19125392,19434471,14467784,14831754,19382553,10733350,16882903,19949284,10611525,11388348,13630387,15378045,20490218,22582746,17476527,19609973,13451575,13940702,17830442,17887534,19951570,21038961,21286456,7506749,11070410,13600431,13614588,15686591,15894701,17929880,18674544,13668641,16743526,19113153,21075995,7536824,17900194,18976663,19071781,19267143,19538470,19805197,20849595,21787256,7501538,13508075,15602781,18807190,20478485,8713513,8956021,12071135,13193875,15630478,16091726,16403116,7432995,18984481,19927464,22475837,7928800,14802418,20115105,10927384,17427420,17905296,18932693,22687293,11893573,14507496,17527103,19461993,7156501,16166105,18040847,18655486,21387564,16415959,18758503,18879861,19829320,15656575,16076828,18110909,19476212,9875239,10130933,15046320,7532663,7668183,12298030,16789372,18558005,19390402,19657443,20959179,11948436,18728002,21957131,18848272,21736574,21742792,15630563,19274145,21025970,10627433,16728333,20144767,20264094,21916139,9712514,16270382,18570258,15011356,16922201,18955075,19084395,11125234,11900115,19546175,15688187,13480909,14916544,20552707,14850767,19134427,20956500,21286168,22433669,16453963,16550382,17512861,21709618,7636217,18675148,14668841,14966968,20127533,20456573,11356401,20887508,15720582,15964727,17155876,21513810,21077191,8724776,13724053,17230245,17811023,20306445,9769410,13959647,14062066,17990642,20693178,21762630,13024444,18825687,17009013,9853501,14447547,16249956,20633535,22221979,14398973,16063160,17039490,20381820,9462532,15373148,17039571,17138739,19002307,16815029,17942584,18890531,19126536,19713649,20083550,20956582,21450760,22226748,7864073,16075599,19535861,20623785,9696498,10627432,17833487,17880055,7967374 ,@PublishOrderSourceIds = 31806,31792,31793,31794,31804,31802,31795,31799,31798,31800,31803,31801,31796 ,
MESSAGE:执行超时已过期。完成操作之前已超时或服务器未响应。
TRACE: 在 System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
在 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
在 System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
在 System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
在 System.Data.SqlClient.SqlDataReader.get_MetaData()
在 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.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
在 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
在 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
在 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
在 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
在 RB.DAL.DBOperator.Query(String SQLString, SqlCommand cmd, Int32 Times, SqlParameter[] cmdParms, Boolean IsWriteDB) 在 RB.DAL.DBOperator.toCommonTipException(SqlException ex, String sql, SqlParameter[] cmdParms)
在 RB.DAL.DBOperator.Query(String SQLString, SqlCommand cmd, Int32 Times, SqlParameter[] cmdParms, Boolean IsWriteDB)
在 RB.DALExtendV2.Generate.RB_Amazon_ProductPublishExtendV2.GetModelsBySkuAndOrderSourceIds(Int32 customerId, List`1 skuList, List`1 orderSourceIdList)
在 PandaBuy.ERP.Web.IrobotBox.Amazon.ProductDistribution.DistributionListinglist.AddSkuAccountGroupGetExistDataByAssignSku()
在 PandaBuy.ERP.Web.IrobotBox.Amazon.ProductDistribution.DistributionListinglist.Page_Load(Object sender, EventArgs e)
在 System.Web.UI.Control.OnLoad(EventArgs e)
在 System.Web.UI.Control.LoadRecursive()
在 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
在 System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
在 System.Web.UI.Page.ProcessRequest()
在 System.Web.UI.Page.ProcessRequest(HttpContext context)
在 ASP.irobotbox_amazon_productdistribution_distributionlistinglist_aspx.ProcessRequest(HttpContext context)
在 System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
在 System.Web.HttpApplication.<>c__DisplayClass285_0.<ExecuteStepImpl>b__0()
在 System.Web.HttpApplication.ExecuteStepImpl(IExecutionStep step)
在 System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)
在 System.Web.HttpApplication.PipelineStepManager.ResumeSteps(Exception error)
在 System.Web.HttpApplication.BeginProcessRequestNotification(HttpContext context, AsyncCallback cb)
在 System.Web.HttpRuntime.ProcessRequestNotificationPrivate(IIS7WorkerRequest wr, HttpContext context)
在 System.Web.Hosting.PipelineRuntime.ProcessRequestNotificationHelper(IntPtr rootedObjectsPointer, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)
在 System.Web.Hosting.PipelineRuntime.ProcessRequestNotification(IntPtr rootedObjectsPointer, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)
在 System.Web.Hosting.UnsafeIISMethods.MgdIndicateCompletion(IntPtr pHandler, RequestNotificationStatus& notificationStatus)
在 System.Web.Hosting.UnsafeIISMethods.MgdIndicateCompletion(IntPtr pHandler, RequestNotificationStatus& notificationStatus)
在 System.Web.Hosting.PipelineRuntime.ProcessRequestNotificationHelper(IntPtr rootedObjectsPointer, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)
在 System.Web.Hosting.PipelineRuntime.ProcessRequestNotification(IntPtr rootedObjectsPointer, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)
[期望]
优化SQL 排查问题 提升性能