首页
学习
活动
专区
工具
TVP
发布
社区首页 >问答首页 >为什么DataTable比DataReader更快?

为什么DataTable比DataReader更快?

提问于 2018-04-25 10:13:14
回答 2关注 0查看 830

DataTable始终比DataReader快。我的代码:

测试装置:

代码语言:javascript
复制
    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));
    }

这是DataReader的DAL:

代码语言:javascript
复制
        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();
    }

这是DataTable的DAL:

代码语言:javascript
复制
        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次迭代:

对于测试工具中的10次迭代
对于测试工具中的10次迭代

在测试线束内进行1000次迭代:

在这里输入图像描述
在这里输入图像描述

回答

和开发者交流更多问题细节吧,去 写回答
相关文章

相似问题

相关问答用户
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档