Subsonic queries information schema at runtime

Question

I use subsonic 2.2 in my web application. I have a database with many tables. When the number of foreign keys gets too many the first call to GetSchema hangs for a long time with this query:

SELECT
    FK_Table  = FK.TABLE_NAME,
    FK_Column = CU.COLUMN_NAME,
    PK_Table  = PK.TABLE_NAME,
    PK_Column = PT.COLUMN_NAME, 
    Constraint_Name = C.CONSTRAINT_NAME,
    Owner = FK.TABLE_SCHEMA
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN
    (   
        SELECT i1.TABLE_NAME, i2.COLUMN_NAME
        FROM  INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
        INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
        WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
    ) 
PT ON PT.TABLE_NAME = PK.TABLE_NAME

which I tracked down to the GetTableSchema call in SQLDataProvider.

In my code I am querying a table that keeps track of a table with dynamic columns: (not sure that it matters. just the first query to the DB in the app)

SubSonic.Select select3 = new SubSonic.Select();
SubSonic.SqlQuery 
query3.Where("[MY_TABLE_NAME_IS_PRIVATE]").IsEqualTo("[MY_TABLE_NAME_IS_PRIVATE]");
List<[MY_TABLE_NAME_IS_PRIVATE]> subSonicList3 = query3.ExecuteTypedList<[MY_TABLE_NAME_IS_PRIVATE]>();

When the query3.Where(...) is called the GetSTableSchema gets called (catching it with SQL Profiler)

My first question is: Why is Subsonic looking at the Schema? It built the schema and the classes and relations when it generated my Data layer?

My second question is: can I make it stop? Is this configurable?

from: With SubSonic is there a way to express relationships without foreign keys?

At runtime SubSonic (at least 2.x) does not rely on any real foreign keys to exist. The information schema is only queried during the DAL generation.

Is this true? Is it possible I have something wrong in my config.

I am using ShareDBConnectionScope due to multiple DBs with Similar schemas but some custom tables.

Answer 1

I had the same problem.

I placed this code in my app before I call SubSonic the first time.

This will load the schema from the generated code rather than querying the information_schema at runtime

foreach (var type in this.GetType().Assembly.GetExportedTypes())
{

    if (type.BaseType != null && type.BaseType.IsGenericType && type.BaseType.FullName != null && type.BaseType.FullName.StartsWith("SubSonic.ActiveRecord`1"))
    {
        type.GetMethod("GetTableSchema", BindingFlags.NonPublic | BindingFlags.Static).Invoke(null, null);
    }

}

I also modified the SubSonic Code of MySqlInnoDbDataProvider to avoid InformationSchema Queries during runtime (I prefer an Exception and fix the code)

    /// <summary>
    /// Gets the table schema.
    /// </summary>
    /// <param name="tableName">Name of the table.</param>
    /// <param name="tableType">Type of the table.</param>
    /// <returns></returns>
    public override TableSchema.Table GetTableSchema(string tableName, TableType tableType)
    {

        if (schemaCollection.ContainsKey(tableName))
            return schemaCollection[tableName];

        // Avoid querying the information_schema @ runtime
        if (DataService.Provider.Name == "MyProviderName")
            throw new InvalidOperationException("Querying Information_Schema at runtime is not a good idea. The caller should use DataService.GetTableSchema(tableName, providerName) instead of DataService.Provider.GetTableSchema(providerName) to force the use of the cached TableSchema definition");