What's the best way to search for a UPC code in a Database?

I have a UPC Database of 12-digit UPC-A formatted barcodes (1,900,000 records). Currently they're stored as a varchar(13) due to leading zeros. I am using SQL Server 2008 R2.

I also have an WCF 4.0 API Method that goes and queries the database based on a UPC-A barcodes match.

  • What's the best way to improve performance of UPC based queries
  • What is the best way to store 12-Digit UPC-A Barcodes. Is my assumption to use varchar(12) okay?

Edit: More Information

Products

  • ProductID (int)
  • Barcode (varchar(12))
  • Name (varchar(50))
  • ImageUrl (varchar(255))

My code:

public JsonResult GetProductByCode(string code)
{
  DBEntities db = new DBEntities;

  Product product = (from prod in db.Products
                    where prod.Barcode == code
                    select prod).FirstOrDefault();

  return Json( product , JsonRequestBehavior.AllowGet );
}

Answers


I take an index on the barcode column as a given.

You could save space if you stored the codes as numbers. Space is time as less bytes can be read faster. Also, the lookup should be faster on numbers. The leading zeros can be reconstructed when needed as UPC-A is a fixed-length code.


Need Your Help

Plot number of observations of bars in a histogram using ggplot

r plot ggplot2 histogram

I want to add above the bar of an histogram the number of observations for each variables. Here is my dataframe.

Register to receive windows messages when an Win32 event is signaled in MFC

c++ windows winapi mfc

I have a simple MFC dialog app. I would like to do some overlapped I/O. Waiting for the overlapped event handle in a separate thread is a pain. I'm wondering if there is a function call to register a

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.