sql-server - 实体框架中的慢保存,非常具体的实体
问题描述
很长一段时间以来,我都有一个非常有趣(且令人困惑)的问题,但无法深究
我有一个基于 的WPF
项目Entity Framework
,有大约 100 个不同的实体/表,它们都运行顺利。除了一种名为 SaleProduct 的实体类型
保存 SaleProduct 大约需要 5 分钟,有时甚至更长时间(其他实体通常需要不到 2 秒)。并且只在窗口中。
出于测试目的,我将此代码放在应用程序启动中:
Using d = GetSystemContext(True)
Dim sp = d.Find(Of SaleProduct)(66246)
sp.DiscountAmount += 1
d.SaveChanges()
End Using
在那里保存还可以(11秒,可以忍受)。
但是当我打开一个以 SaleProduct 为 的窗口DataContext
并保存在那里时,需要几分钟。
所以,我想调试一下。也许我在某处有一些糟糕的代码。但我也没有解决这个问题。如果我在应用程序卡住保存时按下暂停Visual Studio
,它会冻结SaveChanges
在线,这意味着不是一些自定义代码。那么如果我们是在应用启动中还是在窗口中应该有什么区别呢?
但是让我们更进一步,我禁用Just My Code
以更好地了解冻结的内容。它卡在这里:
内部调度程序.cs
try
{
interceptionContext.MutableData.SetExecuted(operation(target, interceptionContext));
}
目标是一个非常简单的SQL
语句
UPDATE [dbo].[Sales]
SET [LastEditedOn] = @0
WHERE ([ID] = @1)
SELECT [AutoPurchaseDate], [GrantingDate], [ReceivedOn], [Name], [TotalVatRate]
FROM [dbo].[Sales]
WHERE @@ROWCOUNT > 0 AND [ID] = @1
运行它SSMS
需要 <1 秒。
但是查看完整的调用堆栈,我发现它实际上卡在System.Data.dll
. 虽然尽我所能,但我找不到它.pdb
。
所以我尝试使用反编译,但我得到一个错误
无法编译模块
尽管如此,在模块窗口中,状态是“已加载符号”。
所以我无法准确定位。
为了好玩,我尝试升级到 .net 4.8(从 4.7)。同样的问题。
我能做些什么?如何进行?我还可以在哪里调查?
笔记
- 表 Sales 和 SaleProducts 都有
Clustered
Primary Keys
。 SELECT * FROM sys.sysprocesses WHERE open_tran = 1
在SSMS
代码卡住时运行,返回 0 行。- 在
SaveChanges
中,ChangeTracker.Entries
恰好返回 2 个实体。SaleProduct 及其父销售。
我在这里发布了 Profiler Trace https://pastebin.com/enLgE0Ln
这是完整的 StackTrace,如果它可以帮助某人
[Managed to Native Transition]
System.Data.dll!SNINativeMethodWrapper.SNIReadSyncOverAsync(System.Runtime.InteropServices.SafeHandle pConn, ref System.IntPtr packet, int timeout) Unknown
System.Data.dll!System.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync() Unknown
System.Data.dll!System.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket() Unknown
System.Data.dll!System.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer() Unknown
System.Data.dll!System.Data.SqlClient.TdsParserStateObject.TryReadByte(out byte value) Unknown
System.Data.dll!System.Data.SqlClient.TdsParser.TryRun(System.Data.SqlClient.RunBehavior runBehavior, System.Data.SqlClient.SqlCommand cmdHandler, System.Data.SqlClient.SqlDataReader dataStream, System.Data.SqlClient.BulkCopySimpleResultSet bulkCopyHandler, System.Data.SqlClient.TdsParserStateObject stateObj, out bool dataReady) Unknown
System.Data.dll!System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() Unknown
System.Data.dll!System.Data.SqlClient.SqlDataReader.MetaData.get() Unknown
System.Data.dll!System.Data.SqlClient.SqlCommand.FinishExecuteReader(System.Data.SqlClient.SqlDataReader ds, System.Data.SqlClient.RunBehavior runBehavior, string resetOptionsString, bool isInternal, bool forDescribeParameterEncryption, bool shouldCacheForAlwaysEncrypted) Unknown
System.Data.dll!System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(System.Data.CommandBehavior cmdBehavior, System.Data.SqlClient.RunBehavior runBehavior, bool returnStream, bool async, int timeout, out System.Threading.Tasks.Task task, bool asyncWrite, bool inRetry, System.Data.SqlClient.SqlDataReader ds, bool describeParameterEncryptionRequest) Unknown
System.Data.dll!System.Data.SqlClient.SqlCommand.RunExecuteReader(System.Data.CommandBehavior cmdBehavior, System.Data.SqlClient.RunBehavior runBehavior, bool returnStream, string method, System.Threading.Tasks.TaskCompletionSource<object> completion, int timeout, out System.Threading.Tasks.Task task, out bool usedCache, bool asyncWrite, bool inRetry) Unknown
System.Data.dll!System.Data.SqlClient.SqlCommand.RunExecuteReader(System.Data.CommandBehavior cmdBehavior, System.Data.SqlClient.RunBehavior runBehavior, bool returnStream, string method) Unknown
System.Data.dll!System.Data.SqlClient.SqlCommand.ExecuteReader(System.Data.CommandBehavior behavior, string method) Unknown
> EntityFramework.dll!System.Data.Entity.Infrastructure.Interception.InternalDispatcher<System.Data.Entity.Infrastructure.Interception.IDbCommandInterceptor>.Dispatch<System.Data.Common.DbCommand, System.Data.Entity.Infrastructure.Interception.DbCommandInterceptionContext<System.Data.Common.DbDataReader>, System.Data.Common.DbDataReader>(System.Data.Common.DbCommand target, System.Func<System.Data.Common.DbCommand, System.Data.Entity.Infrastructure.Interception.DbCommandInterceptionContext<System.Data.Common.DbDataReader>, System.Data.Common.DbDataReader> operation, System.Data.Entity.Infrastructure.Interception.DbCommandInterceptionContext<System.Data.Common.DbDataReader> interceptionContext, System.Action<System.Data.Entity.Infrastructure.Interception.IDbCommandInterceptor, System.Data.Common.DbCommand, System.Data.Entity.Infrastructure.Interception.DbCommandInterceptionContext<System.Data.Common.DbDataReader>> executing, System.Action<System.Data.Entity.Infrastructure.Interception.IDbCommandInterceptor, System.Data.Common.DbCommand, System.Data.Entity.Infrastructure.Interception.DbCommandInterceptionContext<System.Data.Common.DbDataReader>> executed) Line 138 C#
EntityFramework.dll!System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(System.Data.Common.DbCommand command, System.Data.Entity.Infrastructure.Interception.DbCommandInterceptionContext interceptionContext) Line 103 C#
EntityFramework.dll!System.Data.Entity.Core.Mapping.Update.Internal.DynamicUpdateCommand.Execute(System.Collections.Generic.Dictionary<int, object> identifierValues, System.Collections.Generic.List<System.Collections.Generic.KeyValuePair<System.Data.Entity.Core.Mapping.Update.Internal.PropagatorResult, object>> generatedValues) Line 118 C#
EntityFramework.dll!System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.Update() Line 532 C#
EntityFramework.dll!System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction<int>(System.Func<int> func, System.Data.Entity.Infrastructure.IDbExecutionStrategy executionStrategy, bool startLocalTransaction, bool releaseConnectionOnSuccess) Line 2203 C#
EntityFramework.dll!System.Data.Entity.Core.Objects.ObjectContext.SaveChangesToStore(System.Data.Entity.Core.Objects.SaveOptions options, System.Data.Entity.Infrastructure.IDbExecutionStrategy executionStrategy, bool startLocalTransaction) Line 2148 C#
EntityFramework.SqlServer.dll!System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute<int>(System.Func<int> operation) Unknown
EntityFramework.dll!System.Data.Entity.Core.Objects.ObjectContext.SaveChangesInternal(System.Data.Entity.Core.Objects.SaveOptions options, bool executeInExistingTransaction) Line 2046 C#
EntityFramework.dll!System.Data.Entity.Internal.InternalContext.SaveChanges() Line 483 C#
Machshevet.dll!Machshevet.Core.RecordContext.SaveChanges() Line 762 Basic
Machshevet.dll!Machshevet.Windows.RecordWindow.Save() Line 169 Basic
Machshevet.dll!Machshevet.Windows.RecordWindow.<closure>.<lambda35-0>() Line 44 Basic
PresentationCore.dll!System.Windows.Input.CommandBinding.OnExecuted(object sender, System.Windows.Input.ExecutedRoutedEventArgs e) Unknown
PresentationCore.dll!System.Windows.Input.CommandManager.ExecuteCommandBinding(object sender, System.Windows.Input.ExecutedRoutedEventArgs e, System.Windows.Input.CommandBinding commandBinding) Unknown
PresentationCore.dll!System.Windows.Input.CommandManager.FindCommandBinding(System.Windows.Input.CommandBindingCollection commandBindings, object sender, System.Windows.RoutedEventArgs e, System.Windows.Input.ICommand command, bool execute) Unknown
PresentationCore.dll!System.Windows.Input.CommandManager.FindCommandBinding(object sender, System.Windows.RoutedEventArgs e, System.Windows.Input.ICommand command, bool execute) Unknown
PresentationCore.dll!System.Windows.Input.CommandManager.OnExecuted(object sender, System.Windows.Input.ExecutedRoutedEventArgs e) Unknown
PresentationCore.dll!System.Windows.RoutedEventArgs.InvokeHandler(System.Delegate handler, object target) Unknown
PresentationCore.dll!System.Windows.RoutedEventHandlerInfo.InvokeHandler(object target, System.Windows.RoutedEventArgs routedEventArgs) Unknown
PresentationCore.dll!System.Windows.EventRoute.InvokeHandlersImpl(object source, System.Windows.RoutedEventArgs args, bool reRaised) Unknown
PresentationCore.dll!System.Windows.UIElement.RaiseEventImpl(System.Windows.DependencyObject sender, System.Windows.RoutedEventArgs args) Unknown
PresentationCore.dll!System.Windows.Input.RoutedCommand.ExecuteImpl(object parameter, System.Windows.IInputElement target, bool userInitiated) Unknown
PresentationCore.dll!System.Windows.Input.CommandManager.TransferEvent(System.Windows.IInputElement newSource, System.Windows.Input.ExecutedRoutedEventArgs e) Unknown
PresentationCore.dll!System.Windows.Input.CommandManager.OnExecuted(object sender, System.Windows.Input.ExecutedRoutedEventArgs e) Unknown
PresentationCore.dll!System.Windows.RoutedEventArgs.InvokeHandler(System.Delegate handler, object target) Unknown
PresentationCore.dll!System.Windows.RoutedEventHandlerInfo.InvokeHandler(object target, System.Windows.RoutedEventArgs routedEventArgs) Unknown
PresentationCore.dll!System.Windows.EventRoute.InvokeHandlersImpl(object source, System.Windows.RoutedEventArgs args, bool reRaised) Unknown
PresentationCore.dll!System.Windows.UIElement.RaiseEventImpl(System.Windows.DependencyObject sender, System.Windows.RoutedEventArgs args) Unknown
PresentationCore.dll!System.Windows.Input.RoutedCommand.ExecuteImpl(object parameter, System.Windows.IInputElement target, bool userInitiated) Unknown
PresentationFramework.dll!MS.Internal.Commands.CommandHelpers.CriticalExecuteCommandSource(System.Windows.Input.ICommandSource commandSource, bool userInitiated) Unknown
PresentationFramework.dll!System.Windows.Controls.Primitives.ButtonBase.OnClick() Unknown
PresentationFramework.dll!System.Windows.Controls.Button.OnClick() Unknown
System.Windows.Controls.Ribbon.dll!System.Windows.Controls.Ribbon.RibbonButton.OnClick() Unknown
PresentationFramework.dll!System.Windows.Controls.Primitives.ButtonBase.OnMouseLeftButtonUp(System.Windows.Input.MouseButtonEventArgs e) Unknown
PresentationCore.dll!System.Windows.RoutedEventArgs.InvokeHandler(System.Delegate handler, object target) Unknown
PresentationCore.dll!System.Windows.RoutedEventHandlerInfo.InvokeHandler(object target, System.Windows.RoutedEventArgs routedEventArgs) Unknown
PresentationCore.dll!System.Windows.EventRoute.InvokeHandlersImpl(object source, System.Windows.RoutedEventArgs args, bool reRaised) Unknown
PresentationCore.dll!System.Windows.UIElement.ReRaiseEventAs(System.Windows.DependencyObject sender, System.Windows.RoutedEventArgs args, System.Windows.RoutedEvent newEvent) Unknown
PresentationCore.dll!System.Windows.UIElement.OnMouseUpThunk(object sender, System.Windows.Input.MouseButtonEventArgs e) Unknown
PresentationCore.dll!System.Windows.RoutedEventArgs.InvokeHandler(System.Delegate handler, object target) Unknown
PresentationCore.dll!System.Windows.RoutedEventHandlerInfo.InvokeHandler(object target, System.Windows.RoutedEventArgs routedEventArgs) Unknown
PresentationCore.dll!System.Windows.EventRoute.InvokeHandlersImpl(object source, System.Windows.RoutedEventArgs args, bool reRaised) Unknown
PresentationCore.dll!System.Windows.UIElement.RaiseEventImpl(System.Windows.DependencyObject sender, System.Windows.RoutedEventArgs args) Unknown
PresentationCore.dll!System.Windows.UIElement.RaiseTrustedEvent(System.Windows.RoutedEventArgs args) Unknown
PresentationCore.dll!System.Windows.Input.InputManager.ProcessStagingArea() Unknown
PresentationCore.dll!System.Windows.Input.InputManager.ProcessInput(System.Windows.Input.InputEventArgs input) Unknown
PresentationCore.dll!System.Windows.Input.InputProviderSite.ReportInput(System.Windows.Input.InputReport inputReport) Unknown
PresentationCore.dll!System.Windows.Interop.HwndMouseInputProvider.ReportInput(System.IntPtr hwnd, System.Windows.Input.InputMode mode, int timestamp, System.Windows.Input.RawMouseActions actions, int x, int y, int wheel) Unknown
PresentationCore.dll!System.Windows.Interop.HwndMouseInputProvider.FilterMessage(System.IntPtr hwnd, MS.Internal.Interop.WindowMessage msg, System.IntPtr wParam, System.IntPtr lParam, ref bool handled) Unknown
PresentationCore.dll!System.Windows.Interop.HwndSource.InputFilterMessage(System.IntPtr hwnd, int msg, System.IntPtr wParam, System.IntPtr lParam, ref bool handled) Unknown
WindowsBase.dll!MS.Win32.HwndWrapper.WndProc(System.IntPtr hwnd, int msg, System.IntPtr wParam, System.IntPtr lParam, ref bool handled) Unknown
WindowsBase.dll!MS.Win32.HwndSubclass.DispatcherCallbackOperation(object o) Unknown
WindowsBase.dll!System.Windows.Threading.ExceptionWrapper.InternalRealCall(System.Delegate callback, object args, int numArgs) Unknown
WindowsBase.dll!System.Windows.Threading.ExceptionWrapper.TryCatchWhen(object source, System.Delegate callback, object args, int numArgs, System.Delegate catchHandler) Unknown
WindowsBase.dll!System.Windows.Threading.Dispatcher.LegacyInvokeImpl(System.Windows.Threading.DispatcherPriority priority, System.TimeSpan timeout, System.Delegate method, object args, int numArgs) Unknown
WindowsBase.dll!MS.Win32.HwndSubclass.SubclassWndProc(System.IntPtr hwnd, int msg, System.IntPtr wParam, System.IntPtr lParam) Unknown
[Native to Managed Transition]
[Managed to Native Transition]
WindowsBase.dll!System.Windows.Threading.Dispatcher.PushFrameImpl(System.Windows.Threading.DispatcherFrame frame) Unknown
PresentationFramework.dll!System.Windows.Application.RunDispatcher(object ignore) Unknown
PresentationFramework.dll!System.Windows.Application.RunInternal(System.Windows.Window window) Unknown
解决方案
这个问题的具体答案需要查看具体负责的代码。除了表在 DbContext 和数据库架构中定义的关系以及 DbContext 正在跟踪的当前状态之外,单个表可能不是问题。(以及数据库可能锁定的内容,尽管这通常是一种更间歇性的问题)
需要检查的一些事项:在您的 App 示例中,您似乎正在获取 DbContext 的新实例并对其进行处理。(好)11s 时间反映的是 Find+Save,还是只是 Save?DbContext 在应用程序中执行的第一个查询会导致初始化模型的 1-off(认为是静态的)启动成本。对于首次运行场景的更准确计时,您可以执行以下操作:
Using d = GetSystemContext(True)
Dim dump = d.Sales.Any()
' Start timing here...
Dim sp = d.Find(Of SaleProduct)(66246)
sp.DiscountAmount += 1
d.SaveChanges()
' End timing here...
End Using
*抱歉,可悲的是,我的 VB.Net 已经过时了.. :) 无论如何,对 DbContext 使用快速操作(如 an)Any()
会产生一次性启动成本。之后的任何操作都应该更纯粹地表示加载和保存数据所需的时间。
在您有问题的代码中,DbContext 是否保证像这样短暂存在?存活时间更长的 DbContext 会累积对多个实体的引用,并且任何时候持久化任何实体,它都会搜索它的实体树以确定是否需要更新任何相关的实体。短暂的、已处置的 DbContexts 是要走的路。
您是否对数据库运行了探查器以捕获正在运行的确切 SQL?您的示例 SQL 看起来不像 EF 查询,而是引用“Sales”表而不是“SalesProduct”。在调试 EF 应用程序操作时运行探查器对于揭示幕后发生的意外情况非常宝贵。
其他可能性实际上取决于相关代码,但结合断点的分析器通常有助于发现问题。延迟确认只是在 DbContext.SaveChanges 发生时,还是整个保存过程?如果类似于用户单击“保存”按钮时的情况,那么 Profiler 可能会显示您的代码正在触发大量延迟加载调用(加载数据以在保存之前检查和更新)或加载意外数据。(我见过的常见问题是ToList()
过滤语句之前的错误调用,通常是因为开发人员想做一些 EF 无法翻译的事情,并且ToList()
看起来像是“修复”)如果延迟只是来自 SaveChanges,那么探查器可以确认更新语句和正在生产类似产品以及它们需要多长时间才能运行。
推荐阅读
- c# - 双操作返回无限
- apache-kafka - 我在哪里可以在 Landoop kafka UI 中找到 kafka 消息标头?
- php - 如何根据级别数计算结果以放入树视图中?
- javascript - 如何在 React Native 中隐藏 Accordion 特定内容
- c++ - 在 C++ 中提取小数点
- python - Flask - 返回 jsonify 与 dict 时有什么区别?
- graphql - 如何在 GraphQL 上添加适配器以在模式中将蛇案例转换为骆驼案例
- javascript - Sanctuary js 执行 2 步有条件的管道
- light-4j - 是否可以在 light-4j 请求中显示额外的查询参数返回错误
- azure-devops - Azure DevOps Backlogs:如何处理子功能(功能中的功能)?