Which object performs best? SqlDataSource or DataTable?

3

In terms of performance, speed, or security, what is the best object to use as a DataSource ?

Example, I am creating a DataSource , to popular a GridView :

By 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
        {

        }
    
asked by anonymous 27.07.2016 / 15:35

2 answers

4

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

The only way to know is to prepare tests according to what you will use and verify on your own according to your need. It's always that way. If you make a difference, you probably will not. 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 already been done.)

    
27.07.2016 / 15:51
1

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

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 IDataReader . Populate your list and end the DataReader and pass your list to GridView.DataSource .

I wrote an article about this: DataTable - The Hidden Enemy . Title grimace, but the content is very relevant. Everything explaining why one should avoid using DataTable .

    
28.07.2016 / 10:53