I have too many messages to respond to regarding the C#/VFP comparison I carried out, so I'm going to make one big response as there are too many questions & speculation going on for me to answer individually.
If any remote location is down or accessible, ignore the location and inform the user
My original message to Terry did outline this and the results, however I'm going to compare results again, this time using all of your suggestions in "tuning" the fox side of things, not only that but I've written both apps as they were fully intended - the results, IMO, are more correct than the previous ones.
Note: These tests are comparing how long it takes to execute the querys, bundle them into one result and do an age calculation, not much at the end of the day.
Record Count = 164
Visual FoxPro 8.0Test Run | Time-taken
1 0.591
2 0.490
3 0.080
4 0.070
5 0.310
Average 0.308
C#Test Run | Time-taken
1 0.781
2 0.580
3 0.250
4 0.260
5 0.480
Average 0.470
Record Count = 1563
Visual FoxPro 8.0Test Run | Time-taken
1 2.334
2 0.190
3 1.993
4 0.180
5 3.405
Average 1.620
C#Test Run | Time-taken
1 17.574 (?!?)
2 2.152
3 0.350
4 2.303
5 0.340
Average 4.543
Record Count = 13943
Visual FoxPro 8.0Test Run | Time-taken
1 17.625
2 18.357
3 17.695
4 17.945
5 18.277
Average 17.979
C#Test Run | Time-taken
1 36.560
2 16.903
3 16.933
4 16.783
5 17.253
Average 20.886
And there you have it, these seem a bit more realistic, although I am still quite impressed with performance on C#, however, it's not until the query has been cached that it picks up to fox's level...
Here are the apps:
FOX - MAIN.PRGSET EXCLUSIVE OFF
SET TALK OFF
SET DELETED ON
SET CONSOLE OFF
SET EXACT OFF
SET ANSI OFF
SET DATE TO British
SET CENTURY ON
SET SAFETY off
DO FORM Console
READ EVENTS
CLOSE ALL
CLEAR ALL
QUIT
FOX - LIST.PRG
DEFINE CLASS frmlist AS form
Top = 11
Left = 5
Height = 344
Width = 649
DoCreate = .T.
Caption = "Results"
MaxButton = .F.
MinButton = .F.
Name = "frmList"
ADD OBJECT grdlist AS grid WITH ;
ColumnCount = 11, ;
DeleteMark = .F., ;
Height = 324, ;
Left = 12, ;
RecordMark = .F., ;
RecordSource = "Results", ;
Top = 12, ;
Width = 624, ;
Name = "grdList", ;
Column1.ControlSource = "Results.Cl_Title", ;
Column1.Name = "Column1", ;
Column2.ControlSource = "Results.Cl_Sname", ;
Column2.Name = "Column2", ;
Column3.ControlSource = "Results.Cl_Fname", ;
Column3.Name = "Column3", ;
Column4.ControlSource = "Results.Cl_Dob", ;
Column4.Name = "Column4", ;
Column5.ControlSource = "Results.Cl_Age", ;
Column5.Name = "Column5", ;
Column6.ControlSource = "Results.Cl_Sex", ;
Column6.Name = "Column6", ;
Column7.ControlSource = "Results.Pr_Addr1", ;
Column7.Name = "Column7", ;
Column8.ControlSource = "Results.Pr_Addr2", ;
Column8.Name = "Column8", ;
Column9.ControlSource = "Results.Pr_Addr3", ;
Column9.Name = "Column9", ;
Column10.ControlSource = "Results.Pr_Addr4", ;
Column10.Name = "Column10", ;
Column11.ControlSource = "Results.Pr_PCode", ;
Column11.Name = "Column11"
ADD OBJECT frmlist.grdlist.column1.header1 AS header WITH ;
Caption = "Title", ;
Name = "Header1"
ADD OBJECT frmlist.grdlist.column1.text1 AS textbox WITH ;
BorderStyle = 0, ;
Margin = 0, ;
ForeColor = RGB(0,0,0), ;
BackColor = RGB(255,255,255), ;
Name = "Text1"
ADD OBJECT frmlist.grdlist.column2.header1 AS header WITH ;
Caption = "Surname", ;
Name = "Header1"
ADD OBJECT frmlist.grdlist.column2.text1 AS textbox WITH ;
BorderStyle = 0, ;
Margin = 0, ;
ForeColor = RGB(0,0,0), ;
BackColor = RGB(255,255,255), ;
Name = "Text1"
ADD OBJECT frmlist.grdlist.column3.header1 AS header WITH ;
Caption = "Forename", ;
Name = "Header1"
ADD OBJECT frmlist.grdlist.column3.text1 AS textbox WITH ;
BorderStyle = 0, ;
Margin = 0, ;
ForeColor = RGB(0,0,0), ;
BackColor = RGB(255,255,255), ;
Name = "Text1"
ADD OBJECT frmlist.grdlist.column4.header1 AS header WITH ;
Caption = "DOB", ;
Name = "Header1"
ADD OBJECT frmlist.grdlist.column4.text1 AS textbox WITH ;
BorderStyle = 0, ;
Margin = 0, ;
ForeColor = RGB(0,0,0), ;
BackColor = RGB(255,255,255), ;
Name = "Text1"
ADD OBJECT frmlist.grdlist.column5.header1 AS header WITH ;
Caption = "Age", ;
Name = "Header1"
ADD OBJECT frmlist.grdlist.column5.text1 AS textbox WITH ;
BorderStyle = 0, ;
Margin = 0, ;
ForeColor = RGB(0,0,0), ;
BackColor = RGB(255,255,255), ;
Name = "Text1"
ADD OBJECT frmlist.grdlist.column6.header1 AS header WITH ;
Caption = "Sex", ;
Name = "Header1"
ADD OBJECT frmlist.grdlist.column6.text1 AS textbox WITH ;
BorderStyle = 0, ;
Margin = 0, ;
ForeColor = RGB(0,0,0), ;
BackColor = RGB(255,255,255), ;
Name = "Text1"
ADD OBJECT frmlist.grdlist.column7.header1 AS header WITH ;
Caption = "Address 1", ;
Name = "Header1"
ADD OBJECT frmlist.grdlist.column7.text1 AS textbox WITH ;
BorderStyle = 0, ;
Margin = 0, ;
ForeColor = RGB(0,0,0), ;
BackColor = RGB(255,255,255), ;
Name = "Text1"
ADD OBJECT frmlist.grdlist.column8.header1 AS header WITH ;
Caption = "Address 2", ;
Name = "Header1"
ADD OBJECT frmlist.grdlist.column8.text1 AS textbox WITH ;
BorderStyle = 0, ;
Margin = 0, ;
ForeColor = RGB(0,0,0), ;
BackColor = RGB(255,255,255), ;
Name = "Text1"
ADD OBJECT frmlist.grdlist.column9.header1 AS header WITH ;
Caption = "Address 3", ;
Name = "Header1"
ADD OBJECT frmlist.grdlist.column9.text1 AS textbox WITH ;
BorderStyle = 0, ;
Margin = 0, ;
ForeColor = RGB(0,0,0), ;
BackColor = RGB(255,255,255), ;
Name = "Text1"
ADD OBJECT frmlist.grdlist.column10.header1 AS header WITH ;
Caption = "Address 4", ;
Name = "Header1"
ADD OBJECT frmlist.grdlist.column10.text1 AS textbox WITH ;
BorderStyle = 0, ;
Margin = 0, ;
ForeColor = RGB(0,0,0), ;
BackColor = RGB(255,255,255), ;
Name = "Text1"
ADD OBJECT frmlist.grdlist.column11.header1 AS header WITH ;
Caption = "Postcode", ;
Name = "Header1"
ADD OBJECT frmlist.grdlist.column11.text1 AS textbox WITH ;
BorderStyle = 0, ;
Margin = 0, ;
ForeColor = RGB(0,0,0), ;
BackColor = RGB(255,255,255), ;
Name = "Text1"
PROCEDURE Init
GO TOP IN Results
ENDPROC
ENDDEFINE
FOX - CONSOLE.PRG
DEFINE CLASS frmconsole AS form
Top = 28
Left = 23
Height = 86
Width = 193
DoCreate = .T.
Caption = "Client Search"
MaxButton = .F.
MinButton = .F.
Name = "frmConsole"
ADD OBJECT cmdsearch AS commandbutton WITH ;
Top = 48, ;
Left = 12, ;
Height = 27, ;
Width = 168, ;
Caption = "Search", ;
Name = "cmdSearch"
ADD OBJECT txtsurname AS textbox WITH ;
Format = "!K", ;
Height = 25, ;
Left = 12, ;
Top = 12, ;
Width = 169, ;
Name = "txtSurname"
PROCEDURE executesearch
LOCAL Surname
Surname = ALLTRIM(thisForm.txtSurname.Value)
CREATE CURSOR Results(Cl_Title C(4), ;
Cl_Sname C(25), ;
Cl_Fname C(25),;
Cl_Dob D, ;
Cl_Age N(3), ;
Cl_Sex C(7), ;
Pr_Addr1 C(25) NULL, ;
Pr_Addr2 C(25) NULL, ;
Pr_Addr3 C(25) NULL, ;
Pr_Addr4 C(25) NULL, ;
Pr_Pcode C(10) NULL)
CREATE CURSOR Failed (Source C(30))
FOR o = 1 TO ALEN(Source, 1)
Source(o, 2) = ALLTRIM(Source(o, 2))
IF !DIRECTORY(Source(o, 2))
INSERT INTO Failed VALUES(Source(o, 1))
LOOP
ENDIF
SELECT DISTINCT Cl_Title, Cl_Sname, Cl_Fname, Cl_Dob,;
IIF(Cl_Sex="M","Male", IIF(Cl_Sex = "F", "Female", "Unknown")) Cl_Sex,;
Pr_Addr1, Pr_Addr2, Pr_Addr3, Pr_Addr4, Pr_PCode;
FROM (Source(o, 2) + "\Client") Client;
LEFT JOIN (Source(o, 2) + "\Property") Property;
ON Client.Pr_Code = Property.Pr_Code;
WHERE UPPER(Cl_Sname) = Surname;
ORDER BY Cl_Sname, Cl_Fname, Cl_Dob;
INTO CURSOR SourceResults
SELECT Results
APPEND FROM DBF("SourceResults")
UPDATE Results;
SET Cl_Age = thisForm.Getage(Cl_Dob)
ENDFOR
=STRTOFILE(STR(RECCOUNT("Results")) + " Records took " + STR(SECONDS() - Start, 10, 3) + ",", "Results.txt", .T.)
this.CheckAvailability()
this.ShowResults()
ENDPROC
PROCEDURE showresults
DO FORM List
ENDPROC
PROCEDURE checkavailability
LOCAL Failed
Failed = ""
IF RECCOUNT("Failed") > 0
Failed = Failed + CHR(13) + Failed.Source
=MESSAGEBOX("Please note the following sources could not be connected to : " + CHR(13) + Failed, 16, "Attention")
ENDIF
ENDPROC
PROCEDURE getage
LPARAMETERS birthDate
LOCAL Age as Integer
IF EMPTY(birthDate)
RETURN 0
ENDIF
Age = YEAR(DATE()) - YEAR(birthDate)
Then = CTOD(STR(DAY(birthDate)) + "/" + ;
STR(MONTH(birthDate)) + "/" + ;
STR(YEAR(DATE())))
IF (DATE() - Then) >= 0
Age = Age +1
ENDIF
RETURN Age
ENDPROC
PROCEDURE Init
PUBLIC Source[1, 1]
SELECT *;
FROM Z:\USEFUL\TestSites;
INTO ARRAY Source
IF FILE("Results.txt")
DELETE FILE Results.Txt
ENDIF
ENDPROC
PROCEDURE Unload
CLEAR EVENTS
ENDPROC
PROCEDURE cmdsearch.Click
PUBLIC Start
Start = SECONDS()
thisForm.Executesearch()
=STRTOFILE("To show took " + STR(SECONDS() - Start, 10, 3), "Results.txt", .T.)
ENDPROC
ENDDEFINE
C# main.csusing System;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;
using System.Collections;
using System.IO;
namespace Kensa
{
#region Kensan
public class Kensa
{
public static void Main()
{
StreamWriter sw = new StreamWriter("Results.txt");
Console.SetOut(sw);
MainConsole main = new MainConsole();
main.ShowDialog();
sw.Close();
}
public static int GetAge(DateTime val)
{
DateTime then = DateTime.Parse(string.Format("{0}/{1}", val.Day, val.Month));
int age = DateTime.Now.Year - val.Year + (then <= DateTime.Now.Date ? 0 : -1);
return (age < 0) ? 0 : age;
}
}
#endregion
#region MainConsole
public class MainConsole : System.Windows.Forms.Form
{
private System.Windows.Forms.TextBox txtSurname;
private System.Windows.Forms.Button btnGo;
private System.ComponentModel.Container components = null;
private DateTime startTime;
private string commandString = "SELECT DISTINCT Cl_Title, Cl_Sname, Cl_Fname, Cl_Dob, " +
"Cl_Sex, Pr_Addr1, Pr_Addr2, Pr_Addr3, Pr_Addr4, Pr_PCode " +
"FROM Client " +
"LEFT JOIN Property " +
"ON Client.Pr_Code = Property.Pr_Code " +
"ORDER BY Cl_Sname, Cl_Fname, Cl_Dob";
private Hashtable source;
private ArrayList result;
public MainConsole()
{
InitializeComponent();
getSources();
}
private void getSources()
{
source = new Hashtable();
OleDbConnection conn = new OleDbConnection(@"Provider=VFPOLEDB.1;Data Source=z:\useful");
conn.Open();
// Obtain sources
DataTable dt = new DataTable();
OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM TestSites", conn);
da.Fill(dt);
for (int idx = 0; idx < dt.Rows.Count; idx++)
{
conn = null;
if (Directory.Exists(dt.Rows[idx][1].ToString().Trim()))
{
conn = new OleDbConnection(string.Format("Provider=VFPOLEDB.1;Data Source={0}", dt.Rows[idx][1].ToString().Trim()));
conn.Open();
}
source.Add(dt.Rows[idx][0], conn);
new OleDbCommand("SET DELETED ON", conn).ExecuteNonQuery();
new OleDbCommand("SET ANSI OFF", conn).ExecuteNonQuery();
new OleDbCommand("SET EXACT OFF", conn).ExecuteNonQuery();
}
}
private void executeSearch()
{
// START
startTime = DateTime.Now;
result = new ArrayList();
string surname = txtSurname.Text.ToUpper().Trim();
IDictionaryEnumerator de = source.GetEnumerator();
while (de.MoveNext())
{
DataTable dt = new DataTable();
OleDbDataAdapter da = new OleDbDataAdapter(string.Format("{0} Where UPPER(Cl_Sname) = '{1}'",
commandString, surname), de.Value as OleDbConnection);
da.Fill(dt);
result.AddRange(dt.Rows);
}
Console.WriteLine("{0} records took {1}", result.Count, DateTime.Now - startTime);
showResult();
// FINISH
Console.WriteLine("Took {0} to Show", DateTime.Now - startTime);
}
private void showResult()
{
string sources = "";
IDictionaryEnumerator de = source.GetEnumerator();
while (de.MoveNext())
if (de.Value == null) sources += de.Key + "\n";
if (sources != "")
MessageBox.Show("Please note the following sources could not be connected to : \n" + sources);
ListForm form = new ListForm(result);
form.Show();
}
private void btnGo_Click(object sender, System.EventArgs e)
{
executeSearch();
}
#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.txtSurname = new System.Windows.Forms.TextBox();
this.btnGo = new System.Windows.Forms.Button();
this.SuspendLayout();
//
// txtSurname
//
this.txtSurname.Location = new System.Drawing.Point(8, 8);
this.txtSurname.Name = "txtSurname";
this.txtSurname.Size = new System.Drawing.Size(144, 20);
this.txtSurname.TabIndex = 0;
this.txtSurname.Text = "";
//
// btnGo
//
this.btnGo.Location = new System.Drawing.Point(8, 40);
this.btnGo.Name = "btnGo";
this.btnGo.Size = new System.Drawing.Size(144, 23);
this.btnGo.TabIndex = 1;
this.btnGo.Text = "Search";
this.btnGo.Click += new System.EventHandler(this.btnGo_Click);
//
// MainConsole
//
this.AcceptButton = this.btnGo;
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.ClientSize = new System.Drawing.Size(168, 78);
this.Controls.Add(this.btnGo);
this.Controls.Add(this.txtSurname);
this.Name = "MainConsole";
this.Text = "Console";
this.ResumeLayout(false);
}
protected override void Dispose( bool disposing )
{
if( disposing )
{
if(components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
}
#endregion
}
#endregion
#region ListForm
public class ListForm : System.Windows.Forms.Form
{
private System.Windows.Forms.ListView lstResult;
private System.Windows.Forms.ColumnHeader Title;
private System.Windows.Forms.ColumnHeader Surname;
private System.Windows.Forms.ColumnHeader Forename;
private System.Windows.Forms.ColumnHeader DOB;
private System.Windows.Forms.ColumnHeader Age;
private System.Windows.Forms.ColumnHeader Address1;
private System.Windows.Forms.ColumnHeader Address2;
private System.Windows.Forms.ColumnHeader Address3;
private System.Windows.Forms.ColumnHeader Address4;
private System.Windows.Forms.ColumnHeader Postcode;
private System.Windows.Forms.ColumnHeader Gender;
private System.ComponentModel.Container components = null;
public ListForm(ArrayList result)
{
InitializeComponent();
foreach (DataRow row in result)
{
ListViewItem item = new ListViewItem(row[0].ToString());
item.SubItems.Add(row[1].ToString());
item.SubItems.Add(row[2].ToString());
item.SubItems.Add(((DateTime)row[3]).ToShortDateString());
item.SubItems.Add(Kensa.GetAge((DateTime)row[3]).ToString());
item.SubItems.Add((row[4].ToString() == "M") ? "Male" : (row[4].ToString() == "F") ? "Female" : "Unknown");
item.SubItems.Add(row[5].ToString());
item.SubItems.Add(row[6].ToString());
item.SubItems.Add(row[7].ToString());
item.SubItems.Add(row[8].ToString());
item.SubItems.Add(row[9].ToString());
lstResult.Items.Add(item);
}
}
#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.lstResult = new System.Windows.Forms.ListView();
this.Title = new System.Windows.Forms.ColumnHeader();
this.Surname = new System.Windows.Forms.ColumnHeader();
this.Forename = new System.Windows.Forms.ColumnHeader();
this.DOB = new System.Windows.Forms.ColumnHeader();
this.Age = new System.Windows.Forms.ColumnHeader();
this.Address1 = new System.Windows.Forms.ColumnHeader();
this.Address2 = new System.Windows.Forms.ColumnHeader();
this.Address3 = new System.Windows.Forms.ColumnHeader();
this.Address4 = new System.Windows.Forms.ColumnHeader();
this.Postcode = new System.Windows.Forms.ColumnHeader();
this.Gender = new System.Windows.Forms.ColumnHeader();
this.SuspendLayout();
//
// lstResult
//
this.lstResult.Columns.AddRange(new System.Windows.Forms.ColumnHeader[] {
this.Title,
this.Surname,
this.Forename,
this.DOB,
this.Age,
this.Gender,
this.Address1,
this.Address2,
this.Address3,
this.Address4,
this.Postcode});
this.lstResult.Dock = System.Windows.Forms.DockStyle.Fill;
this.lstResult.Location = new System.Drawing.Point(0, 0);
this.lstResult.Name = "lstResult";
this.lstResult.Size = new System.Drawing.Size(616, 326);
this.lstResult.TabIndex = 0;
this.lstResult.View = System.Windows.Forms.View.Details;
//
// Title
//
this.Title.Text = "Title";
//
// Surname
//
this.Surname.Text = "Surname";
//
// Forename
//
this.Forename.Text = "Forename";
//
// DOB
//
this.DOB.Text = "DOB";
//
// Age
//
this.Age.Text = "Age";
//
// Address1
//
this.Address1.Text = "Address 1";
//
// Address2
//
this.Address2.Text = "Address 2";
//
// Address3
//
this.Address3.Text = "Address 3";
//
// Address4
//
this.Address4.Text = "Address 4";
//
// Postcode
//
this.Postcode.Text = "Postcode";
//
// Gender
//
this.Gender.Text = "Gender";
//
// ListForm
//
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.ClientSize = new System.Drawing.Size(616, 326);
this.Controls.Add(this.lstResult);
this.Name = "ListForm";
this.Text = "ListForm";
this.ResumeLayout(false);
}
protected override void Dispose( bool disposing )
{
if( disposing )
{
if(components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
}
#endregion
}
#endregion
}
