Slow speed

[BlueJay924]

When i place my database on the server, my application tends to run slower. When i'm running a query, i'm not placing it in a dataset. i place the data into a combo box. would it be faster for me to place the returned data into a dataset first and then into the combo box? i've never placed anything in a dataset before and i read somewhere that this would speed things up? Actually, the article was for recordsets.

[angula]

From Mastering Visual Basic .NET Database Programming

by Evangelos Petroutsos and Asli Bilgin

 

The DataReader Object

The DataReader object is a fast mechanism for retrieving forward-only, read-only streams of data. The SQL Server .NET provider have completely optimized this mechanism, so use it as often as you can for fast performance of read-only data. Unlike ADO RecordSets, which force you to load more in memory than you actually need, the DataReader is a toned-down, slender data stream, using only the necessary parts of the ADO.NET Framework. You can think of it as analogous to the server-side, read-only, forward-only cursor that you used in native OLE DB/ADO. Because of this server-side connection, you should use the DataReader cautiously, closing it as soon as you are finished with it. Otherwise, you will tie up your Connection object, allowing no other operations to execute against it (except for the Close() method, of course).

 

As we mentioned earlier, you can create a DataReader object by using the ExecuteReader() method of the Command object. You would use DataReader objects when you need fast retrieval of read-only data, such as populating combo-box lists.

 

Listing 6.2 depicts an example of how you create the DataReader object, assuming you've already created the Connection object connNorthwind.

 

Listing 6.2: Creating the DataReader Object

 

 

Dim strCustomerSelect as String = "SELECT * from Customers"

Dim cmdCustomers as New SqlCommand(strCustomerSelect, connNorthwind)

Dim drCustomers as SqlDataReader

connNorthwind.Open()

drCustomers = cmdCustomers.ExecuteReader()

 

 

 

Note The code in Listing 6.2 can be found in the click event of the Create DataReader button on the startup form for the Working with ADO.NET solution on the companion CD.

 

 

Notice that you can't directly instantiate the DataReader object, but must go through the Command object interface.

 

Warning You cannot update data by using the DataReader object.

 

 

The DataReader absolves you from writing tedious MoveFirst() and MoveNext() navigation. The Read() method of the DataReader simplifies your coding tasks by automatically navigating to a position prior to the first record of your stream and moving forward without any calls to navigation methods, such as the MoveNext() method. To continue our example from Listing 6.2, you could retrieve the first column from all the rows in your DataReader by typing in the following code:

 

While(drCustomers.Read())

Console.WriteLine(drCustomers.GetString(0))

End While

Note The Console.WriteLine statement is similar to the Debug.Print() method you used in VB6.

 

 

Because the DataReader stores only one record at a time in memory, your memory resource load is considerably lighter. Now if you wanted to scroll backward or make updates to this data, you would have to use the DataSet object, which we discuss in the next section. Alternately, you can move the data out of the DataReader and into a structure that is updateable, such as the DataTable or DataRow objects.

 

Warning By default, the DataReader navigates to a point prior to the first record. Thus, you must always call the Read() method before you can retrieve any data from the DataReader object.

 

DataSet to follow...

[angula]

The DataSet Object

There will come a time when the DataReader is not sufficient for your data manipulation needs. If you ever need to update your data, or store relational or hierarchical data, look no further than the DataSet object. Because the DataReader navigation mechanism is linear, you have no way of traversing between relational or hierarchical data structures. The DataSet provides a liberated way of navigating through both relational and hierarchical data, by using array-like indexing and tree walking, respectively.

 

Unlike the managed provider objects, the DataSet object and friends do not diverge between the OleDb and SqlClient .NET namespaces. You declare a DataSet object the same way regardless of which .NET data provider you are using:

 

Dim dsCustomer as DataSet

Realize that DataSets stand alone. A DataSet is not a part of the managed data providers and knows nothing of its data source. The DataSet has no clue about transactions, connections, or even a database. Because the DataSet is data source agnostic, it needs something to get the data to it. This is where the DataAdapter comes into play. Although the DataAdapter is not a part of the DataSet, it understands how to communicate with the DataSet in order to populate the DataSet with data.

 

DataSets and XML

The DataSet object is the nexus where ADO.NET and XML meet. The DataSet is persisted as XML, and only XML. You have several ways of populating a DataSet: You can traditionally load from a database or reverse engineer your XML files back into DataSets. You can even create your own customized application data without using XML or a database, by creating custom DataTables and DataRows. We show you how to create DataSets on the fly in this chapter in the section "Creating Custom DataSets."

 

