SAMPLE: Sqlfstin: Speed Up OLE DB Inserts By Using IRowsetFastLoad()

The Sqlfstin.exe sample demonstrates how to perform quick, transactional inserts of a large number of records into SQL Server by using the IRowsetFastLoad interface and the ATL OLE DB consumer templates. The sample also shows how to roll back such transactions.

The following file is available for download from the Microsoft Download Center:
Release Date: Feb. 18, 2000

For additional information about how to download Microsoft Support files, click the following article number to view the article in the Microsoft Knowledge Base: Microsoft scanned this file for viruses. Microsoft used the most current virus-detection software that was available on the date that the file was posted. The file is stored on security-enhanced servers that help to prevent any unauthorized changes to the file.

In some instances, the wizard-generated ATL consumer code that uses IRowsetChange may not provide sufficient performance when a large number of inserts are made. To increase the performance, the IRowsetFastLoad interface may be used.

Steps to Run the Sample

1. Run the Setuptable.sql script with SQL Server 7.0 to create the table in the Pubs database.
2. Modify the DBPROP_INIT_DATASOURCE property to make sure that it points to the right datasource. If you don't use Microsoft Windows NT integrated security, you also need to add the property on UID and PWD.
3. Compile and run the application. When the dialog box is displayed, type in the number of records you want to insert into the table (the default is 10,000), and then click either Use SQLOLEDB, Use MSDASQL, or Use IRowsetFastLoad, which uses IRowsetFastLoad to perform the insertions. Near the Use IRowsetFastLoad button is a Commit check box. This check box only affects the IRowsetFastLoad functionality. When selected, the intended functionality is to show how to commit the IRowsetFastLoad transaction by calling CdboTestTable3::FastCommit.
   
   NOTE: If the Commit check box is not selected when using IRowsetFastLoad, the inserts are never committed so the whole transaction is in effect rolled back.
4. To verify the performance difference between the indexed table and the non-indexed table, run the Sqladdindex.sql script to create an index on the table. Rerun the application and compare the results.

Steps to Use the Sample

1. To use the IRowsetFastLoad interface, you must set the SQLOLEDB provider-specific data source property SSPROP_ENABLEFASTLOAD to VARIANT_TRUE. If you use code generated by the wizard, you must manually add this property. See the sample code for details.
2. When you set ENABLEFASTLOAD on the datasource or connection, this property is propagated to the session object. The new session allows the consumer access to the IRowsetFastLoad interface. However, this also means that you cannot open another standard consumer rowset on this session because none of the interfaces exposed by this session object (for information on interfaces, see the SQL books online) supports opening a standard consumer rowset because it requires IDBCreateCommand.
3. You can still open a standard rowset but you cannot reuse the session object. In order to reuse the session object, make the following call, which resets the ENABLEFASTLOAD property on the connection object. The benefit is that thereafter, each newly opened session object will have default session properties without having to re-create the datasource or the connection:

            session.Close();
   	dso.AddProperty(SSPROP_ENABLEFASTLOAD, false);
   	hr = pIDBProps->SetProperties(1, &dso);
   	session.Open(connection);
   						

4. You cannot fast-load into multiple tables at the same time by using IRowsetFastLoad, which is the interface to the SQL Server bulk copy component. You need to commit the previous fast load before you start a new one because the overlapped transaction is not allowed when doing bulk copy.

For additional information, 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.