read only database performance question

[Bruce Hendry]

In a nutshell, I have a stored proc that takes appx 15 minutes when it uses
a read-only database, and about 2 minutes when the database is not
read-only.

Details:

SQL Server 7

The stored proc exists on a writable database on the same server. It stuffs
a subset of about 20000 rows into a temp table and repeatedly updates the
temp table while doing selects and joins on the read only database, for
example,

Select *
into #LocalTempTable
from SAMESERVER.dbo.ReadOnlyDatabaseTable

Update tmp set tmp.SomeColumn = 1
from #LocalTempTable tmp
inner join SAMESERVER.dbo.ReadOnlyDatabaseTable x on
tmp.ID = x.ID
where SomeCriteria = SomeValue, etc, etc.

If I change the read-only database to writable it works fine. There are no
explicit transactions used in the stored proc. On the same server, a
production database exists and is constantly using transactions and temp
tables, but I wouldn't think this is an overall server performance issue
because it handles the same load when the suspect database is not read-only.

Any thoughts will be greatly appreciated. I'm searching all over and can't
seem to find reference to this type of issue.

[Simon Hayes]

"Bruce Hendry" <br***@binya.com> wrote in message
news:yN*****************@newssvr30.news.prodigy.co m...

In a nutshell, I have a stored proc that takes appx 15 minutes when it
uses a read-only database, and about 2 minutes when the database is not
read-only.

Details:

SQL Server 7

The stored proc exists on a writable database on the same server. It
stuffs a subset of about 20000 rows into a temp table and repeatedly
updates the temp table while doing selects and joins on the read only
database, for example,

Select *
into #LocalTempTable
from SAMESERVER.dbo.ReadOnlyDatabaseTable

Update tmp set tmp.SomeColumn = 1
from #LocalTempTable tmp
inner join SAMESERVER.dbo.ReadOnlyDatabaseTable x on
tmp.ID = x.ID
where SomeCriteria = SomeValue, etc, etc.

If I change the read-only database to writable it works fine. There are
no explicit transactions used in the stored proc. On the same server, a
production database exists and is constantly using transactions and temp
tables, but I wouldn't think this is an overall server performance issue
because it handles the same load when the suspect database is not
read-only.

Any thoughts will be greatly appreciated. I'm searching all over and
can't seem to find reference to this type of issue.

I don't know, but the usual advice would be to use Profiler and/or Query
Analyzer to trace the procedure execution and view the execution plan, to
see exactly what's taking so long.

To take a complete guess, in the writable database, MSSQL is adding
statistics dynamically to the table to improve performance. Although the
execution times are very different, and I'm not really convinced that
missing statistics could make such a difference. You might also put an index
on #LocalTempTable(ID) if you haven't already.

Simon