DataSets are perfect for working with data transfer across Internet applications, especially when working with WebServices. Unlike native OLE DB/ADO, which uses a proprietary COM protocol, DataSets transfer data by using native XML serialization, which is a ubiquitous data format. This makes it easy to move data through firewalls over HTTP. Remoting becomes much simpler with XML over the wire, rather than the heavier binary formats you have with ADO RecordSets. We demonstrate how you do this in Chapter 16, "Working with WebServices."

 

As we mentioned earlier, DataSet objects take advantage of the XML model by separating the data storage from the data presentation. In addition, DataSet objects separate navigational data access from the traditional set-based data access. We show you how DataSet navigation differs from RecordSet navigation later in this chapter in Table 6.4.

 

DataSets versus RecordSets

As you can see in Figure 6.4, DataSets are much different from tabular RecordSets. You can see that they contain many types of nested collections, such as relations and tables, which you will explore throughout the examples in this chapter.

 

 

Figure 6.4: The ADO.NET DataSet object model

What's so great about DataSets? You're happy with the ADO 2. x RecordSets. You want to know why you should migrate over to using ADO.NET DataSets. There are many compelling reasons. First, DataSet objects separate all the disconnected logic from the connected logic. This makes them easier to work with. For example, you could use a DataSet to store a web user's order information for their online shopping cart, sending deltagrams to the server as they update their order information. In fact, almost any scenario where you collect application data based on user interaction is a good candidate for using DataSets. Using DataSets to manage your application data is much easier than working with arrays, and safer than working with connection-aware RecordSets.

 

Another motivation for using DataSets lies in their capability to be safely cached with web applications. Caching on the web server helps alleviate the processing burden on your database servers. ASP caching is something you really can't do safely with a RecordSet, because of the chance that the RecordSet might hold a connection and state. Because DataSets independently maintain their own state, you never have to worry about tying up resources on your servers. You can even safely store the DataSet object in your ASP.NET Session object, which you are warned never to do with RecordSets. RecordSets are dangerous in a Session object; they can crash in some versions of ADO because of issues with marshalling, especially when you use open client-side cursors that aren't streamed. In addition, you can run into threading issues with ADO RecordSets, because they are apartment threaded, which causes your web server to run in the same thread

 

DataSets are great for remoting because they are easily understandable by both .NET and non- .NET applications. DataSets use XML as their storage and transfer mechanism. .NET applications don't even have to deserialize the XML data, because you can pass the DataSet much like you would a RecordSet object. Non-.NET applications can also interpret the DataSet as XML, make modifications using XML, and return the final XML back to the .NET application. The .NET application takes the XML and automatically interprets it as a DataSet, once again.

 

Last, DataSets work well with systems that require tight user interaction. DataSets integrate tightly with bound controls. You can easily display the data with DataViews, which enable scrolling, searching, editing, and filtering with nominal effort. You will have a better understanding of how this works when you read Chapter 8.

 

Now that we've explained how the DataSet gives you more flexibility and power than using the ADO RecordSet, examine Table 6.3, which summarizes the differences between ADO and ADO.NET.

 

Let's explore how to work with the various members of the DataSet object to retrieve and manipulate data from your data source. Although the DataSet is designed for data access with any data source, in this chapter we focus on SQL Server as our data source.

 

Working with DataSets

Often you will work with the DataReader object when retrieving data, because it offers you the best performance. As we have explained, in some cases the DataSet's powerful interface for data manipulation will be more practical for your needs. In this section, we discuss techniques you can use for working with data in your DataSet.

 

The DataSet is an efficient storage mechanism. The DataSet object hosts multiple result sets stored in one or more DataTables. These DataTables are returned by the DBMS in response to the execution of a command. The DataTable object uses rows and columns to contain the structure of a result set. You use the properties and methods of the DataTable object to access the records of a table. Table 6.4 demonstrates the power and flexibility you get with ADO.NET when retrieving data versus classic ADO.

[angula]

There are three main ways to populate a DataSet:

 

After establishing a connection to the database, you prepare the DataAdapter object, which will retrieve your results from your database as XML. You can use the DataAdapter to fill your DataSet.

 

You can read an XML document into your DataSet. The .NET Framework provides an XMLDataDocument namespace, which is modeled parallel to the ADO.NET Framework. You will explore this namespace in Chapter 7.

 

