KnowDotNet NetRefactor

Harnessing SSCEDirect for fast SQLCE data loading

A really cool Pocket PC Product

by William Ryan and Brady Moritz

When Microsoft released Sql Server CE, developers for Pocket PC and CE based devices were given a excellent platform for building data intense mobile applications. This mobile database engine provides a set of features that are very similar to the full-scale Sql Server product, which are enhanced by powerful replication capabilities for keeping this data syncronized with a backend Sql Server. Since the majority of mobile applications use local databases as a form of cache for server-based data, this syncronization capabilities is very useful. Sql Ce became even more useful when the .Net compact framework was released as it could then be utilized via ADO.Net interfaces with a powerful object-oriented language.
  
As mobile solutions continue to grow in popularity, developers are increasingly facing data connectivity challenges and are not always able replicate their backend data from a Sql Server. Legacy data can be exposed in a variety of ways and many systems are exposing data via a more generic XML or Webservice interface, and mobile developers are increasingly interfacing with these sources.

However, when a mobile developer attempts to use ADO.Net to syncronize more than a few records with these alternative data sources, they often discover the hard way that performance issues can make this a very slow operation. The process of downloading an XML or flat file and trying to load it into SQL CE via an ADO.Net DataSet can take a number of minutes for a few thousand records. 10,000 records can even require 10 minutes! With growing memory capacities of modern devices, mobile data requirements can easily expand into many tens or even hundreds of thousands of records.

SSCEDirect is a product recently release by Fitiri Inc. as a way to speed up these bulk data loading operations with Sql Server CE. Performance improvements seen with a sample 10,000 records is a reduction from 10 minutes using a DataSet to approx 20 seconds using SSCEdirect.

Lets walk through a simple example of how to use the SSCEDirect software (Fitiri offers a demo version at their website that allows you to try the full features, but will randomly alter some of your database varchar data) Request a demo of the software at this url: http://www.fitiri.com/SSCEDirect.html

Lets review some example C# source code to do a performance comparison. The first example will use a DataSet to insert new records into a Sql Ce database. Code to track elapsed time should be added here to calculate how long it takes, but is left out of this example to keep it cleaner.

public void SampleLoad(){

  DataSet ds = new DataSet();

  //We will manually load a dataset for this example, normally this would be done by loading data from serialized XML.

  DataTable dt = new DataTable("sampletable");
  dt.Columns.Add(new DataColumn("col1", typeof(System.Int32)));
  dt.Columns.Add(new DataColumn("col2", typeof(System.String)));
  dt.Columns.Add(new DataColumn("col3", typeof(System.String)));
  dt.Columns.Add(new DataColumn("col4", typeof(System.DateTime)));
  dt.Columns.Add(new DataColumn("col5", typeof(System.Decimal)));
  dt.Columns.Add(new DataColumn("col6", typeof(System.Double)));
  dt.Columns.Add(new DataColumn("col7", typeof(System.Single)));
  dt.Columns.Add(new DataColumn("col8", typeof(System.Int64)));
  dt.Columns.Add(new DataColumn("col9", typeof(System.Boolean)));
  dt.Columns.Add(new DataColumn("col10", typeof(System.Guid)));
  dt.Columns.Add(new DataColumn("col11", typeof(System.Int16)));
  dt.Columns.Add(new DataColumn("col12", typeof(System.Byte)));
  dt.Columns.Add(new DataColumn("col13", typeof(System.Decimal)));
        
  //add (cnt) dummy records to the dataset.
  for(int i = 0; i< cnt; i++)<BR>   {
    DataRow dr = dt.NewRow();
    dr[0]=123456;
    dr[1]="This is a string of data to be inserted";
    dr[2]="some nchar data";
    dr[3]=DateTime.Now;
    dr[4]=(Decimal)(123456789012345);
    dr[5]=(Double)56789.123;
    dr[6]=(Single)123.456;
    dr[7]= 112233445566;
    dr[8] = true;
    dr[9] = new Guid(1,2,3,4,5,6,7,8,9,0,1);
    dr[10] = (Int16)12345;
    dr[11] = (Byte)123;
    dr[12] = (Decimal)(123.1266666);
    dt.Rows.Add(dr);
  }
  dt.AcceptChanges();

  //add the DataTable to the DataSet object
  ds.Tables.Add(dt);

  //this is a way to get the schema from the database table, without filling any data.
  da = new SqlCeDataAdapter("select * from sampletable where 1 = 0",conn);
  da.FillSchema(ds,SchemaType.Mapped, "sampletable");
  
  //force the DataAdapter to generate the insert command, can also be manually set.
  cb = new SqlCeCommandBuilder(da);
  da.MissingMappingAction = MissingMappingAction.Passthrough;
  
  //seems this is needed to force the insert command to be assigned.
  da.InsertCommand = cb.GetInsertCommand();
  
  //call update to move all the data from sampletable DataTable into the database table
  da.Update(ds, "sampletable");
  da.Dispose();

}


SSCEDirect has two basic objects that can be used for loading data, these are the RowsetLoader and the DataTableLoader. RowsetLoader exposes methods that allow row-by-row insertion of data, and thus is useful for .Net code that parses a flat file or xml file and inserts each row of data sequentially. However, if your data is already in a DataTable object, the DataTableLoader class allows its entire contents to be loaded into the selected database table with a single method call. Lets first look at how the same sample data can be loaded using the RowsetLoader.

