Sybase with dirty reads

Sybase with dirty reads

Post by Tere » Fri, 27 Sep 2002 09:59:22



We have a client that have written a  large number of ad-hoc reports
that they run against the  a production server, which then is migrated
to a "backup server)  During this migration, the client wants to make
sure that the reports are written in a way that minimises the load
they place on the system - in particular their impact on Sybase's/the
server's ability to satisfy other requests at the same time.

We have told the client that one of the simplest changes that we have
found is to switch to explicitly use Dirty Reads (at Isolation level
0) in almost all cases. This is completely safe as all these adhoc
reports (queries) are running during times that no updates are being
made to the database.  Now the cliet  has asked two questions:

If there are no updates running, is a Dirty Read any less impactful
than a Read with the default Isolation level?
Is there a way to specify the default Isolation level that is to be
applied at a more global level than on each individual query, while
still controlling the scope of impact - for example by setting an
environment variable for ISQL in each process?

Can someone please help me with this
Thanks
Teresa

 
 
 

Sybase with dirty reads

Post by Michael Pepple » Sat, 28 Sep 2002 00:04:40



> If there are no updates running, is a Dirty Read any less impactful than
> a Read with the default Isolation level?

I'm not 100% sure, but I *think* that *if* there are no
updates/inserts/deletes to the tables then using isolation 0 won't gain
you anything.

Quote:> Is there a way to specify the
> default Isolation level that is to be applied at a more global level
> than on each individual query, while still controlling the scope of
> impact - for example by setting an environment variable for ISQL in each
> process?

You can use SET TRANSACTION ISOLATION LEVEL 0 in isql (or in any other
client app) and get that default isolation level for the rest of the
session.

Michael
--


ZetaTools: Call perl functions as Sybase stored procedures!

 
 
 

Sybase with dirty reads

Post by Larry Coo » Sat, 28 Sep 2002 00:11:12



> We have a client that have written a  large number of ad-hoc reports
> that they run against the  a production server, which then is migrated
> to a "backup server)  During this migration, the client wants to make
> sure that the reports are written in a way that minimises the load
> they place on the system - in particular their impact on Sybase's/the
> server's ability to satisfy other requests at the same time.

> We have told the client that one of the simplest changes that we have
> found is to switch to explicitly use Dirty Reads (at Isolation level
> 0) in almost all cases. This is completely safe as all these adhoc
> reports (queries) are running during times that no updates are being
> made to the database.  

Be careful with this one -- it burned me once.  If there
isn't a unique index to use, it can actually take longer
for queries to run.  See the T-SQL User's Guide section
on isolation levels and make sure you understand it
completely.  In particular: "By default, a unique index
is required for a level 0 scan on a table that does not
reside in a read-only database."  If it doesn't have one,
it uses a worktable.

Quote:> Now the cliet  has asked two questions:

> If there are no updates running, is a Dirty Read any less impactful
> than a Read with the default Isolation level?

Depends on how you define "no updates" running.  Locks
are released when the transaction ends, not when the
update itself ends.

Quote:> Is there a way to specify the default Isolation level that is to be
> applied at a more global level than on each individual query, while
> still controlling the scope of impact - for example by setting an
> environment variable for ISQL in each process?

set transaction isolation level 0

Larry Coon
University of California


 
 
 

1. Sybase 11 Dirty Reads from a C++ client

I want to set from a C++ client
(with RogueWave) the transaction
isolation level of my connection
to 0 (zero) for Dirty Reads.

RogueWave's objects don't work.
We tried RWDBConnection.isolation(0),
RWDBDatabase.executeSql("set transaction isolation level 0")
and neither works.

We tried building a stored procedure
in which we 'say'
  set transaction isolation level 0
and call this using RWDBStoredProc::execute()
but this does not work. In fact, even in
isql, executing the stored procedure appears
not to work!

Why not? Is a stored procedure a context/transaction
scope of its own? The isolation level does not seem
to persist, but changes back to the default (1).

Any ideas for a workaround? I really need the
performance of those Dirty Reads! Help!!!


--
Chuck Herrick - Senior Consultant
Performance Software Services Inc

I speak only for myself. In no way does anything I say represent
any company or any other person.

2. Should I use Oracle 8 or SQL Server 7 on the Library System?

3. Dirty reads: possible in Sybase?

4. Connecting Access Proj to SQL Server

5. Dirty reads on Sybase

6. Fatal Error - Please Help

7. Row locked on reads even under dirty read

8. How do I determine the size of each table?

9. SQL Server: dirty-reads possible?

10. Dirty reads

11. MSSQL 6.5 + triggers + "dirty reads"

12. dirty read from linkserver table

13. Allow dirty read