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

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开发者_Go百科 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?

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?

I would recommend using an OdbcDataReader along with a Transform function. Something like the following should work:

public class OdbcQuery
{
    OdbcCommand Command { get; set; }

    public OdbcQuery(OdbcConnection connection, string cmdText)
    {
        Command = new OdbcCommand(cmdText, connection); 
    }

    public List<T> Transform<T>(Func<OdbcDataReader, T> transformFunction)
    {
        Command.Connection.Open();

        OdbcDataReader reader = Command.ExecuteReader(CommandBehavior.Default);

        List<T> tList = new List<T>();

        while (reader.Read())
        {
            tList.Add(transformFunction(reader));
        }

        Command.Connection.Close();

        return tList; 
    }
}

Here is an example transform function, that will create an instance of type T for each row in the query. In this case its just a Foo object,

public class Foo
{
    public Foo() { }

    public string FooString { get; set; }
    public int FooInt { get; set; }
}

class Program
{
    public static List<Foo> GetFooList(string connectionString, string cmdText)
    {
        OdbcQuery query = new OdbcQuery(new OdbcConnection(connectionString), cmdText);

        List<Foo> fooList = query.Transform(
            rdr =>
            {
                Foo foo = new Foo();

                foo.FooInt = rdr.GetInt32(0);
                foo.FooString = rdr.GetString(1); 

                return foo; 
            });

        return fooList; 
    }

This should perform well because your domain-specific objects are being created on the first go-round through the database results, no second or third translation phases necessary.

If you are looking for speed do not use datasets/dataadapters they are an old technology not built for speed. Use a datareader like Sean suggested.

In addition to the DataReader you can check if your query is well designed.

Perhaps you can optimize it or event split it into multiple parallel actions