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:

enter image description here

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?

How much of that 10 seconds is simply the time to receive the data? Not loading the datatable, just *getting* it. The problem might not be the code, it could be the database, the network, etc. — Anthony Pegram, Jul 08 '11 at 05:11

nbushnell · Accepted Answer · 2011-07-08T05:17:20.093

2

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?

edited Jul 08 '11 at 05:17

answered Jul 08 '11 at 05:10

nbushnell

1,724
1
14
14

score 1 · Answer 2 · answered Jul 08 '11 at 05:27

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.

thanks .. i converted to using your code above. while it makes sense why it would be more efficient than what i am doing above, after profiling it (using dottrace) the overall time is pretty much the same — leora, Jul 08 '11 at 13:40

score 0 · Answer 3 · answered Jul 08 '11 at 05:29

0

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.

answered Jul 08 '11 at 05:29

Biff MaGriff

8,102
9
61
98

score 0 · Answer 4 · answered Jul 08 '11 at 05:33

0

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

answered Jul 08 '11 at 05:33

Boas Enkler

12,264
16
69
143

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

4 Answers4