SQL Server表结构用于存储大量图像 [英] SQL Server table structure for storing a large number of images

问题描述

在SQL Server 2008中存储大量图像数据的最佳实践是什么?我预计将使用约5个存储空间来存储大约50,000张图像.目前，我正在使用带有列的单个表进行此操作:

What's the best practice for storing a large amount of image data in SQL Server 2008? I'm expecting to store around 50,000 images using approx 5 gigs of storage space. Currently I'm doing this using a single table with the columns:

ID: int/PK/identity
Picture: Image
Thumbnail: Image
UploadDate: DateTime

我很担心，因为大约占我预期总容量的10％，似乎插入物会花费很长时间.典型的图像约为20k-30k.是否存在更好的逻辑结构来存储此数据?还是我需要研究集群或其他IT解决方案以适应数据负载?

I'm concerned because at around 10% of my expected total capacity it seems like inserts are taking a long time. A typical image is around 20k - 30k. Is there a better logical structure to store this data? Or do I need to look into clustering or some other IT solution to accommodate the data load?

推荐答案

Image是SQL Server 2008中不推荐使用的数据类型.从SQL Server 2005开始，它已替换为VARBINARY(MAX).如果决定存储映像在数据库中，则应使用VARBINARY(MAX)字段并考虑添加FILESTREAM选项.

Image is a deprecated data type in SQL Server 2008. It has been replaced with VARBINARY(MAX) since SQL Server 2005. If you decide to store the image in the DB, then you should use VARBINARY(MAX) fields and consider adding the FILESTREAM option.

对于流式传输数据(如图像)，根据FILESTREAM比单独的VARBINARY(MAX)快得多. ="nofollow noreferrer">本白皮书:

For streaming data, like images, FILESTREAM is much faster than VARBINARY(MAX) alone, according to this white paper:

_{(来源: microsoft.com )}

_{(source: microsoft.com)}

请注意，要实现此流传输性能，必须在设计中使用适当的API并获取

Note that to achieve this streaming performance you must use the proper API in your design and obtain the Win32 handle of the BLOB. Note that updates into a FILESTREAM column (including INSERTS) will be slower than VARBINARY(MAX).

这篇关于SQL Server表结构用于存储大量图像的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！