//Set license key at startup using static method. Set only once per application lifespan.
RowsetLoader.SetLicense("Trial License", "LHTT3Z4XX7");
...
public void SampleLoad(){
RowsetLoader rl = new RowsetLoader();
rl.Open("demotable", txtDB.Text);
rl.Delimiter = "|";    //We are using a delimited string of data, so set the delimiter.
rl.ParseDates = false; //Faster load method, but supports limited date formats.
ErrCodes err;

//test a delimited string of data. Can also pass a string array instead.
string Testdata = "123456|Hello RowsetLoader|Char Data|2002-10-10 10:10:10|" +
                   "123456789012345|56789.123|123.456|112233445566|true|{" +
                   new Guid(1,2,3,4,5,6,7,8,9,0,1).ToString()+ "}|12345|123|123.12345678";

//for testing, insert the same data (cnt) times. Would normally loop through rows of real data here.
for(int i = 0; i < cnt; i++)<BR> {
   //You should check return value for error
   err = rl.AddRow(Testdata);
}
rl.Close();
}

And this example shows how the DataTableLoader would do the job (the DataTable population is a clone of the first example):

public void SampleLoad()
{
  //Manually build a DataTable object, normally this is loaded directly from an XML file.
  //The datatypes listed are currently all the types supported by SSCEDirect.
  DataTable dt = new DataTable("testtable");
  dt.Columns.Add(new DataColumn("col1", typeof(System.Int32)));
  dt.Columns.Add(new DataColumn("col2", typeof(System.String)));
  dt.Columns.Add(new DataColumn("col3", typeof(System.String)));
  dt.Columns.Add(new DataColumn("col4", typeof(System.DateTime)));
  dt.Columns.Add(new DataColumn("col5", typeof(System.Decimal)));
  dt.Columns.Add(new DataColumn("col6", typeof(System.Double)));
  dt.Columns.Add(new DataColumn("col7", typeof(System.Single)));
  dt.Columns.Add(new DataColumn("col8", typeof(System.Int64)));
  dt.Columns.Add(new DataColumn("col9", typeof(System.Boolean)));
  dt.Columns.Add(new DataColumn("col10", typeof(System.Guid)));
  dt.Columns.Add(new DataColumn("col11", typeof(System.Int16)));
  dt.Columns.Add(new DataColumn("col12", typeof(System.Byte)));
  dt.Columns.Add(new DataColumn("col13", typeof(System.Decimal)));
        
  //add (cnt) dummy records to the dataset.
  for(int i = 0; i< cnt; i++)<BR>   {
    DataRow dr = dt.NewRow();
    dr[0]=123456;
    dr[1]="This is a string of data to be inserted";
    dr[2]="some nchar data";
    dr[3]=DateTime.Now;
    dr[4]=(Decimal)(123456789012345);
    dr[5]=(Double)56789.123;
    dr[6]=(Single)123.456;
    dr[7]= 112233445566;
    dr[8] = true;
    dr[9] = new Guid(1,2,3,4,5,6,7,8,9,0,1);
    dr[10] = (Int16)12345;
    dr[11] = (Byte)123;
    dr[12] = (Decimal)(123.1266666);
    dt.Rows.Add(dr);
  }
  dt.AcceptChanges();
  
  DataTableLoader dtl = new DataTableLoader();
  start = DateTime.Now; //this will only time the insert time, not the time just used to load the DataTable
  ErrCodes err;
  err = dtl.Open(dt.TableName,txtDB.Text);
  
  //call CheckDataTable to verify the datatable conforms to the open database table.
  //this will also set the maxlength of any string type columns, since maxlength must be set before calling LoadTable.
  err = dtl.CheckDataTable(dt);
  
  //LoadTable actually copies the DataTable data into the SqlCE database table.
  err = dtl.LoadTable(dt);
  dtl.Close();
  MessageBox.Show("Done");
}



SSCEDirect can also be instructed how to load data that is not in the exact format as the destination table, or if certain data should be ignored. The following example demonstrates some methods to use if a column should be set to null or if the column should be skipped on insert, such as in the the case of an identity column that auto-generates its own value.

  ...
  //After calling the Open() method, set any columns that should not have values inserted.
  
  //ignoresource indicates that, when skipping input to a destination column,
  //the source column does exist and should be skipped over as well
  rl.IgnoreSource = true;
  
  //now set a couple of columns to skip input for. If the column has a default value
  //or is an identity, these values will be inserted instead.
  rl.IgnoreColumn(0);
  rl.IgnoreColumn(1);
  
  //continue with LoadTable() or AddRow(), followed by Close()
  ...
  
  //while looping and calling AddRow, this will set the 5th column to a null database value.
  rl.NullColumn(4);

  ...

These examples should be adequate to get you started with speeding up your database loading in your own Compact Framework project.
When you start on your own, keep in mind that the current version of the software does have a few shortcomings, the most notable one being lack of support for blob items or NTEXT. Support for NTEXT is expected to be included by the next release.

Fitiri also has some new features slated for a future version which will enhance the product's capabilities even further. One planned feature is direct WebService support (to speed up the .Net unmarshalling process, another bottleneck with the Compact Framework). Also planned is direct flat file instead of requiring the developer to pass each row individually, which is expected to nearly double the speed of generic flat file loading.

I hope this helps enable you to focus your development work on the business logic of your mobile solutions and not have to worry about performance issues with the Compact Framework.  I would love to hear your feedback on this article.





Writing Add-Ins for Visual Studio .NET
Writing Add-ins for Visual Studio .NET
by Les Smith
Apress Publishing