“内存不足”；处理大记录集时出错 [英] "Out of memory" error when processing large recordsets
问题描述
我有一个Windows脚本宿主脚本，该脚本在我们的数据库中执行存储过程，并返回一个包含约225列的大型记录集（该列数的确因查询而异，将来可能会更大）。 / p>
I have a Windows Script host script which executes a Stored Procedure in our database and returns back a large recordset with about 225 columns (the column count does vary depending on the query, it may well get larger in the future).
var adUseServer = 2;
var adUseClient = 3;
var adOpenForwardOnly = 0;
var adLockReadOnly = 1;
var dbc = new ActiveXObject("ADODB.connection")
dbc.connectionString = "Driver={SQL Server};Server=MYDBSERVER;Database=MYDB;uid=USER;Pwd=PASSWORD;"
dbc.CursorLocation = adUseClient
dbc.connectionTimeout = 3600
dbc.commandTimeout = 3600
dbc.open
// Setup test table
WScript.echo( "Setting table..." );
dbc.execute( "IF OBJECT_ID(N'export_test', N'U') IS NOT NULL DROP TABLE export_test;;" );
dbc.execute( "create table export_test ( channelID int )" );
// Changing to 130 works (for me)
for( var n = 1; n < 131; n++ )
{
dbc.execute( "alter table export_test ADD test_" + n + " NVARCHAR(4000) DEFAULT '0' not null" );
}
WScript.echo( "Starting query..." );
var rsData = serverQuery( "Select * from export_test" )
WScript.echo( "Starting loop..." );
var count = 0;
while( rsData.eof == 0 )
{
WScript.echo( count++ );
rsData.moveNext();
}
WScript.echo( "All done" );
function serverQuery( sql )
{
var rsData = new ActiveXObject("ADODB.recordset");
rsData.CursorLocation = adUseServer;
rsData.CursorType = adOpenForwardOnly;
rsData.LockType = adLockReadOnly;
rsData.MaxRecords = 1; // This makes no difference
rsData.open( sql, dbc );
return rsData;
}
使用客户端游标执行此查询时，出现内存错误；如果我使用服务器游标执行，则会收到没有足够的存储空间来完成此操作错误。
When I execute this query using a client cursor, I get an "Out of memory" error; if I execute with a server cursor I get a "Not enough storage is available to complete this operation" error.
我尝试减少数据量，因此没有太多行被退回并且问题仍然存在。
I have tried reducing the data down so not many rows are being returned and the problem persists.
我要尝试分页结果，但是我担心在完成这项工作后，由于我尝试减少结果，因此无济于事返回的行并没有帮助，所以我认为可能是大量的列（这是动态的，并且随着时间的推移会越来越多）是问题的根本原因。
I was going to try 'paging' the results, but I fear after doing the work that it wont help as I have tried reducing the returned rows and that didnt help, so I think it may be the large number of columns (which is dynamic and may well get more and more over time) being the fundamental cause of the issue.
在SO和其他站点上阅读后，我尝试将数据库日志文件的大小更改为不受限制的增长，但它不允许我继续转换为最大2GB，但这是一个不同的问题（
After reading on SO and other sites, I have tried changing the database log file size to "unrestricted growth" but it wont let me and keeps reverting back to 'max size of 2GB', but thats a different problem (I think) as its currently nowhere near that si.
有人对它有进一步的想法或见识吗？
Does anyone have any futher ideas or insight into this.
谢谢。
推荐答案
对，我基本上总结说这在某处是一个限制，所以我要做的是创建一个COM对象以卸载此工作并返回可以在脚本中使用的结果数据的JSON或XML版本。
Right I basically summised that it was a limitation somewhere, so what I did was create a COM object to offload this work and return back a JSON or XML version of the result data which I could use within my script.
对于任何关心的人，下面是C＃代码（很抱歉，如果有点麻烦，由于我现在是唯一的开发人员，我现在没有人再进行代码审查了）：
For anyone that cares, below is the C# code (apologies if its a bit naff, I have no-one to code review with anymore as I am a sole dev now):
using System;
using System.Runtime.InteropServices;
using System.Text;
using System.Data.SqlClient;
using System.Xml;
/*
* To create strong name key for signing: "C:\Program Files\Microsoft SDKs\Windows\v6.0A\Bin\sn" -k SQLInterchange_Key.snk"
*/
namespace SQLInterchange
{
[Guid( "4E97C259-80EC-40dc-8F7D-DB56BE9F123E" )]
public interface ISQLInterchange
{
[DispId( 1 )]
bool Open( string databaseServer, string databaseName, string userID, string userPassword );
[DispId( 2 )]
void Close();
[DispId( 3 )]
bool ExecuteRecordset( string selCommand );
[DispId( 4 )]
void CloseRecordset();
[DispId( 5 )]
bool Execute( string selCommand );
[DispId( 6 )]
string GetJSONData();
[DispId( 7 )]
string GetXMLData( string recordElementName, bool encoded );
}
// Events interface Database_COMObjectEvents
[Guid( "31A125AA-81D5-495b-86E6-7A4B24B08BAA" ),
InterfaceType( ComInterfaceType.InterfaceIsIDispatch )]
public interface SQLInterchange_Events
{
}
[Guid( "6B0B6A04-3BAF-4e14-9770-A0C10425E2CE" ),
ClassInterface(ClassInterfaceType.None),
ComSourceInterfaces(typeof(SQLInterchange_Events))]
public class Connection : ISQLInterchange
{
private SqlConnection _connection = null;
private SqlDataReader _reader = null;
public Connection()
{
}
public bool Open( string databaseServer, string databaseName, string userID, string userPassword )
{
// no need to throw as it throws a com compatible exception automatically
string myConnectString = "user id=" + userID + ";password=" + userPassword +";Database=" + databaseName + ";Server=" + databaseServer + ";Connect Timeout=30";
_connection = new SqlConnection( myConnectString );
_connection.Open();
return true;
}
public bool ExecuteRecordset( string selCommand )
{
if( _reader != null )
_reader.Close();
SqlCommand myCommand = new SqlCommand( selCommand );
myCommand.Connection = _connection;
myCommand.CommandTimeout = 3600;
myCommand.ExecuteNonQuery();
_reader = myCommand.ExecuteReader();
return true;
}
public bool Execute( string selCommand )
{
if( _reader != null )
_reader.Close();
SqlCommand myCommand = new SqlCommand( selCommand, _connection );
myCommand.CommandTimeout = 3600;
int rows = myCommand.ExecuteNonQuery();
return true;
}
public void Close()
{
if( _connection != null )
_connection.Close();
}
public void CloseRecordset()
{
if( _reader != null )
_reader.Close();
}
public string GetJSONData()
{
StringBuilder sb = new StringBuilder();
sb.Append( "[" );
if( _reader != null )
{
int count = _reader.FieldCount;
StringBuilder sbRecord = new StringBuilder();
while( _reader.Read() )
{
if( sbRecord.Length > 0 )
{
sbRecord.Append( "," );
}
sbRecord.Append( "{" );
// get the results of each column
for( int n = 0; n < count; n++ )
{
string name = _reader.GetName( n );
string data = Convert.ToString( _reader[ n ] );
sbRecord.Append( "\"" + _safeJSONElementName( name ) + "\":\"" );
sbRecord.Append( _safeJSON( data ) );
sbRecord.Append( "\"" );
if( n + 1 < count )
{
sbRecord.Append( "," );
}
}
sbRecord.Append( "}" );
}
sb.Append( sbRecord.ToString() );
}
sb.Append( "]" );
return sb.ToString();
}
public string GetXMLData( string recordElementName, bool encoded )
{
_lt = "<";
_gt = ">";
if( encoded )
{
_lt = "<";
_gt = ">";
}
StringBuilder sb = new StringBuilder();
if( _reader != null )
{
int count = _reader.FieldCount;
while( _reader.Read() )
{
_addXMLElement( sb, recordElementName, 1, true );
// get the results of each column
for( int n = 0; n < count; n++ )
{
string name = _reader.GetName( n );
string data = Convert.ToString( _reader[ n ] );
_addXMLElement( sb, name, 2, false );
sb.Append( _escapeXML( data ) );
_addXMLElement( sb, "/" + name, 0, true );
}
_addXMLElement( sb, "/" + recordElementName, 1, true );
}
}
return sb.ToString();
}
private string _safeJSON( string s )
{
s = s.Replace( "\n", "\\n" );
s = s.Replace( "\r", "\\r" );
s = s.Replace( "\t", "\\t" );
s = s.Replace( "\"", "\\\"" );
return s;
}
private string _safeJSONElementName( string s )
{
s = s.Replace( ".", "_" );
s = s.Replace( " ", "_" );
return s;
}
private string _lt = "<";
private string _gt = ">";
private void _addXMLElement( StringBuilder sb, string s, int tabs, bool last )
{
for( int n = 0; n < tabs; n++ )
{
sb.Append( "\t" );
}
sb.Append( _lt );
sb.Append( s );
sb.Append( _gt );
if( last ) sb.Append( "\n" );
}
private string _escapeXML( string unescaped )
{
XmlDocument doc = new XmlDocument();
var node = doc.CreateElement("root");
node.InnerText = unescaped;
return node.InnerXml;
}
}
}
这篇关于“内存不足”；处理大记录集时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
