This question already has an answer here:
I am making select compiled queries as shown Here
but my question is how to make compiled queries for Insert and Update?
mine Insert query is
DataClassesDataContext db = new DataClassesDataContext();
tbl_desc_index tabledesc = new tbl_desc_index();
tabledesc.ed_journal_id = Convert.ToInt32(Server.HtmlEncode(Request.Cookies["j_id"].Value));
tabledesc.editor_id = Convert.ToInt32(Server.HtmlEncode(Request.Cookies["editorid"].Value));
tabledesc.short_desc = ck1.Text;
tabledesc.abt_journal = ck2.Text;
tabledesc.aim_scope = ck3.Text;
tabledesc.indexed_in = ck4.Text;
db.tbl_desc_indexes.InsertOnSubmit(tabledesc);
db.SubmitChanges();
Update query is
DataClassesDataContext db = new DataClassesDataContext();
var update = db.tbl_desc_indexes.First(p => p.ed_journal_id == Convert.ToInt32(Server.HtmlEncode(Request.Cookies["j_id"].Value)) && p.editor_id == Convert.ToInt32(Server.HtmlEncode(Request.Cookies["editorid"].Value)));
update.short_desc = ck1.Text;
update.abt_journal = ck2.Text;
update.aim_scope = ck3.Text;
update.indexed_in = ck4.Text;
db.SubmitChanges();
What part of the code would you want to speed up? The first seven lines have nothing to do with Linq per se. Line 8, db.tbl_desc_indexes.InsertOnSubmit(tabledesc); is merely passing a reference.
The statement that does all the hard work is
db.SubmitChanges();
That essentially does two things: find out what inserts and updates should be sent to the database, and send them. Generating the actual statements, part of the second step, is very efficient, especially for insert statments. In the case of update statements, the generation is more complex (i.e. has some overhead), because it generates different update statements depending on which columns got updated, but that results is more efficient network usage and often faster execution by SQL Server.
The only thing you can do, is create stored procedures for insert and update (and delete), tailored to your specific needs. There is no guarantee those would be faster though, but you can try and measure.
That being said, in some situations you can have very significant performance gains, by doing set operations for update or delete, i.e. updating or deleting more that one row at a time. Stored procedures are the way to go then.
Server.HtmlEncodecalls? They don't make no sense to me. They cetainly don't do anything usefull in the case those cookies contain integers, nor in the case they don't. Or am I missing something? - Kris Vandermotten