The Impact of the Just Enough Approach | Ado Examples and Best Practices

Team-Fly

The Impact of the "Just Enough" Approach

Let's look again at what the Profiler reports about the parameter-based queries. I discovered something interesting here that probably will surprise you as much as it did me.

The TSQL statements being sent make a lot of sense—or they seem to at first.

First, ADO asks SQL Server to return (just) metadata for the titleauthor query.

 SET FMTONLY ON select * from titleauthor SET FMTONLY OFF

Next, ADO performs the new parameter-based query using the concatenated parameter we supplied in code (from the TextBox on the form). This returns an initial rowset with just the California authors.

 select au_id,au_lname, au_fname, phone, Address, city, state, zip from authors where state ='CA'

Next, ADO makes a round-trip to find out what the metadata looks like for the key-based query against the TitleAuthor table. We're at three round-trips so far.

 SET FMTONLY ON select au_id from titleauthor SET FMTONLY OFF

At this point, ADO creates a temporary stored procedure used to fetch columns from the TitleAuthor table. This query fires whenever a specific Authors table row is selected. Because we select a whole series of author rows, we can expect this to be executed quite a few times—once for each row in the parent Recordset. Note that the final SELECT in this batch is simply returning the parameter @P1 from the sp_prepare statement.

 sp_prepare @P1 output, N'@P1 varchar(11)', N' select * from titleauthor where au_id = @P1', 1 select @P1

The final TSQL code reported by the profiler is the fetch (finally!) for our first parent row. That is, the Shape provider knows that the au_id for the first row is '172-32-1176', so it queries the server for any titles matching this author id.

 sp_execute 1, '172-32-1176'   17:02:13.987

Well, we're not done yet. At this point, we have only positioned to the first row of the parent Recordset and fetched the first set of child (titleauthor) rows. As we fetch additional rows, we would expect ADO and the Shape provider to reuse temporary stored procedure #1 (as created above) to fetch additional rows from the TitleAuthor table. Sadly, this is not the case. As we can see in the Profiler dump, the Shape provider tells SQL Server to drop (unprepare) the preceding temporary stored procedure and create another just like it. Sigh. It then executes the new temporary stored procedure to return the next set of child rows.

 sp_unprepare 1 sp_prepare @P1 output, N'@P1 varchar(11)', N' select * from titleauthor where au_id = @P1', 1 select @P1    17:21:01.367 sp_execute 2, '213-46-8915'

Now this was a surprise to me. I would have expected the Shape provider to be able to at least leverage the work done to create the childrow queries already created. As you can see, this "just enough" approach has its problems. It requires several more round-trips per row than the "all-at-once" approach, which, frankly, looks like it has quite a bit of merit at this point. The "just enough" approach does fetch current data for each child row, but it depends on a static set of parent rows to drive the secondary query.

You can see how adding additional children to the hierarchy might be interesting. For example, customer-order-item queries would be fairly common. However, you can also see how any additional complexity in the hierarchy would be translated exponentially in increased overhead.

Team-Fly