OleDb performance change?

Question

I'm currently trying to use OleDb / JET to export SQL Server data to an Excel 2003 file. At first I encoded the export in a console application and it worked quite fast. However, executing the same code during an ASP.Net request takes about three times as much. In particular, it slows down during a call to OleDbCommand.ExecuteQuery, which inserts a record into an Excel file.

The only difference between the two literally is that it works in a console application and the other works in IIS. In addition, both applications:

• works on my development machine
• the same code works as below, from the common dll library, which refers to both applications
• connecting to the same database with the same connection string
• using the same exact selection operator with the same exact parameter values
• creating a completely new file in the same place on my development machine and writing to it
• generating byte-byte-identical files

Is there something inherently slow when using OleDb in ASP.Net that I don't know about?

UPDATE: This is the code in question. The import command used both in the console application and on ASP.Net sites is identical. They both connect to a database that is local to my machine, and both applications run 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 may be worth noting that Microsoft advises you not to use ACE in ASP.NET . Perhaps they know something about an implementation that is not documented?

The redistributable Access Database Engine 2010 Redistributable is not intended ... for use by a system service or server program where the code will run under a system account, or will process multiple user IDs at the same time or be very reentrant and expect stateless behavior. Examples include a program that starts from the task scheduler when no user is logged on, or a program called from a server-side web application, such as ASP.NET, or a distributed component running under COM + services.

But if you are going to continue this path, I would suggest getting rid of your application as an intermediary and request INSERT source data directly. ACE must support this syntax :

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

Answer 2

After your comment that this is called from a button handler on an aspx page, I think the extra delay comes from blocking the session.

Even if you are not using a session, the session blocks the entire request until this work is completed and the page ends. This lock may delay the request, and if you make two or three calls of the same routing, one of them will block the other, but also, if you see other pages, blocking the session will add an additional delay.

To avoid blocking this session that has just been disabled in the page declaration, set EnableSessionState="false"

Look also at

call an aspx page to return the image randomly slower

Replacing an Entire ASP.Net Session

Answer 3

Guess it can clear your mind.

performance errors

asp-net-slower-than-console-application