By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,756 Members | 1,721 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,756 IT Pros & Developers. It's quick & easy.

ReadOnly DB? - Improved Performance for Data Mart

P: n/a
We have a DataMart (Approx 10GB) that is updated in batch at night. Is
there a way that the database can be configured to be in a "Read Only"
mode during the day?
MS SQL offers this - improved performance as there is no locking of
tables, logging, etc. Thanks.

Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies

P: n/a
Not aware of any benefits to making it 'read-only' for you here:
- your writes occur during the night, so they won't impact
queries during the day anyway
- selects just result in share locks
- there are other very easy ways to stop users from writing to
a database

Are you trying to eliminate logging of multi-step sql statements?

Anyway, you'll probably find better performance from
- modeling
- sql tuning
- storage layout for tempspace, indexes and storage
- memory layout for bufferpools and sort heaps
- server configuration for parallelism, etc

IBM has "redbooks" that you might want to check out for details on
these subjects.

Nov 12 '05 #2

P: n/a

You normally enforce a Read Only Database through the use of
authorizations. You can create all of the users into a group that has
SELECT privileges only on the Database Tables.
Graham Martin

Nov 12 '05 #3

P: n/a
You can check if Isolation Level change can help (setting to
Uncommitted Red).


A transaction at the READ UNCOMMITTED isolation level is very weak: it
makes no locks and ignores others' locks.


Nov 12 '05 #4

P: n/a
will prevent from writing to that tablespace/table.

will remove quiesced state.

You can also use table level locking.

However if you are looking for performance I agree with previous posts
-- start from tuning your system (for "big" queries pay attention to
system temporary tablespace). Also, you can think about Automated
Summary Tables, MDC.

-- Artur Wronski

Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.