First off I want to thank Frans for giving me some suggestions on solving this problem, here is what I ended up with if anyone else is interested.

Our issue was pretty simple we have just under 2k stored procs, doing a catalog refresh in some cases was taking upwards of 1 hour, I'm not sure why it was sometimes this high other than some exponential memory growth during the refresh process, but even in best cases it was taking 10-20 minutes.

The portion of the refresh that was taking so long was the part that determines how many result sets are returned from a stored procedure, to mark it as an Action, or Retrieval and in the case of a retrieval is it a Data Table or a Data Set.

What I've done is put in a caching mechanisim with Frans help in where to put it, basically what it does, is keeps a serialized SortedList on the hard drive for each database the key is the procedure name, the value is the number of result sets returned.

The code reads the cache, then cycles through the procedure list like normal, but now checks this cache before hitting the database, if it finds it, it uses the cached value, if its not found, it makes the long call to SqlServer then adds it to the cache. Once done, it re-writes the cache back out for future use.

This process does not remove old stored procedures that have been deleted, nor does it catch a situation where an action procedure is changed to a retrievial and vice versa. If you have this scenario you simply delete the cache file on disk (dbo.dbname.sproccache.dat) in the llblgen folder, and next time llblgen is run it will update all the procs.

I have a simple grid that reads the cache file and allows me to delete an individual entry, but I have not included it as its really like 20 lines of code, and if you need something similar, I'm assuming you can whip one up too. =)

Oh I spent like 20 minutes on this so if there is an obvious error sorry =P and also I am only giving the code for this, not a compiled DLL.

Grab the SDK, open up the SqlServerDBDriver project in the Drivers solution and find the RetrieveAmountOfResultsetsPerStoredProcedure method in SqlServerSchemaRetriever and replace it with the following code.

You will also need to add the references: using System.IO; using System.Runtime.Serialization; using System.Runtime.Serialization.Formatters.Binary;

Compile the driver, save off a copy of Frans original DLL, and copy this one over it and your done.

