使用 LINQ 加快查询速度
我正在将大约 350 行（带有一些数据收集）从 MS SQL Server 传输到 iSeries 进行处理。我感觉这个过程太慢了，大约一分钟左右。我正在 LINQ2SQL 中完成所有 MS SQL 的工作。以下是我目前正在做的基础知识：
- 收集所有车辆主数据以一次处理一个。
- SUM() 车辆的燃油使用量
- SUM() 车辆的油使用量
- SUM() 车辆使用的零件
- SUM() 车辆的人工
- SUM() 车辆的外部维修
- SUM() 车辆的事故成本
我意识到这是很多查询，但其中大部分来自 MS SQL Server 中的不同表。所有这些都至少需要一个连接。我正在考虑将“石油和零件”加入到一个查询中，将“外部维修和事故成本”加入到一个查询中，因为这两个查询都存储在同一个表中，看看这是否会提高性能。
您还有其他建议吗？
请注意，这是供应商提供的产品，我宁愿不创建数据库中尚未存在的任何存储过程或视图（基本上没有）。
更新：我有另一篇文章寻找替代方案以提高速度。
I am transferring about 350 rows (with some data collection) from a MS SQL Server to the iSeries for processing. I feel the process is too slow which is about a minute or so. I am doing all of the MS SQL stuff in LINQ2SQL. Here is the basics of what I am doing currently:
- Collect all of the vehicle master data to process one-at-a-time.
- SUM() Fuel usage by vehicle
- SUM() Oil usage by vehicle
- SUM() Parts used by vehicle
- SUM() Labor by vehicle
- SUM() Outside Repairs by vehicle
- SUM() Accident Costs by vehicle
I realize this is a lot of queries, but most of these are from different tables in the MS SQL Server. All of these require at lease one join. I am thinking of joining Oil and Parts in to one query and Outside Repairs and Accident Costs into one query since both of those are stored in the same tables and see if that improves performance any.
Do you have any other suggestions?
Note that this is a vendor delivered product and I would prefer to not create any stored procedures or views (which is basically none) that aren't already in the database.
Update: I had another post looking at alternatives to improving speed.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您也许可以将这些查询启动到单独的线程中并等待它们返回？然后，所有计算将在大约相同的时间内完成，比方说，我猜，现在所需时间的一半。
在我看来，对每个表的结果进行分组是一个好主意，因为您已经在处理这些数据。
将每个表的查询分组并将它们启动到不同的线程中肯定会提高性能。这完全取决于这是否最适合您的情况。
You could perhaps launch these queries into separated threads and wait for them to return? Then, all of your calculations would get done in about the same time as for, let's say, half the time it requires now, I guess.
Grouping your results per table is in my point of view a good idea as you're already processing these datum.
Grouping your queries per table and launching them into different threads would for sure gain in performance. It all depends on if this is optimal for your situation.
看来数据库设计得很糟糕。无论 LINQ 在后台生成什么，它都是非常低效的代码。我并不是说 LINQ 不好，只是它对这个数据库不好。我转换为快速组合的 .XSD 设置，处理时间从 1.25 分钟缩短到 15 秒。一旦我进行了适当的重新设计，我只能猜测我会再节省几秒钟。改天我会在更好的数据库上再次尝试 LINQ。
It appears like the database was poorly designed. Whatever LINQ was generating in the background it was highly inefficient code. I am not saying the LINQ is bad, it just was bad for this database. I converted to a quickly thrown together .XSD setup and the processing time went from 1.25 minutes to 15 seconds. Once I do a proper redesign, I can only guess I'll shave a few more seconds off of that. I'll try LINQ again some other day on a better database.
如果性能很重要（一分钟是个问题？），您可能会考虑使用汇总表。然后您只需查询报告的汇总表即可。可以使用触发器或每晚批量提取到汇总表来构建汇总表。
If performance is important (one minute is a problem?) you might consider using a summary table. Then you just have to query the summary table for your report. The summary table could be built with triggers or with a nightly batch extraction to the summary table.