How to Choose a Data-Access Model

Each of the three data-access models discussed in the previous sections has advantages and disadvantages. Which data-access model you choose for a particular task depends on the requirements of the situation in which you are manipulating data.

Following are the considerations that you should bear in mind when choosing one data-access model over the others:

• The Execute Direct data-access model is best suited for onetime data-access statements. This is because the Execute Direct model executes more efficiently than the Prepare/Execute model the first time that you run a statement. The Execute Direct model is more efficient than the Prepare/Execute model the first time it is run, because the Prepare/Execute model requires the provider to compile the statement the first time that it runs. So, the first time through, Execute Direct runs faster.
  
  Examples of one-time data-access statements that you could use with the Execute Direct model might include SQL statements entered on-the-fly by users (it would be impossible for the programmer to know these in advance) or statements formed dynamically by your program.

• The Prepare/Execute model is best suited for data-access statements that you will use more than once during the same program session, but that won't be used outside of the program session. As just stated, statements that are used only one time should be executed with the Execute Direct model, because they will run faster the first time than statements executed with Prepare/Execute. On subsequent executions, however, the Prepare/Execute model will be more efficient.
  
  Statements formed on-the-fly by your application to be used more than one time during the current session are good candidates for the Prepare/Execute model. If the statement's contents can be known between sessions and are valid for more than one user, it is better to use stored procedures.

• The Stored Procedures model is best suited for statements that will be in permanent use. Obviously, one-time-only statements won't work with stored procedures, because you can't know them ahead of time.