Pre-fetch Capability

Pre-fetch is another important performance-related feature of SMO. Sometimes the user knows he needs to walk through an entire collection of objects of some type. For this purpose, it makes sense to retrieve the entire collection of properties from the server in one trip, using the aforementioned SetDefaultInitFields setting. This concept can be better demonstrated with Figures 11-8 and 11-9. Figure 11-8 shows how the entire tree is populated by default. The legacy SQL-DMO model followed this pattern as well.

Figure 11-8. Sequential object instantiation (SQL-DMO Model).

Figure 11-9. Per-Level Object Instantiation (SMO Pre-fetch Model).

Figure 11-9 shows how you can efficiently cut down on the number of queries submitted to the Database Engine by using pre-fetch. The entire Table objects collection is retrieved in one query, and all table columns are retrieved in the next query.

Tip

Pre-fetch is used extensively by SMO itself to simplify scripting operations. However, sometimes SMO can make the wrong choice on whether to use pre-fetch or not based on how many objects actually need to be scripted. In this case, you may need to tune the application by calling pre-fetch yourself or by setting the PrefetchObjects flag on the Scripter object to false.

Let's demonstrate forcing pre-fetch by example. Suppose you want to pre-fetch all Table objects of a particular database before accessing the collection. This is how it is done:

database.PrefetchObjects(typeof(Table)); 

Pre-fetching all tables of the database significantly speeds up subsequent access to the tables collection.