KB_Soft Group - Software Vendor's Development Partner
Start page
Site map

Relational DB Schema Reader



In this project, we had to modify the Customer's software product. The product implements active work with different DBMSs. The main of them are MS SQL, MDB, and Oracle. To work with the databases of the listed types, the Customer's program implements a number of standalone architectural components. These components provide creating the connection with the database, sending any custom queries, insertion, reading, and deletion of data from tables. They are based
  on the Microsoft ActiveX Data Objects (ADO) components and on Microsoft ActiveX Data Objects Extensions (ADOX) components as well.

The problem was that these components worked inadmissibly slowly while processing a large amount of information (tens of thousands of records). Also, in some cases (e.g. while working with Oracle) the components worked incorrectly and violated the database integrity.


While implementing this project, we redesigned the existing components of the Customer to optimize their work. We designed and developed the database schema storage. When the database was accessed for the first time, the schema for all its tables was put into the storage according to the ADO architectural terms (the field name, its type, size, etc). At that, for each database opened, its schema was read only once during one session of the software product execution. This approach
  allowed to increase the performance of the components working with the database, and to essentially optimize the memory use as well.

For instance, the use of the database schema storage allowed to avoid using the ADO Recordset object in many places in the code, and to replace it with the functionality of the ADO Command object, which resulted in the considerable growth of the product's performance.

Developer's comments

Q: How long did the project last?

KB_Soft: In sum, it took about four months, including the time spent on investigations.

Q: What kind of investigations did you have to implement in terms of this project?

KB_Soft: One of the Customer's requirements was to optimize the work of the components. That is why we had to work a lot to study the settings for different ADO objects (such as Connection, Command, Recordset). To put it more precisely, we studied the influence of different object settings on the speed of data processing, stable work with different types of databases, and the data integrity in stress situations (e.g. while multiple users accessing the same table, or large amount of data (more than 10000 records), are being inserted
  or deleted, etc.). As a result, we managed to choose the most optimal and effective ADO and ADOX component settings for this product, which, in the whole, optimized the program work with databases.

Q: Were there any peculiarities in this project that you would like to highlight?

KB_Soft: Undoubtedly. In our opinion, this is the Oracle database support. It is an opened secret, that this DBMS implements a different architecture of a database as an entity, than SQL and MDB do. For example, it is the possibility of two tables with identical names existing within the same database. It was this very distinction between Oracle and SQL/MDB that made the task more complicated, as the project objective was to design unified components able to work with all three DBMSs using the ADO and ADOX facilities.