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