Linq to SQL Performance

Succinct

How do I performance tune my Linq To SQL DAL methods? Specifically the amount of data being transferred.

Verbose

I have a Winform app that uses Linq To Sql to access it's data. We have 5 branches, 1 physically at the same location as the SQL server and the other 4 at various distances and bandwidths. My app is your typical Enterprise LOB CRUD app. You Search for a Person, Select them, and then as you select a control, say Demographics, it loads that person's Demographics and displays them on screen.

I only store the "key stone" ID's and then make DB calls using those Primary Keys. This works great in the Main location, with the SQL Server on site. However, the branches are experiencing severe delay loading some of the controls.

This is a fairly typical example(loading Person Details user control) of my DAL:

    public static DataTable GetGeneralPersonInfo(int personID)
    {
        using (var context = ConnectDataContext.Create())
        {
            var generalPersonInfo = from person in context.tblPersons
                                    where person.PersonID == personID
                                    join addresse in context.tblAddresses.Where(a =>a.AddressTypeID == 'm')  on person.PersonID equals
                                        addresse.PersonID 
                                    select
                                        new
                                            {
                                                pe开发者_如何学Gorson.PersonID,
                                                person.FirstName,
                                                person.MiddleName,
                                                person.LastName,
                                                person.SuffixID,
                                                person.TitleID,
                                                addresse.AddressLine1,
                                                addresse.AddressLine2,
                                                addresse.AddressLine3,
                                                addresse.CityName,
                                                addresse.StateID,
                                                addresse.ZipCode,
                                                addresse.ZipPlus,
                                                addresse.DirectionsToAddress,
                                                addresse.CountyID,
                                                person.ResidencyCountyID,
                                                person.ResponsibilityCountyID,
                                                person.EmailAddress,
                                                person.SSN,
                                                person.Gender,
                                                person.BirthDate,
                                                person.DeathDate,
                                                person.DriversLicenseNumber,
                                                person.DriversLicenseStateID,
                                                person.HispanicOriginFlag,
                                                person.CitizenFlag,
                                                person.VeteranFlag,
                                                person.MaritalStatusID,
                                                person.PrimaryRaceID,
                                                person.SecondaryRaceID,
                                                person.UpdateUserID,
                                                person.UpdateDateTime,
                                                person.InsertDateTime,
                                                person.InsertUserID,
                                            };

            return generalPersonInfo.CopyLinqToDataTable();
        }
    }

Anything glaringly wrong there?

To provide further disclosure, every table has a uniqueidentifier that is NOT the PK. Also, I have very wide tables(60+ columns someplaces) with large text fields(varchar(500 - max)).

I only store the "key stone" ID's and then make DB calls using those Primary Keys

This is your problem.

Assume you load entities for 100 keys.

• Local branch, 1ms latency. That is 100ms just for the network. 0.1 seconds - performance is "ok".

• Remote branch, let's say 60ms latency. That is 6000ms just for the network. 6 seconds. And itonly goes down from there.

Old rule, teached to me 15 years ago: DO NOT WRITE CHATTY INTERFACES FOR TIER BOUNDARIES. Make as few calls as possible. You made an interface with as many calöls as possible.

The rest is caching (lare fields - how often do they change?).

I'm going to guess this has something to do with your branches connection to the main location where the DB is. If you are getting decent performance at the main branch, but the offsite branch locations are all acting slowly, its probably got nothing to do with your DB query.