Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6

    Unanswered: Does (nolock) work in a view?

    Let's say I created a view and I wanted to join another view and some tables to form a view. If I put (nolock) at each join, even when I am joining to another view will that help prevent locking? I accidently ran a 40 million row query which locked out production system up and our .net developer had to restart web services etc. I want to prevent doing that again any recommendations so I don't look like an idiot. I usually put nolock in my queries but since I am importing this view into MS Access I don't have control over the locking (I don't think) unless I can mitigate locking at the view.

    Thoughts?

    I'm very risk averse (scared cat) but sometimes I have to test queries and they may run for 15 minutes or so.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Well that might help a little. However part of the problem might be the size of the record set. On web pages and reports if I am doing a real good job at keeping a clean house on a busy system I enforce a thresh hold on the amount of data I will return through a reporting tool or a web page. After a certain amount of data you need to make a ETL request. You might consider requesting your data in set based batches.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Access doesn't respect locking hints.
    Seriously.
    George
    Home | Blog

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm just restating what gvee and Thrasymachus have already said, but this is truly a symptom of a design problem.

    Access doesn't respect locking hints, and depending on how you put the request to MS-Access it might actually strip out any hints that you add. Without a lot of insight into what you're doing and how you're doing it, this is dangerous territory!

    With only a very few exceptions, MS-Access can't efficiently process more than about a thousand rows of data. I generally limit MS-Access to about 50 rows at a time and will make exceptions for requests when there's a good reason. No version of MS-Access can properly handle a 40,000,000 row result set and it takes an awfully beefy client to even fetch such a beast!

    You really need to re-think then re-architect your underlying process instead of wasting more effort trying to figure out ways to make it work!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6


    That's why I come on here to get such great insight.

    I set some parameters on the query in Access to peel back date ranges. This helps shed 40 million rows down to 2 - 3 million, but wow it white screens for several minutes before it returns anything. On top of it I added some VBA to automate the output of the query to an Excel file and that takes forever!

    The solutions was supposed to take all these joins from one simple view into an access database then set a query out of the output with a parameter set on date range. This way they could create outputs to Excel and build invoices or whatever.

    So since this is a flawed method what would you recommend.

    I have a view created in the database (SSMS)
    From here I want to create excel outputs by vendor and date range. date range = max(date range) and vendor. However I need to set script to loop to each unique vendor then generate an output file.

    Does this sound like a job for webmatrix 3? I'm not a developer but webmatrix looks like it could be leveraged over access or excel for the intermediary.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    It depends on your skill level, to be honest.
    SSIS is a difficult tool to pick up, but it would do this with relative ease.
    However, some might consider SSIS overkill in this scenario.

    Does the process have to be automated?

    We use Excel as a reporting tool quite a lot here: we basically create a data connection, where you can specify a query/object which is refreshable with user intervention (open file, hit refresh).
    George
    Home | Blog

  7. #7
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Normally I use ODBC MS Query to do what you stated buy the data set is over a million even for a date range. This is why I am going to something more robust like Access, but it's really complete junk IMO.

    I'll look into SSIS and see if I can create something from the GUI to perform this function. In SSIS don't you need to know C# or VB to fully utilize the toolset?

  8. #8
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Follow up I spoke to the business users about the dilemma and we were able to create two reports out of the set and choke down the criteria a little more. I set up two ADODB connections so I could pass parameters through a variable in VBA. I set all my constraints in the view exception for date which is now captured in VBA "WHERE DATE " ' variable ' " " I use a message box to allow the user to type in the date range. Only problem is if they miss the prompt here comes a MASSIVE QUERY.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •