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
{
person.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)).