[WM]在SQL CE 3.5上用SqlCeResultSet优化查询

本文内容来自MSDN,版权不归笔者所有,特此声明:

http://msdn.microsoft.com/en-us/library/dd938877.aspx

 本来想翻译原文的,但是里面有很都初级内容,所以就把关键的部分抽取出来.

1.       插入1000条数据的性能比较

1)       DataSet插入数据

 

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插入数据

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插入数据

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语句.

 

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

　　

 

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

 

 

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

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更新结果

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的数据是可信的.