You can use DataTables to build your DataSet in memory without the use of XML files or a data source of any kind. You will explore this option in the section "Updating Your Database by Using DataSets" later in this chapter.

 

Let's work with retrieving data from the Northwind database. First, you must prepare the DataSet object, which can be instantiated with the following statement:

 

Dim dsCustomers As New DataSet()

Assuming you've prepared your DataAdapter object, all you would have to call is the Fill() method. Listing 6.3 shows you the code to populate your DataSet object with customer information.

 

Listing 6.3: Creating the DataSet Object

 

 

Dim strSelectCustomers As String = "SELECT * FROM Customers ORDER BY CustomerID"

Dim strConnString As String = "data source=(local);" & _

"initial catalog=Northwind;integrated security=SSPI;"

Dim daCustomers As New SqlDataAdapter(strSelectCustomers, strConnString)

Dim dsCustomers As New DataSet()

Dim connNorthwind As New SqlConnection(strConnString)

 

daCustomers.Fill(dsCustomers, "dtCustomerTable")

MsgBox(dsCustomers.GetXml, , "Results of Customer DataSet in XML")

 

 

 

 

Note The code in Listing 6.3 can be found in the click event of the Create Single Table DataSet button on the startup form for the Working with ADO.NET solution on the companion CD.

 

 

This code uses the GetXml() method to return the results of your DataSet as XML. The rows of the Customers table are retrieved through the dsCustomers object variable. The DataTable object within the DataSet exposes a number of properties and methods for manipulating the data by using the DataRow and DataColumn collections. You will explore how to navigate through the DataSet in the upcoming section, "Navigating Through DataSets." However, first you must understand the main collections that comprise a DataSet, the DataTable, and DataRelation collections.

 

The DataTableCollection

Unlike the ADO RecordSet, which contained only a single table object, the ADO.NET DataSet contains one or more tables, stored as a DataTableCollection. The DataTableCollection is what makes DataSets stand out from disconnected ADO RecordSets. You never could do something like this in classic ADO. The only choice you have with ADO is to nest RecordSets within RecordSets and use cumbersome navigation logic to move between parent and child RecordSets. The ADO.NET navigation model provides a user-friendly navigation model for moving between DataTables.

 

In ADO.NET, DataTables factor out different result sets that can come from different data sources. You can even dynamically relate these DataTables to one another by using DataRelations, which we discuss in the next section.

 

Note If you want, you can think of a DataTable as analogous to a disconnected RecordSet, and the DataSet as a collection of those disconnected RecordSets.

 

 

Let's go ahead and add another table to the DataSet created earlier in Listing 6.3. Adding tables is easy with ADO.NET, and navigating between the multiple DataTables in your DataSet is simple and straightforward. In the section "Creating Custom DataSets," we show you how to build DataSets on the fly by using multiple DataTables. The code in Listing 6.4 shows how to add another DataTable to the DataSet that you created in Listing 6.3.

 

Note The code in Listing 6.4 can be found in the click event of the Create DataSet WithTwoTables button on the startup form for the Working with ADO.NET solution on the companion CD.

 

 

Listing 6.4: Adding Another DataTable to a DataSet

 

 

Dim strSelectCustomers As String = "SELECT * FROM Customers ORDER BY CustomerID"

Dim strSelectOrders As String = "SELECT * FROM Orders"

Dim strConnString As String = "data source=(local);" & _

"initial catalog=Northwind;integrated security=SSPI;"

Dim daCustomers As New SqlDataAdapter(strSelectCustomers, strConnString)

Dim dsCustomers As New DataSet()

Dim daOrders As New SqlDataAdapter(strSelectOrders, strConnString) daCustomers.Fill(dsCustomers, "dtCustomerTable")

daOrders.Fill(dsCustomers, "dtOrderTable")

Console.WriteLine(dsCustomers.GetXml)

 

 

 

 

Warning DataTables are conditionally case sensitive. In Listing 6.4, the DataTable is called dtCustomerTable. This would cause no conflicts when used alone, whether you referred to it as dtCustomerTable or dtCUSTOMERTABLE. However, if you had another DataTable called dtCUSTOMERTABLE, it would be treated as an object separate from dtCustomerTable.

 

 

As you can see, all you had to do was create a new DataAdapter to map to your Orders table, which you then filled into the DataSet object you had created earlier. This creates a collection of two DataTable objects within your DataSet. Now let's explore how to relate these DataTables together.

 

The DataRelation Collection