Enjoy. John

        /// <summary>
        /// Retrieves the amount of resultsets of all stored procedures found. This can be time consuming. The routine will reset the
        /// subtask progress view and will step through the stored procedures reflecting each step. The technique to determine the
        /// schema of the resultset is the Fill() call of the data-adapter plus the usage of SET FTMONLY ON. When this option is set to ON on 
        /// an active connection, no procedure processing is done.
        /// </summary>
        /// <param name="openSqlConnection">Open SqlConnection object to use for schemadata retrieval</param>
        private void RetrieveAmountOfResultsetsPerStoredProcedure(SqlConnection openSqlConnection)
        {
            // Read any existing definitions
            SortedList ProcedureList;
            FileStream CacheFile = null;
            string CacheFileName = _currentSchema.SchemaOwner + "." + openSqlConnection.Database + ".SProcCache.soap";
            BinaryFormatter BinFormat = new BinaryFormatter();
            try
            {
                CacheFile = new FileStream(CacheFileName, FileMode.Open);
                ProcedureList = (SortedList)BinFormat.Deserialize(CacheFile);
            }
            catch (Exception ex)
            {
                ProcedureList = new SortedList(_currentSchema.StoredProcedures.Count);
            }
            finally
            {
                if (CacheFile != null)
                    CacheFile.Close();
            }


            // for all stored procedures found, retrieve their resultset.
            base.SubTaskProgressInitHandler(_currentSchema.StoredProcedures.Count);
            IDBStoredProcedure currentStoredProcedure=null;

            // connection is open, first execute the SET FMTONLY ON command
            SqlCommand command = new SqlCommand("SET FMTONLY ON", openSqlConnection);
            command.CommandType = CommandType.Text;
            bool fmtOnlySwitchedOn=false;
            try
            {
                command.ExecuteNonQuery();
                fmtOnlySwitchedOn=true;

                // it is now safe to call stored procs over this connection 
                command.CommandType = CommandType.StoredProcedure;

                ArrayList procsToRemoveFromSchema=new ArrayList();
                for(int i=0;i<_currentSchema.StoredProcedures.Count;i++)
                {
                    try
                    {
                        currentStoredProcedure = (IDBStoredProcedure)_currentSchema.StoredProcedures.GetByIndex(i);
                        currentStoredProcedure.AmountResultsets=0;  // initialize to 0, so when an exception is caught the stored procedure will be seen as a procedure without a resultset.

                        base.SubTaskProgressTaskStartHandler("Determining resultset column definitions of stored procedure:" + Environment.NewLine + currentStoredProcedure.StoredProcedureName);

                        command.CommandText = String.Format("[{0}].[{1}]", currentStoredProcedure.ContainingSchema.SchemaOwner, currentStoredProcedure.StoredProcedureName);
                        command.CommandType = CommandType.StoredProcedure;

                        // lookup procedure in cache
                        if (ProcedureList.ContainsKey(currentStoredProcedure.StoredProcedureName))
                        {
                            // the amount of tables == to cache value
                            currentStoredProcedure.AmountResultsets = (int)ProcedureList[currentStoredProcedure.StoredProcedureName];
                        }
                        else
                        {
                            ProcedureList.Add(currentStoredProcedure.StoredProcedureName, 0);
                            // Create the parameters. These will be filled with NULL. 
                            command.Parameters.Clear();
                            CreateEmptyParameters(ref currentStoredProcedure, ref command);

                            SqlDataAdapter adapter = new SqlDataAdapter(command);
                            DataSet resultsets = new DataSet("ResultsetSchema");

                            adapter.Fill(resultsets);

                            // the amount of tables == the amount of resultsets
                            currentStoredProcedure.AmountResultsets=resultsets.Tables.Count;
                            ProcedureList[currentStoredProcedure.StoredProcedureName] = resultsets.Tables.Count;
                        }

                        base.SubTaskProgressTaskCompletedHandler();
                    }
                    catch(SqlException ex)
                    {
                        StringBuilder exceptionMessage = new StringBuilder();

                        for (int j=0;j < ex.Errors.Count; j++)
                        {
                            exceptionMessage.Append("Index #" + j + "\n" +
                                "Message: " + ex.Errors[j].Message + "\n" +
                                "Number: " + ex.Errors[j].Number.ToString() + "\n" + 
                                "Native Error Code: " + ex.Errors[j].State.ToString() + "\n" +
                                "Source: " + ex.Errors[j].Source + "\n");
                        }

                        // All exceptions will lead to the stored procedure to be an action stored procedure.
                        ((DBSchema)_currentSchema).LogError(ex, "Stored procedure '" + currentStoredProcedure.StoredProcedureName + 
                                "' caused an SqlServer error " + ex.Errors[0].State + 
                                ". This stored procedure is no longer reported as having a resultset.", "SqlServerSchemaRetriever::RetrieveAmountOfResultsetsPerStoredProcedure");
                        ((DBSchema)_currentSchema).LogError(ex, exceptionMessage.ToString(), "SqlServerSchemaRetriever::RetrieveAmountOfResultsetsPerStoredProcedure");
                        base.SubTaskProgressTaskCompletedHandler();
                    }
                    catch(Exception ex)
                    {
                        // serious error, can't die, log it, mark the stored procedure as being an action procedure.
                        ((DBSchema)_currentSchema).LogError(ex, "Stored procedure '" + currentStoredProcedure.StoredProcedureName + 
                            "' caused an exception " + ex.Message +
                            ". This stored procedure is no longer reported as having a resultset.", "SqlServerSchemaRetriever::RetrieveAmountOfResultsetsPerStoredProcedure");
                        base.SubTaskProgressTaskCompletedHandler();
                    }
                }

                // remove the procs to remove from the schema
                for (int i = 0; i < procsToRemoveFromSchema.Count; i++)
                {
                    _currentSchema.StoredProcedures.Remove(procsToRemoveFromSchema[i]);
                }
            }
            finally
            {
                // switch FMTONLY OFF
                if(fmtOnlySwitchedOn)
                {
                    if(openSqlConnection.State==ConnectionState.Open)
                    {
                        command = new SqlCommand("SET FMTONLY OFF", openSqlConnection);
                        command.CommandType = CommandType.Text;
                        command.ExecuteNonQuery();
                        fmtOnlySwitchedOn=false;
                    }
                }
            }

            // write out new cache
            try
            {
                CacheFile = new FileStream(CacheFileName, FileMode.OpenOrCreate);
                BinFormat.Serialize(CacheFile, ProcedureList);
            }
            finally
            {
                if (CacheFile != null)
                    CacheFile.Close();
            }
            // done
        }

Thanks for posting this workaround, John!