I see this topic debated from time to time.   People who spend a fair amount of time writing queries against the Prophet 21 database are sometimes confused between using the views and writing queries directly against the tables.   Most of the time the standard answer I see is “Always use the Prophet 21 views”.   It’s my humble opinion that the answer to a question like this rarely begins with the words ‘always’ or ‘never’.    Like many things related to Prophet 21, my answer to this question is “it depends”.  I tend to believe that there is a time and place for both methods and this post will be a deep dive on my thoughts on how to approach the issue in an objective manner.

What is NOLOCK?

NOLOCK is a table hint in the SQL Server database system.  Table hints change how the query analyzer behaves during the execution of the query.  A table hint only lasts for the execution of the SQL statement.  Additionally, table hints only apply to the table that they are hinting against.   If you want to use a table hint on multiple tables, it must be specified for each table.

Both NOLOCK and READUNCOMMITTED both serve the exact same purpose.  The purpose of the hint is to allow a “dirty read”.  A dirty read, means that the query runs without placing a shared lock on the accessed data.  Also, locks on the data by other transactions do not prevent the query from running.

Pros of NOLOCK

The biggest advantage of NOLOCK is speed and concurrency.  Ignoring data locks during query execution eliminates the wait time associated with other transactions that touch the same data.  The other major plus is that you need not worry about your query deadlocking the database other users having what will feel like a Prophet 21 lock up.  Have you ever executed a big, expensive query on the invoice header table and seconds later, someone in Accounting calls the help desk because his session is locked?  Guess what, you have just experienced a deadlock.

Cons of NOLOCK

NOLOCK does have a big drawback.  Because locks are ignored, there is a chance that the data you read may be inconsistent.  For example, let’s say you are reading inventory data with the NOLOCK table hint at the same time that a large adjustment is being imported into Prophet 21.  Since you are not waiting for the import to complete, your query will not account for the changes to the data that are caused by the import.

Prophet 21 Views Explained

Prophet 21 ships with many SQL views that aid in query development.  Though not always true, these queries often begin with a prefix that reads p21_view.   In some cases, the P21 query simply selects the table with the NOLOCK table hint.  In other cases, the view contains one or more joined tables to provide commonly accessed fields to the query.

EXAMPLE 1: “p21_view_inv_loc”

In this example, we will review the p21_view_inv_loc predefined view to analyze how it is built and what it does.

SELECT
     dbo.inv_loc.location_id,
     ...[remaining inv_loc fields]....
     ,dbo.inv_loc.slab_track_bins_flag
     , dbo.inv_loc.edi_832_discontinued_sent_flag
     , dbo.inv_mast.item_id
FROM
     dbo.inv_loc WITH (NOLOCK)
          INNER JOIN dbo.inv_mast WITH (NOLOCK)
               ON dbo.inv_mast.inv_mast_uid = dbo.inv_loc.inv_mast_uid

The structure of the view joins in the inv_mast table for the purpose of putting the item_id field into the query.  This is a very simple, basic predefined view, suitable for use in most queries.  It is not really expensive, and chances are, since it is so simple, using it often will not cause any problems.

Example 2: “p21_view_invoice_line”

This example looks at another seemingly simple view…

SELECT 
   ...[columns]...
FROM         
     dbo.invoice_line WITH (NOLOCK) 
          INNER JOIN dbo.invoice_hdr WITH (NOLOCK) 
              ON dbo.invoice_hdr.invoice_no = dbo.invoice_line.invoice_no 
          LEFT OUTER JOIN dbo.currency_line WITH (NOLOCK) 
              ON dbo.currency_line.currency_line_uid = dbo.invoice_hdr.currency_line_uid

This view is a little more complex.  There are joins back to the invoice_hdr table and a left join back to the currency_line table.  There are also 7 CASE statements in the view.  Clearly there is more going on meets the eye.

Choosing a Method

Ad Hoc Queries for Prophet 21

I typically classify queries in 2 major camps.   The first camp are ad hoc queries I am only using for a short period of time.  This usually happens when a project requires a rather quick custom dump of data or something similar.  In other words, this query see production use on an ongoing basis.  In an ad hoc query, I almost always use the Prophet 21 predefined views.  The development time is a little faster, and I do not have any real concern worrying over the speed of the query.  Finally, an ad hoc query, by its very nature, gets thrown away, so why obsess over it?

Queries for Production Use

By contrast, the second class of query, obviously, is a query that will be put into daily use to drive some extended functionality or reporting.  This includes queries used to drive add-on applications, DynaChange business rules, or SQL Reporting Services reports.  For these queries, I also give weight to how often I expect the query to run in production.

As a query becomes more complex and/or more often used, I spend more time tuning it.  Tuning the query serves the dual purpose of reducing stress on the database and improving the user’s perception of speed and performance.  For instance, if I am working with the inv_loc table and I need more than just item_id from inv_mast, then using the predefined view doesn’t make sense to me.  It is the same thing as joining the inv_mast table into the query twice.  This adds extra cost that the query doesn’t benefit from.

There have been multiple instances where I used a Prophet 21 view and found that the majority of cost in the execution plan came from the view itself.  This happened due to either additional table joins or a strange, costly index access that slowed everything down.   Removing the view and using the table with a NOLOCK hint dramatically improved query performance.  This happened on several queries that worked with invoice related tables.

Rolling without NOLOCK in Prophet 21

Most queries I write are at a very low risk of being affected by a read without locking.  Typically, the query looks at historical data, not normally subject to changes.  Additionally, the decisions made from the subject data tolerate a little bit of variance without issue.

There may be some instances though where uncommitted data must be accounted for or the query reads data that changes in real-time.  As a result, those cases may require queries against the tables without a NOLOCK hint.  These happen to me so infrequently that I cannot recall the last time I needed to do it.  Ususally, managing the expectations of the user suffices to allow for putting in the NOLOCK hint.  Should you choose to roll without this hint, beware that you query is subject to deadlocking and may affect other users.  If you have a slow, expensive query that does not use a view or NOLOCK, it is best to run it during low usage hours.  This reduces the chance of a deadlock affecting other users.

Wrap Up

Summing it all up, as I stated early on, the answer is “it depends”.  While there is no hard rule for you to use a certain method,  selecting carefully can greatly improve system performance.  I also recommend looking at execution plans for complex queries, especially when using Prophet 21 views.   At the very least, you have the chance to look for problems that may reveal themselves in the form of a slow running app or report.

 

Read More about Prophet 21 in our Knowledge Base.