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

• Microsoft OLE DB Provider for SQL Server, version 7.0
• Microsoft Visual C++, 32-bit Editions, version 6.0

SYMPTOMS

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.

CAUSE

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:

Q197462 Slow Performance Using Cursor Positioned Update If No PK

RESOLUTION

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:

  Q240412 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.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.

MORE INFORMATION

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(); 

REFERENCES

(c) Microsoft Corporation 1999, All Rights Reserved. Contributions by Thomas Klevmar, Microsoft Corporation.

Additional query words:

Keywords : kbATL kbDatabase kbOLEDB kbSQLServ kbVC600 kbConsumer kbGrpVCDB kbGrpMDAC kbDSupport
Version : WINDOWS:7.0; winnt:6.0
Platform : WINDOWS winnt
Issue type : kbprb