在数据库中搜索UPC代码的最佳方式是什么?

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.

我有一个12位数的UPC数据库——格式化条形码(900000条记录)。目前，由于前导零，它们被存储为varchar(13)。我正在使用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.

我还有一个WCF 4.0 API方法，它可以根据UPC-A条形码匹配来查询数据库。

What's the best way to improve performance of UPC based queries
改进基于UPC查询性能的最佳方法是什么
What is the best way to store 12-Digit UPC-A Barcodes. Is my assumption to use varchar(12) okay?
存储12位upcode的最佳方式是什么?我的假设是使用varchar(12)好吗?

Edit: More Information

编辑:更多信息

Products

产品

ProductID (int)
ProductID(int)
Barcode (varchar(12))
条形码(varchar(12))
Name (varchar(50))
名称(varchar(50))
ImageUrl (varchar(255))
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 );
}

3 个解决方案

#1

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.

如果将代码存储为数字，则可以节省空间。空间就是时间，因为可以更快地读取更少的字节。此外，查找数字的速度应该更快。当需要时，可以重构前导0，因为UPC-A是一个固定长度的代码。

#2

I think storing as varchar(12) is probably fine. The #1 thing you can do to ensure the performance of your barcode queries is to make sure you have an index on the barcode column. Depending on your use of the data, you might consider making it a clustered index.

我认为储存为varchar(12)可能是可以的。要确保条码查询的性能，首先要做的是确保条码列上有索引。根据您对数据的使用，您可以考虑将其作为集群索引。

#3

Make sure that your sql search criteria doesn't include a function, otherwise your query is not sargable.

确保您的sql搜索条件不包含函数，否则您的查询是不可sargable的。

I would guess that your reads far outnumber your writes, if the data is sensical without the leading zeros I would incur the cost of truncating them at write-time and searching on the exact value. Furthermore, UPC-A is numeric-only data. I would expect a more performant search on the numeric data than the varchar as you stated space is not a concern so you can even store both values if you desire.

我猜你的阅读量远远超过你的写作量，如果数据没有前导0，那我就会在写时截断它们，并搜索准确的值。此外，UPC-A是数字数据。我希望对数字数据进行比varchar更高效的搜索，因为您说空间不是问题，所以如果您愿意，您甚至可以存储这两个值。

You also need an index on the column.

您还需要列上一个索引。

#1