is there any faster way to populate a resultset from a database query?

Question

i have the following code that queries a db and populates a datatable.

private DataTable Run(string sql)
    {
        var conn = new OdbcConnection();

        string connString = "[myConnString];";

        conn.ConnectionString = connString;
         conn.Open();

        var da = new OdbcDataAdapter {SelectCommand = conn.CreateCommand()};
        da.SelectCommand.CommandText = sql;
        var dt = new DataTable();
        da.Fill(dt);
        da.Dispose();
        conn.Close();

        return dt;
    }

I just ran a profiler on it and it show that it takes a really long time on this line:

 da.Fill(dt);

The query only returns about 1000 rows. Here is the profile detail of whats going on inside .net on this call:

Given that i an running a query, converting it into a datatable and then converting that table to a list of objects, is there anything below that I can do to optimize this (somehow convert from the data adapter to list of objects directly ??). I am basically looking for a workaround to this performance bottleneck in this code?

Answer 1

I think making sure your query runs quickly is the answer. The code cannot be much quicker but optimizing the query can make a huge difference. Can you use SQL profiler and check the execution of the raw SQL query?

For example adding database indexes or returning fewer columns. Network latency can also cause the slowness. Is the database on the same LAN as the code is executing?