In this five part series on SQL Server Compact performance, I will demonstrate and measure the performance of the two available data access APIs provider by the SQL Server Compact ADO.NET provider (System.Data.SqlServerCe.dll). The information in this blog post applies to both version 3.5 and 4.0. I will not cover usage of the unmanaged OLEDB interfaces (using C and C++)
As mentioned, the ADO.NET provider provides two APIs for data access (CRUD) operations:
One: The T-SQL standards based APIs - SqlCeCommand: ExecuteReader, ExecuteNonQuery, ExecuteScalar, that allows you to use text based SQL statements, including parameterized SQL. Most ORM implementations that support SQL Server Compact, like LINQ to SQL and Entity Framework, use this API, with the exception of the high perfromant OpenNETCF.ORM Framework, which is able to take advantage of
Two: The “table direct” APIs that allow you to bypass the T-SQL parsing and interpretation process, and that alone provides an obvious saving in processing. These include:
SqlCeResultSet (that inherits from SqlCeDataReader) - in particular when used with a SqlCeCommandType of TableDirect
As you can see, none of these is available with the SQL Server ADO.NET objects, and they are particular to SQL Server Compact. This blog post series will demonstrate how to use these as alternatives to the “standard” methods.
The diagram from Laxmi’s blog post here explains the advantage graphically:
The “table direct” APIs bypass the Query Processor layer, and instead uses an ISAM like access pattern.
In the next blog post in this series, I will compare INSERT with SqlCeResultSet.Insert( SqlCeUpdateableRecord ) and demonstrate how to use the “Table Direct” APIs for INSERTs.
No comments:
Post a Comment