67822

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:

<ol><li>Collect all of the vehicle master data to process one-at-a-time.</li> <li>SUM() Fuel usage by vehicle </li> <li>SUM() Oil usage by vehicle</li> <li>SUM() Parts used by vehicle</li> <li>SUM() Labor by vehicle</li> <li>SUM() Outside Repairs by vehicle</li> <li>SUM() Accident Costs by vehicle</li> </ol>

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.

<strong>Update:</strong> I had <a href="https://stackoverflow.com/questions/2418571/whats-faster-struct-array-or-datatable" rel="nofollow">another post</a> looking at alternatives to improving speed.

Answer1:

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.

Answer2:

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.

Answer3:

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.

Recommend

  • How to get system's closed caption font size?
  • What is the paradigmatic way to use large global static tables that are initialized once and serve p
  • Scroll till end of the page using appium-android
  • Dynamic checkbox html using jquery from database
  • How can I create a file using the PHP function fopen() with UTF 16 encoding?
  • Is there any other Data structure to represent Graph other than Adjacency List or Adjacency Matrix?
  • Streaming bytes via HTTP PUT with JAX-RS
  • Full stack trace for intercepted methods at call site in Unity
  • How do I vertically align text inside an anchor element, which is nested within an unordered list
  • Set nextVisibleTime in Azure Webjobs SDK
  • How can a function find a parent element of the anchor which originally called the function?
  • sending emails in python weird behaviour
  • How to convert SVG to jVectorMap format
  • How can I detect mongodb reconnect failed event
  • remove date from DateTimePicker for Compact Framework
  • Gradle refresh removes source folders in build path
  • plot dirac function in matlab
  • Running iPhone crash Logs from testers on XCode
  • Nested AJAX Calls using .done
  • if you have a DLL creating a bitmap in memory, how to return it to the browser?
  • Use awk to convert GPS Position to Latitude & Longitude
  • what do lines starting with double-slash '//' mean in a .npmrc?
  • characters not allowed in DOM ids by spec, and by browser
  • Google TV VideoView playing YouTube rtsp videos
  • Adding native code to an existing Worklight hybrid app
  • opencv deskewing a contour
  • What are advantages/disadvantages of using Selenium for Java vs .NET applications?
  • Reload Page with Javascript after Database changes
  • How to redirect into different page by user type in php and mysql
  • Regex not working in java 1.5
  • How can I ssh into a server that requires 2 password authentication using python's paramiko mod