从文件中读取图片，与从数据库表中读取图片评测

一、在 d:\ 下创建 image 文件夹，再放10 张图片， 名称从 1.png 到 10.png .

二、先创建新库 db1, 然后按下面脚本创建初始环境：

USE db1
GO
IF OBJECT_ID('t_path') IS NOT NULL DROP TABLE t_path
GO
CREATE TABLE t_path(
	id INT IDENTITY(1,1) PRIMARY KEY,
	uname NVARCHAR(20) NOT NULL,
	imgPath VARCHAR(50)	 NOT NULL
)
GO
IF OBJECT_ID('t_img') IS NOT NULL DROP TABLE t_img
GO
CREATE TABLE t_img(
	id INT IDENTITY(1,1) PRIMARY KEY,
	uname NVARCHAR(20)  NOT NULL,
	img VARBINARY(MAX)   NULL
)
GO
INSERT INTO t_path(uname,imgPath)
SELECT 'a'+LTRIM(sv.number),'d:\image\'+LTRIM(sv.number)+'.png' 
FROM [master].dbo.spt_values AS sv WHERE sv.[type]='P' AND sv.number BETWEEN 1 AND 10

INSERT INTO t_img(uname)
SELECT 'a'+LTRIM(sv.number) 
FROM [master].dbo.spt_values AS sv WHERE sv.[type]='P' AND sv.number BETWEEN 1 AND 10

三、测试两者的读取速度：

注：第一次要更新图片，后面再注释掉： UpdateImg()；

using System;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using System.Threading.Tasks;

namespace ConsoleApp4
{
    class Program
    {
        //如果使用 tempdb ，则两者差别不大
        //如果不用异步，则放前面的慢，后面的快
        static readonly string ConnString = "data source=(local)\\sqlserver2014;initial catalog=db1;Integrated Security=True";
        static void Main(string[] args)
        {
            //UpdateImg();
            Task.Run(() =>
            {
                ReadImgByteByFile();
            });

            Task.Run(() =>
            {
                ReadImgByteByDB();
            });

            Console.Read();
        }

        static void UpdateImg()
        {
            byte[] byteArr = File.ReadAllBytes("d:\\image\\1.png");
            using(SqlConnection conn = new SqlConnection(ConnString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("update t_img set img=@img", conn);
                cmd.Parameters.AddWithValue("@img", byteArr);
                cmd.ExecuteNonQuery();
            }
        }

        static void ReadImgByteByFile()
        {
            Stopwatch sw = new Stopwatch();
            sw.Start();

            for (int i = 1; i <= 10; i++)
            {
                using (SqlConnection conn = new SqlConnection(ConnString))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand("select imgPath from t_path where id=@id", conn);
                    cmd.Parameters.AddWithValue("@id", i);
                    string path = cmd.ExecuteScalar().ToString();
                    byte[] arr = File.ReadAllBytes(path);
                }
            }
            Console.WriteLine("ReadImgByteByFile: {0} ms", sw.ElapsedMilliseconds);
        }

        static void ReadImgByteByDB()
        {
            Stopwatch sw = new Stopwatch();
            sw.Start();

            for (int i = 1; i <= 10; i++)
            {
                using (SqlConnection conn = new SqlConnection(ConnString))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand("select img from t_img where id=@id", conn);
                    cmd.Parameters.AddWithValue("@id", i);
                    byte[] arr =(byte[]) cmd.ExecuteScalar();
                }
            }
            Console.WriteLine("ReadImgByteByDB: {0} ms", sw.ElapsedMilliseconds);
        }
    }
}

 

两者的大小比较：

File+Table:  5.39 + 0.008=5.398 MB

Table( varbinary ): 5.578 MB