Optimizing Query and DML Performance with SQL Server Compact Edition 3.5 and SqlCeResultSet

4/7/2010

Pete Vickers, APPAMundi Limited

June 2009

Summary

This article compares record insertion and retrieval techniques when you use Microsoft® SQL Server® Compact Edition 3.5 SP1 as a local data store in mobile device applications, with particular focus on the use of the SqlCeResultSet object. The sample projects show how to insert, update, and retrieve records from sample databases.

Microsoft SQL Server Compact 3.5 Service Pack 1 for Windows Mobile

Sample Code

Applies To

Windows Mobile 5 and later

Microsoft SQL Server Compact Edition 3.5 SP1

Microsoft Visual Studio 2008 SP1

Microsoft Visual C# 2008

Microsoft Visual Basic 2008

Microsoft .NET Framework 3.5

Introduction

Programming SQL Server Compact Edition as a High-Performance Local Data Store

Creating a SQL Server Compact Edition Database

Creating the Test Application

Coding Record Insertion Logic

Using SqlCeResultSet to Query Data from SQL Server Compact Edition

Forms Design and Data Components

Comparing DataSet and SqlCeResultSet to Retrieve from Joined Tables

Using SetRange to Filter Records from a Single Table by Using an Index

Updating Records by Using SqlCeResultSet

Conclusion

Introduction

Microsoft SQL Server Compact Edition is a small memory footprint, efficient database application, ideal for use as a local data store for applications on mobile devices and portable computers. SQL Server Compact Edition follows the pattern of the .NET Compact Framework in supporting the same programming models as its desktop counterparts. Therefore, developers familiar with programming the DataAdapter and DataSet objects to retrieve and update data from SQL Server in a desktop application can use the same techniques when they work with a SQL Server Compact Edition database in a mobile device application.

This compatibility of programming models yields obvious benefits in that developers can program easily on both desktop and mobile platforms. However, the DataAdapter-DataSet model, although it makes perfect sense for a large database server that might have many hundreds or thousands of connections from client programs, is not the most efficient model for a local data store for a single application.

SQL Server Compact Edition offers the SqlCeResultSet object as an alternative, which yields better record insert and retrieval performance compared to processing Transact-SQL commands or using the DataAdapter and DataSet objects. The techniques described are supported by code samples that are written in Visual C#® and Visual Basic® that you can download by using the link under "Downloads."

The techniques in this article do not only apply to the .NET Compact Framework. Adding a reference of System.Data.SqlServerCe to your Windows® Forms application enables developers for desktop and mobile device applications to take advantage of the improved performance.

Programming SQL Server Compact Edition as a High-Performance Local Data Store

Reading from a database conventionally follows the desktop approach of using a DataAdapter object to connect to a database, to retrieve rows, and then to load the data into a DataSet object or using SqlCECommand objects to execute Transact-SQL commands. SqlCeResultSet gives you an alternative and improved performance. With a SqlCeResultSet object, you are working directly against the data that is stored in the database, and you avoid the overhead of reading out of the database, only to cache it in memory in a DataSet object before working with it in your application. The SqlCeResultSet object makes much more sense on memory-constrained devices.

The following code examples shows the differences in performance when 1000 records are inserted by using Transact-SQL, a DataSet object, and the SqlCeResultSet object.

Creating a SQL Server Compact Edition Database

The following steps show how to create the database and how to add it to a project so that it deploys when you run your project.

To create a new database

1. Start SQL Server Management Studio, select SQL Server Compact Edition, and then choose Connect.
2. Select New Database from the drop-down list.
   This will bring up the window Create New SQL Server Compact Database.
3. Enter the name of the database that you want to create, in this case AppDatabase.sdf.
   
4. Choose OK and accept the warning about creating a database that uses a blank password.
5. In Object Explorer, expand the database, right-click Tables, and select New Table.
   
6. Next, create a new table, as shown in the following window. It makes EntryID an Identity field and a primary key. This table represents the kind of tables that you would use in your applications.
   The database is now created.
7. Close SQL Server Management Studio.

The sample with this article already has the database added. However, to show how to add a database to a project, perform the following steps.

To add a database to a project

1. Open Microsoft Visual Studio 2008, and create a new Smart Device project.
2. Choose Project, choose Add Existing Item, and then from the Objects of type drop-down list, select the Microsoft SQL Server Compact 3.5 database.
3. Browse to the AppDatabase.sdf file that you created earlier and choose Add.
   The Data Source Configuration Wizard runs automatically.
