Which object performs best? Sqldatasource or Datatable?

Asked

Viewed 310 times

4

In terms of performance, speed, or even safety, what is the best object to be used as a DataSource?

Example, I’m creating a DataSource, to popular a GridView:

For SqlDataSource:

this.sqlDataSource1.ConnectionName = "sql brayton max";
        this.sqlDataSource1.Name = "sqlDataSource1";
        columnExpression7.ColumnName = "id";
        table3.MetaSerializable = "0|0|125|100";
        table3.Name = "NotaFiscal.Cidades";
        columnExpression7.Table = table3;
        column7.Expression = columnExpression7;
        columnExpression8.ColumnName = "cd_uf";
        columnExpression8.Table = table3;
        column8.Expression = columnExpression8;
        columnExpression9.ColumnName = "ds_cidade";
        columnExpression9.Table = table3;
        column9.Expression = columnExpression9;
        selectQuery3.Columns.Add(column7);
        selectQuery3.Columns.Add(column8);
        selectQuery3.Columns.Add(column9);
        selectQuery3.Name = "NotaFiscal_Cidades";
        selectQuery3.Tables.Add(table3);
        this.sqlDataSource1.Queries.AddRange(new DevExpress.DataAccess.Sql.SqlQuery[] {
        selectQuery3});
        this.sqlDataSource1.ResultSchemaSerializable = resources.GetString("sqlDataSource1.ResultSchemaSerializable");

By Datatable:

DataTable cli = new DataTable();

        string sqconn, _sql;

        sqconn = ConfigurationManager.ConnectionStrings["sql brayton max"].ConnectionString;

        _sql = @"SELECT id,cd_uf,ds_cidade FROM NotaFiscal.Cidades";

        SqlConnection con = new SqlConnection(sqconn);

        try
        {
            SqlCommand cmd = new SqlCommand(_sql, con);

            con.Open();

            cmd.CommandType = CommandType.Text;

            SqlDataAdapter da = new SqlDataAdapter(cmd);

            da.Fill(cli);
        }
        catch
        {

        }
c# sql .net performance ado.net

2 answers

4


It actually depends. I would say that using a DataSource (will use with DataReader, right? ). The way to use one or the other will determine more about performance. DataTable is a very complex object and many people don’t even use it anymore. If using wrong anyone can be slow.

The only way to know is to prepare tests according to what you will use and check on your own according to your need. It always is, if it makes a difference, because it probably won’t. Performance problems are solved with specific techniques, hardly a technology will make so much difference (it even does, but in extreme cases, when everything else has been done).

  • I understand. now I have a question, today I use the Datatable, for popular some Gridview, it is better to use a Datareader with Dictionary?

  • So it depends on what you need. The DataTable may be easier, but slightly slower. Does it matter? I don’t think so. I’ve never used it.

  • Perfect, I’m using cloud bench, so every second is questionable, I’ll perform some tests thank you.

  • 1

    We’re not talking second, maybe not millisecond.

  • Right, a doubt, I found a page in which describes very detailed this doubt, it would be better to just leave the link? or make a response by placing the data source?

  • 1

    Depends :P Not seen, can put in question, can make an answer (as long as it is not only the link)

  • @Thomaserichpimentel Datareader has a very specific function which is to process a large volume of data, just ahead, read-only. It is fast in this role because you can start processing records before they have even been fully returned from the database, and you don’t need a RAM the size of the amount of records because they can be discarded once processed. Datatable has a completely different function: to work with all data in memory, in a disconnected way. Using datareader p/ sending all records to a grid makes no sense.

Show 2 more comments

2

The question is performance/speed? So the answer is NONE.

Both are extremely slow and expensive to be allocated, solved, populated, read, etc. They are very complete objects, but with complex metadata.

The best? Lists! Definitely.

Access your database only with ADO.NET, using only one IDataReader. Populate your list and finish the Datareader and pass your list to GridView.DataSource.

I wrote an article about it: Datatable - The Hidden Enemy. Grimace title, but the content is very relevant. All explaining why one should avoid using Datatable.

Browser other questions tagged c# sql .net performance ado.net

You are not signed in. Login or sign up in order to post.