Sure. Here's the data (average for several passes in ms, first pass left
out)
DataReader/ListView 950
DataReader 170
Fill/DataGrid 260
Fill 220
As you can see, looping in DataReader indeed takes 70-80% of Fill() time
(170/220 *100 = 77% in this case).
Binding to the Grid is very fast (because only a few records are shown).
And ListView is not very efficient data storage.
So, if you come up with efficient data storage you can bound to a GUI
controls, you could expect up to (if you storage is infinitely fast) 20-30%
performance gain compared to DataTable/Fill().
Is it worth it? I don't think so, that's a lot of coding to do and results
might be disappointing (don't get me wrong, but it's really hard to come up
with infinitely fast data storage).
So, if you have a lot of records, and, thus, performance problems, just
wait a bit for V2 to come out and use SqlCeResultSet.
For 100-200 records DataTable/Grid is the way to go, 250-500 ms is quite
responsive.
As to opening and closing connection, I'm not sure what do you mean by that.
Connection is handled in exactly the same way: open it before using Fill()
or executing DataReader, close it as you done. It's the same code (see
below).
As I mentioned, Fill() actually using DataReader to get the data, so it's
pretty much the same code.
Best regards,
Ilya
This posting is provided "AS IS" with no warranties, and confers no rights.
using System;
using System.Drawing;
using System.Collections;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlServerCe;
using System.IO;
using System.Reflection;
namespace lvtest
{
/// <summary>
/// Summary description for Form1.
/// </summary>
public class Form1 : System.Windows.Forms.Form
{
private System.Windows.Forms.ListView listView1;
private System.Windows.Forms.DataGrid dataGrid1;
private System.Windows.Forms.Button button1;
private System.Windows.Forms.Button button2;
private System.Windows.Forms.Button button3;
private System.Windows.Forms.TextBox textBox1;
private System.Windows.Forms.Button button4;
private System.Windows.Forms.Button button5;
private System.Windows.Forms.MainMenu mainMenu1;
public Form1()
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
//
// TODO: Add any constructor code after InitializeComponent call
//
}
/// <summary>
/// Clean up any resources being used.
/// </summary>
protected override void Dispose( bool disposing )
{
base.Dispose( disposing );
}
#region Windows Form Designer generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.mainMenu1 = new System.Windows.Forms.MainMenu();
this.listView1 = new System.Windows.Forms.ListView();
this.dataGrid1 = new System.Windows.Forms.DataGrid();
this.button1 = new System.Windows.Forms.Button();
this.button2 = new System.Windows.Forms.Button();
this.button3 = new System.Windows.Forms.Button();
this.textBox1 = new System.Windows.Forms.TextBox();
this.button4 = new System.Windows.Forms.Button();
this.button5 = new System.Windows.Forms.Button();
//
// listView1
//
this.listView1.FullRowSelect = true;
this.listView1.Location = new System.Drawing.Point(8, 8);
this.listView1.Size = new System.Drawing.Size(224, 80);
this.listView1.View = System.Windows.Forms.View.Details;
//
// dataGrid1
//
this.dataGrid1.Location = new System.Drawing.Point(8, 96);
this.dataGrid1.Size = new System.Drawing.Size(224, 88);
this.dataGrid1.Text = "dataGrid1";
//
// button1
//
this.button1.Location = new System.Drawing.Point(8, 192);
this.button1.Size = new System.Drawing.Size(64, 20);
this.button1.Text = "Listview";
this.button1.Click += new System.EventHandler(this.button1_Click);
//
// button2
//
this.button2.Location = new System.Drawing.Point(8, 216);
this.button2.Size = new System.Drawing.Size(64, 20);
this.button2.Text = "Grid";
this.button2.Click += new System.EventHandler(this.button2_Click);
//
// button3
//
this.button3.Location = new System.Drawing.Point(168, 240);
this.button3.Size = new System.Drawing.Size(64, 20);
this.button3.Text = "Quit";
this.button3.Click += new System.EventHandler(this.button3_Click);
//
// textBox1
//
this.textBox1.Location = new System.Drawing.Point(8, 240);
this.textBox1.Size = new System.Drawing.Size(144, 22);
this.textBox1.Text = "Click button to start..";
//
// button4
//
this.button4.Location = new System.Drawing.Point(88, 192);
this.button4.Size = new System.Drawing.Size(64, 20);
this.button4.Text = "Reader";
this.button4.Click += new System.EventHandler(this.button4_Click);
//
// button5
//
this.button5.Location = new System.Drawing.Point(88, 216);
this.button5.Size = new System.Drawing.Size(64, 20);
this.button5.Text = "Fill";
this.button5.Click += new System.EventHandler(this.button5_Click);
//
// Form1
//
this.Controls.Add(this.button5);
this.Controls.Add(this.button4);
this.Controls.Add(this.textBox1);
this.Controls.Add(this.button3);
this.Controls.Add(this.button2);
this.Controls.Add(this.button1);
this.Controls.Add(this.dataGrid1);
this.Controls.Add(this.listView1);
this.Menu = this.mainMenu1;
this.Text = "Form1";
}
#endregion
/// <summary>
/// The main entry point for the application.
/// </summary>
static void Main()
{
Application.Run(new Form1());
}
private void button1_Click(object sender, System.EventArgs e)
{
try
{
this.listView1.Clear();
this.textBox1.Text = "Testing listview...";
string path =
Path.GetDirectoryName(Assembly.GetExecutingAssembly().GetName().CodeBase);
SqlCeConnection con = new
SqlCeConnection(String.Format("DataSource={0}\\NorthwindDemo.sdf", path));
con.Open();
SqlCeCommand cmd = con.CreateCommand();
cmd.CommandText = "Customers";
cmd.CommandType = CommandType.TableDirect;
int ticks = System.Environment.TickCount; // Start timer
SqlCeDataReader r = cmd.ExecuteReader(); // Get reader
this.listView1.BeginUpdate();
for (int i = 0; i < r.FieldCount; i++ ) // Set up columns
{
this.listView1.Columns.Add(r.GetName(i), -2,
HorizontalAlignment.Center);
}
string[] columns = new string[r.FieldCount];
while (r.Read())
{
for (int i = 0; i < r.FieldCount; i++ ) // Load data from the reader
{
columns
= r.ToString();
}
this.listView1.Items.Add(new ListViewItem(columns));
// And populate ListView with it.
}
this.listView1.EndUpdate();
ticks = System.Environment.TickCount - ticks;
// Stop timer
r.Close();
cmd.Dispose();
con.Close();
this.textBox1.Text = String.Format("{0} rows in {1} ms",
this.listView1.Items.Count, ticks);
}
catch (Exception ex)
{
this.textBox1.Text = String.Format ("Exception: {0}", ex);
}
}
private void button3_Click(object sender, System.EventArgs e)
{
this.Close();
}
private void button2_Click(object sender, System.EventArgs e)
{
try
{
this.textBox1.Text = "Testing datagrid...";
string path =
Path.GetDirectoryName(Assembly.GetExecutingAssembly().GetName().CodeBase);
SqlCeConnection con = new
SqlCeConnection(String.Format("DataSource={0}\\NorthwindDemo.sdf", path));
con.Open();
SqlCeCommand cmd = con.CreateCommand();
cmd.CommandText = "Customers";
cmd.CommandType = CommandType.TableDirect;
SqlCeDataAdapter da = new SqlCeDataAdapter(cmd);
DataTable table = new DataTable();
int ticks = System.Environment.TickCount; // Start timer
da.Fill(table); // Get data
this.dataGrid1.DataSource = table; // Show data
ticks = System.Environment.TickCount - ticks;
// Stop timer
da.Dispose();
cmd.Dispose();
con.Close();
this.textBox1.Text = String.Format("{0} rows in {1} ms",
table.Rows.Count, ticks);
}
catch (Exception ex)
{
this.textBox1.Text = String.Format ("Exception: {0}", ex);
}
}
private void button5_Click(object sender, System.EventArgs e)
{
try
{
this.textBox1.Text = "Testing Fill()...";
string path =
Path.GetDirectoryName(Assembly.GetExecutingAssembly().GetName().CodeBase);
SqlCeConnection con = new
SqlCeConnection(String.Format("DataSource={0}\\NorthwindDemo.sdf", path));
con.Open();
SqlCeCommand cmd = con.CreateCommand();
cmd.CommandText = "Customers";
cmd.CommandType = CommandType.TableDirect;
SqlCeDataAdapter da = new SqlCeDataAdapter(cmd);
DataTable table = new DataTable();
int ticks = System.Environment.TickCount; // Start timer
da.Fill(table); // Get data
ticks = System.Environment.TickCount - ticks;
// Stop timer
da.Dispose();
cmd.Dispose();
con.Close();
this.textBox1.Text = String.Format("{0} rows in {1} ms",
table.Rows.Count, ticks);
}
catch (Exception ex)
{
this.textBox1.Text = String.Format ("Exception: {0}", ex);
}
}
private void button4_Click(object sender, System.EventArgs e)
{
try {
this.textBox1.Text = "Testing Reader...";
string path =
Path.GetDirectoryName(Assembly.GetExecutingAssembly().GetName().CodeBase);
SqlCeConnection con = new
SqlCeConnection(String.Format("DataSource={0}\\NorthwindDemo.sdf", path));
con.Open();
SqlCeCommand cmd = con.CreateCommand();
cmd.CommandText = "Customers";
cmd.CommandType = CommandType.TableDirect;
int ticks = System.Environment.TickCount; // Start timer
SqlCeDataReader r = cmd.ExecuteReader(); // Get reader
object dummy;
int rows = 0;
for (int i = 0; i < r.FieldCount; i++ ) // Set up columns
{
dummy = r.GetName(i);
}
while (r.Read())
{
for (int i = 0; i < r.FieldCount; i++ ) // Load data from the reader
{
dummy = r; // Read data and discard
}
rows++;
}
ticks = System.Environment.TickCount - ticks;
// Stop timer
r.Close();
cmd.Dispose();
con.Close();
this.textBox1.Text = String.Format("{0} rows in {1} ms", rows, ticks);
}
catch (Exception ex)
{
this.textBox1.Text = String.Format ("Exception: {0}", ex);
}
}
}
}
--------------------
From: "Jan Yeh [MVP]" <
[email protected]>
References: <
[email protected]>
<[email protected]>
<HY#[email protected]>
Subject: Re: what is faster ?
Date: Sun, 11 Apr 2004 11:45:34 +0800
Lines: 421
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Message-ID: <
[email protected]>
Newsgroups: microsoft.public.dotnet.framework.compactframework
NNTP-Posting-Host: 61-231-51-102.hinet-ip.hinet.net 61.231.51.102
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.compactframework:50711
X-Tomcat-NG: microsoft.public.dotnet.framework.compactframework
Hi, Ilya
Would you make a little modification to your test code?
Show us the performance
1. Data loading by DataReader and DataAdapter.Fill()
2. Minus from your last result by the time of DataReader and Fill, that
show us approx. time of databinding of DataGrid vs adding items of
ListView.
BTW, although XML overhead is zero as you said, Fill() has to open and close
the connection by itself, that is what it did more than DataReader.
Interesting topic..
--
Best Regards,
Jan Yeh
MVP(Windows CE.NET), MCAD, .NETcf Developer & Consultant
Mobile Mind Co., Ltd. @ Taiwan
"Ilya Tumanov [MS]" said:
You are correct, Fill() is indeed doing "everything that DataReader does".
In fact, Fill() uses DataReader under the hood to retrieve the data.
However, XML is not used in this case at all, so "all the xml overhead" is
exactly zero.
There's an overhead of managing dynamic storage, verifying constraints,
supporting relations and so on, however.
Generally, looping in DataReader takes about 70-80% of total Fill() time,
so 20-30% is all you could gain from switching from Fill() to DataReader.
That's noticeable, but you would have to come up with efficient way to
store data (and ListView is not).
The best way to go is to load only visible part of data. This approach is
used in SqlCeResultSet class in V2.
No matter how many records you have in a data base, only few will be loaded
and this is really fast.
Best regards,
Ilya
This posting is provided "AS IS" with no warranties, and confers no rights.
Subject: Re: what is faster ?
Date: Fri, 9 Apr 2004 15:08:04 -0700
Lines: 334
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Message-ID: <
[email protected]>
Newsgroups: microsoft.public.dotnet.framework.compactframework
NNTP-Posting-Host: 204.249.181.133
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.compactframework:50677
X-Tomcat-NG: microsoft.public.dotnet.framework.compactframework
I expect Fill to be a slower option because it has to do everything that
DataReader does plus all the xml overhead. Unfortunately the use of ListView
throws an extra variable into this equation....
--
Alex Feinman
---
Visit
http://www.opennetcf.org
Rob,
I see no reasons why ListView/DataReader approach should be faster than
DataTable/Fill/DataGrid approach.
You're loading and storing the same amount of data in both cases, right?
I would also suspect DataTable is more efficient data storage than
ListView
(which is, after all, a GUI control, not a storage).
Let's run a simple test (below) to find out which way is faster...
It uses NorthwindDemo.sdf database (included with VS samples) and loads
data from 'Customer' table.
If you click on a "Reader" button, it will use DataReader to populate
ListView.
If you click on a "Fill" button, it will use DataAdapter.Fill() to
populate
DataTable and bind it to DataGrid.
Results (SP2, time in ms on Toshiba e750):
First attempt Consequential attempts
ListView/DataReader 1200 950
DataGrid/Fill 847 260
I would say, DataGrid/Fill is noticeably faster.
Best regards,
Ilya
This posting is provided "AS IS" with no warranties, and confers no
rights.
using System;
using System.Drawing;
using System.Collections;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlServerCe;
using System.IO;
using System.Reflection;
namespace lvtest
{
/// <summary>
/// Summary description for Form1.
/// </summary>
public class Form1 : System.Windows.Forms.Form
{
private System.Windows.Forms.ListView listView1;
private System.Windows.Forms.DataGrid dataGrid1;
private System.Windows.Forms.Button button1;
private System.Windows.Forms.Button button2;
private System.Windows.Forms.Button button3;
private System.Windows.Forms.TextBox textBox1;
private System.Windows.Forms.MainMenu mainMenu1;
public Form1()
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
//
// TODO: Add any constructor code after InitializeComponent call
//
}
/// <summary>
/// Clean up any resources being used.
/// </summary>
protected override void Dispose( bool disposing )
{
base.Dispose( disposing );
}
#region Windows Form Designer generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.mainMenu1 = new System.Windows.Forms.MainMenu();
this.listView1 = new System.Windows.Forms.ListView();
this.dataGrid1 = new System.Windows.Forms.DataGrid();
this.button1 = new System.Windows.Forms.Button();
this.button2 = new System.Windows.Forms.Button();
this.button3 = new System.Windows.Forms.Button();
this.textBox1 = new System.Windows.Forms.TextBox();
//
// listView1
//
this.listView1.FullRowSelect = true;
this.listView1.Location = new System.Drawing.Point(8, 8);
this.listView1.Size = new System.Drawing.Size(224, 80);
this.listView1.View = System.Windows.Forms.View.Details;
//
// dataGrid1
//
this.dataGrid1.Location = new System.Drawing.Point(8, 96);
this.dataGrid1.Size = new System.Drawing.Size(224, 88);
this.dataGrid1.Text = "dataGrid1";
//
// button1
//
this.button1.Location = new System.Drawing.Point(8, 200);
this.button1.Text = "Reader";
this.button1.Click += new System.EventHandler(this.button1_Click);
//
// button2
//
this.button2.Location = new System.Drawing.Point(8, 240);
this.button2.Text = "Fill";
this.button2.Click += new System.EventHandler(this.button2_Click);
//
// button3
//
this.button3.Location = new System.Drawing.Point(160, 240);
this.button3.Text = "Quit";
this.button3.Click += new System.EventHandler(this.button3_Click);
//
// textBox1
//
this.textBox1.Location = new System.Drawing.Point(96, 200);
this.textBox1.Size = new System.Drawing.Size(136, 22);
this.textBox1.Text = "Click button to start..";
//
// Form1
//
this.Controls.Add(this.textBox1);
this.Controls.Add(this.button3);
this.Controls.Add(this.button2);
this.Controls.Add(this.button1);
this.Controls.Add(this.dataGrid1);
this.Controls.Add(this.listView1);
this.Menu = this.mainMenu1;
this.Text = "Form1";
}
#endregion
/// <summary>
/// The main entry point for the application.
/// </summary>
static void Main()
{
Application.Run(new Form1());
}
private void button1_Click(object sender, System.EventArgs e)
{
try
{
this.listView1.Clear();
this.textBox1.Text = "Testing listview...";
string path =
Path.GetDirectoryName(Assembly.GetExecutingAssembly().GetName().CodeBase);
SqlCeConnection con = new
SqlCeConnection(String.Format("DataSource={0}\\NorthwindDemo.sdf", path));
con.Open();
SqlCeCommand cmd = con.CreateCommand();
cmd.CommandText = "Customers";
cmd.CommandType = CommandType.TableDirect;
int ticks = System.Environment.TickCount; // Start timer
SqlCeDataReader r = cmd.ExecuteReader(); // Get reader
this.listView1.BeginUpdate();
for (int i = 0; i < r.FieldCount; i++ ) // Set up columns
{
this.listView1.Columns.Add(r.GetName(i), -2,
HorizontalAlignment.Center);
}
string[] columns = new string[r.FieldCount];
while (r.Read())
{
for (int i = 0; i < r.FieldCount; i++ ) // Load data from the reader
{
columns = r.ToString();
}
this.listView1.Items.Add(new ListViewItem(columns));
// And populate ListView with it.
}
this.listView1.EndUpdate();
ticks = System.Environment.TickCount - ticks;
// Stop timer
r.Close();
cmd.Dispose();
con.Close();
this.textBox1.Text = String.Format("{0} rows in {1} ms",
this.listView1.Items.Count, ticks);
}
catch (Exception ex)
{
this.textBox1.Text = String.Format ("Exception: {0}", ex);
}
}
private void button3_Click(object sender, System.EventArgs e)
{
this.Close();
}
private void button2_Click(object sender, System.EventArgs e)
{
try
{
this.textBox1.Text = "Testing datagrid...";
string path =
Path.GetDirectoryName(Assembly.GetExecutingAssembly().GetName().CodeBase);
SqlCeConnection con = new
SqlCeConnection(String.Format("DataSource={0}\\NorthwindDemo.sdf", path));
con.Open();
SqlCeCommand cmd = con.CreateCommand();
cmd.CommandText = "Customers";
cmd.CommandType = CommandType.TableDirect;
SqlCeDataAdapter da = new SqlCeDataAdapter(cmd);
DataTable table = new DataTable();
int ticks = System.Environment.TickCount; // Start timer
da.Fill(table); // Get data
this.dataGrid1.DataSource = table; // Show data
ticks = System.Environment.TickCount - ticks;
// Stop timer
da.Dispose();
cmd.Dispose();
con.Close();
this.textBox1.Text = String.Format("{0} rows in {1} ms",
table.Rows.Count, ticks);
}
catch (Exception ex)
{
this.textBox1.Text = String.Format ("Exception: {0}", ex);
}
}
}
}
From: "Rob Tiffany, eMVP"
References: <
[email protected]>
Subject: Re: what is faster ?
Date: Fri, 9 Apr 2004 09:38:19 -0500
Lines: 24
Organization: Hood Canal Systems
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Message-ID: <
[email protected]>
Newsgroups: microsoft.public.dotnet.framework.compactframework
NNTP-Posting-Host: cs662552-60.houston.rr.com 66.25.52.60
Path:
cpmsftngxa06.phx.gbl!cpmsftngxa10.phx.gbl!TK2MSFTFEED02.phx.gbl!TK2MSFTNGXA0
