[WM]在SQL CE 3.5上用SqlCeResultSet优化查询
本文内容来自MSDN,版权不归笔者所有,特此声明:
http://msdn.microsoft.com/en-us/library/dd938877.aspx
本来想翻译原文的,但是里面有很都初级内容,所以就把关键的部分抽取出来.
1. 插入1000条数据的性能比较
1) DataSet插入数据
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
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());
}
性能:
2) T-SQL插入数据
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
Code
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());
}
性能:
3) SqlCeResultSet插入数据
Code
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());
}
性能:
4) 综合比较
2. 比较DataSet和SqlCeResultSet在表连接的性能差异
需要被执行的T-SQL语句.
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
SELECT [Order Details].[Order ID], [Order Details].[Product ID], Products.[Product Name], [Order Details].[Unit Price], [Order Details].Quantity, [OrderDetails].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]
1) DataSet
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
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");
}
表现结果:
2) SqlCeResultSet
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
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");
}
性能:
3) 比较
3. 使用SetRange在单表查询时使用索引过滤查询结果
需要被执行的SQL语句:
SELECT * FROM Products WHERE [Product ID] >= 30 and [Product ID] <= 40
Code
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");
}
4. 用SqlCeResultSet更新结果
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
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");
}
性能:
MSDN原话:
与基于 DataSet 的代码相比,基于 SqlCeResultSet 的代码运行速度更快,使用的内存更少,并且更为简练。这是因为 SqlCeResultSet 使用指针直接操作数据库,而 DataSet 则是在设备应用程序中存储数据库中数据的副本。
PS:
上面节选非别对应增,改,查.有人问删怎么办?估计还得T-SQL.
我开始是一只使用T-SQL,文章里面插入1K行数据,每行30-40ms的数据是可信的.