SqlServer2008存大文件

2011-09-20 08:53:10 493 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

MSSQL2008数据库邮件通知配置 MSSQL2008数据库邮件通知配置配置数据库邮件1. 连接到MSSQL2008 数据库服务器，右键数据库实例，单击“方面”，然后在“方面”选项卡中选择“外配配置管理器”，修改SqlMailEnabled 属性为True ，便可启用数据库邮件；2. 展开“管理”，右键“数据库邮件”，选择“配置数据库邮件”； 3. 出现欢迎页面后下一步，选择“通过执行以下任务来安装数阅读详情

客户要求把100M+加的附件存入数据库（否定了文件存磁盘，数据库存路径的方法），因为性能问题，决定采用SqlServer2008的FileStream特性，经过测试发现用了FileStream以后，存大文件的速度加快了一倍。现在问题是，不管是否开启FileStream特性，往数据库中插入大文件时，SqlServer的内存都快速上涨，貌似内存没用完之前，文件多大，内存就长多大。设置了最大内存后，内存增长大一定程度就不再增长，但此时数据库响应很慢，请问有遇到同样问题的么？该如何解决？

...全文

从零理解Brep：用Python玩转3D建模的边界表示法（附OpenCASCADE示例）本文深入解析了边界表示法（Brep）在3D建模中的应用，通过Python和OpenCASCADE的实战示例，详细介绍了Brep的核心数据结构、布尔运算、网格转换及3D打印优化技巧。适合CAD设计、3D打印和计算机图形学领域的开发者学习与实践。阅读详情

Silence_Smile

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

微wx笑 2012-09-04

打赏
举报

SqlServer2008的FileStream就是个鸡肋……

lovezx1028 2011-09-20

打赏
举报

In the previous post "Working with SQL Server 2008 Date/Time Data Types using v1.2 JDBC driver",I talked about how the current JDBC driver can interop with the new Date/Time/DateTime2/DatetimeOffset data types.  This time around, I will describe how to interop with SQL Server 2008 Filestream.



Please refer to "SQL Server 2008 Books Online" for detailed information about Filestream.



To demostrate how the v1.2 driver can retrieve a resultset containing a filestream column, I used the following T-SQL query to create the test database and table:



CREATE DATABASE Archive

ON

PRIMARY ( NAME = Arch1,

    FILENAME = 'c:\data\archdat1.mdf'),

FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = Arch3,

    FILENAME = 'c:\data\filestream1')

LOG ON  ( NAME = Archlog1,

    FILENAME = 'c:\data\archlog1.ldf')

GO



CREATE TABLE foo

(

   id int NOT NULL PRIMARY KEY,

   Photo varbinary(max) FILESTREAM NULL,

   MyRowGuidColumn uniqueidentifier NOT NULL ROWGUIDCOL

   UNIQUE DEFAULT NEWID()

)

GO



Let's assume that the table has already been populated with some data.  Here is the code snippet that I used to retrieve the column values:



strCmd = "select * from foo";

rs = stmt.executeQuery(strCmd);

if (rs.next())

{

    ResultSetMetaData rsmd = rs.getMetaData();

    if (null != rsmd)

    {

        String x = rsmd.getColumnTypeName(2);

        String name = rsmd.getColumnName(2);

        System.out.println("Column " + name + " is data type: " + x);

        int i = rsmd.getColumnType(2);

        switch (i)

        {

        case (java.sql.Types.LONGVARBINARY):

            System.out.println("Column is of JDBC type LONGVARBINARY.");

            InputStream bStream = rs.getBinaryStream(2);

            byte[] y = new byte[1024];

            if (null != bStream)

            {

                // write the retrieved filestream data as a file.

                System.out.println("Write the filestream data out as a file.");

                FileOutputStream fs = new FileOutputStream("c:\\myFile");

                int read = bStream.read(y);

                while (read != -1)

                {

                     fs.write(y);

                     fs.flush();

                     read = bStream.read(y);

                }

                fs.close();

            }

            break;

        default:

            throw new Exception("Unexpected data type found: " + String.valueOf(i) + ". Expected LONGVARBINARY.");

        }

    }



    // now, update the data with a new file content.

    System.out.print("Now, let's update the filestream data.");

    FileInputStream iStream = new FileInputStream("c:\\testFile.xml");

    rs.updateBinaryStream(2, iStream, -1);

    rs.updateRow();

    iStream.close();

}

rs.close();



When executed as a console application, you'll get the following output:



Column Photo is data type: varbinary

Column is of JDBC type LONGVARBINARY.

Write the filestream data out as a file.

Now, let's update the filestream data.