4. Select the PerfTest table from the Data Source Configuration Wizard. Select the Tables check box, and enable the project to create a dataset.
   This adds the database to your project, and sets the Deployment property to Copy if newer so that it copies to the target device, if the database is newer than the copy on the device.

Creating the Test Application

The test application consists of a single form that contains a DataGrid control and a menu. The table in the database, which is empty at first, is populated by using three techniques, then the inserted records are displayed in the DataGrid control, and finally the application displays the elapsed time for the operation.

To create a test application

1. In Microsoft Visual Studio 2008, add a bindingSource to the project from the Toolbox, and change the name to perfTestBindingSource. Choose the DataSource property, and select the AppDatabaseDataSet object that you created earlier.
   The next step is to create a DataSet and TableAdapter.
2. Choose Project, choose Add New Item and then select DataSet, which adds a dataset to the project. In Solution Explorer, double-click the dataset that you just created, then open Server Explorer, and drag the Perf table from Server Explorer onto the DataSet designer.
3. Next, add a DataGrid control to the project, set the Dock property to fill, and the DataSource property to the perfTestBindingSource created earlier.
4. Next, create the menu for the form with an option to Exit on the left button, and the three tests and a Clear Table option on an Options menu on the right button.
   Finally, add a reference to SQL Server Compact Edition.
5. Choose Project, choose Add Reference, and then select System.Data.SQLServerCe as shown here. Choose OK.
   The pieces are now all assembled to manipulate our data.

Coding Record Insertion Logic

At the start of the program, the connection to the database has to be opened.

public Form1()
{
    InitializeComponent();
    this.perfTestTableAdapter.Connection.Open();
}

You must make sure that you close and dispose of the connection when the form is closed at the end of the run.

private void Form1_Closing(object sender, CancelEventArgs e)
{
    this.perfTestTableAdapter.Connection.Close();
    this.perfTestTableAdapter.Dispose();
}

Each approach that is used to populate the table follows the same pattern. First a new StopWatch object is started and 1000 records are inserted into the table. The records are then read out of the table again and used to populate the grid. Finally, each approach displays the elapsed time for the operation as recorded by the StopWatch object.

The following code example shows how to use the DataSet approach to insert the records.

private void menuItemDataSet_Click(object sender, EventArgs e)
{
    Stopwatch sw = Stopwatch.StartNew();
    Cursor.Current = Cursors.WaitCursor;
    // Insert 1000 records    this.appDatabaseDataSet = new AppDatabaseDataSet();

    for (int i = 0; i < 1000; i++)
    {
        appDatabaseDataSet.PerfTest.AddPerfTestRow
            ("Some test data", DateTime.Now);
    }

    // Insert into the database
    this.perfTestTableAdapter.Update(appDatabaseDataSet);

    // Refresh display
    this.perfTestBindingSource.DataSource = this.appDatabaseDataSet;
    this.perfTestBindingSource.DataMember = "PerfTest";

    Cursor.Current = Cursors.Default;

    MessageBox.Show(
        "Elapsed ms: " + sw.ElapsedMilliseconds.ToString());
}

The code adds 1000 rows to a DataSet instance, uses the Update method on the DataAdapter instance to insert the records into the table, and then displays the results in a grid. The following screen display results from using a DataSet object.

The code that runs for the T-SQL menu option uses a parameterized insert and then calls the Prepare method that yields optimal performance when you use this technique. This means that everything possible is being done to optimize the query.

