为什么数据表比DataReader的速度更快 [英] Why is DataTable faster than DataReader
问题描述
因此，我们不得不在工作了激烈的争论到数据访问路线走：数据表或DataReader的
So we have had a heated debate at work as to which DataAccess route to take: DataTable or DataReader.
免责声明我在DataReader的一边，这些成果已经动摇了我的世界。
DISCLAIMER I am on the DataReader side and these results have shaken my world.
我们最后写一些基准测试速度的差异。人们普遍认为，一个DataReader速度更快，但我们想看看如何更快。
We ended up writing some benchmarks to test the speed differences. It was generally agreed that a DataReader is faster, but we wanted to see how much faster.
结果让我们感到吃惊。该数据表始终比DataReader的速度更快。走近快，有时两次。
The results surprised us. The DataTable was consistently faster than the DataReader. Approaching twice as fast sometimes.
于是我转交给你，所以成员。为什么，当大多数的文件，甚至微软，状态一个DataReader速度更快是我们的测试显示并非如此。
So I turn to you, members of SO. Why, when most of the documentation and even Microsoft, state that a DataReader is faster are our test showing otherwise.
和现在的code：
测试工具：
private void button1_Click(object sender, EventArgs e)
{
System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
sw.Start();
DateTime date = DateTime.Parse("01/01/1900");
for (int i = 1; i < 1000; i++)
{
using (DataTable aDataTable = ArtifactBusinessModel.BusinessLogic.ArtifactBL.RetrieveDTModified(date))
{
}
}
sw.Stop();
long dataTableTotalSeconds = sw.ElapsedMilliseconds;
sw.Restart();
for (int i = 1; i < 1000; i++)
{
List<ArtifactBusinessModel.Entities.ArtifactString> aList = ArtifactBusinessModel.BusinessLogic.ArtifactBL.RetrieveModified(date);
}
sw.Stop();
long listTotalSeconds = sw.ElapsedMilliseconds;
MessageBox.Show(String.Format("list:{0}, table:{1}", listTotalSeconds, dataTableTotalSeconds));
}
这是DAL的DataReader的：
internal static List<ArtifactString> RetrieveByModifiedDate(DateTime modifiedLast)
{
List<ArtifactString> artifactList = new List<ArtifactString>();
try
{
using (SqlConnection conn = SecuredResource.GetSqlConnection("Artifacts"))
{
using (SqlCommand command = new SqlCommand("[cache].[Artifacts_SEL_ByModifiedDate]", conn))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@LastModifiedDate", modifiedLast));
using (SqlDataReader reader = command.ExecuteReader())
{
int formNumberOrdinal = reader.GetOrdinal("FormNumber");
int formOwnerOrdinal = reader.GetOrdinal("FormOwner");
int descriptionOrdinal = reader.GetOrdinal("Description");
int descriptionLongOrdinal = reader.GetOrdinal("DescriptionLong");
int thumbnailURLOrdinal = reader.GetOrdinal("ThumbnailURL");
int onlineSampleURLOrdinal = reader.GetOrdinal("OnlineSampleURL");
int lastModifiedMetaDataOrdinal = reader.GetOrdinal("LastModifiedMetaData");
int lastModifiedArtifactFileOrdinal = reader.GetOrdinal("LastModifiedArtifactFile");
int lastModifiedThumbnailOrdinal = reader.GetOrdinal("LastModifiedThumbnail");
int effectiveDateOrdinal = reader.GetOrdinal("EffectiveDate");
int viewabilityOrdinal = reader.GetOrdinal("Viewability");
int formTypeOrdinal = reader.GetOrdinal("FormType");
int inventoryTypeOrdinal = reader.GetOrdinal("InventoryType");
int createDateOrdinal = reader.GetOrdinal("CreateDate");
while (reader.Read())
{
ArtifactString artifact = new ArtifactString();
ArtifactDAL.Map(formNumberOrdinal, formOwnerOrdinal, descriptionOrdinal, descriptionLongOrdinal, formTypeOrdinal, inventoryTypeOrdinal, createDateOrdinal, thumbnailURLOrdinal, onlineSampleURLOrdinal, lastModifiedMetaDataOrdinal, lastModifiedArtifactFileOrdinal, lastModifiedThumbnailOrdinal, effectiveDateOrdinal, viewabilityOrdinal, reader, artifact);
artifactList.Add(artifact);
}
}
}
}
}
catch (ApplicationException)
{
throw;
}
catch (Exception e)
{
string errMsg = String.Format("Error in ArtifactDAL.RetrieveByModifiedDate. Date: {0}", modifiedLast);
Logging.Log(Severity.Error, errMsg, e);
throw new ApplicationException(errMsg, e);
}
return artifactList;
}
internal static void Map(int? formNumberOrdinal, int? formOwnerOrdinal, int? descriptionOrdinal, int? descriptionLongOrdinal, int? formTypeOrdinal, int? inventoryTypeOrdinal, int? createDateOrdinal,
int? thumbnailURLOrdinal, int? onlineSampleURLOrdinal, int? lastModifiedMetaDataOrdinal, int? lastModifiedArtifactFileOrdinal, int? lastModifiedThumbnailOrdinal,
int? effectiveDateOrdinal, int? viewabilityOrdinal, IDataReader dr, ArtifactString entity)
{
entity.FormNumber = dr[formNumberOrdinal.Value].ToString();
entity.FormOwner = dr[formOwnerOrdinal.Value].ToString();
entity.Description = dr[descriptionOrdinal.Value].ToString();
entity.DescriptionLong = dr[descriptionLongOrdinal.Value].ToString();
entity.FormType = dr[formTypeOrdinal.Value].ToString();
entity.InventoryType = dr[inventoryTypeOrdinal.Value].ToString();
entity.CreateDate = DateTime.Parse(dr[createDateOrdinal.Value].ToString());
entity.ThumbnailURL = dr[thumbnailURLOrdinal.Value].ToString();
entity.OnlineSampleURL = dr[onlineSampleURLOrdinal.Value].ToString();
entity.LastModifiedMetaData = dr[lastModifiedMetaDataOrdinal.Value].ToString();
entity.LastModifiedArtifactFile = dr[lastModifiedArtifactFileOrdinal.Value].ToString();
entity.LastModifiedThumbnail = dr[lastModifiedThumbnailOrdinal.Value].ToString();
entity.EffectiveDate = dr[effectiveDateOrdinal.Value].ToString();
entity.Viewability = dr[viewabilityOrdinal.Value].ToString();
}
这是DAL的数据表：
internal static DataTable RetrieveDTByModifiedDate(DateTime modifiedLast)
{
DataTable dt= new DataTable("Artifacts");
try
{
using (SqlConnection conn = SecuredResource.GetSqlConnection("Artifacts"))
{
using (SqlCommand command = new SqlCommand("[cache].[Artifacts_SEL_ByModifiedDate]", conn))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@LastModifiedDate", modifiedLast));
using (SqlDataAdapter da = new SqlDataAdapter(command))
{
da.Fill(dt);
}
}
}
}
catch (ApplicationException)
{
throw;
}
catch (Exception e)
{
string errMsg = String.Format("Error in ArtifactDAL.RetrieveByModifiedDate. Date: {0}", modifiedLast);
Logging.Log(Severity.Error, errMsg, e);
throw new ApplicationException(errMsg, e);
}
return dt;
}
结果：
有关测试工具中的10次迭代
For 10 iterations within the Test Harness
有关测试工具中的1000次迭代
For 1000 iterations within the Test Harness
这些结果是第二次运行，以减轻因创建连接的差异。
These results are the second run, to mitigate the differences due to creating the connection.
推荐答案
我看到三个问题：
- 您使用DataReader否定它的方式是大的单项式内存的优势通过将其列出，
- 您正在运行显著的生产有利于数据表和方式不同基准的环境
- 您把时间浪费在转换DataReader的记录神器未DataTable中的code复制的对象。
一个DataReader的主要优点是，你不必事事加载到内存中一次。这应该是DataReader的一个巨大的优势，在网络应用程序，其中的内存，而不是CPU，往往是瓶颈，但通过增加每行你否定这是一个通用的清单。这也意味着，你改变你的code只使用一条记录时，即使比较，差异可能无法在你的基准测试显示，因为你的系统上运行他们大量的可用内存，这将有利于数据表。此外，DataReader的版本是花时间分析结果变成神器对象，该数据表还没有完成。
The main advantage of a DataReader is that you don't have to load everything into memory at once. This should be a huge advantage for DataReader in web apps, where memory, rather than cpu, is often the bottleneck, but by adding each row to a generic list you've negated this. That also means that even after you change your code to only use one record at a time, the difference might not show up on your benchmarks because you're running them on a system with lot of free memory, which will favor the DataTable. Also, the DataReader version is spending time parsing the results into Artifact objects that the DataTable has not done yet.
要，变更名单，其中固定DataReader的使用问题; ArtifactString＆GT; 到的IEnumerable＆LT; ArtifactString＆GT; 随处可见，而在您DataReader的DAL改变这一行：
To fix the DataReader usage issue, change List<ArtifactString> to IEnumerable<ArtifactString> everywhere, and in your DataReader DAL change this line:
artifactList.Add(artifact);
这样：
yield return artifact;
这意味着你还需要添加code，它遍历结果到您的DataReader的测试工具让事情变得公平。
This means you also need to add code that iterates over the results to your DataReader test harness to keep things fair.
我不知道如何调整基准创造一个更典型的场景就是公平对待的DataTable和DataReader的，除了要建立两个版本的页面，并在类似的产品 - 成为了每个版本一小时一级负荷，使我们拥有真正的内存pressure ...做一些真正的A / B测试。此外，请确保您覆盖转换数据表行神器......如果说法是，你需要做这样的一个DataReader，而不是一个数据表，这是完全错误的。
I'm not sure how to adjust the benchmark to create a more typical scenario that is fair to both DataTable and DataReader, except to build two versions of your page, and serve up each version for an hour under a similar production-level load so that we have real memory pressure... do some real A/B testing. Also, make sure you cover converting the DataTable rows to Artifacts... and if the argument is that you need to do this for a DataReader, but not for a DataTable, that is just plain wrong.
这篇关于为什么数据表比DataReader的速度更快的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
