Linq Asp.net code optimization
I'm using AdventureWorks as a database to get familiar with linq and asp.net interface and was wondering if anyone could take a look at my code behind in C# and give me a optimized way to get the results populated when searched and then updated when submitted.
protected void btnSearch_Click(object sender, EventArgs e)
{
TestDataClassDataContext dc = new TestDataClassDataContext();
Individual ind = new Individual();
var q = from Individual in dc.Individuals
where Individual.CustomerID == Convert.ToInt32(txtCustID.Text)
select Individual;
if (q.Count() > 0)
{
ind = q.First();
Contact con = new Contact();
var q2 = from Contact in dc.Contacts
where Contact.ContactID == ind.ContactID
select Contact;
if (q2.Count() > 0)
{
con = q2.First();
txtFname.Text = con.FirstName;
txtLname.Text = con.LastName;
txtMname.Text = con.MiddleName;
txtPhone.Text = con.Phone;
txtPword.Text = con.PasswordSalt;
txtSuff.Text = con.Suffix;
txtTitle.Text = con.Title;
txtEmail.Text = con.EmailAddress;
ddlEmailPromo.SelectedValue = con.EmailPromotion.ToString();
}
}
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
TestDataClassDataContext dc = new TestDataClassDataContext();
Individual ind = new Individual();
var q = from Individual in dc.Individuals
where Individual.CustomerID == Convert.ToInt32(txtCustID.Text)
select Individual;
if (q.Count() > 0)
{
ind = q.First();
Contact con = new Contact();
var q2 = from Contact in dc.Contacts
where Contact.ContactID == ind.ContactID
select Contact;
if (q2.Count() > 0)
{
con = q2.First();
con.FirstName = txtFname.Text;
con.LastName = txtLname.Text;
con.MiddleName = txtMname.Text;
con.Phone = txtPhone.Text;
con.PasswordSalt = txtPword.Text;
con.Suffix = txtSuff.Text;
con.Title = txtTitle.Text;
con.EmailAddress = txtEmail.Text ;
ddlEmailPromo.Se开发者_开发百科lectedValue = con.EmailPromotion.ToString();
}
}
In one query:
TestDataClassDataContext dc = new TestDataClassDataContext();
var con = (from individual in dc.Individuals
join contact in dc.Contacts on individual.ContactID equals contact .ContactID
where individual.CustomerID == Convert.ToInt32(txtCustID.Text)
select contact).FirstOrDefault();
if(con == null) return;
txtFname.Text = con.FirstName;
txtLname.Text = con.LastName;
txtMname.Text = con.MiddleName;
txtPhone.Text = con.Phone;
txtPword.Text = con.PasswordSalt;
txtSuff.Text = con.Suffix;
txtTitle.Text = con.Title;
txtEmail.Text = con.EmailAddress;
ddlEmailPromo.SelectedValue = con.EmailPromotion.ToString();
Use the same query into the submit changes function!
Pierre-Luc's answer is spot on. It might be worth also mentioning that because the btnSearch_Click method is only reading from the database there is no need to have .net track the ojects for changes as it does for updates or deletes so putting:
dc.ObjectTrackingEnabled = false;
just under the DataContext initialization will tell .net to just open the connection for a read request which can help when trying to optimize your code. More info here: http://www.sidarok.com/web/blog/content/2008/05/02/10-tips-to-improve-your-linq-to-sql-application-performance.html
If you have the relationships setup properly in SQL, and both tables exist on your DBML, the first SQL query should fetch the second part of your query too.
Example:
TestDataClassDataContext dc = new TestDataClassDataContext();
Individual individual = dc.Individuals.Where(a => a.CustomerID == Convert.ToInt32(txtCustID.Text).FirstOrDefault();
if(individual != null)
{
Contact contact = individual.Contacts.FirstOrDefault();
if(contact != null)
{
txtFname.Text = contact.FirstName;
}
}
1) First start of an asp.net application always takes more time, than just handling a request.
2) Debugging enabled in web.config alsow slows the application.
3) (Maybe the most significant) Lets look closer to your code.
var q = from Individual in dc.Individuals
where Individual.CustomerID == Convert.ToInt32(txtCustID.Text)
select Individual;
q DOES NOT contain any objects - it's just a query. Now, lets count, how many times it is executed:
1.if (q.Count() > 0)
ind = q.First();
q2.Count() > 0
At least 3 times per request your connection is established, opened, and closed. It takes time. Try to use your resources use sparingly.
Hope this helps, Ilya.
Also note that each call to .Count() will enumerate over the complete result set to return a count.
What you're looking for is the .Any() method, which will run in constant time.
精彩评论