T-SQL from ASP – Stored Proc Performance? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

T-SQL from ASP – Stored Proc Performance?

Hi, I am maintaining an IIS6 ASP Web Application which has become slow during heavy useage periods (anything over about 60 concurrent users of the web site). The server in question is lowly resourced – Single 2.4GHz processor, 1.5GB RAM – and is hosting IIS, SQL & FTP all on its lonesome. (Not for too much longer thankfully) I am relatively new to SQL and have already done a lot to tidy up occurences of lazy select statements Eg frequent use of "select * from" when in fact only 2 – 20 of the 50+ available fields are actually being used – and also recordsets not being closed etc. I have moved the Transaction Logs to a separate disk and introduced better indexing also. Due to the nature of the application, the queries are pretty simple and I wouldn’t imagine are requiring much complexity of execution plan.
Eg Select fld1,fld2,fld3,fld4 From X where fld1=’Y’ Order By fld1 This being the main type of query being hit by similtaneous users of the site, with fld1 being a user ID for example. I guess my question becomes; given all the above, should I expect performance to be improved much from converting such queries to stored procedures in SQL. Any advice appreciated.
Richard.
Yes SPs have adavantages over the just T-SQL http://www.sql-server-performance.com/asp_sql_server.asp
http://www.sql-server-performance.com/transact_sql_select.asp
http://www.sql-server-performance.com/stored_procedures.asp
Hi Richard,
have a look at this articles and then decide you should implement SPs or not.
http://www.sql-server-performance.com/gv_sp_views_selects.asp
Thank you both very much.
The benefits of SP’s are now obvious.
Richard.
You could also implement using sp_ExecuteSQL (see BOL) with parameters. This allows SQL to cache the execution plan and re-use it when a similar query is issued.
]]>