The DataSet object eliminates the cumbersome shaping syntax you had to use with ADO RecordSets, replacing it with a more robust relationship engine in the form of DataRelation objects. The DataSet contains a collection of DataRelation objects within its Relations property. Each DataRelation object links disparate DataTables by using referential integrity such as primary keys, foreign keys, and constraints. The DataRelation doesn't have to use any joins or nested DataTables to do this, as you had to do with ADO RecordSets.

 

In classic ADO, you create relationships by nesting your RecordSets into a single tabular Record- Set. Aside from being clumsy to use, this mechanism also made it awkward to dynamically link disparate sets of data.

 

With ADO.NET, you can take advantage of new features such as cascading referential integrity. You can do this by adding a ForeignKeyConstraint object to the ConstraintCollection within a DataTable. The ForeignKeyConstraint object enforces referential integrity between a set of columns in multiple DataTables. As we explained in Chapter 2, in the "Database Integrity" section, this will prevent orphaned records. In addition, you can cascade your updates and deletes from the parent table down to the child table.

 

Listing 6.5 shows you how to link the CustomerID column of your Customer and Orders DataTables. Using the code from Listing 6.3, all you have to do is add a new declaration for your DataRelation.

 

Listing 6.5: Using a Simple DataRelation

 

 

Dim drCustomerOrders As DataRelation = New DataRelation("CustomerOrderRelation",

dsCustomers.Tables("Customers").Columns("CustomerID"),

dsCustomers.Tables("Orders").Columns("CustomerID"))

dsCustomers.Relations.Add(drCustomerOrders)

 

 

 

Note The code in Listing 6.5 can be found in the click event of the Using Simple DataRelations button on the startup form for the Working with ADO.NET solution on the companion CD.

 

 

As you can with other ADO.NET objects, you can overload the DataRelation constructor. In this example, you pass in three parameters. The first parameter indicates the name of the relation. This is similar to how you would name a relationship within SQL Server. The next two parameters indicate the two columns that you wish to relate. After creating the DataRelation object, you add it to the Relations collection of the DataSet object.

 

Warning The data type of the two columns you wish to relate must be identical.

 

 

Listing 6.6 shows you how to use DataRelations between the Customers and Orders tables of the Northwind database to ensure that when a customer ID is deleted or updated, it is reflected within the Orders table.

 

Listing 6.6: Using Cascading Updates

 

 

Dim fkCustomerID As ForeignKeyConstraint

fkCustomerID = New ForeignKeyConstraint

("CustomerOrderConstraint", dsCustomers.Tables

("Customers").Columns("CustomerID"), dsCustomers.Tables("Orders").Columns("CustomerID"))

fkCustomerID.UpdateRule = Rule.Cascade

fkCustomerID.AcceptRejectRule = AcceptRejectRule.Cascade

dsCustomers.Tables("CustomerOrder").Constraints.Add

(fkCustomerID)

dsCustomers.EnforceConstraints = True

 

 

 

Note The code in Listing 6.6 can be found in the click event of the Using Cascading Updates button on the startup form for the Working with ADO.NET solution on the companion CD.

 

 

In this example, you create a foreign key constraint with cascading updates and add it to the ConstraintCollection of your DataSet. First, you declare and instantiate a ForeignKeyConstraint object, as you did earlier when creating the DataRelation object. Afterward, you set the properties of the ForeignKeyConstraint, such as the UpdateRule and AcceptRejectRule, finally adding it to your ConstraintCollection. You have to ensure that your constraints activate by setting the EnforceConstraints property to True.

[wyrd]

When i place my database on the server, my application tends to run slower. When i'm running a query, i'm not placing it in a dataset. i place the data into a combo box. would it be faster for me to place the returned data into a dataset first and then into the combo box? i've never placed anything in a dataset before and i read somewhere that this would speed things up? Actually, the article was for recordsets.

 

You're getting a hit in performance because the info. has to travel over a network to get to your computer, rather than hard drive to memory. Plus, you also have to connect to a server across a network rather then localy, so you're probably also getting some 'net lag.

 

If you plan on using the data again in your app, then yes it'd be speed up your program to keep the data in memory on the client (using a DataSet). However, if you're only interested in displaying the data and doing nothing else with it (ie; doesn't have to be displayed elsewhere or used again for any other purposes), then you more then likely won't see any difference in speed.

 

If you're retrieving a lot of info. from your database (1,000+ records), then you can speed things up a bit by allowing paging, and only displaying 20 records at a time.

 

You can also gain some performance by putting your select statement inside a stored procedure, and using the stored procedure to get your data.