As you've probably already noticed, SQL Server 2008 Filestream is simply a LONGVARBINARY to the v1.2 JDBC driver.  This means you can operate on a Filestream like any other LONGVARBINARY, though I recommend that you stick with binaryStream to minize the amount of buffering the driver has to do.

http://blogs.msdn.com/b/jdbcteam/archive/2008/10/03/working-with-sql-server-2008-filestream-using-v1-2-jdbc-driver.aspx

Silence_Smile 2011-09-20

打赏
举报

Accessing FILESTREAM data using Win32 Streaming has a number of advantages over accessing it using TSQL. When accessing FILESTREAM data using TSQL, SQL Server reads the content of the FILESTREAM data file and serves it to the client. SQL Server memory is used for reading the content of the data file. Accessing FILESTREAM data using Win32 Streaming does not use SQL Server memory. In addition it allows the application to take advantage of the Streaming capabilities of the NT File System.
问题找到，谢谢大家！

Silence_Smile 2011-09-20

打赏
举报

看了一下MSDN，明白为什么用C#是不存在这样的问题的，用SqlFileStream写如FileStream字段的时候貌似就不通过SqlServer了,看来Java还用不了这个特性啊！

using (SqlConnection connection = new SqlConnection(

    connStringBuilder.ToString()))

{

    connection.Open();



    SqlCommand command = new SqlCommand("", connection);

    command.CommandText = "select Top(1) Photo.PathName(), "

    + "GET_FILESTREAM_TRANSACTION_CONTEXT () from employees";



    SqlTransaction tran = connection.BeginTransaction(

        System.Data.IsolationLevel.ReadCommitted);

    command.Transaction = tran;



    using (SqlDataReader reader = command.ExecuteReader())

    {

        while (reader.Read())

        {

            // Get the pointer for file

            string path = reader.GetString(0);

            byte[] transactionContext = reader.GetSqlBytes(1).Buffer;



            FileStream fileStream = new SqlFileStream(path,

                (byte[])reader.GetValue(1),

                FileAccess.ReadWrite,

                FileOptions.SequentialScan, 0);



            // Seek to the end of the file

            fs.Seek(0, SeekOrigin.End);



            // Append a single byte 

            fileStream.WriteByte(0x01);

            fileStream.Close();

        }

    }

    tran.Commit();

}

bzcode 2011-09-20

打赏
举报

内存总是会被吃光的吧？

Silence_Smile 2011-09-20

打赏
举报

[Quote=引用 5 楼 fredrickhu 的回复:]

先插入空和长度为零的FILESTREAM的数据再插入指定字符串的数据
[/Quote]
我网上找了一下，如果是C#的话，用SqlFileStream就行，但现在是Java应用，不知要如何实现呢？

Silence_Smile 2011-09-20

打赏
举报

[Quote=引用 4 楼 nbdba 的回复:]

FileStream有两种存取方式，不知道搂主用的哪一种

建议楼主改变下存取方式试下，文件方式的操作时分多步，先插入这个字段空的纪录，然后取出这条记录的文件指针，然后像文件一样操作写入文件内容。
[/Quote]
你指的是追加模式么？我感觉现在的问题是，sqlserver在内存够用的情况下，会把存的大文件在内存里缓存起来，基本上插了几个大文件后，sqlserver的内存占用就保持在max server memory这个水平了。但按理说，有后续操作，内存不够用的情况下，sqlserver应该会自动管理缓存才对，但是实际操作的时候发现很慢...

--小F-- 2011-09-20

打赏
举报

先插入空和长度为零的FILESTREAM的数据再插入指定字符串的数据

NBDBA 2011-09-20

打赏
举报

FileStream有两种存取方式，不知道搂主用的哪一种

建议楼主改变下存取方式试下，文件方式的操作时分多步，先插入这个字段空的纪录，然后取出这条记录的文件指针，然后像文件一样操作写入文件内容。

Silence_Smile 2011-09-20

打赏
举报

[Quote=引用 1 楼 qianjin036a 的回复:]

数据库的强项,是数据管理.你把操作系统的强项,硬让数据库来做,最终结果会怎样,你自己清楚.
[/Quote]
嗯，这个我知道，客户要求这样做，我们也没办法。有没有什么解决办法呢？毕竟SqlServer2008提供了FileStream这样的特性，应该还是可以支持大文件的啊！

NBDBA 2011-09-20

打赏
举报

FileStream的资料太少，谢谢搂主分享

-晴天 2011-09-20

打赏
举报

数据库的强项,是数据管理.你把操作系统的强项,硬让数据库来做,最终结果会怎样,你自己清楚.

SqlServer2008存大文件

相关推荐