SEO

#1 (**permalink**) 02-29-2008, 06:51 AM

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.

#2 (**permalink**) 02-29-2008, 06:51 AM

"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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

read only database performance question