1) 如果您启用了调试,coldfusion 将保留您的查询,直到页面完成。把它关掉!
2) 您可能需要 structDelete() 查询变量以允许它被垃圾收集,否则只要引用它的作用域存在,它就可能会持续存在。例如。,<cfset structDelete(variables,'myQuery') />
3) cfquery 将整个 ResultSet 拉入内存。大多数时候这都很好。但对于大型结果集的报告,您不需要这样。一些 JDBC 驱动程序支持设置 fetchSize,它以向前、只读的方式,让您一次获得一些结果。通过这种方式,您可以处理成千上万的行,而不会占用内存。我刚刚在大约 80 秒内生成了一个 1GB 的 csv 文件,使用了不到 100mb 的堆。这需要退出到 Java。但一石二鸟。它减少了 JDBC 驱动程序一次引入的数据量,并且由于您直接使用 ResultSet,因此不会遇到 @orangepips 提到的 cfloop 问题。当然,它不适合那些没有 Java 基础的人。
你可以这样做(你的构建路径中需要 cfusion.jar):
import java.io.BufferedWriter;
import java.io.FileWriter;
import java.sql.ResultSet;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import au.com.bytecode.opencsv.CSVWriter;
import coldfusion.server.ServiceFactory;
public class CSVExport {
public static void export(String dsn,String query,String fileName) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
FileWriter fw = null;
BufferedWriter bw = null;
try {
DataSource ds = ServiceFactory.getDataSourceService().getDatasource(dsn);
conn = ds.getConnection();
// we want a forward-only, read-only result.
// you may want need to use a PreparedStatement instead.
stmt = conn.createStatement(
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY
);
// we only want to go forward!
stmt.setFetchDirect(ResultSet.FETCH_FORWARD);
// how many records to pull back at a time.
// the hard part is balancing memory usage, and round trips to the database.
// basically sacrificing speed for a lower memory hit.
stmt.setFetchSize(256);
rs = stmt.executeQuery(query);
// do something with the ResultSet, for example write to csv using opencsv
// the key is to stream it. you don't want it stored in memory.
// so excel spreadsheets and pdf files are out, but text formats like
// like csv, json, html, and some binary formats like MDB (via jackcess)
// that support streaming are in.
fw = new FileWriter(fileName);
bw = new BufferedWriter(fw);
CSVWriter writer = new CSVWriter(bw);
writer.writeAll(rs,true);
}
catch (Exception e) {
// handle your exception.
// maybe try ServiceFactory.getLoggingService() if you want to do a cflog.
e.printStackTrace();
}
finally() {
try {rs.close()} catch (Exception e) {}
try {stmt.close()} catch (Exception e) {}
try {conn.close()} catch (Exception e) {}
try {bw.close()} catch (Exception e) {}
try {fw.close()} catch (Exception e) {}
}
}
}
弄清楚如何传递参数、日志记录、将其转换为后台进程(提示:扩展线程)等是单独的问题,但如果您理解这段代码,它应该不会太困难。
4)也许看看Jackson用于生成 json。它支持流媒体,并结合 fetchSize 和 BufferedOutputStream,您应该能够降低内存使用量。