C ++ merging OCI/occi is 10 times faster than C ++ merging ado

Introduction

OracleIt is widely used in many fields with its superior stability and excellent performance. Its high performance isOracleBetter than othersDatabase. However, terminal applications are affected.ProgramPerformance is not justOracleService programs, hardware environments, application development languages, and development methods also have an important impact.

Currently, it can be developed in multiple development languagesOracleApplications can be developed in advanced development languages such as VB and Delphi. They can be developed in C ++ or Java, it can even be accessed through scripting languages such as ASP, JSP, and Perl.Oracle. These Language Environments have their own advantages. c ++ is undoubtedly the first choice for the ultimate speed.

In C ++, we can also use multiple interface methods.ADOCommon interface, which can be usedOCI/Occi. You can also use ODBC, oo4o, and oledb. In these methods,ADOAndOCIThis article focuses on the development methods of these two interfaces in Visual C ++ 6.0 and the comparison of their features.

1. Introduction to ADO APIs

ADO(ActiveX Data Objects, ActiveX Data Object) is a programming interface at the application layer designed by Microsoft for the powerful data access interface oledb (Object link and embedded database. UseADOApplications can access and operate data on the database server through the oledb provider.ADOThe main advantage is the ease of use and various accesses.DatabaseAnd can be developed in multiple languages.

BecauseADOBased on automationADOIs widely used, not only in Visual Basic and other advanced language development environments, but also in some scripting languages.Web ApplicationsAnd scripts on ASP (Active Server Page, dynamic server homepage)CodeTo access the database.

ADOAccessDatabaseSee figure 1.

Figure 1ADOModel

We can see thatADOIt is based on ole db and encapsulates ole db.ADOIt is actually an application layer interface of ole db, which is an intermediate layer between ole db and applications. This structure provides good scalability for consistent data access interfaces, and is no longer limited to specific data sources, as long as the data sources supported by ole db,ADOCan be well supported.

ADOIs easy to use. It encapsulates the complex interfaces of ole db, it is built with three subject objects: connection, recordset, and command, and four set objects: errors, properties, parameters, and fields.ADOFor the object model, see:

Figure 2ADOObject Model

Through these seven objects and their methods, we can complete the vast majority of database access tasks to achieve our application goals.

Introduction to OCI APIs

OCI (Oracle call interface) YesOracle DatabaseThe interface calledOracleProvides C/C ++ development class libraries for developing front-end applications and middleware. PassOCITo control the execution of all types of SQL statements, including DDL, control statements (transactions, sessions, systems), queries, DML, PL/SQL, and embedded SQL.OCIMaximum control over program running and executionOracleThe server allows many database operations and can accessOracle DatabaseAll data types in the server, including scalar value, set, and instances of all object types, can access the object and its metadata as a reference, and can dynamically obtain and modify the attribute values of the object.

OCIPrograms not only have SQL non-procedural advantages, but also have the procedural and flexibility of the third generation programming language, so that the developed programming technology has stronger data processing capabilities.

At the same time,OCIRunOracleThe platform has good portability, and the program can run in Unix/Linux/Windows or even embedded environments without too much modification.

OCIOr otherOracleUnderlying implementation of the development interface, as shown in figureADOAnd JDBC are all inOCIEncapsulation of the upper layer, becauseOCIIs a relatively low-level interface, with a lot of layers of encapsulation, can provide applications andOracle, SoOCIIt provides the best performance. Based on this,OracleSome of its own tools and many famousOracle DatabaseTools are also usedOCIDeveloped, such as the famous toad. The author's oraexplorer is also based onOCIInterface development.

However, as C/C ++ is difficult to learn, OCI development is more difficultADODevelopment interfaces such as JDBC are much larger than each other. Each function of OCI has many parameters and is hard to understand. In China, there are very few learning materials on OCI development, therefore, programmers who use OCI to develop applicationsADOThe number of programmers is much smaller.

3. Comparison Experiment Design

This article will compare it with a specific experiment.ADOAndOCIPerformance gap. The performance of the database access interface is mainly reflected in two aspects: Data Reading Speed and Data Writing speed.ADOAndOCIEach program develops a visual C ++ 6.0 Application to perform these two experiments. Each program completes the following functions:

1) establish a connection

First, you need to connect to the Oracle server. In this step, you need to provide Sid, user name, and password information.

2) Data Initialization

To make the experiment universal and easy for readers to reproduce, this experiment is operated directly under the System user. A table with a large number of records is required. First, the all_objects table image is used to generate the my_objects table, then, perform five recursive images on the my_objects table itself to generate a table with a million records at most. The related SQL statements are as follows:

Truncate table my_objects; Drop table my_objects; Create Table my_objects as select * From all_objects; Insert into my_objects select * From my_objects; Insert into my_objects select * From my_objects; Insert into my_objects select * From my_objects; Insert into my_objects select * From my_objects; Insert into my_objects select * From my_objects;

3) Data Reading

Traverse the data table, read the value of the object_name field, and record the time spent.

4) Data Writing

-- Create a new table Truncate table my_objects2; Drop table my_objects2; Create Table my_objects2 ( Owner varchar2 (30 ), Object_name varchar2 (30 ), Subobject_name varchar2 (30 ), Object_id int, Data_object_id INT );

Write 0.1 million records to the table and record the time spent.

5) Clear the environment

