subsonic - collection load versus query

Question

I am setting the data source of a datagridview to a subsonic collection

        TcolorCollection tc = new TcolorCollection().Load();
        dataGridView1.DataSource = tc;

and I've noticed that the previous code is much (way to much) slower then the following

        SubSonic.Query q3 = new SubSonic.Query("tcolor");
        q3.QueryType = SubSonic.QueryType.Select;
        IDataReader reader = q3.ExecuteReader();
        DataTable dt = new DataTable();
        dt.Load(reader);
        dataGridView1.DataSource = dt;

I would like to use the collection class and it's connectivity, but I need a way for the .Load() to be faster.

fyi the table I am using has 8000+ records. Not small, but not huge either.

Answer 1

Couple of thoughts...

If you have to use a DataSet/DataTable you could just do this:

grid.DataSource=new Select().From("tcolor").ExecuteDataSet();

Both of the things you show above use the same core bits - not sure why one is slower than the other.

Answer 2

The TcolorCollection().Load() creates 8000 Tcolor objects and add's them to the list. From my experience it is not a good idea to use a collection if you expect more than 1000 records.

In this case, as Rob suggested, ExecuteDataSet() is way faster.

Or you could use ExecuteTypedList() to map the query to your own poco class and decorate it with the SubSonic features you need.

e.g.

public class TestPoco()
{
    public int Id {get;set;}
    public String Name {get;set;}

    public bool IsNew { get { return Id == 0 } };
    public void Delete()
    {
        // Test is an SubSonic IActiveRecord Object
        Test.Destroy(Id);
    }
}

private void MyTask()
{
    List<TestPoco> list =
          DB.Select("Id", "Name").From<Test>().ExecuteTypedList<TestPoco>();

   foreach (var item in list)
   {
       if (item.Name.Contains("acme"))
           item.Delete();
   }
}

should be pretty close in execution time to native DB access.