Speeding Up Queries with LINQ

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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.