加快与LINQ查询 [英] Speeding Up Queries with LINQ

问题描述

我从MS SQL Server的转移大约350行（有一些数据收集）到iSeries进行处理。我觉得这个过程太慢这是约一分钟左右。我做的所有LINQ2SQL的MS SQL的东西。这里是什么我目前做的最基础的：

1. 收集所有车辆的主数据处理一处，一时间。


2. 将车辆SUM（）燃料的使用


3. SUM（）油由车辆使用


4. SUM（）通过车用零部件


5. SUM（）按车辆劳动


6. SUM（）外部装修，按车辆


7. SUM（）按车辆

我意识到这是一个很大的查询，但其中大部分都是从MS不同的表事故成本SQL Server中。所有这些都要求在租赁一个连接。我想在一个查询和外部装修，加入油和零部件和事故成本成为一个查询，因为这两个都存储在同一个表中，并看看是否能提高性能的。

你有什么其他的建议？

请注意，这是一个供应商提供的产品，我宁愿。不能创建已经不在数据库中的任何存储过程或视图（这基本上是无）

更新：我<一个HREF =http://stackoverflow.com/questions/2418571/whats-faster-struct-array-or-datatable>另一个帖子寻找替代品来提高速度。

< DIV CLASS =h2_lin>解决方案

看起来像数据库设计不当。无论LINQ的背景是产生这是非常低效的代码。我不是说的LINQ是坏，它只是坏该数据库。我转换为迅速扔在一起.XSD设置和处理时间从1.25分钟过去了15秒。有一次，我做了正确的重新设计，我只能猜测，我会刮胡子的几秒钟内关闭这一点。我会尝试再次LINQ改天更好的数据库。

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:

1. Collect all of the vehicle master data to process one-at-a-time.
2. SUM() Fuel usage by vehicle
3. SUM() Oil usage by vehicle
4. SUM() Parts used by vehicle
5. SUM() Labor by vehicle
6. SUM() Outside Repairs by vehicle
7. 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.

解决方案

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.

这篇关于加快与LINQ查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！