>>A Dictionary should give close to O(1) look up time, whereas an IEnumerable will be O(n). Depending on the size of the list and how often its used, the speed difference is probably negligible. It is easy enough to create the Dictionary though:
>>
Dictionary<string, ColumnSchema> columnTypes;
>>
>> using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.SchemaOnly))
>> {
>>
>> columnTypes = (from DataRow r in reader.GetSchemaTable().AsEnumerable()
>> select new ColumnSchema
>> {
>> ColumnName = (string) r["ColumnName"],
>> ColumnSize = (Int32) r["ColumnSize"],
>> DbType = (SqlDbType) r["ProviderType"],
>> DataType = (Type) r["DataType"]
>> }).ToDictionary(cs=> cs.ColumnName);
>> }>
>I made this change, but as far as I can tell, the speed I was getting last night using iteration by each row remained the same. E.g. the whole complex procedure I am testing takes about ~ 800ms.It's about 3 times quicker than VFP version.
>
>I guess there may be other places for optimization here.
>
>That's my current code for the reference:
>
>
>/// <summary>
> /// Adds table schema to the tableSchema dictionary of all tables schemas
> /// </summary>
> /// <param name="tableName"></param>
> /// <param name="returnMessage"></param>
> private void AddTableSchema(String tableName, ref String returnMessage)
> {
> Logging.LogFormat(3, "Info: Did not find key value for '{0}' in schema dictionary ... Adding now...", tableName);
>
> Dictionary<String, ColumnSchema> columnTypes = new Dictionary<String, ColumnSchema>(StringComparer.OrdinalIgnoreCase);
> String commandText = String.Format("select * from dbo.{0} where 0 = 1;", tableName);
> SqlConnection siriusSqlConnection = this.sqlConnection;
> if (null == siriusSqlConnection)
> return;
>
> using (SqlCommand command = new SqlCommand(commandText, siriusSqlConnection))
> {
> try
> {
> Dictionary<String, ColumnSchema> columns;
>
> using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.SchemaOnly))
> {
> columns = (from DataRow r in reader.GetSchemaTable().AsEnumerable()
> select new ColumnSchema
> {
> ColumnName = r["ColumnName"].ToString(),
> DataType = reader.GetFieldType((int)r["ColumnOrdinal"]),
> DbType = (SqlDbType)(Convert.ToInt32(r["ProviderType"])),
> Precision = Convert.ToInt16(r["NumericPrecision"]),
> Scale = Convert.ToInt16(r["NumericScale"]),
> IsIdentity = r.Field<Boolean>("IsIdentity")
> }).ToDictionary(cs => cs.ColumnName);
> ////DataTable tbl = reader.GetSchemaTable();
> //foreach (DataRow r in tbl.Rows)
> //{
>
> // colSchema.ColumnName = r["ColumnName"].ToString();
> // colSchema.ColumnSize = Convert.ToInt32(r["ColumnSize"]);
> // colSchema.DataType = sqlDataReader.GetFieldType((int)r["ColumnOrdinal"]);
> // colSchema.DbType = (SqlDbType)(Convert.ToInt32(r["ProviderType"]));
> // colSchema.Precision = Convert.ToInt16(r["NumericPrecision"]);
> // colSchema.Scale = Convert.ToInt16(r["NumericScale"]);
> // colSchema.IsIdentity = r.Field<Boolean>("IsIdentity");
> // columnTypes.Add(colSchema.ColumnName, colSchema);
> //}
>
> tableSchema.Add(tableName, new TableSchema(tableName, columns));
> }
> }
> catch (Exception ex)
> {
> returnMessage = ex.ToString();
> }
> }
> }Going from an iterative approach to an equivalent LINQ approach is rarely going to improve speed, and it may even slow it down due to the overhead involved. It does however allow you to easily make it parallel by adding an AsParallel() after AsEnumerable(), which may improve the speed. I don't know if the data reader's GetFieldType method is thread-safe, so that may not be an option. You could also use the appropriate .Field< Type> instead of Convert if they are already the correct type.
Of course you'll need to test if the LINQ statement is actually the slow part of your code.
