PRB: Slow Inserts Using SQLOLEDB Provider When No Index on SQL7.0 Table

When you are using the Microsoft Foundation Class Library (MFC) AppWizard or ClassWizard to generate an Active Template Library (ATL) consumer template class wrapper for a table in your database, a class derived from CCommand<CAccessor<TAccessor> > is created.

When calling Insert directly on this derived class object to insert a large number of records (for example, 10,000 records), the performance when using SQL OLE DB (SQLOLEDB) Provider is slower than when using ODBC (MSDASQL) Provider if the table does not have a primary key or unique index.

When SQL OLE DB Provider is used, and when calling Insert on the derived class object (for example, CRowset::Insert), SQL server treats each insertion as a "positioned" update so that an API cursor (sp_cursor) is generated and used to perform the insert. The insertion statement is not prepared. Without indexing on the table, large numbers of insertions will downgrade the performance considerably. The nonlinear performance degradation is caused by a bug in SQL Server 7.0. For additional information, click the article number below to view the article in the Microsoft Knowledge Base: However, if ODBC provider is used, when the Insert call reaches SQL server, it will prepare the insert statement and then use it to perform the subsequent insertions, which is very efficient.

SQL Server 7.0 profiler can be used to verify the different mechanisms for insertion between these two providers. With SQL OLE DB provider, from the trace log, sp_cursor is created; whereas with ODBC provider, sp_prepare and sp_execute are used when performing insertions.

If AppWizard- or ClassWizard-generated code is used and the Insert call is made on the derived class object (for example, CRowset::Insert is called), and if you need to perform a large number of insertions on a SQL 7.0 table without a unique index, Microsoft recommends that you use ODBC provider for relatively better performance.

However, you may also want to consider the following techniques to improve the performance of large numbers of insertions, depending upon your needs:

• Use a direct insert statement, INSERT INTO table1 VALUES(?, ?, ?). In this case, a parameter accessor is required. Because the primary concern is to perform the insert, there no need to return a CRowset. Please see the following sample code for an example of this approach:

  //CUsers is the parameter Accessor.
  CCommand<CAccessor< CUsers> > users;
  //Omit some code for simplicity.
  HRESULT hr = users.Create(session, _T("insert into users values(?,?,?)")) ;
  //Populate the necessary parameters,
  //then call users.Open() to execute the command.
  						

  One advantage of this approach is that you don't have to call CCommand::Prepare directly on the insert statement. Because "?" is used, an internal mechanism of SQL 7.0 does the prepare automatically for you.
• Put the parameterized insert statement into a SQL stored procedure and call CCommand::Prepare on it. For additional information and for sample code, click the article number below to view the article in the Microsoft Knowledge Base:
  240412  (http://support.microsoft.com/kb/240412/EN-US/ ) HOWTO: ExecuteParameterized Command Multiple Times with ATLOLEDB consumer
• Use the IRowsetFastLoad interface. This interface is primarily intended for doing bulk copy through SQLOLEDB provider. This approach might be considerably faster than the other methods mentioned. However, one disadvantage of this approach is that once a failure occurs, a manual recovery is required. See the SQL Server Help for detailed documentation of this interface. Sample code for this method is given in a Microsoft Knowledge Base article mentioned in the "References" section of this article.

NOTE: Whatever technique you use, if you have a really large number of insertions, and if the speed is the ultimate goal, you may want to put the transaction around the insertions, which saves tremendous round-trip communication time between the client application and SQL server. Also, by using the transaction, you save time because SQL Server writes a single entry for the whole transaction instead of one for each insertion.

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

Steps to Reproduce Behavior

1. Set up the following table on SQL Server:

   	CREATE TABLE [dbo].[TestTable] (  
   		[Id]  uniqueidentifier ROWGUIDCOL  NOT NULL ,
   		[Text1] [varchar] (10) NOT NULL ,
   		[Text2] [char] (10) NOT NULL ,
   		[Int1] [int] NOT NULL ,
   		[Float1] [float] NOT NULL ,
   		[Short1] [smallint] NOT NULL 
   		) ON [PRIMARY]
   	GO
   						

2. Use the ATL wizard to generate an OLE DB consumer. Then add the following code:

   	m_rsTestTable.m_session.StartTransaction();
   	strcpy( m_rsTestTable.m_Text1, "ABCDEF");
   	strcpy( m_rsTestTable.m_Text2, "GHIJKL");
   	m_rsTestTable.m_Int1 = 123456;
   	m_rsTestTable.m_Float1 = 123.456;
   	m_rsTestTable.m_Short1 = 123;<BR/>
   
   	for (DWORD i = 1; i <= 10000 ; i++)
   	{
   		m_rsTestTable.Insert();
   	}
   
   	m_rsTestTable.m_session.Commit();
   						

Compare the time it takes to perform 10,000 insertions when SQL OLE DB provider and ODBC provider are used. With SQLOLEDB, the performance is noticeably slower.

For additional information on how to use the IRowsetFastLoad interface, click the article number below to view the article in the Microsoft Knowledge Base: (c) Microsoft Corporation 1999, All Rights Reserved. Contributions by Thomas Klevmar, Microsoft Corporation.