从SQL Server中选择百万条记录
我们需要将存储在SQL Server表中的所有记录编入索引(在ASP.NET中)。该表在每行中都有大约2M个记录(nvarchar)数据。从SQL Server中选择百万条记录
是否可以一次取出所有记录,因为我们需要索引它们(用于搜索)?另一种选择是什么(我想避免分页)?
注意:我没有显示这些记录,只需要一个一个地去做这些记录,这样我就可以通过后台线程为它们编制索引。
我是否需要为我的查询设置任何超时时间?如果是,如果我正在从ASP.NET页面运行查询,那么设置较长超时的最有效方法是什么?
可以一次提取所有记录,因为我们需要为它们编制索引 (用于搜索)吗?另一种选择是什么(我想避免分页)?
内存管理问题/性能问题
你可以面对系统内存溢出异常的你带来2百万的记录 正如你将保持在数据集中的所有这些记录和数据集存储情况将在RAM中。
我是否需要设置任何长期超时为我的查询?如果是,如果我正在从ASP.NET页面运行 查询,那么 最有效的方法是设置更长的超时时间?
using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand())
{
cmd.CommandTimeout = 0;
}
建议
- 这是更好地从数据库级别筛选出的记录...
- 从数据库中获取的所有记录,并将其保存在一个文件中。访问该文件进行任何中间操作。
如果我需要这样的东西,只要从数据库端考虑它,我可能会将它导出到一个文件。然后该文件可以很容易地移动。移动大数据集对所有参与者来说都是巨大的痛苦。您可以在批处理命令中使用SSIS,sqlcmd甚至bcp来完成它。
然后,你只需要担心你在应用程序端做了什么,不用担心在导出数据库时锁定了所有的数据。
您在提取转换加载(ETL)中描述的内容。有2个选项我所知道的:
- SSIS这是SQL Server的一部分
- Rhino.ETL
我喜欢Rhino.Etl因为它comletely C#语言编写,可以在创建脚本Boo,测试和编写ETL过程要容易得多。并且该库的目的是处理大量数据,因此内存管理是内置的。
最后一点:尽管asp.net可能是启动索引过程的入口,但我不会在因为它可能需要几分钟或几小时,这取决于记录和处理的数量。
取而代之的是有asp.net作为触发后台任务来处理记录的切入点。理想情况下,完全独立于asp.net,以避免任何超时或关闭问题。
分批处理您的记录。你将会遇到两个主要问题。 (1)您需要索引所有现有记录。 (2)您需要更新索引,添加,更新或删除记录。它可能听起来像eaiser只是放弃索引并重新创建它,但应尽可能避免它。以下是以批处理10,000条记录从AdventureWorks2008R2
数据库处理[Production].[TransactionHistory]
的示例。它不会将所有记录加载到内存中。我的本地计算机上的输出产生Processed 113443 records in 00:00:00.2282294
。显然,这不考虑每个记录的远程计算机和处理时间。
class Program
{
private static string ConnectionString
{
get { return ConfigurationManager.ConnectionStrings["db"].ConnectionString; }
}
static void Main(string[] args)
{
int recordCount = 0;
int lastId = -1;
bool done = false;
Stopwatch timer = Stopwatch.StartNew();
do
{
done = true;
IEnumerable<TransactionHistory> transactionDataRecords = GetTransactions(lastId, 10000);
foreach (TransactionHistory transactionHistory in transactionDataRecords)
{
lastId = transactionHistory.TransactionId;
done = false;
recordCount++;
}
} while (!done);
timer.Stop();
Console.WriteLine("Processed {0} records in {1}", recordCount, timer.Elapsed);
}
/// Get a new open connection
private static SqlConnection GetOpenConnection()
{
SqlConnection connection = new SqlConnection(ConnectionString);
connection.Open();
return connection;
}
private static IEnumerable<TransactionHistory> GetTransactions(int lastTransactionId, int count)
{
const string sql = "SELECT TOP(@count) [TransactionID],[TransactionDate],[TransactionType] FROM [Production].[TransactionHistory] WHERE [TransactionID] > @LastTransactionId ORDER BY [TransactionID]";
return GetData<TransactionHistory>((connection) =>
{
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.AddWithValue("@count", count);
command.Parameters.AddWithValue("@LastTransactionId", lastTransactionId);
return command;
}, DataRecordToTransactionHistory);
}
// funtion to convert a data record to the TransactionHistory object
private static TransactionHistory DataRecordToTransactionHistory(IDataRecord record)
{
TransactionHistory transactionHistory = new TransactionHistory();
transactionHistory.TransactionId = record.GetInt32(0);
transactionHistory.TransactionDate = record.GetDateTime(1);
transactionHistory.TransactionType = record.GetString(2);
return transactionHistory;
}
private static IEnumerable<T> GetData<T>(Func<SqlConnection, SqlCommand> commandBuilder, Func<IDataRecord, T> dataFunc)
{
using (SqlConnection connection = GetOpenConnection())
{
using (SqlCommand command = commandBuilder(connection))
{
using (IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
T record = dataFunc(reader);
yield return record;
}
}
}
}
}
}
public class TransactionHistory
{
public int TransactionId { get; set; }
public DateTime TransactionDate { get; set; }
public string TransactionType { get; set; }
}
抓取它们,你的意思是'select * from table'?是的,你可以做到这一点,然后你从sql中逐行读取它们。避免将它们都放在记忆中,然后采取行动。 – Aristos 2012-02-10 23:31:36
'索引'他们如何,为了什么目的?我假设你不是指像SQL索引那样,因为你通常只是告诉数据库管理者构建它认为它需要的任何索引...... – 2012-02-10 23:32:08
@ X-Zero我认为海报意味着从clob字段和用于在另一个表中搜索关键字aka store的“索引”。 – 2012-02-10 23:33:40