Home > Articles > Data

  • Print
  • + Share This
This chapter is from the book

Know Your Database System

You may think your database system supports all the functionality that is specified in the standards-based APIs (such as ODBC, JDBC, and ADO.NET). That is likely not true. Yet, the driver you use may provide the functionality, which is often a benefit to you. For example, if your application performs bulk inserts or updates, you can improve performance by using arrays of parameters. Yet, not all database systems support arrays of parameters. In any case, if you use a database driver that supports them, you can use this functionality even if the database system does not support it, which 1) results in performance improvements for bulk inserts or updates, and 2) eliminates the need for you to implement the functionality yourself.

The trade-off of using functionality that is not natively supported by your database system is that emulated functionality can increase CPU use. You must weigh this trade-off against the benefit of having the functionality in your application.

The protocol of your database system is another important implementation detail that you should understand. Throughout this chapter, we discussed design decisions that are affected by the protocol used by your database system of choice: cursor-based or streaming. Explanations of these two protocols can be found in “One Connection for Multiple Statements” on page 16.

Table 2-3 lists some common functionality and whether it is natively supported by five major database systems.

Table 2-3. Database System Native Support

Functionality

DB2

Microsoft SQL Server

MySQL

Oracle

Sybase ASE

Cursor-based protocol

Supported

Supported

Not supported

Supported

Not supported

Streaming protocol

Not supported

Not supported

Supported

Not supported

Supported

Prepared statements

Native

Native

Native

Native

Not supported

Arrays of parameters

Depends on version

Depends on version

Not supported

Native

Not supported

Scrollable cursors1

Supported

Supported

Not supported

Not supported

Depends on version

Auto-commit mode

Not supported

Not supported

Native

Native

Native

LOB locators

Native

Native

Not supported

Native

Not supported

  • + Share This
  • 🔖 Save To Your Account