"Bruce Hendry" <bruce@binya.com> wrote in message
news:yNf2e.3954$oy3.2375@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