Doesn't matter read-only for SQL Server?

Question

I was tasked with optimizing a rather unpleasant stored procedure in the old system. Its searchable database and a new copy is generated every day, while many complex associations are de-normalized. No entries are made, only SELECT, so I decided that some light improvements could be made by making the entire database read-only and changing the recovery model to Simple.

To my great surprise, this did not help - at all! The stored procedure still takes the same time. If a fact, I was so surprised that I decided that I did it wrong!

My questions:

• Do I need to do anything other than setting Read-Only Database to true?
• Is it wrong to expect significant performance improvements by making the database read-only?
• The same goes for the recovery model: Shouldnt "Simple" have a noticeable effect?
• Are there other similar database configurations that can improve performance in this scenario?

The stored procedure is huge, with temporary tables, 40+ tables combined into 20+ queries. But Id like to optimize the database before I edit this process.

Answer 1

Since no writes are made by your SP, there is no reason to expect a noticeable performance improvement from changing the recovery model and read-write mode.

, .

: , . , fillfactor 100 - .
: ALTER INDEX ALL ON table_name REBUILD WITH (FILLFACTOR = 100).
, , .

, SQL Server 2005 : , . , sys.dm_exec_query_stats .
, Tuning Advisor, , .

Answer 2

, SQL Server? , . , (, , , 30 !), . , , , , . , , , , , .

Answer 3

I had a similar setup, large stored procedures with lots of large temp tables.
Our problem was that connections to and between temporary tables were very slow. I recommend that you look at the execution plan and try adding the appropriate indexes to the temp tables if you haven't already.