在 ASP.NET 页面上检索大型动态指定表的最佳策略
寻求一些有关如何优化我们的项目的建议。我们有一个 ASP.NET/C# 系统，它从 SQL2008 数据中检索数据并将其呈现在 DevExpress ASPxGridView 上。检索到的数据可以来自多个数据库之一 - 所有这些数据库都略有不同，并且会定期添加和删除。向用户呈现实时“公司”列表，并从相应的数据库中检索数据。
目前，正在使用标准 SqlDataSource 和动态创建的 SQL SELECT 语句检索数据。语句中有一些 JOIN，以及可选的 WHERE 约束，这些约束也是根据数据库和用户的权限级别动态创建的。
除了性能之外，所有这些都非常有效（老实说！）。对于某些数据库，有数十万行，检索和分页数据非常慢（数据库已经正确索引）。因此，我一直在寻找加速系统速度的方法，似乎可以归结为两个选择：XPO 或 LINQ。
LINQ 似乎是流行的选择，但我不确定使用本质上如此动态的系统来实现有多容易 - 我是否需要为 LINQ 可以访问的每个数据库创建“定义”？我也有点不确定是否要动态创建 LINQ 查询，尽管查看了一些示例，这部分至少看起来是可行的。
另一方面，XPO 似乎允许我动态创建 XPO 数据源。但是，我找不到太多关于如何 JOIN 到其他表的信息。
任何人都可以提供关于哪种方法（如果有的话）是最好的尝试和改造到该项目的建议吗？或者当前使用的动态 SQL 模型是否与 LINQ 和 XPO 根本不同，最好不要管？
Looking for a bit of advice on how to optimise one of our projects. We have a ASP.NET/C# system that retrieves data from a SQL2008 data and presents it on a DevExpress ASPxGridView. The data that's retrieved can come from one of a number of databases - all of which are slightly different and are being added and removed regularly. The user is presented with a list of live "companies", and the data is retrieved from the corresponding database.
At the moment, data is being retrieved using a standard SqlDataSource and a dynamically-created SQL SELECT statement. There are a few JOINs in the statement, as well as optional WHERE constraints, again dynamically-created depending on the database and the user's permission level.
All of this works great (honest!), apart from performance. When it comes to some databases, there are several hundreds of thousands of rows, and retrieving and paging through the data is quite slow (the databases are already properly indexed). I've therefore been looking at ways of speeding the system up, and it seems to boil down to two choices: XPO or LINQ.
LINQ seems to be the popular choice, but I'm not sure how easy it will be to implement with a system that is so dynamic in nature - would I need to create "definitions" for each database that LINQ could access? I'm also a bit unsure about creating the LINQ queries dynamically too, although looking at a few examples that part at least seems doable.
XPO, on the other hand, seems to allow me to create a XPO Data Source on the fly. However, I can't find too much information on how to JOIN to other tables.
Can anyone offer any advice on which method - if any - is the best to try and retro-fit into this project? Or is the dynamic SQL model currently used fundamentally different from LINQ and XPO and best left alone?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
在您开始更改应用程序与数据库通信的整个方式之前，您是否已查看以下内容：
通过性能分析器（例如 Redgate 的性能分析器）运行您的代码，结果通常会令人惊讶。< /p>
如果您正在动态构建 SQL 字符串，您是否使用了 .Net 最佳实践，例如 String.Concat("str1", "str2") 而不是 "str1" + "str2"。请记住，多个小收益加起来会带来大收益。
您是否考虑过拥有一个定期更新的汇总表或数据库（例如每 15 分钟一次，您可能需要运行一项服务来自动更新此数据。），以便您只访问一个数据库。与数据库的新连接非常昂贵。
您是否查看过正在运行的 SQL 的查询计划？今天，我将动态创建的 SQL 字符串移至存储过程（仅更改了 1 个参数），并缩短了 5-10 秒的运行时间（根据某些条件，它被调用了 100-10000 次）。
如果您使用 LINQ，只是一个警告。我见过一些决定使用 LINQ 的开发人员编写了效率更低的代码，因为他们不知道自己在做什么（例如，当他们需要检查 1 条记录时，却提取了 36,000 条记录）。这些事情很容易被忽视。
只是一些让您开始的东西，希望其中有一些您没有想到的东西。
干杯，
斯图
Before you go and change the whole way that your app talks to the database, have you had a look at the following:
Run your code through a performance profiler (such as Redgate's performance profiler), the results are often surprising.
If you are constructing the SQL string on the fly, are you using .Net best practices such as String.Concat("str1", "str2") instead of "str1" + "str2". Remember, multiple small gains add up to big gains.
Have you thought about having a summary table or database that is periodically updated (say every 15 mins, you might need to run a service to update this data automatically.) so that you are only hitting one database. New connections to databases are quiet expensive.
Have you looked at the query plans for the SQL that you are running. Today, I moved a dynamically created SQL string to a sproc (only 1 param changed) and shaved 5-10 seconds off the running time (it was being called 100-10000 times depending on some conditions).
Just a warning if you do use LINQ. I have seen some developers who have decided to use LINQ write more inefficient code because they did not know what they are doing (pulling 36,000 records when they needed to check for 1 for example). This things are very easily overlooked.
Just something to get you started on and hopefully there is something there that you haven't thought of.
Cheers,
Stu
据我了解，当所有数据操作完成时，您正在谈论所谓的服务器模式 DB 服务器而不是它们发送到 Web 服务器并在那里进行处理。在这种模式下，网格可以非常快速地处理包含数十万条记录的数据源。如果要使用此模式，您应该创建相应的 LINQ 类或 XPO 类。如果您决定使用基于 LINQ 的服务器模式，则 LINQServerModeDataSource 会提供 Selecting 事件，该事件可以是用于设置自定义 IQueryable 和 KeyExpression。我建议您在应用程序中使用 LINQ。我希望这些信息对您有所帮助。
As far as I understand you are talking about so called server mode when all data manipulations are done on the DB server instead of them to the web server and processing them there. In this mode grid works very fast with data sources that can contain hundreds thousands records. If you want to use this mode, you should either create the corresponding LINQ classes or XPO classes. If you decide to use LINQ based server mode, the LINQServerModeDataSource provides the Selecting event which can be used to set a custom IQueryable and KeyExpression. I would suggest that you use LINQ in your application. I hope, this information will be helpful to you.
我想在这种情况下，有两点可以调整性能。我假设您直接访问数据库而不是通过某种辅助层。
首先，您没有说明如何显示数据本身。如果您要将数千条记录加载到网格中，那么无论其他一切有多快，这都需要时间。显然，这里的技巧是显示数据的子集并允许用户分页等。如果您不这样做，那么这可能是一个很好的起点。
其次，您说这些表已正确建立索引。如果是这种情况，并且假设您没有一次将 1,000 条记录加载到页面中并且一次仅检索子集，那么您应该没问题。
但是，如果您只是对 SQL 连接执行 ExecuteQuery() 来获取数据集，我不知道 Linq 或其他任何东西将如何帮助您。我想说，问题显然出在数据库方面。
因此，要解决数据库问题，您需要分析正在运行的不同 SELECT 语句，检查查询计划并确定速度变慢的地方。您可能希望首先使用 SQL Server Profiler，但是如果您有一个好的 DBA，有时只需查看查询计划（可以从 Management Studio 获得）通常就足够了。
I guess there are two points where performance might be tweaked in this case. I'll assume that you're accessing the database directly rather than through some kind of secondary layer.
First, you don't say how you're displaying the data itself. If you're loading thousands of records into a grid, that will take time no matter how fast everything else is. Obviously the trick here is to show a subset of the data and allow the user to page, etc. If you're not doing this then that might be a good place to start.
Second, you say that the tables are properly indexed. If this is the case, and assuming that you're not loading 1,000 records into the page at once and retreiving only subsets at a time, then you should be OK.
But, if you're only doing an
ExecuteQuery()against an SQL connection to get a dataset back I don't see how Linq or anything else will help you. I'd say that the problem is obviously on the DB side.So to solve the problem with the database you need to profile the different SELECT statements you're running against it, examine the query plan and identify the places where things are slowing down. You might want to start by using the SQL Server Profiler, but if you have a good DBA, sometimes just looking at the query plan (which you can get from Management Studio) is usually enough.