Analyzing the Impact

Team-Fly

---

Okay, so let's not get wrapped around the axle with the Data Environment Designer and the MSHFlexGrid. I discuss the strengths and weaknesses of the Data Environment Designer in Chapter 12, where we visit the Visual Database Tools.

Before we go on, let's see what impact the default (demo) program has on the server. We'll then compare that to the dent inflicted by the first example. Next, we'll try to see what can be done to Bondo over the dimples.

Remember that our initial query didn't use the Data Environment Designer—it used the same open-ended queries to return the rows: two "SELECT * FROM <table>" queries as generated by the Data Environment Designer. Using SQL Server 7.0's Profiler, we can see what impact these queries have on SQL Server. The Profiler displays the raw TSQL queries and other operations (such as opening connections), but you know that, we've discussed this before.

Note 

For some reason, when I was developing the data source-based samples (using the ADC and the Data Environment Designer), I noticed that the server eventually started returning connection errors for no apparent reason. The Profiler told me that ADO, the ADC, or Visual Basic had not bothered to close and release the connection(s) when I pressed the Stop button in the Visual Basic IDE or even ended the code normally. I tried to add code to explicitly close the Recordsets and Connection objects to no avail. I had to exit Visual Basic completely to get the Connections closed. Folks, this is the same problem I reported when I wrote my first book on VBSQL seven years ago.

Let's take a look at the Profiler output for (part of) the example runs.

 sp_prepare @P1 output, NULL, N'select * from titleauthor', 1 select @P1 select au_id,au_lname, au_fname, Address, city, state, zip from authors;select * from titleauthor 

The Profiler tells us that ADO told SQL Server to "prepare" a query plan for the titleauthor query and to then execute the two open-ended SELECT queries against authors and titleauthor. That's it. The Shape provider accepts all of the rows from both SELECT queries and passes the data back to the application to process. ADO makes no further trips to the server. This means that the Shape provider considers this datastatic—it does not attempt to get fresh data when it positions from row to row.

Note 

The queries generated by the Data Environment Designer using the drag-and-drop method are identical to those generated by our hard-coded example1 code shown above.

Because the two tables are small (Authors has about 22 rows and TitleAuthor only about 25 rows), only about 50 rows are fetched—no big deal. So what if there were 16,000 authors and 50,000 titleauthor rows? Obviously, this approach would not work, or at least not very quickly. In addition, because these are open-ended queries, the negative impact on scalability would be easily apparent. In addition, consider that this is the simplest of shapes—it only has two family members: a parent and a single child. What if there were two or more children? The number of rows being sent back to the client over the network would start getting pretty ghastly before too long.

---

Team-Fly