private void menuItemTSQL_Click(object sender, EventArgs e)
{
    Stopwatch sw = Stopwatch.StartNew();
    Cursor.Current = Cursors.WaitCursor;

    // Insert 1000 records
    SqlCeCommand cmd = new SqlCeCommand(
        "INSERT INTO PERFTEST(EntryData, TimeInserted) " 
        + "VALUES(@EntryData, @TimeInserted)",  
        this.perfTestTableAdapter.Connection);
    cmd.CommandType = CommandType.Text;
    cmd.Parameters.Add(
        new SqlCeParameter(
            "@EntryData", 
            SqlDbType.NVarChar, 
            100, 
            "EntryData"));
    cmd.Parameters.Add(
        new SqlCeParameter(
            "@TimeInserted", 
            SqlDbType.DateTime, 
            8, 
            "TimeInserted"));
    cmd.Prepare();

    for (int i = 0; i < 1000; i++)
    {
        cmd.Parameters[0].Value = "Some test data";
        cmd.Parameters[1].Value =  DateTime.Now;
        cmd.ExecuteNonQuery();
    }
    // Refresh display
    SqlCeCommand cmd1 = 
        new SqlCeCommand(
           "PERFTEST", 
           this.perfTestTableAdapter.Connection);
    cmd1.CommandType = CommandType.TableDirect;
    rsltSet = cmd1.ExecuteResultSet(ResultSetOptions.Scrollable);
    this.perfTestBindingSource.DataSource = rsltSet.ResultSetView;

    Cursor.Current = Cursors.Default;

    MessageBox.Show(
        "Elapsed ms: " + sw.ElapsedMilliseconds.ToString());
    }

The code creates a parameterized query, prepares it, and then performs 1000 iterations calling the ExecuteNonQuery method of the SqlCeCommand object to insert the records. The code then uses a SqlCeResultSet object to read the records from the database and to bind them to the BindingSource for display. The following screen shows the output from using this Transact-SQL technique.

This gives an improvement of 4236 milliseconds.

Finally, the following code shows how to use the SqlCeResultSet approach.

private void menuItemResultSet_Click(object sender, EventArgs e)
{
    Stopwatch sw = Stopwatch.StartNew();
    Cursor.Current = Cursors.WaitCursor;

    // Insert 1000 records
    SqlCeCommand cmd = 
        new SqlCeCommand(
            "PERFTEST", 
            this.perfTestTableAdapter.Connection);
    cmd.CommandType = CommandType.TableDirect;
    rsltSet = cmd.ExecuteResultSet(
        ResultSetOptions.Scrollable | ResultSetOptions.Updatable);
    rsltSet.Read();

    for (int i = 0; i < 1000; i++)
    {
        SqlCeUpdatableRecord record = rsltSet.CreateRecord();
        record.SetString(1, "Some test data");
        record.SetDateTime(2, DateTime.Now);
        rsltSet.Insert(record, DbInsertOptions.PositionOnInsertedRow);
    }

    // Refresh display
    this.perfTestBindingSource.DataSource = rsltSet.ResultSetView;

    Cursor.Current = Cursors.Default;

    MessageBox.Show(
        "Elapsed ms: " + sw.ElapsedMilliseconds.ToString());
}

A SqlCeCommand instance is used in CommandType.TableDirect mode against the Perf table and the ExecuteResultSet method called to create a SqlCeResultSet object that initially is empty of data. You must then call SqlCeResultSet.Read to position the cursor at the start of the record set. After that, the code loops to create 1000 records. The following screen shows the output from using SqlCeResultSet.

This is a 1931 millisecond improvement over Transact-SQL and 6137 milliseconds improvement over the DataSet approach, a near 4-fold improvement.

Many queries are posted on newsgroups asking how to improve performance when you use SQL Server Compact Edition. As the application and chart show, the SqlCeResultSet object is the best way to achieve faster record inserts.

Using SqlCeResultSet to Query Data from SQL Server Compact Edition

SqlCeResultSet not only gives performance benefits on inserts as shown earlier, but also in complex Transact-SQL queries involving joins and reading from single tables.

When it reads from a single table by using TableDirect, the query processor is bypassed. It enables faster access to the data. In this case selection can be done on a table index by using the SetRange property for the data selection. In the demo application, retrieving from a single table with selection is compared by using SqlCeResultSet with SetRange, SqlCeResultSet and DataSet.

Forms Design and Data Components

For this application, use the NorthWind sample database to perform the tests. The interface resembles the last project, reading the database, and displaying the results in a grid.

Comparing DataSet and SqlCeResultSet to Retrieve from Joined Tables

The first query uses a DataSet approach, joining the Order Details table to the Product Details and displaying the results. The query joins the tables on a common column that in this case is the Product ID column. The following code example shows the Transact-SQL statement that is used to retrieve the data.

