So should I alter the views to use NOLOCK? Or the update statements?
Or both? The update statements are fairly simple.
UPDATE Contributions
SET MntRevenue = IsNull(SumOfPSTDAMNT,0)
from contributions
INNER JOIN vw_monthly_cnt_rev_rollup ON
(Contributions.Year1 = vw_monthly_cnt_rev_rollup.Year1)
AND (Contributions.PeriodID = vw_monthly_cnt_rev_rollup.PeriodID)
AND (Contributions.Custnmbr = vw_monthly_cnt_rev_rollup.CUSTNMBR)
where locked ='0'
The views have a large number of rows, but no more than 4 joined tables,
but they do reference a different database.
Many thanks
Jeff Metcalf
ComputerPlus Sales & Service
(864) 801 9003 ext 2016
Someday, in the event that mankind figures out what it is that this
world revolves around, Thousands of people are going to be shocked and
perplexed to find out that it was not them.
-----Original Message-----
From: Smith Dano
[mailto:mssqldba-ezmlmshield-x511936.[Email address protected]
Sent: Friday, September 29, 2006 3:09 PM
To: LazyDBA Discussion
Subject: RE: today is my Friday but have great unknown unsearchable
question. . . .
RDB's need to meet the 4 ACID rules
A - Atomic - each statement / transaction succeeds or fails as a whole C
- Consistency - the data must be consistent within transactions I -
Integrity - Database must [be able] to ensure integrity of the data
(constraints)
D - Durable - the data that is "committed" must be ... durable...
NOLOCK - is safe to use as long as you expect and desire data that
"could" change a moment after the query. Typical data-warehouse data is
a perfect candidate. It is unlikely that a "historical" record could be
changing during the ETL process...
If you have a 45 minute process that joins many tables - expect a
significant performance increase....
-----Original Message-----
From: Jeff Metcalf
[mailto:mssqldba-ezmlmshield-x44717050.[Email address protected]
Sent: Friday, September 29, 2006 2:56 PM
To: LazyDBA Discussion
Subject: RE: today is my Friday but have great unknown unsearchable
question. . . . [bcc][faked-from]
Importance: Low
Ummm...pardon if asking a dumb question...but what are the ACID rules?
I have some rather large views that I use to load warehousing tables and
other apps. Should I use the NOLOCK on them? Most of the stored procs
and DTS packages run after hours. Some of them take up to 45 minutes to
run.
Thank you,
Jeff Metcalf
ComputerPlus Sales & Service
(864) 801 9003 ext 2016
Someday, in the event that mankind figures out what it is that this
world revolves around, Thousands of people are going to be shocked and
perplexed to find out that it was not them.
-----Original Message-----
From: John A DAVIS
[mailto:mssqldba-ezmlmshield-x6154725.[Email address protected]
Sent: Friday, September 29, 2006 10:50 AM
To: LazyDBA Discussion
Subject: RE: today is my Friday but have great unknown unsearchable
question. . . .
thanks! what a great weekend!
>>> "Smith Dano "
<mssqldba-ezmlmshield-x76902323.[Email address protected] 9/29/2006 5:55
AM >>>
The NOLOCK or READ UNCOMMITTED stays in scope on a spid until an
operation that requires higher locking (like an update statement) is
called. So yes NOLOCK / READ UNCOMMITTED does work on views, etc but
will be ignored when higher degree of locking is required to meet the
ACID rules.
Dano Smith
-----Original Message-----
From: Carol Green
[mailto:mssqldba-ezmlmshield-x13484670.[Email address protected]
Sent: Friday, September 29, 2006 5:43 AM
To: LazyDBA Discussion
Subject: RE: today is my Friday but have great unknown unsearchable
question. . . . [bcc][faked-from]
Importance: Low
I'm not sure about whether setting the transaction isolation level will
apply to the view as well, but if not, you could use the NOLOCK
optimiser hint in the view to prevent locks.
-----Original Message-----
From: John A DAVIS
[mailto:mssqldba-ezmlmshield-x3227073.[Email address protected]
Sent: 29 September 2006 00:08
To: LazyDBA Discussion
Subject: today is my Friday but have great unknown unsearchable
question. . . .
We are running select queries on tons of Views for our Drinking Water
website with PHP pages. Now, I can put this: SET TRANSACTION ISOLATION
LEVEL READ UNCOMMITTED at the front of the SELECT * FROM vwYourViewHere
in PHP and everything runs and the idea is to not lock any tables.
. . . . . .but. . . . . does this work with Views? Don't the views lock
the table because they are farther down the food chain?
thank you
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
MS Sql Server LazyDBA home page