|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Inserting Records Best PracticeCould anyone point me in the right direction for the best way to insert a
record into a SQL database using ADO.NET with 150+ columns? 1. DataReader + SqlCommand.CommandText + Insert statement 2. DataReader + SqlCommand.Paramerterized Query + Stored Procedure 3. DataSet + AddNew() 4. Other?? Thanks, What do you do with DataReader?
5. DataSet + DataAdapter is the correct answer :-). Don't forget to use batching on ado.net 2. -- Show quoteMiha Markic [MVP C#, INETA Country Leader for Slovenia] RightHand .NET consulting & development www.rthand.com Blog: http://cs.rthand.com/blogs/blog_with_righthand/ "Mario G." <Mar***@discussions.microsoft.com> wrote in message news:AE8CBCFF-B0FB-4753-8038-E7637834102C@microsoft.com... > Could anyone point me in the right direction for the best way to insert a > record into a SQL database using ADO.NET with 150+ columns? > > 1. DataReader + SqlCommand.CommandText + Insert statement > 2. DataReader + SqlCommand.Paramerterized Query + Stored Procedure > 3. DataSet + AddNew() > 4. Other?? > > Thanks, Hi Miha,
I'm not sure what I was thinking when I added the DataReader in the first 2 options. What I meant was utilizing the SqlCommand with either a CommandType.Text or CommandType.StoredProcedure. I know stored procedures execute quicker as they are already compiled but my concern was having 150+ Parameters to pass to the stored procedure. Which would be faster. As for the DataSet and DataAdapter method (my option #3), what would be the best method to accomplish this? In light of the new details would you still recommend utilizing the DataSet + DataAdapter over the SqlCommand to insert the record (with over 150 columns)? Thanks, Show quote "Miha Markic [MVP C#]" wrote: > What do you do with DataReader? > 5. DataSet + DataAdapter is the correct answer :-). Don't forget to use > batching on ado.net 2. > > -- > Miha Markic [MVP C#, INETA Country Leader for Slovenia] > RightHand .NET consulting & development www.rthand.com > Blog: http://cs.rthand.com/blogs/blog_with_righthand/ > > "Mario G." <Mar***@discussions.microsoft.com> wrote in message > news:AE8CBCFF-B0FB-4753-8038-E7637834102C@microsoft.com... > > Could anyone point me in the right direction for the best way to insert a > > record into a SQL database using ADO.NET with 150+ columns? > > > > 1. DataReader + SqlCommand.CommandText + Insert statement > > 2. DataReader + SqlCommand.Paramerterized Query + Stored Procedure > > 3. DataSet + AddNew() > > 4. Other?? > > > > Thanks, > > Mario,
> I know stored procedures execute quicker as they are already compiled Are you using an IBM DB2 database. If you want to read about this, search this newsgroup for Frans Bouma, who have had very long discussions about the misunderstanding that Stored Procedures are compiled. They are compiled at the same time as Text transaction strings. Cor Cor,
We are actually using SQL Server 2000 (with a project to implement 2005 soon). I'm very intrigued about the misconseptions that Stored Procedures are compiled and thus quicker. I'm definitely going to review Frans comments and perform some tests. Thanks for the info. Show quote "Cor Ligthert [MVP]" wrote: > Mario, > > > I know stored procedures execute quicker as they are already compiled > > Are you using an IBM DB2 database. If you want to read about this, search > this newsgroup for Frans Bouma, who have had very long discussions about the > misunderstanding that Stored Procedures are compiled. They are compiled at > the same time as Text transaction strings. > > Cor > > > Cor,
I've searched for Frans Bouma and any discussions regarding Compiled Stored Procedures but could not find any such discussion. You wouldn't happen to have a link handy (or subject line)? Thanks, Show quote "Cor Ligthert [MVP]" wrote: > Mario, > > > I know stored procedures execute quicker as they are already compiled > > Are you using an IBM DB2 database. If you want to read about this, search > this newsgroup for Frans Bouma, who have had very long discussions about the > misunderstanding that Stored Procedures are compiled. They are compiled at > the same time as Text transaction strings. > > Cor > > > Hi Mario,
"Mario G." <Mar***@discussions.microsoft.com> wrote in message Both approaches require parameters, so no big difference there. Furthermore, news:3F716F23-2855-43F8-B11B-757987D74069@microsoft.com... > Hi Miha, > > I'm not sure what I was thinking when I added the DataReader in the first > 2 > options. What I meant was utilizing the SqlCommand with either a > CommandType.Text or CommandType.StoredProcedure. I know stored procedures > execute quicker as they are already compiled but my concern was having > 150+ > Parameters to pass to the stored procedure. Which would be faster. there isn't a significant performance difference between the two approaches. SP approach should be a bit faster because you are sending less data over the wire I suppose. > Definitely the best approach. It does same job as you would done manually + > As for the DataSet and DataAdapter method (my option #3), what would be > the > best method to accomplish this? > > In light of the new details would you still recommend utilizing the > DataSet > + DataAdapter over the SqlCommand to insert the record (with over 150 > columns)? it supports batching. And batching improves performance dramatically. -- Miha Markic [MVP C#, INETA Country Leader for Slovenia] RightHand .NET consulting & development www.rthand.com Blog: http://cs.rthand.com/blogs/blog_with_righthand/ Miha,
I've come to the conclusion that I will be be utilizing the DataAdapter to batch the work I need to perform. Now for another question. I have 5 methods created to insert a new record or update an existing record into their respective tables. 1. Do I need to have a DataAdapter and DataSet per table (in each method) and instantiate a NewRow for each Insert? 2. The primary insert generates a unique (numeric) key which is required as a foreign key for the remaining tables. How can I retrieve this? I also need to make this transaction (ACID) and am using conn.BeginTransaction. Is this framework correct? class DataAccess { private SqlConnection cn = new SqlConnection(Configuration.Manager(...)); private SqlTransaction trans = null; private DataAdapter da1, da2...; public void BeginTrans() { trans = cn.BeginTransaction(): } public void CommitTrans() { trans.Commit(); } public void InsertTable1(DataSet ds) { Instantiate DataAdapter, DataSet and DataRow //Need Primary Key } public void InsertTable2..5(long foreignkey, DataSet ds) { Instantiate DataAdapter, DataSet and DataRow //Assign Foreign Key } public void BatchUpdate() { da1.Update(); da2.Update(); da3.Update(); da4.Update(); da5.Update(); } } somewhere in the business logic routines it would : DataAccess da = new DataAccess(); da.BeginTrans(); long foreignkey = da.InsertTable1(ds); da.InsertTable2(foreignkey, ds) da.InsertTable3(foreignkey, ds) da.InsertTable4(foreignkey, ds) da.InsertTable5(foreignkey, ds) da.BatchUpdate(); da.CommitTrans(); Thanks for all your help. Mario Show quote "Miha Markic [MVP C#]" wrote: > Hi Mario, > > "Mario G." <Mar***@discussions.microsoft.com> wrote in message > news:3F716F23-2855-43F8-B11B-757987D74069@microsoft.com... > > Hi Miha, > > > > I'm not sure what I was thinking when I added the DataReader in the first > > 2 > > options. What I meant was utilizing the SqlCommand with either a > > CommandType.Text or CommandType.StoredProcedure. I know stored procedures > > execute quicker as they are already compiled but my concern was having > > 150+ > > Parameters to pass to the stored procedure. Which would be faster. > > Both approaches require parameters, so no big difference there. Furthermore, > there isn't a significant performance difference between the two approaches. > SP approach should be a bit faster because you are sending less data over > the wire I suppose. > > > > > As for the DataSet and DataAdapter method (my option #3), what would be > > the > > best method to accomplish this? > > > > In light of the new details would you still recommend utilizing the > > DataSet > > + DataAdapter over the SqlCommand to insert the record (with over 150 > > columns)? > > Definitely the best approach. It does same job as you would done manually + > it supports batching. And batching improves performance dramatically. > > -- > Miha Markic [MVP C#, INETA Country Leader for Slovenia] > RightHand .NET consulting & development www.rthand.com > Blog: http://cs.rthand.com/blogs/blog_with_righthand/ > Mario,
SQLCommand.ExecuteNonQuery with a CommandText or SP with parameters, a lot of work be aware that the Microsoft generaters only go to 100 columns. Maybe you use parts of this code, although it is completely the opposite direction can you maybe use it to create your procedure in a loop. http://www.vb-tips.com/dbPages.aspx?ID=49f2cff5-56ad-44fc-a4c6-fc0d5c470f53 Cor Show quote "Mario G." <Mar***@discussions.microsoft.com> schreef in bericht news:AE8CBCFF-B0FB-4753-8038-E7637834102C@microsoft.com... > Could anyone point me in the right direction for the best way to insert a > record into a SQL database using ADO.NET with 150+ columns? > > 1. DataReader + SqlCommand.CommandText + Insert statement > 2. DataReader + SqlCommand.Paramerterized Query + Stored Procedure > 3. DataSet + AddNew() > 4. Other?? > > Thanks, 1) Review your normalization. 150 columns is quite a few.
2) Where is the data coming from? Based on the other comments it sounds like you're importing data, massaging it and sending it back out via an INSERT. ADO.NET is not really designed for this type of operation. I suggest investigating BCP or SqlBulkCopy. 3) Stored procedures are no longer pre-compiled. Their performance is gained from a cached query plan and then only if the plan matches the operation. They are also designed to help build systems in a team environment. -- Show quote____________________________________ William (Bill) Vaughn Author, Mentor, Consultant Microsoft MVP INETA Speaker www.betav.com/blog/billva www.betav.com Please reply only to the newsgroup so that others can benefit. This posting is provided "AS IS" with no warranties, and confers no rights. __________________________________ Visit www.hitchhikerguides.net to get more information on my latest book: Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) ----------------------------------------------------------------------------------------------------------------------- "Mario G." <Mar***@discussions.microsoft.com> wrote in message news:AE8CBCFF-B0FB-4753-8038-E7637834102C@microsoft.com... > Could anyone point me in the right direction for the best way to insert a > record into a SQL database using ADO.NET with 150+ columns? > > 1. DataReader + SqlCommand.CommandText + Insert statement > 2. DataReader + SqlCommand.Paramerterized Query + Stored Procedure > 3. DataSet + AddNew() > 4. Other?? > > Thanks, See inline comments:
"William (Bill) Vaughn" wrote: Unfortunately this DB is not normalized. We have 2 major projects planned > 1) Review your normalization. 150 columns is quite a few. for next year (SQL 2005 upgrade including normalization, application rewrite to .NET). The actual number of columns in the largest table is 250. > 2) Where is the data coming from? Based on the other comments it sounds like The data is coming from a 3rd party companies web service in XML format. We > you're importing data, massaging it and sending it back out via an INSERT. > ADO.NET is not really designed for this type of operation. I suggest > investigating BCP or SqlBulkCopy. then have to parse and apply business logic to the data and store it into various tables in our database. > 3) Stored procedures are no longer pre-compiled. Their performance is gained Cached query is not an option as the details are dynamic in nature. I've > from a cached query plan and then only if the plan matches the operation. > They are also designed to help build systems in a team environment. read the discussions by Frans Bouma and have come to the same conclusion. Show quote > > -- > ____________________________________ > William (Bill) Vaughn > Author, Mentor, Consultant > Microsoft MVP > INETA Speaker > www.betav.com/blog/billva > www.betav.com > Please reply only to the newsgroup so that others can benefit. > This posting is provided "AS IS" with no warranties, and confers no rights. > __________________________________ > Visit www.hitchhikerguides.net to get more information on my latest book: > Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) > and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) > ----------------------------------------------------------------------------------------------------------------------- > > "Mario G." <Mar***@discussions.microsoft.com> wrote in message > news:AE8CBCFF-B0FB-4753-8038-E7637834102C@microsoft.com... > > Could anyone point me in the right direction for the best way to insert a > > record into a SQL database using ADO.NET with 150+ columns? > > > > 1. DataReader + SqlCommand.CommandText + Insert statement > > 2. DataReader + SqlCommand.Paramerterized Query + Stored Procedure > > 3. DataSet + AddNew() > > 4. Other?? > > > > Thanks, > > > I would still investigate importing the data from XML to a SQL table via
bulk copy and doing the manipulation server-side. -- Show quote____________________________________ William (Bill) Vaughn Author, Mentor, Consultant Microsoft MVP INETA Speaker www.betav.com/blog/billva www.betav.com Please reply only to the newsgroup so that others can benefit. This posting is provided "AS IS" with no warranties, and confers no rights. __________________________________ Visit www.hitchhikerguides.net to get more information on my latest book: Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) ----------------------------------------------------------------------------------------------------------------------- "Mario G." <Mar***@discussions.microsoft.com> wrote in message news:A78DDDFF-80E9-4373-890E-7A34FB8DD4FB@microsoft.com... > See inline comments: > > "William (Bill) Vaughn" wrote: > >> 1) Review your normalization. 150 columns is quite a few. > > Unfortunately this DB is not normalized. We have 2 major projects planned > for next year (SQL 2005 upgrade including normalization, application > rewrite > to .NET). The actual number of columns in the largest table is 250. > >> 2) Where is the data coming from? Based on the other comments it sounds >> like >> you're importing data, massaging it and sending it back out via an >> INSERT. >> ADO.NET is not really designed for this type of operation. I suggest >> investigating BCP or SqlBulkCopy. > > The data is coming from a 3rd party companies web service in XML format. > We > then have to parse and apply business logic to the data and store it into > various tables in our database. > >> 3) Stored procedures are no longer pre-compiled. Their performance is >> gained >> from a cached query plan and then only if the plan matches the operation. >> They are also designed to help build systems in a team environment. > > Cached query is not an option as the details are dynamic in nature. I've > read the discussions by Frans Bouma and have come to the same conclusion. > > >> >> -- >> ____________________________________ >> William (Bill) Vaughn >> Author, Mentor, Consultant >> Microsoft MVP >> INETA Speaker >> www.betav.com/blog/billva >> www.betav.com >> Please reply only to the newsgroup so that others can benefit. >> This posting is provided "AS IS" with no warranties, and confers no >> rights. >> __________________________________ >> Visit www.hitchhikerguides.net to get more information on my latest book: >> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) >> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) >> ----------------------------------------------------------------------------------------------------------------------- >> >> "Mario G." <Mar***@discussions.microsoft.com> wrote in message >> news:AE8CBCFF-B0FB-4753-8038-E7637834102C@microsoft.com... >> > Could anyone point me in the right direction for the best way to insert >> > a >> > record into a SQL database using ADO.NET with 150+ columns? >> > >> > 1. DataReader + SqlCommand.CommandText + Insert statement >> > 2. DataReader + SqlCommand.Paramerterized Query + Stored Procedure >> > 3. DataSet + AddNew() >> > 4. Other?? >> > >> > Thanks, >> >> >>  |
|||||||||||||||||||||||
Other interesting topics