SELECT     [Order Details].[Order ID], [Order Details].[Product ID], Products.[Product Name], [Order Details].[Unit Price], [Order Details].Quantity,  [Order Details].Discount, CONVERT(money, ([Order Details].[Unit Price] * [Order Details].Quantity) * (1 - [Order Details].Discount) / 100) * 100 AS ExtendedPrice FROM     Products INNER JOIN [Order Details] ON Products.[Product ID] = [Order Details].[Product ID]

private void mnuiDataSet_Click(object sender, EventArgs e)
{
    StringBuilder bldr = new StringBuilder();
    bldr.Append("SELECT [Order Details].[Order ID], ");
    bldr.Append("[Order Details].[Product ID], ");
    bldr.Append("Products.[Product Name], ");
    bldr.Append("[Order Details].[Unit Price], ");
    bldr.Append("[Order Details].Quantity, ");
    bldr.Append("[Order Details].Discount, ");
    bldr.Append("CONVERT(money, ([Order Details].[Unit Price] ");
    bldr.Append(" * [Order Details].Quantity) * (1 -"); 
    bldr.Append(" [Order Details].Discount) / 100) * 100 ");
    bldr.Append("AS ExtendedPrice");
    bldr.Append(" FROM     Products INNER JOIN");
    bldr.Append(" [Order Details] ON Products.[Product ID] ");
    bldr.Append(" = [Order Details].[Product ID] ");

    Stopwatch sw = Stopwatch.StartNew();
    Cursor.Current = Cursors.WaitCursor;

    SqlCeDataAdapter da;
    DataSet dsGeneral = null;

    sqlcmd = new SqlCeCommand(bldr.ToString(), connection);
    sqlcmd.CommandType = CommandType.Text;
    da = new SqlCeDataAdapter(sqlcmd);
    dsGeneral = new DataSet();
    da.Fill(dsGeneral, "General");
    int rowCount = dsGeneral.Tables["General"].Rows.Count;
    
    rsBindingSource.DataSource = dsGeneral.Tables["General"];

    Cursor.Current = Cursors.Default;

    MessageBox.Show(rowCount + 
        " Records retrieved in " + 
         sw.ElapsedMilliseconds.ToString() + " elapsed ms");

}

The following screen shows the output from this code example by using a DataSet object.

Next, use the same query. However, this time, use a SqlCeResultSet object to retrieve the data.

private void mnuiJoined_Click(object sender, EventArgs e)
{
    StringBuilder bldr = new StringBuilder();
    bldr.Append("SELECT [Order Details].[Order ID], ");
    bldr.Append("[Order Details].[Product ID], ");
    bldr.Append("Products.[Product Name], ");
    bldr.Append("[Order Details].[Unit Price], ");
    bldr.Append("[Order Details].Quantity, ");
    bldr.Append("[Order Details].Discount, ");
    bldr.Append("CONVERT(money, ([Order Details].[Unit Price] ");
    bldr.Append(" * [Order Details].Quantity) * (1 -"); 
    bldr.Append(" [Order Details].Discount) / 100) * 100 ");
    bldr.Append("AS ExtendedPrice");
    bldr.Append(" FROM     Products INNER JOIN");
    bldr.Append(" [Order Details] ON Products.[Product ID] ");
    bldr.Append(" = [Order Details].[Product ID] ");

    Stopwatch sw = Stopwatch.StartNew();
    Cursor.Current = Cursors.WaitCursor;

    sqlcmd = new SqlCeCommand(bldr.ToString(), connection);
    rs = sqlcmd.ExecuteResultSet(ResultSetOptions.Scrollable);
    rsBindingSource.DataSource = rs.ResultSetView;
    int rowCount = ((IBindingList)rs.ResultSetView).Count;

    Cursor.Current = Cursors.Default;

    MessageBox.Show(rowCount + 
        " Records retrieved in " + 
        sw.ElapsedMilliseconds.ToString() + " elapsed ms");
}

The following screen shows the result by using a SqlCeResultSet object.

The following illustration shows an improvement of 2522 milliseconds over the DataSet approach or a 2.4-fold speed increase.

Using SetRange to Filter Records from a Single Table by Using an Index

You have already seen how to use a SqlCeCommand object in TableDirect mode as a fast way to retrieve records from a single table through a SqlCeResultSet. You can also set the SetRange property to retrieve a subset of records from a table by filtering on a range of values of a named index from the table. The following example fetches records from the Products table fetching only those records with a ProductID between 30 and 40.

