Varying OleDb performance?

Question

I'm currently trying to use OleDb/JET to export SQL Server data to an Excel 2003 file. I initially coded the export in a console application, and it worked fast enough. However, running the same code during an ASP.Net request takes roughly three times longer. Specifically, it's slowing down during a call to OleDbCommand.ExecuteQuery, which is inserting the record to the Excel file.

The only difference between the two is literally that one is running in a console app and the other is running in IIS. Other than that, both applications are:

• running on my development machine
• running the same code as below from a shared dll that both applications reference
• connecting to the same database with the same connection string
• using the same exact select statement with the same exact parameter values
• creating a brand new file in the same location on my development machine and writing to it
• generating byte-for-byte identical files

Is there something inherently slow when using OleDb in ASP.Net that I'm unaware of?

UPDATE: This is the code in question. The import command used in both the console application and ASP.Net sites are identical. They are both connecting to a database that is local to my machine, and both applications are running on my machine.

public void Convert(IDbCommand importCommand, string savePath, string sheetName)
{
    var excelConnString = new OleDbConnectionStringBuilder();
    excelConnString.Provider = "Microsoft.ACE.OLEDB.12.0";
    excelConnString.DataSource = savePath;
    excelConnString["Extended Properties"] = "Excel 8.0;HDR=Yes";

    using (var dr = importCommand.ExecuteReader())
    {
        var columnCount = dr.FieldCount;

        using (var oleConn = new OleDbConnection(excelConnString.ToString()))
        {
            oleConn.Open();
            var headers = new string[columnCount];
            var formattedHeaders = new List<string>();
            var qs = new List<string>();

            var insertCmd = oleConn.CreateCommand();

            for (var curCol = 0; curCol < dr.FieldCount; curCol++)
            {
                var name = dr.GetName(curCol);
                headers[curCol] = name;
                formattedHeaders.Add("[" + name + "]");
                qs.Add("?");
                insertCmd.Parameters.Add(name, OleDbType.LongVarChar, 20000);
            }

            using (var cmd = oleConn.CreateCommand())
            {
                cmd.CommandText = string.Format("create table {0}({1})", sheetName,
                                                string.Join(",", formattedHeaders.Select(x => x + " longtext")));
                cmd.ExecuteNonQuery();
            }

            insertCmd.CommandText = string.Format("insert into {0} ({1}) values ({2})", sheetName, string.Join(",", formattedHeaders), string.Join(",", qs));
            insertCmd.Prepare();

            var values = new object[columnCount];

            while (dr.Read())
            {
                dr.GetValues(values);
                for (var i = 0; i < columnCount; i++)
                {
                    insertCmd.Parameters[headers[i]].Value = values[i];
                }
                insertCmd.ExecuteNonQuery();
            }
        }
    }
}

Answer 1

Sounds like you need a profiler.

It's probably worth noting that Microsoft tell you not to use ACE in ASP .NET. Perhaps they know something about the implementation that's not documented?

The Access Database Engine 2010 Redistributable is not intended ... (to) be used by a system service or server-side program where the code will run under a system account, or will deal with multiple users identities concurrently, or is highly reentrant and expects stateless behavior. Examples would include a program that is run from task scheduler when no user is logged in, or a program called from server-side web application such as ASP.NET, or a distributed component running under COM+ services.

But if you're going to continue down this road, I would suggest getting rid of your application as the middleman and having the INSERT query the source data directly. ACE should support this syntax:

SELECT *
FROM ExternalTable IN '' [ODBC;Driver={SQL Server}; Server=ServerName; Database=DatabaseName; Trusted_Connection=Yes]