|
|
You're in the
sql server programming
group:SQL2000/ASP.Net performance question
sql server programming:
pass the entire dataset to sqlserver in one batch. [quoted text, click to view] "Bill Cohagan" <bill@teraXNOSPAMXquest.com> wrote in message news:uRIu9gOgEHA.2468@TK2MSFTNGP12.phx.gbl... > I'm building an ASP.Net app that will process a web form containing a few > hundred (~300) fields, the results going into a SQL database. Each field > from the web form will generate a record in a single table in the database. > I've got to be able to handle a few hundred users submitting these forms at > "the same time". > > Given my current data layer I'm connecting to the database for each record > added to the table. What I'm wondering about is whether this will cause > performance problems given the loads I anticipate. Another alternative is to > connect to the database only once per form, inserting all the new records > using that single connection. This would require modifying the data layer > which isn't a huge problem, but I'd rather not bother if it isn't a problem > doing it the "easy" way. Although this reduces the number of connections, > it'd keep those connections around longer -- perhaps causing locking > problems? > > I'm a relative newbie at this so feel free to point me to sources where I > might learn more about these issues. Of course, just explaining the > tradeoffs would be OK too! > > Thanks in advance, > Bill > >
I'm building an ASP.Net app that will process a web form containing a few
hundred (~300) fields, the results going into a SQL database. Each field from the web form will generate a record in a single table in the database. I've got to be able to handle a few hundred users submitting these forms at "the same time". Given my current data layer I'm connecting to the database for each record added to the table. What I'm wondering about is whether this will cause performance problems given the loads I anticipate. Another alternative is to connect to the database only once per form, inserting all the new records using that single connection. This would require modifying the data layer which isn't a huge problem, but I'd rather not bother if it isn't a problem doing it the "easy" way. Although this reduces the number of connections, it'd keep those connections around longer -- perhaps causing locking problems? I'm a relative newbie at this so feel free to point me to sources where I might learn more about these issues. Of course, just explaining the tradeoffs would be OK too! Thanks in advance, Bill
Thanks for the response. Yes, I'm aware of the dataadapter approach and
would probably use that approach if batching the update is preferable. That functionality is not currently supported by the existing data layer however and so would have to be added. Again, that's not a huge problem, but what I'm trying to find out is whether it's necessary. In other words, given the scale I've described, am I likely to have performance problems with the existing "record at a time" update? Do you have any feeling for that? Thanks, Bill [quoted text, click to view] "oj" <nospam_ojngo@home.com> wrote in message news:et6zUhPgEHA.3948@TK2MSFTNGP11.phx.gbl... > Have you taken a look at sqldataadapter.update() method. This would allow you to > pass the entire dataset to sqlserver in one batch. > > > "Bill Cohagan" <bill@teraXNOSPAMXquest.com> wrote in message > news:uRIu9gOgEHA.2468@TK2MSFTNGP12.phx.gbl... > > I'm building an ASP.Net app that will process a web form containing a few > > hundred (~300) fields, the results going into a SQL database. Each field > > from the web form will generate a record in a single table in the database. > > I've got to be able to handle a few hundred users submitting these forms at > > "the same time". > > > > Given my current data layer I'm connecting to the database for each record > > added to the table. What I'm wondering about is whether this will cause > > performance problems given the loads I anticipate. Another alternative is to > > connect to the database only once per form, inserting all the new records > > using that single connection. This would require modifying the data layer > > which isn't a huge problem, but I'd rather not bother if it isn't a problem > > doing it the "easy" way. Although this reduces the number of connections, > > it'd keep those connections around longer -- perhaps causing locking > > problems? > > > > I'm a relative newbie at this so feel free to point me to sources where I > > might learn more about these issues. Of course, just explaining the > > tradeoffs would be OK too! > > > > Thanks in advance, > > Bill > > > > > >
Better, to an extent, but not enough.
the da.Update() method loops through the records and fires individual INSERT/UPDATE commands. While very maintainable, it does not yield much higher perf than pulling connections from pool and firing off individual INSERT/UPDATE. For better perf, the XML capabilities of SQL Server are the way to go. This requires some XSLT-ing of the DataSet XML, but it works quite nicely. -- Gregory A. Beamer MVP; MCP: +I, SE, SD, DBA ************************************************ Think Outside the Box! ************************************************ [quoted text, click to view] "oj" <nospam_ojngo@home.com> wrote in message news:et6zUhPgEHA.3948@TK2MSFTNGP11.phx.gbl... > Have you taken a look at sqldataadapter.update() method. This would allow you to > pass the entire dataset to sqlserver in one batch. > > > "Bill Cohagan" <bill@teraXNOSPAMXquest.com> wrote in message > news:uRIu9gOgEHA.2468@TK2MSFTNGP12.phx.gbl... > > I'm building an ASP.Net app that will process a web form containing a few > > hundred (~300) fields, the results going into a SQL database. Each field > > from the web form will generate a record in a single table in the database. > > I've got to be able to handle a few hundred users submitting these forms at > > "the same time". > > > > Given my current data layer I'm connecting to the database for each record > > added to the table. What I'm wondering about is whether this will cause > > performance problems given the loads I anticipate. Another alternative is to > > connect to the database only once per form, inserting all the new records > > using that single connection. This would require modifying the data layer > > which isn't a huge problem, but I'd rather not bother if it isn't a problem > > doing it the "easy" way. Although this reduces the number of connections, > > it'd keep those connections around longer -- perhaps causing locking > > problems? > > > > I'm a relative newbie at this so feel free to point me to sources where I > > might learn more about these issues. Of course, just explaining the > > tradeoffs would be OK too! > > > > Thanks in advance, > > Bill > > > > > >
Encapsulate the INSERTs from a single form in a single procedure. Then, use
a SqlCommand object with parameters and offload the work to SQL Server. That is one option that is extremely high perf. Make sure you do server side validation on required fields (whether or not you use client side). I am not sure what user load you are talking, but I see nothing in here that is extremely troublesome for a web server against a database server. -- Gregory A. Beamer MVP; MCP: +I, SE, SD, DBA ************************************************ Think Outside the Box! ************************************************ [quoted text, click to view] "Bill Cohagan" <bill@teraXNOSPAMXquest.com> wrote in message news:uRIu9gOgEHA.2468@TK2MSFTNGP12.phx.gbl... > I'm building an ASP.Net app that will process a web form containing a few > hundred (~300) fields, the results going into a SQL database. Each field > from the web form will generate a record in a single table in the database. > I've got to be able to handle a few hundred users submitting these forms at > "the same time". > > Given my current data layer I'm connecting to the database for each record > added to the table. What I'm wondering about is whether this will cause > performance problems given the loads I anticipate. Another alternative is to > connect to the database only once per form, inserting all the new records > using that single connection. This would require modifying the data layer > which isn't a huge problem, but I'd rather not bother if it isn't a problem > doing it the "easy" way. Although this reduces the number of connections, > it'd keep those connections around longer -- perhaps causing locking > problems? > > I'm a relative newbie at this so feel free to point me to sources where I > might learn more about these issues. Of course, just explaining the > tradeoffs would be OK too! > > Thanks in advance, > Bill > >
If you're after faster dataload, consider bulkload. The cost for processing xml
with sql2k is considerably more than a bunch of inserts/updates. If you're looking at doing xml bulkload for just a few rows, the effort/cost for doing this is still much more higher than the simple cmd.execute. "Cowboy (Gregory A. Beamer) [MVP]" <NoSpamMgbworld@comcast.netNoSpamM> wrote in message news:OG81N3VgEHA.3348@TK2MSFTNGP12.phx.gbl... [quoted text, click to view] > Better, to an extent, but not enough. > > the da.Update() method loops through the records and fires individual > INSERT/UPDATE commands. While very maintainable, it does not yield much > higher perf than pulling connections from pool and firing off individual > INSERT/UPDATE. > > For better perf, the XML capabilities of SQL Server are the way to go. This > requires some XSLT-ing of the DataSet XML, but it works quite nicely. > > -- > Gregory A. Beamer > MVP; MCP: +I, SE, SD, DBA > > ************************************************ > Think Outside the Box! > ************************************************ > "oj" <nospam_ojngo@home.com> wrote in message > news:et6zUhPgEHA.3948@TK2MSFTNGP11.phx.gbl... > > Have you taken a look at sqldataadapter.update() method. This would allow > you to > > pass the entire dataset to sqlserver in one batch. > > > > > > "Bill Cohagan" <bill@teraXNOSPAMXquest.com> wrote in message > > news:uRIu9gOgEHA.2468@TK2MSFTNGP12.phx.gbl... > > > I'm building an ASP.Net app that will process a web form containing a > few > > > hundred (~300) fields, the results going into a SQL database. Each field > > > from the web form will generate a record in a single table in the > database. > > > I've got to be able to handle a few hundred users submitting these forms > at > > > "the same time". > > > > > > Given my current data layer I'm connecting to the database for each > record > > > added to the table. What I'm wondering about is whether this will cause > > > performance problems given the loads I anticipate. Another alternative > is to > > > connect to the database only once per form, inserting all the new > records > > > using that single connection. This would require modifying the data > layer > > > which isn't a huge problem, but I'd rather not bother if it isn't a > problem > > > doing it the "easy" way. Although this reduces the number of > connections, > > > it'd keep those connections around longer -- perhaps causing locking > > > problems? > > > > > > I'm a relative newbie at this so feel free to point me to sources where > I > > > might learn more about these issues. Of course, just explaining the > > > tradeoffs would be OK too! > > > > > > Thanks in advance, > > > Bill > > > > > > > > > > > >
Hi Bill,
I think the following documents will be very helpful for you, help it helps:) HOW TO: Troubleshoot Application Performance Issues http://support.microsoft.com/default.aspx?scid=KB;EN-US;298475 INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems http://support.microsoft.com/default.aspx?scid=kb;EN-US;224453 HOW TO: Troubleshoot Application Performance with SQL Server http://support.microsoft.com/default.aspx?scid=KB;EN-US;224587 HOW TO: Troubleshoot the Performance of Ad-Hoc Queries http://support.microsoft.com/default.aspx?scid=KB;EN-US;243588 Thank you for your patience and cooperation. If you have any questions or concerns, don't hesitate to let me know. We are here to be of assistance! Sincerely yours, Mingqing Cheng Microsoft Developer Community Support --------------------------------------------------------------- Introduction to Yukon! - http://www.microsoft.com/sql/yukon This posting is provided "as is" with no warranties and confers no rights. Please reply to newsgroups only, many thanks!
[quoted text, click to view] "Bill Cohagan" <bill@teraXNOSPAMXquest.com> wrote in message news:uRIu9gOgEHA.2468@TK2MSFTNGP12.phx.gbl... > I'm building an ASP.Net app that will process a web form containing a few > hundred (~300) fields, the results going into a SQL database. Each field > from the web form will generate a record in a single table in the database. > I've got to be able to handle a few hundred users submitting these forms at > "the same time". > > Given my current data layer I'm connecting to the database for each record > added to the table. What I'm wondering about is whether this will cause > performance problems given the loads I anticipate. Another alternative is to > connect to the database only once per form, inserting all the new records > using that single connection. This would require modifying the data layer > which isn't a huge problem, but I'd rather not bother if it isn't a problem > doing it the "easy" way. Although this reduces the number of connections, > it'd keep those connections around longer -- perhaps causing locking > problems? > > I'm a relative newbie at this so feel free to point me to sources where I > might learn more about these issues. Of course, just explaining the > tradeoffs would be OK too! > > Thanks in advance, > Bill Recently I built sites for a company that handles insurance quotes for insurance companies. The forms were comprised of 100-200 fields. Most of the 90% of the fields were never used in queries other than to store and print. In this case, I build a field manager that took the data stored in the controls on the form and placed them into a hash table based on the field name. Inside the application it would simply pull the values or set values in its computations inside this hash table. When the quote would be saved, the key fields that it did need to query were stored in a single record. Then all fields were serialized into a blob and stored with the record. This made it easy in the application to read/write all those fields in one pass while still exposing fields that need to be queried in the same record. Worked really slick. When a field was added or removed, I only had to change the field on the form and everything still linked up. The field manager also handled basic validation as the first letter of the control name for a field on the form would start with an 'R" if it was a required field and then the next few letters would be the type of data (such as ddl for a drop down list, dbl for a double value, etc). The rest of the letters of the control name were the field name it would be stored in the hash table under. This only works if most of the fields are not used in SQL queries. -- Rocky Moore www.HintsAndTips.com / Share your tips - Earn rewards www.MyQuickPoll.com / 2004 Election poll ID #33 www.GotTheAnswerToSpam.com / Block virtually all spam email www.RJSoft.com/Products/RJContentPanel/ - Free web user template content control!
Hi Bill,
I wanted to post a quick note to see if you would like additional assistance or information regarding this particular issue. We appreciate your patience and look forward to hearing from you! Thank you for your patience and cooperation. If you have any questions or concerns, don't hesitate to let me know. We are here to be of assistance! Sincerely yours, Mingqing Cheng Microsoft Developer Community Support --------------------------------------------------------------- Introduction to Yukon! - http://www.microsoft.com/sql/yukon This posting is provided "as is" with no warranties and confers no rights. Please reply to newsgroups only, many thanks!
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |