SqlServer2008存大文件

Silence_Smile 2011-09-20 08:53:10
客户要求把100M+加的附件存入数据库(否定了文件存磁盘,数据库存路径的方法),因为性能问题,决定采用SqlServer2008的FileStream特性,经过测试发现用了FileStream以后,存大文件的速度加快了一倍。现在问题是,不管是否开启FileStream特性,往数据库中插入大文件时,SqlServer的内存都快速上涨,貌似内存没用完之前,文件多大,内存就长多大。设置了最大内存后,内存增长大一定程度就不再增长,但此时数据库响应很慢,请问有遇到同样问题的么?该如何解决?
...全文
344 13 打赏 收藏 转发到动态 举报
写回复
用AI写文章
13 条回复
切换为时间正序
请发表友善的回复…
发表回复
微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
  • 打赏
  • 举报
回复
数据库的强项,是数据管理.你把操作系统的强项,硬让数据库来做,最终结果会怎样,你自己清楚.

22,206

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