SQL SERVER 读取大二进制数据太慢? 偶用SQL SERVER 2000把7M多在文件以二进制流存进数据库里,需时不到1秒,但读出来放到硬盘时却要20多秒,改用MYSQL是存取都不用1秒的,是不是SQL SERVER里要设置什么参数啊?求赐教~~~平台是WINDOWS + JAVA + JDBC 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 我用MYSQL测试都是用同一段代码的,现在在想会不会是JDBC的问题,一会做个.NET的测试看看 import java.io.File;import java.io.FileInputStream;import java.io.FileOutputStream;import java.io.InputStream;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;public class UpAndDown { //数据库配置------------------------------- private String jdbcDriver = "com.microsoft.jdbc.sqlserver.SQLServerDriver"; //private String jdbcDriver = "com.mysql.jdbc.Driver"; private String url = "localhost"; private String userName = "sa"; private String password = "12345"; //----------------------------------------- private String DbUrl = "jdbc:microsoft:sqlserver://" + url + ":1433;DataBaseName=largeFile"; //private String DbUrl = "jdbc:mysql://" + url + ":3306/largeFile"; private Connection conn = null; //文件上传到数据库,String filePath:文件名(包括路径) public void uploadFile(String filePath) throws Exception { PreparedStatement ps = null; FileInputStream fis = null; File uploadFile = new File(filePath); if(!uploadFile.exists()) { System.out.println("文件不存在"); return; } Class.forName(jdbcDriver); conn = java.sql.DriverManager.getConnection(DbUrl, userName, password); fis = new FileInputStream(uploadFile); ps = conn.prepareStatement("INSERT INTO myFile (largeFile) VALUES (?)"); ps.setBinaryStream(1, fis, (int)uploadFile.length()); ps.executeUpdate(); fis.close(); ps.close(); conn.close(); System.out.println("文件上传成功"); } //文件下载到本地,String path:下载目的地,带"\\" public void downloadFile(String path) throws Exception { PreparedStatement ps = null; ResultSet rs = null; File filePath = new File(path); File downloadFile = null; InputStream is = null; FileOutputStream fos = null; if(!filePath.exists()) filePath.mkdir(); Class.forName(jdbcDriver); conn = java.sql.DriverManager.getConnection(DbUrl, userName, password); ps = conn.prepareStatement("SELECT * FROM myFile", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); rs = ps.executeQuery(); while(rs.next()) { byte[] buffer = new byte[1024]; downloadFile = new File(path + rs.getString("id") + ".mpg"); if(!downloadFile.exists()) { fos = new FileOutputStream(downloadFile); is = rs.getBinaryStream("largeFile"); int count = 0; while((count = is.read(buffer)) != -1) { fos.write(buffer, 0, count); } } else System.out.println("文件:" + downloadFile.getName() + "已存在"); } rs.close(); ps.close(); fos.close(); is.close(); conn.close(); System.out.println("文件下载成功"); } //清空文件所在的数据库表,因为在企业管理器里手动删不了,需要用SQL语句删除 public void clearDB() throws Exception { java.sql.PreparedStatement ps = null; Class.forName(jdbcDriver); conn=java.sql.DriverManager.getConnection(DbUrl, userName, password); ps = conn.prepareStatement("DELETE FROM myFile"); ps.executeUpdate(); System.out.println("清空成功"); } public static void main(String[] args) { UpAndDown ud = new UpAndDown(); try { ud.uploadFile("d:\\33.mpg");//上传 //ud.downloadFile("d:\\down\\");//下载,参数要带"\\" //ud.clearDB();//清空文件所在的数据库表 } catch (Exception e) { e.printStackTrace(); } }}数据库相关,请自行添加:数据库名:largeFile表: [id] [int] IDENTITY (1, 1) NOT NULL primary key,[largeFile] [image] NULL测试代码临时写的,请莫见怪。测试时上传很快,大概1秒,但下载好慢,7M多要20多秒。但用MYSQL测试就上传和下载都是1秒的,所以猜想是不是要在SQL SERVER里面设置点什么配置才行。 我用.NET处理一样很慢,不过把缓存设置的大一些的话会加快读取的速度.我用102400个字节做为缓存要比1024个字节做为缓存快上很多.不过还是达不到理想的要求用102400字节做为缓存大概需要24秒读取30M的文件.用1024000需要3秒左右读取30M的文件.用4096000需要1秒左右读取30M的文件.可见如果内存大一些的话,使用大的缓存能加快读取的速度,如果存的文件小于10M的话,建议一次读取出来,这样速度会更快一些.SqlConnection conn = new SqlConnection(@"Server=.\SQLEXPRESS;uid=sa;pwd=123;Initial Catalog=TEST"); conn.Open(); SqlCommand cmd = new SqlCommand("SELECT * FROM AA", conn); byte[] bs; cmd.CommandText = "INSERT INTO AA(ID,VALUE) VALUES(11,@VALUE)"; using (System.IO.FileStream fs = new System.IO.FileStream("k.db", System.IO.FileMode.Open, System.IO.FileAccess.Read)) { bs = new byte[fs.Length]; fs.Read(bs, 0, (int)fs.Length); } cmd.Parameters.Add("@VALUE", System.Data.SqlDbType.VarBinary); cmd.Parameters["@VALUE"].Value = bs; cmd.ExecuteNonQuery(); cmd.CommandText = "SELECT * FROM AA"; SqlDataReader dr = cmd.ExecuteReader(); int i = 0; bs = new byte[4096 * 1000]; while (dr.Read()) { Console.WriteLine(DateTime.Now); long offset = 0; long readlength = dr.GetBytes(1, offset, bs, 0, bs.Length); while (readlength > 0) { //fs.Write(bs, 0, (int)readlength); offset += readlength; readlength = dr.GetBytes(1, offset, bs, 0, bs.Length); } Console.WriteLine(DateTime.Now); } conn.Close(); 存储过程为什么效率高? 怎么远程连接数据库 在线等候 各位老大,请帮忙看看,一个很容易的问题,关于asp调用sql存储过程的返回值。 想对数据库某字段的内容进行更改!求助 这个问题叫人稀里糊涂... XP装SQL200 不能建数据源! MSSQL 存储过程问题一个非常奇怪的问题 讨论SQL SERVER 求教个SQL脚本(sqlserver) 显示 消息 102,级别 15,状态 1,第 2 行 ',' 附近有语法错误。 sql中@的作用有哪些? 高分求优化一条Sql语句,急!!
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;public class UpAndDown {
//数据库配置-------------------------------
private String jdbcDriver = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
//private String jdbcDriver = "com.mysql.jdbc.Driver";
private String url = "localhost";
private String userName = "sa";
private String password = "12345";
//-----------------------------------------
private String DbUrl = "jdbc:microsoft:sqlserver://" + url + ":1433;DataBaseName=largeFile";
//private String DbUrl = "jdbc:mysql://" + url + ":3306/largeFile";
private Connection conn = null;
//文件上传到数据库,String filePath:文件名(包括路径)
public void uploadFile(String filePath) throws Exception {
PreparedStatement ps = null;
FileInputStream fis = null;
File uploadFile = new File(filePath);
if(!uploadFile.exists()) {
System.out.println("文件不存在");
return;
}
Class.forName(jdbcDriver);
conn = java.sql.DriverManager.getConnection(DbUrl, userName, password);
fis = new FileInputStream(uploadFile);
ps = conn.prepareStatement("INSERT INTO myFile (largeFile) VALUES (?)");
ps.setBinaryStream(1, fis, (int)uploadFile.length());
ps.executeUpdate();
fis.close();
ps.close();
conn.close();
System.out.println("文件上传成功");
} //文件下载到本地,String path:下载目的地,带"\\"
public void downloadFile(String path) throws Exception {
PreparedStatement ps = null;
ResultSet rs = null;
File filePath = new File(path);
File downloadFile = null;
InputStream is = null;
FileOutputStream fos = null;
if(!filePath.exists()) filePath.mkdir(); Class.forName(jdbcDriver);
conn = java.sql.DriverManager.getConnection(DbUrl, userName, password);
ps = conn.prepareStatement("SELECT * FROM myFile", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
rs = ps.executeQuery();
while(rs.next()) {
byte[] buffer = new byte[1024];
downloadFile = new File(path + rs.getString("id") + ".mpg");
if(!downloadFile.exists()) {
fos = new FileOutputStream(downloadFile);
is = rs.getBinaryStream("largeFile");
int count = 0;
while((count = is.read(buffer)) != -1) {
fos.write(buffer, 0, count);
}
} else System.out.println("文件:" + downloadFile.getName() + "已存在");
}
rs.close();
ps.close();
fos.close();
is.close();
conn.close();
System.out.println("文件下载成功");
} //清空文件所在的数据库表,因为在企业管理器里手动删不了,需要用SQL语句删除
public void clearDB() throws Exception {
java.sql.PreparedStatement ps = null;
Class.forName(jdbcDriver);
conn=java.sql.DriverManager.getConnection(DbUrl, userName, password);
ps = conn.prepareStatement("DELETE FROM myFile");
ps.executeUpdate();
System.out.println("清空成功");
}
public static void main(String[] args) {
UpAndDown ud = new UpAndDown();
try {
ud.uploadFile("d:\\33.mpg");//上传
//ud.downloadFile("d:\\down\\");//下载,参数要带"\\"
//ud.clearDB();//清空文件所在的数据库表
} catch (Exception e) {
e.printStackTrace();
}
}
}
数据库相关,请自行添加:
数据库名:largeFile
表: [id] [int] IDENTITY (1, 1) NOT NULL primary key,
[largeFile] [image] NULL测试代码临时写的,请莫见怪。测试时上传很快,大概1秒,但下载好慢,7M多要20多秒。
但用MYSQL测试就上传和下载都是1秒的,所以猜想是不是要在SQL SERVER里面设置点什么配置才行。
我用102400个字节做为缓存要比1024个字节做为缓存快上很多.不过还是达不到理想的要求
用102400字节做为缓存大概需要24秒读取30M的文件.
用1024000需要3秒左右读取30M的文件.
用4096000需要1秒左右读取30M的文件.可见如果内存大一些的话,使用大的缓存能加快读取的速度,如果存的文件小于10M的话,建议一次读取出来,这样速度会更快一些.SqlConnection conn = new SqlConnection(@"Server=.\SQLEXPRESS;uid=sa;pwd=123;Initial Catalog=TEST");
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM AA", conn);
byte[] bs;
cmd.CommandText = "INSERT INTO AA(ID,VALUE) VALUES(11,@VALUE)";
using (System.IO.FileStream fs = new System.IO.FileStream("k.db", System.IO.FileMode.Open, System.IO.FileAccess.Read))
{
bs = new byte[fs.Length];
fs.Read(bs, 0, (int)fs.Length);
}
cmd.Parameters.Add("@VALUE", System.Data.SqlDbType.VarBinary);
cmd.Parameters["@VALUE"].Value = bs;
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT * FROM AA";
SqlDataReader dr = cmd.ExecuteReader();
int i = 0;
bs = new byte[4096 * 1000];
while (dr.Read())
{
Console.WriteLine(DateTime.Now);
long offset = 0;
long readlength = dr.GetBytes(1, offset, bs, 0, bs.Length);
while (readlength > 0)
{
//fs.Write(bs, 0, (int)readlength);
offset += readlength;
readlength = dr.GetBytes(1, offset, bs, 0, bs.Length);
}
Console.WriteLine(DateTime.Now);
} conn.Close();