Reducing roundtrips between client and server

Question

Hi,

We are developing desktop application in vs2008 with winforms and sql server 2008.At present, we make lot of dbase calls(linq to sql) on form load, so this make the application real slow.One option is to load all the data required at the start of application load, and store it in a cache(just static collections).What other options we should look at? Are there any available guidelines available online?

thanks

Answer 1

Option 1. Drop LINQ to SQL. Seriously. Unless you really know what you are doing it is extremely easy to end up with tons of performance problems... Like the one you are describing.

Option 2. Don't do everything in the form load. Instead, what until AFTER the form has loaded, then start running those queries. Do you really need everything before the user has seen the form? Most likely not.

Option 3. Break your forms into multiple forms. In other words reduce the complexity of each individual form to the point that the amount of data it's dealing with is smaller.

Option 4. Do you have lots of basically static lists (like US States for example) that you are pulling from the database? Consider putting those into enums instead. Look at anything that has an extremely small chance of actually changing.

Answer 2

Junky,

I see this all the time when I first come to client sites. Most of the home grown apps follow a "Load it all and then filter it" approach to data. This is really easy and helps you get going quickly but it will inevitably cause what you just mentioned.

I challenge you to start from the assumption that your app doesn't need to load any data while starting up except for it's basic configuration.

Let that sink in, then consider making filtered calls to your database as the application is used to get the chunks.

Also, take a harder look at your LINQ and make sure your underlying data model is optimized. LINQ is going to be slow if you have like 3 huge tables, try normalizing, LINQ does better.

Also, the LINQ itself should have as many filters in the "where" section as possible.

And then of course theres always just limiting the number of rows returned. Ask yourself if the user can really look at 100,000 things? Good luck.

Answer 3

You probably have lots of little calls for your CRUD operations - once you've got your application working, (and only then) it's time to optimize those calls into higher level operations that get exactly the data you need for your front-end.

Hopefully, you've separated your LINQ stuff from the rest of your app in a DAL class - if you haven't do that - it will make optimizing further with real stored procedures (for your most intensive operations) easier.

Be painstakingly thorough with your DAL methods - pass simple types in, almost as if they were stored procedures themselves. Make a new type for the return type of EACH method's row data (return an array if there are more than one "row"). Don't use the auto-generated LINQ types outside of your DAL, instead, copy the values into your new type during select.

Also, watch out for LINQ and its use of connections for multiple sql calls within a single transaction - you're probably using the Distributed Transaction Coordinator without realizing it.

Answer 4

Several answers here seem to be heavy on assumption and gloss over the most important point:

• Before you can usefully optimize anything, you need to find out what is slowing it down.

Use a profiler. A good free one is EQATEC. Or if it's really, really slow, just step through the program. Also use the SQL Server Profiler to see what's going on with the queries themselves.

The problem may not be one of caching at all. It may be a poorly-optimized database. It may be a slow network connection. It may even be something totally unrelated in your code. There's no way to know for sure unless you check.

Having said that, if the problem really is simply having "too many" database calls, there are a few approaches you can take that won't cause a major spike in the application's load time:

• Cache data the first time it is used;

• Switch to using Stored Procedures that return multiple result sets (using IMultipleResult - example here). This requires only one round trip for several sets of data. Or, if you can, combine the results into one query/result, if the data that you are currently querying separately can all be JOINed together.

• Perform your data-access operations in a background thread (i.e. using BackgroundWorker). Often the problem isn't really the actual speed, just the fact that you block the UI while it's happening.

• Start your application right away without pre-loading any data, but immediately start pre-loading in a background thread. Add some synchronization code so that if the user does something that requires the data before it's fully loaded, it will wait for the preloader to finish.

• Use an abstraction layer, such as a WebService, that "lives" very close to the database server and isn't hurt by a large number of round trips. This can then supply all the data relevant to a single UI screen/form.

All of these are complex options that may require a major overhaul to the application architecture. Your life will be much easier if you can simply optimize a few queries with indexes, or combine multiple queries into one. First try to find out why - and I mean specifically why the application is slow; then and only then should you be seriously considering any of the above options.