WITH (NOLOCK) Question

Question

WITH (NOLOCK) Question

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Katerine459 SSC Eights! Points: 945 More actions · Answer 1

Matt Miller (#4) (2/27/2015)
I am not going to wade in to the main part of the pool (plenty of good advice already out there), but I will point out this tidbit: Access isn't something designed to take on hundreds of concurrent users. The optimistic locking it uses works okay for a handful of users, but turns into a performance pit after you exceed MS' guidelines (25 users). Frankly I don't think it even takes 25 before you start to see a major impact but that really depends on how busy your users really are.
You REALLY want to look at using something that leverages looser binding of forms (ASP.NET or pretty much any of the form options implemented via .NET). Keeping things locked while you're editing will make your life miserable with the kind of concurrency you mentioned.

Oh, it's not hundreds of concurrent users. It's a maximum of 3 users with edit permissions (that's how many total users there are, with edit permissions), plus up to 30 people who have read-only permissions, each running their own copy of the front-end. 🙂 Access is designed for that.

Using ADO instead of bound forms is better in general, I agree, but it's also much more expensive to implement. Instead of just having a form wizard create a basic form for you, that automatically handles adding/updating/saving records for you, you have to code all the save functions. Manually. And the edit functions, the New records functions, the delete functions, the checks to make sure that people remember to save... all that takes time, and we operate on a per-hour basis, and they only have a limited budget. So that's where we are. 🙂

Nevyn SSChampion Points: 14210 More actions · Answer 2

Although you have gotten me wondering if I shouldn't add a non-clustered index on StartDate (in the Clients table)...

To solve this problem? Probably not.

If its really a 200 row table indexing is not likely to be the issue, and I believe you need all the rows from Clients anyway, so Luis' query would not use it.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Katerine459 (2/27/2015)
... what is that?

This generates a Tally table on the fly with zero reads.

Could you please take me through what that does? Your comment, "Enough for 12 years" implies that it returns 144 numbers (one for each month for 12 years)?

That's right. Well, actually it generates 145 because of the SELECT 0 UNION ALL.

And that there's a cross join or an outer join in there somewhere? Is "E, E x" shorthand for "E CROSS JOIN E"?

That's right, it's known as the old-join syntax which creates a cross join because there's no condition to join.

And I'm familiar (slightly) with using SELECT Row_NUMBER() OVER(ORDER BY), but only in the context of getting the first/last/maximum/minumum row for something... what does SELECT NULL do in this context?

I'm using (SELECT NULL) because ORDER BY in the OVER() clause doesn't admin constants. I could use anything but the important reason is that I don't care about the order, I just want to get consecutive numbers from 1 to 144 ()in this case. You can check what it does if you run that part of the code. Here's how I test cascading CTEs:

WITH E(N) AS(

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 --12 rows

)

--,cteTally(N) AS(

SELECT 0

UNION ALL

SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))

FROM E, E x --Enough for 12 years

--)

--SELECT ClientID,

-- MONTH( DATEADD(MONTH, N, ISNULL(StartDate,'20140101'))) MonthNum,

-- YEAR( DATEADD(MONTH, N, ISNULL(StartDate,'20140101'))) YearNum

--FROM cteTally t

--JOIN dbo.Client_Clients c ON DATEADD( MONTH, t.N * -1, GETDATE()) >= StartDate

-- OR (DATEADD( MONTH, t.N * -1, GETDATE()) >= '20140101'

-- AND StartDate IS NULL)

--WHERE ExitDate IS NULL

--AND N % SheetsRequiredNumMonths = 0

----AND ClientID = @ClientID

Sorry about the stupid questions, but I really like to understand the ins and outs, and my head is so. Very. Foggy!
Thanks again, (to everybody, not just Luis Cazares). 🙂

I don't believe that those are stupid questions since you had most of the answer and it's good to be sure about the code you're implementing.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Michael L John One Orange Chip Points: 27053 More actions · Answer 4

They are PK indexes, so they are clustered.

A primary key does not have to be a clustered index.

A clustered index does not have to be the primary key.

When you create a primary key using the GUI in SQL Server, it automatically makes it clustered (I think?).

This may or may not be the best use of the clustered index, your mileage may vary.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Katerine459 SSC Eights! Points: 945 More actions · Answer 5

Michael L John (2/27/2015)
They are PK indexes, so they are clustered.
A primary key does not have to be a clustered index.
A clustered index does not have to be the primary key.
When you create a primary key using the GUI in SQL Server, it automatically makes it clustered (I think?).
This may or may not be the best use of the clustered index, your mileage may vary.

Ah. Ok, good to know. 🙂

I did create the PK using the table designer in SSMS, so it did create clustered indexes (according to the Indexes list in SSMS). I can't imagine a situation where you'd want a PK that's not a clustered index, though (at least, I can't imagine one right now :doze: ) 😉

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 6

You should consider setting the database to Read Committed Snapshot.

That would probably eliminate the need to use NOLOCK, because readers would not be blocked, and you would be returning consistent, committed data.

I'm sure that you could redo the function to eliminate the loop, but I doubt that it is the root cause of the problem. It sounds like the underlying problem is with Access holding update locks while waiting for user action.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Michael Valentine Jones (2/28/2015)
You should consider setting the database to Read Committed Snapshot.

This. Absolutely this.

Bear in mind that nolock doesn't just mean dirty reads. Keeping nolock may seem fine until that one day where data is changing and the nolock hint allows a report to read a few rows multiple times. Nothing tends to annoy users more than results that are incorrect, except business decisions made off that incorrect data (eg if it's financial data duplicate/missed row may result in an account total showing too high or too low, resulting in the business approving/denying transactions incorrectly)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

RonKyle SSC-Dedicated Points: 31554 More actions · Answer 8

I did create the PK using the table designer in SSMS, so it did create clustered indexes (according to the Indexes list in SSMS). I can't imagine a situation where you'd want a PK that's not a clustered index, though (at least, I can't imagine one right now )

There are lots of good reasons. There was a good article on just this point recently. As a general you, you want to cluster where you get the most benefit.