469,904 Members | 2,413 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,904 developers. It's quick & easy.

read only database performance question

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.
Jul 23 '05 #1
1 3011

"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
Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

10 posts views Thread by Yang Li Ke | last post: by
19 posts views Thread by dchow | last post: by
39 posts views Thread by JKop | last post: by
9 posts views Thread by =?Utf-8?B?TUNN?= | last post: by
1 post views Thread by Waqarahmed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.