Monday 31 December 2012

Using MS-SQL's NOLOCK for faster queries

NOLOCK (aka READUNCOMMITED) is a t-sql hint (directive) that allows MS SQL Server to ignore the normal locks that are placed and held for a transaction and allows the query to complete without having to wait for the first transaction to finish and therefore release the locks.

Using NOLOCK gives significant improvement on large tables, where insert / update commands cantake 3-15 seconds.

However you need to be very carefully with using NOLOCK. Remember you can get some records that were partially updated or inserted. It is safe to use NOLOCK on rows that you know are not changing right now.For example, records that belong to some user and he is running reports, but not updates, however some users can do updates / inserts at the same time.

Example:

SELECT * FROM ORDERS (NOLOCK) WHERE orderdate < GETDATE() - 1

Issues

You can get dirty reads using the NOLOCK hint. These are also other terms you may encounter for this hint.

  • Dirty Reads - this occurs when updates are done, so the data you select could be different.
  • Nonrepeatable Reads - this occurs when you need to read the data more than once and the data changes during that process
  • Phantom Reads - occurs where data is inserted or deleted and the transaction is rolled back. So for the insert you will get more records and for the delete you will get less records.

Understanding the SQL Server NOLOCK hint

MSDN >> Concurrency Effects

Mobile Site vs. Full Site

Good mobile user experience requires a different design than what's needed to satisfy desktop users. Two designs, two sites, and cross-linking to make it all work.

  • Build a separate mobile-optimized site (or mobile site ) if you can afford it. When people access sites using mobile devices, their measured usability is much higher for mobile sites than for full sites.
  • If mobile users arrive at your full site's URL, auto-redirect them to your mobile site. Sadly, many search engines still don't rank mobile sites high enough for mobile users, so people are often (mis)guided to full sites instead of the mobile ones, which offer a vastly superior user experience.
  • Offer a clear link from your full site to your mobile site for users who end up at the full site despite the redirect.
  • Offer a clear link from your mobile site to your full site for those (few) users who need special features that are found only on the full site.

The findings and guidelines regarding mobile and full sites are the same on all the currently popular platforms (including iPhone, Android, Windows Phone, and BlackBerry).

The guidelines are different for large tablets (10-inch form factor, as in Apple iPad, Lenovo IdeaPad, Samsung Galaxy, etc.), where full sites work reasonably well. For small tablets (7-inch form factor, as in Amazon Kindle Fire) the ideal would be to create yet a third design optimized for mid-sized devices, though most companies can get away with serving their mobile site to Kindle Fire users.

Mobile-optimized sites

The basic ideas are to:

  • cut features, to eliminate things that are not core to the mobile use case;
  • cut content, to reduce word count and defer secondary information to secondary pages; and
  • enlarge interface elements, to accommodate the "fat finger" problem.

The challenge is to eliminate features and word count without limiting the selection of products. A mobile site should have less information about each product and fewer things users can do with the products, but the range of items should remain the same as on the full site. If users can't find a product on a mobile site, they assume the company doesn't sell it and go elsewhere.

Why full-sites don't work for mobile use

It's common today to hear people argue the following: Mobile users have increasingly high expectations for what they should be able to accomplish on their phones, so eliminating content or features will inevitably disappoint some people. It's therefore better, the (flawed) argument goes, to serve the full site to everybody, including mobile users.

This analysis is flawed because it assumes that the only choice is between the full-featured desktop site and a less-featured mobile site. However, any mobile site that complies with the usability guidelines will provide links to the full site wherever features or content are missing, so users have access to everything when and if they need it.

The design challenge is to place the cut between mobile and full-site features in such a way that the mobile site satisfies almost all the mobile users' needs. If this goal is achieved, the extra interaction cost of following the link to the full site will be incurred fairly rarely.

The correct analysis goes as follows:

  • For the vast majority of tasks , mobile users will get a vastly better user experience from a well-designed mobile site than from the full site.
  • For a small minority of tasks , mobile users will be slightly delayed by the extra click to the full site.

A big gain that's experienced often will comfortably outweigh a small penalty that's suffered rarely.

A second argument against the mobile site option is that you could just optimize the entire website for mobile in the first place. Then, giving mobile users the "full" site wouldn't cause them any trouble. While true, this analysis neglects the penalty imposed on desktop users when you give them a design that's suboptimal for bigger screens and better input devices (see sidebar on mouse vs. fingers). If desktop users were a minute minority this might be acceptable, but almost all websites get substantially more traffic (and even more business) from desktop users than from mobile users. So, while we do want to serve mobile users, we can't neglect desktop users— who, after all, pay most of our salaries.

The basic point? The desktop user interface platform differs from the mobile user interface platform in many ways, including interaction techniques, how people read, context of use, and the plain number of things that can be grasped at a glance. This inequality is symmetric : mobile users need a different design than desktop users. But, just as much, desktop users need a different design than mobile users.

source: http://www.nngroup.com/articles/mobile-site-vs-full-site/