Use of Stored Procedures
In old times, it was a common trend to have embedded queries (ie, having SQL code inside your application code). These days, most database supports stored procedures and hence would recommend to use Stored Procedures. If you have any database related functionalities that can be performed at backend, please do it so inside the Stored Proc itself.
An additional benefit to using stored procedures is that the execution plans are typically updated as part of scheduled maintenance on the DBMS. This means that every time data is moved around on the physical disk for optimization by the server maintenance jobs, the stored procedure execution plans are automatically updated as well. The result is that users experience continued improvement to performance rather than executed some plan that was stored years ago when they first ran the dynamic SQL statement in the application.
Set NoCount On at the beginning of stored procedures can reduce overhead dramatically as well if the rowcount is not required as part of processing the transaction.
Query Optimization
Another way to ensure good performance is to view the execution plan of any SQL statements (either dynamic or stored procedures) in SQL Server Management Studio or equivalent, to determine lock levels and possible efficiencies. Methods like dirty reads (uncommitted or with no lock) should be used whenever possible. Other things like making sure you are using the indexes which are set up are critical to performance. For example, if you cast or convert a value to be different from the type that the index is set on, you lose the value of the index entirely.
If you are facing issues with temporary data, check out this link for options - http://support.microsoft.com/default.aspx/kb/305977
post a message... 
