SubSonic Forums
All Your Database Are Belong To Us

Slow performance with new Select (ParamArray of Collumns) and Execute as Collection

Latest post 08-27-2008 3:56 AM by Juergen. 1 replies.
  • 08-27-2008 3:07 AM

    Slow performance with new Select (ParamArray of Collumns) and Execute as Collection

    I use the SubSonic Select Command to query a table with about 20 rows and bind the result to a combobox. The statement looks something like this (sorry, VB Code):

    ComboBox1.DataSource = New [Select]() _
                        .From(TableObject.Schema).OrderAsc(TableObject.NameColumn.PropertyName) _
                        .ExecuteAsCollection(Of TableObjectCollection)()

    Execution Time: about 2 ms

    Today I thought I could improve the execution time, if I only query the columns I need (Id, Name) and changed the statement to

    ComboBox1.DataSource = New [Select](TableObject.IdColumn, TableObject.NameColumn) _
                        .From(TableObject.Schema).OrderAsc(TableObject.NameColumn.PropertyName) _
                        .ExecuteAsCollection(Of TableObjectCollection)()

    Execution Time 300 ms

    As you can see, the amount of time increases, I expected the opposite.
    If I change the code to ExecuteDataSet the Performance is good again:
    ComboBox1.DataSource = New [Select](TableObject.IdColumn, TableObject.NameColumn) _
                        .From(TableObject.Schema).OrderAsc(TableObject.NameColumn.PropertyName) _
                        .ExecuteDataSet()

    Execution Time: 2 ms

    So I thought the problem could be related to the generation of the sql code, but for every statement the code:
    dim s as [Select] = new [Select]....
    dim sql as string = s.BuildSqlStatement

    renders in no time.
    I am a little confused about this.

    Oh, just to mention: MySql MYISAM Table, SubSonic DAL generated in C#, Gui in VB, SubSonic 2.1.1 from trunk

  • 08-27-2008 3:56 AM In reply to

    Re: Slow performance with new Select (ParamArray of Collumns) and Execute as Collection

    Update:

    I found out that the execution only takes 300ms in debug mode, this is because a "System.IndexOutOfRangeException" occures during execution of dataReader[col.ColumnName] for every column that is not used in the query:
                foreach(TableSchema.TableColumn col in BaseSchema.Columns)
                {
                    try
                    {
                        SetColumnValue(col.ColumnName, dataReader[col.ColumnName]);
    ....

    RecordBase.cs, ~ line 820): function Load(IDataReader dataReader)

    Don´t know how to realize this (the dataReader has not .Contains Method), but SetColumnValue should only be executed for columns that are in the reader.

Page 1 of 1 (2 items) | RSS