Release memory and disconnect.

4. key code of ADO Program

First, add the following code in stdafx. h:

// Eliminate unnecessary warnings # Pragma warning (Disable: 4146 4244) // Import the ADO application library and rename the namespace and EOF # Import "C: \ Program Files \ common files \ System \ ADO \ msado15.dll "\ Rename_namespace ("myado") Rename ("EOF", "endoffile ") The following is the key implementation code: // Initialize the connection object HR = m_pconnection.createinstance (_ uuidof (myado: Connection )); // Initialize the record set object M_prs.createinstance (_ uuidof (myado: recordset )); // Set connection Parameters Cstring strconnection; Strconnection. Format (_ T ("provider = oraoledb. oracle.1 ;" "Password = % s ;" "User ID = % s ;" "Data Source = % s "), Strpwd, struser, strsid ); // Connect to the database M_pconnection-> open (_ bstr_t) strconnection, _ T (""), _ T (""),-1 ); // Execute the SQL statement Strsql. Format ("truncate table my_objects "); M_pconnection-> execute (_ bstr_t (strsql), null, myado: ad1_text ); // Open the record set Strsql. Format ("select object_name from my_objects "); M_prs-> open (_ bstr_t) strsql, M_pconnection.getinterfaceptr (), Myado: adopenforwardonly, Myado: adlockreadonly, Myado: ad1_text ); // Traverse and read data While (! M_prs-> endoffile) { Varvalue = m_prs-> fields-> getitem (long (0)-> getvalue (); M_prs-> movenext (); } Strsql. Format ("insert into my_objects2 values ('Testtest', 0, 0 )"); // Write data For (INT I = 0; I <100000; I ++) { M_pconnection-> execute ( _ Bstr_t (strsql), null, myado: ad1_text ); } // Close the connection M_pconnection-> close ();

The key codes of OCI are not listed for long.

In this tutorialSource code, Executable programs and OCI executable programs can be downloaded to the http://www.snowywolf.net, the source code of OCI due to technical confidentiality issues, can not be made public, please forgive me.

5. Experiment results

To eliminate network impact, Oracle servers and applications run on the same laptop.

• Hardware environment
• CPU: Intel dual-core 2.0g
• Hard Disk: 7200 to 100 GB
• Memory: 1 GB
• Software Environment
• Operating System: WINXP SP2
• Database: Oracle 9.2.0.1.0

The number of lines of code in the statistical result is the number of valid lines of code after the common code is removed.

Vi. Comparison and Analysis of ADO and OCI

1. Functions

In order to maintain its versatility, ADO has good support for Ms sqlserver, and has no way to provide excellent functions for other databases, just like Oracle. By accessing Oracle through ado, You can implement common functions, such as DML, DDL, query, and transaction control, however, special elements such as objects, sets, and lob in Oracle cannot be well supported, and PL/SQL is not completely supported. OCI has a significant advantage in this regard. It can fully support all Oracle functions. It can be said that it is the most complete development interface for Oracle functions. OCI supports multiple operating systems, while ADO can only run on Windows operating systems.

But in terms of support, ADO has a great advantage: on the one hand, it supports almost all popular databases, such as MS sqlserver, Oracle, Sybase, db ii, etc, write the code for accessing a database. You can modify it to support another database. This is a huge advantage of ADO. On the other hand, it also supports almost all development environments currently, you can use ADO to access the database whether using VC or VB, or even using Delphi or ASP.

2. Performance

From the experiment results, we can see that OCI also has a great advantage in terms of performance. The speed of reading data is more than four times faster, and the speed of writing data is more than one hundred times faster.

Because OCI saves the middle encapsulation layer between the application and the Oracle server, it can directly access data. In addition, OCI is a C interface that can be directly manipulated internally, so its access speed is very fast, system resources and network resources are also much less occupied.

In my development experience, the performance advantage of OCI is more obvious for binary data stored in Oracle (such as spatial data and image data) and long string data ..

3. Development difficulty

In this regard, the advantages of ADO are obvious. First, it is difficult to learn ADO. ADO is encapsulated with object-oriented ideas. Its objects and methods are both easy to learn and use, in addition, the learning materials of ADO are rich, and a large number of documents and source code can be found on the Internet; while OCI is a process-encapsulated development interface, although it does not have many functions, however, the relationship between functions is complex, and many function parameters are involved. Many functions have more than a dozen parameters, which is difficult to understand. In addition, there are few programmers using OCI in China, and there are very few available references, so it is very difficult to learn OCI.

On the other hand, the program developed by OCI has much more code than ado, and the relationship between codes is complex and difficult to maintain.

In order to solve this problem, the author encapsulated the OCI-based idea, simplified the OCI development model, and closed the OCI exception handling mechanism, the difficulty of OCI development is greatly reduced, and the amount of code can be close to that of ADO and easier to maintain. This part of content does not belong to the scope of this article, so it is not discussed here.

VII. Conclusion

ADO and OCI are two common interfaces used to develop Oracle Applications in VC. They have their own characteristics, unique advantages, and limitations, in the development process, we need to select which development interface to use based on the actual situation.

If an application needs to access multiple databases, both the data stored in Oracle and the data stored in ms SQL Server, ADO is the best choice, it can use a set of code to access multiple databases.

If the application only needs to access the Oracle database and has high performance requirements, OCI is the best choice.