Use a SqlCeCommand object that has its CommandType property set to CommandType.TableDirect, setting the IndexName property to the name of the index that you want to use, and setting the SetRange property to the range of values that you want to retrieve. In this case, the index is called Products_PK, and the range is 30 to 40 inclusive.

private void mnuiSetRange_Click(object sender, EventArgs e)
{
    Stopwatch sw = Stopwatch.StartNew();
    Cursor.Current = Cursors.WaitCursor;

    sqlcmd = connection.CreateCommand();
    sqlcmd.CommandType = CommandType.TableDirect;
    sqlcmd.CommandText = "Products";
    sqlcmd.IndexName = "Products_PK";
    sqlcmd.SetRange(        DbRangeOptions.InclusiveStart | DbRangeOptions.InclusiveEnd,
        new object[] { 30 }, 
        new object[] { 40 });

    rs = sqlcmd.ExecuteResultSet(ResultSetOptions.Scrollable);
    rsBindingSource.DataSource = rs.ResultSetView;
    int rowCount = ((IBindingList)rs.ResultSetView).Count;

    Cursor.Current = Cursors.Default;

    MessageBox.Show(rowCount + 
        " Records retrieved in " + 
        sw.ElapsedMilliseconds.ToString() + " elapsed ms");
}

The following screen shows the output from the test program from this operation.

The sample code includes examples that perform similar filtering by using a SqlCeResultSet and a DataSet object but filtering the records by using a WHERE clause in a Transact-SQL statement as the following code example shows.

SELECT * FROM Products WHERE [Product ID] >= 30 and [Product ID] <= 40

Using these other techniques, the SqlCeResultSet approach using the Transact-SQL selection example takes 96 milliseconds. The DataSet approach using Transact-SQL selection example takes 139 milliseconds to perform the same record retrieval.

Updating Records by Using SqlCeResultSet

To this point in this article, you have learned how to use the SqlCeResultSet object to insert and retrieve records, but SqlCeResultSet also gives better performance than a DataAdapter and DataSet object or Transact-SQL when it updates records.

The following code example shows how to use a SqlCeResultSet object to update a column on a table.

private void mnuiLastUpdated_Click(object sender, EventArgs e)
{
    string strSql = "SELECT * from Products";

    Stopwatch sw = Stopwatch.StartNew();
    Cursor.Current = Cursors.WaitCursor;

    sqlcmd = new SqlCeCommand(strSql, connection);
    rs = sqlcmd.ExecuteResultSet(
        ResultSetOptions.Scrollable | ResultSetOptions.Updatable);
    rsBindingSource.DataSource = rs.ResultSetView;
    int rowCount = ((IBindingList)rs.ResultSetView).Count;

    foreach (SqlCeUpdatableRecord rec in rs)
    {
        rs.SetSqlDateTime(
            rs.GetOrdinal("last_updated"), 
            System.DateTime.Now);
        rs.Update();
    }

    Cursor.Current = Cursors.Default;

    MessageBox.Show(rowCount + 
       " Records retrieved and updated in " + 
        sw.ElapsedMilliseconds.ToString() + " elapsed ms");
}

The data is retrieved by using the SqlCeResultSet object, and then loop through updating each record with the current date and time by using the foreach construct. The following screen shows the results.

Conclusion

SQL Server Compact Edition 3.5 SP1 continues the evolution of the database for the mobile device AND for the desktop.

The SqlCeResultSet object has been a feature of SQL Server Compact Edition since V3.0 but many developers are unaware of how to use it or of how to get the benefits of using it. As demonstrated in this article, SqlCeResultSet gives better insert and query performance and flexibility than DataSet, and is also easy to use. And remember, this applies to both the .NET Compact Framework and Windows Forms applications on the desktop.

Author Bio

Pete Vickers is a co-founder of APPAMundi Limited and is a Technical Project Leader and Consultant at the company. He has been a Device Application Development MVP since 2002, and has been in the computing industry longer than he cares to remember. He has produced several utilities, including database utilities for mobile devices. He can be contacted at pete.vickers@appamundi.com.

Additional Information

Main Microsoft SQL Server Compact Edition3.5 product Web site at Microsoft SQL Server Compact 3.5

Microsoft SQL Server Compact Edition 3.5 Books Online

See Also

Other Resources

Getting Started with Building Windows Mobile Solutions with Visual Studio and Windows Mobile 6 SDK