是否通过LINQ访问带有varbinary数据的行导致超时？怎么修？

I have a number of tables in SQL. One is Controls (a typical CRUD sort of object) and one is Attachments. Attachments references Controls via a FK (there can be many attachments). Attachments also includes, amongst other things, a name and a varbinary column with file data.

我在SQL中有许多表。一个是Controls(典型的CRUD类型的对象),一个是Attachments。附件引用通过FK进行控制(可以有许多附件)。附件还包括名称和带有文件数据的varbinary列。

Through linq, Control has an Attachments property.

通过linq,Control具有Attachments属性。

I have a Controls view (MVC) that displays a lot of information, including a listing of the existing attachments. That listing is done via a helper method:

我有一个控件视图(MVC),它显示了很多信息,包括现有附件的列表。该列表通过辅助方法完成:

public static string FileBox(this HtmlHelper helper, string name, IEnumerable<Models.Attachment> files, bool writable)
    { ... }

This function loops through the attachments and writes out a unordered list with the attachment names.

此函数循环遍历附件并写出带有附件名称的无序列表。

Infrequently, I get a timeout error, and here is a snippet of that error:

我很少遇到超时错误,这是一个错误的片段:

System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

...

at System.Data.Linq.SqlClient.ObjectReaderCompiler.ObjectReader`2.MoveNext()
   at System.Data.Linq.EntitySet`1.Load()
   at System.Data.Linq.EntitySet`1.GetEnumerator()
   at System.Linq.Enumerable.WhereEnumerableIterator`1.MoveNext()
   at IRSoxCompliance.Helpers.Html.FileBox(HtmlHelper helper, String name, IEnumerable`1 files, Boolean writable) in C:\Documents and Settings\Administrator\My Documents\Visual Studio 2008\Projects\IRSoxCompliance\IRSoxCompliance\IRSoxCompliance\Helpers\Html.cs:line 228
   at ASP.views_edit_control_edit_test_aspx.__Render__control1(HtmlTextWriter __w, Control parameterContainer) in c:\inetpub\wwwroot\Views\Edit\Control_Edit_Test.aspx:line 109

...

So...

Am I correct to assume that these timeouts are due to the fact that, while the control has already been loaded, the Attachments rows are lazy-loaded, and only being loaded from the helper? And that this is mostly due to the fact that I'm grabbing maybe 50 mb of data that I don't need?

我是否正确地假设这些超时是由于以下事实:虽然控件已经加载,但是附件行是延迟加载的,只是从帮助程序加载?而这主要是因为我抓住了50万个我不需要的数据?
How do I prevent this? a) I'd like to avoid splitting the table. b) Can I create a AttachmentsNoBinary partial property on the Control that returns a new class that has everything but the binary? c) It appears I can turn on 'delay loaded' on just the binary column. Will this work? If so -- I've made it a point of not changing anything in the DBML, because I have a habit of clearing a table and then reloading. So I'd lose this setting. Is there any way to make sure I don't lose it? Can I set it from my partial? Or maybe a unit test that can assert that it's turned on?

我该如何防止这种情况? a)我想避免拆分桌子。 b)我可以在Control上创建一个AttachmentsNoBinary部分属性,它返回一个除了二进制文件之外的所有内容的新类吗? c)看来我可以在二进制列上打开'延迟加载'。这会有用吗?如果是这样 - 我已经明确表示不改变DBML中的任何内容,因为我习惯于清除表然后重新加载。所以我会失去这个设置。有什么方法可以确保我不会失去它吗?我可以从我的部分设置吗?或者可能是一个可以断言它已打开的单元测试?

SOLUTION: Based on the answer, I realized that instead of:

解决方案:基于答案,我意识到,而不是:

foreach (Attachment file in controls.Attachments) {
  response.write(file.name);
}

I can instead do:

我可以这样做:

foreach (string filename in controls.Attachments.Select(a => a.name)) {
  response.write(filename);
}

Though I ended up defer-loading the varbinary column, hoping that I don't forget to set that option again should I reset my dbml file.

虽然我最终推迟加载varbinary列,希望我不要忘记在我重置dbml文件时再次设置该选项。

Thanks, James

1 个解决方案

#1

You are right that Linq is lazy loading your attachments. Whether or not it is dragging the varbinary data down from the database depends on the query. Can you post it?

你是对的,Linq懒得装你的附件。是否从数据库中拖拽varbinary数据取决于查询。你可以发布吗?
You don't need to change the structure of your database to sort out the problem. Make sure that your query isn't touching the varbinary field - you'll need an appropriate select/projection to ensure this. Also if you know that you are always going to get all the attachments related to a controller you could use LoadWith to instruct Linq to get it all at once rather than lazy loading on demand. This will cut down the number of database round trips required to get the job done.

您无需更改数据库的结构即可解决问题。确保您的查询没有触及varbinary字段 - 您需要一个适当的选择/投影来确保这一点。此外,如果您知道您将始终获得与控制器相关的所有附件,则可以使用LoadWith指示Linq立即获取所有附件,而不是根据需要延迟加载。这将减少完成工作所需的数据库往返次数。

LoadWith info on MSDN

在MSDN上加载信息

Also worth checking that your indexes are ok on the tables.

还值得检查表上的索引是否正常。

To get some real insight into what is going on point SQL Query Analyser at your database and then run the code. You'll see exactly what SQL is hitting the database and can grab and run it in Management Studio to see exactly what data is getting dragged back.

要真正了解数据库中SQL Query Analyzer的内容,然后运行代码。您将确切地看到SQL正在访问数据库,并且可以在Management Studio中抓取并运行它以确切地查看哪些数据被拖回。

Hope this helps a bit.

希望这个对你有帮助。

#1

You are right that Linq is lazy loading your attachments. Whether or not it is dragging the varbinary data down from the database depends on the query. Can you post it?

你是对的,Linq懒得装你的附件。是否从数据库中拖拽varbinary数据取决于查询。你可以发布吗?
You don't need to change the structure of your database to sort out the problem. Make sure that your query isn't touching the varbinary field - you'll need an appropriate select/projection to ensure this. Also if you know that you are always going to get all the attachments related to a controller you could use LoadWith to instruct Linq to get it all at once rather than lazy loading on demand. This will cut down the number of database round trips required to get the job done.

您无需更改数据库的结构即可解决问题。确保您的查询没有触及varbinary字段 - 您需要一个适当的选择/投影来确保这一点。此外,如果您知道您将始终获得与控制器相关的所有附件,则可以使用LoadWith指示Linq立即获取所有附件,而不是根据需要延迟加载。这将减少完成工作所需的数据库往返次数。