cajin


I am working on a project which accesses foxpro 9.0sp1 database through OLEDB. The datbase has about 1million records. My code simple and straighforward just like below,

conn.Open();

OleDbCommand cmd = conn.CreateCommand();

cmd.CommandText = "DELETE FROM mytable"

cmd.ExecuteNonQuery()

conn.Close();

But it will take a very long time to mark all records as deleted - 10 minutes. But if I open the database in VFP directly, and delete all records, it takes only a few seconds.

If I have a larger table, for example 3 million records. It takes about 40 minutes to delete all records through OLEDB but still takes only a few seconds to delete within VFP.

Anyone knows what's wrong with the VFP oledb provider Or is it just because I am not using it correctly

Thanks very much.




Re: Performance of Foxpro OLEDB

Naomi Nosonovsky


I haven't worked much through OleDb, but perhaps you may try DELETE ALL instead of Delete from Delete all would be VFP syntax as opposed to SQL syntax. You may be needed to do it through execscript, though.




Re: Performance of Foxpro OLEDB

cajin

Thank for your reply. I did try that, but DELETE ALL needs a table alias, that means, within VFP, I can use

USE mytable IN 0

DELETE ALL.

But through OLEDB, I am not sure how to put two commands in one commandText.

cmd.CommandText = "USE mytable IN 0; DELETE ALL"

It just did nothing. If I put them into two command, I does not work either.






Re: Performance of Foxpro OLEDB

Naomi Nosonovsky

cmd.CommandText =[execscript("USE myTable in 0" + chr(13)+chr(10) + "DELETE ALL in myTable")]



Re: Performance of Foxpro OLEDB

cajin

I am not sure what language you are using. I am using C#, so I change it to

cmd.CommandText = "[execscript(\"USE mytable in 0\r\nDELETE ALL\")]";

It did not work. the exception message is "unrecognized command verb"




Re: Performance of Foxpro OLEDB

Naomi Nosonovsky

I'm sorry, use the single quote instead of []. I used VFP syntax here, in C# single quote would work, e.g.

cmd.CommandText = "execscript('USE mytable in 0' + chr(13) + chr(10) + 'DELETE ALL')";

It should be written exactly like this, single quote inside the double quote for execscript.

execscript('USE mytable in 0' + chr(13) + chr(10) + 'DELETE ALL') // this is a character variable, so inside we may have VFP commands





Re: Performance of Foxpro OLEDB

cajin

Thanks a lot. I does work significantly faster. But, if I put 'DELETE FROM mytable' in the execscript, it is also faster. Seems like it is 'execscript' which make it faster, no matter it is DELETE SQL command or DELETE foxpro command.



Re: Performance of Foxpro OLEDB

Naomi Nosonovsky

Interesting. I'm not sure why is that. Perhaps EXECSRIPT uses different environmental settings that speed things up (say, different SET EXCLUSIVE or SET DELETED status).



Re: Performance of Foxpro OLEDB

CetinBasoz

Cajin,

VFP handles 'exclusive' faster, plus Delete from (SQL) and delete (xBase) locking semantics are different. Delete commands in VFP do not actually physically remove the records but marks them as deleted.

ExecScript has its own environment and looks like running the command directly from within VFP with a precompilation.

In case of:

"delete from table" which means delete all, if you don't need to recall records later then that means you do not need the marking and thus could use the much faster "zap". For the following on a 1.5 million records table (all were copies with different names) my timings were:

Method1: 39689

Method2: 3397

Method3: 1564

Method4: 13

Code Snippet

using System;
using System.Data;
using System.Data.OleDb;
using System.Diagnostics;

class test
{
static void Main()
{
Method1();
Method2();
Method3();
Method4();
}

private static void Method1()
{
Stopwatch tmr = new Stopwatch();
tmr.Start();
string strCon = @"Provider=VFPOLEDB;Data Source=c:\deltest";
OleDbConnection con = new OleDbConnection(strCon);
OleDbCommand cmd = con.CreateCommand();
con.Open();
cmd.CommandText = "delete from test1";
cmd.ExecuteNonQuery();
con.Close();
tmr.Stop();
Console.WriteLine("Elapsed milli: {0}",tmr.ElapsedMilliseconds);
}

private static void Method2()
{
Stopwatch tmr = new Stopwatch();
tmr.Start();
string strCon = @"Provider=VFPOLEDB;Data Source=c:\deltest";
OleDbConnection con = new OleDbConnection(strCon);
OleDbCommand cmd = con.CreateCommand();
con.Open();
cmd.CommandText = "set exclusive on";
cmd.ExecuteNonQuery();
cmd.CommandText = "delete from test2";
cmd.ExecuteNonQuery();
con.Close();
tmr.Stop();
Console.WriteLine("Elapsed milli: {0}",tmr.ElapsedMilliseconds);
}

private static void Method3()
{
Stopwatch tmr = new Stopwatch();
tmr.Start();
string strCon = @"Provider=VFPOLEDB;Data Source=c:\deltest";
OleDbConnection con = new OleDbConnection(strCon);
OleDbCommand cmd = con.CreateCommand();

con.Open();
string strScript = @"set safety off
use test3 exclusive
delete all";
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "ExecScript";
cmd.Parameters.Add("myScript",OleDbType.Char).Value = strScript;
cmd.ExecuteNonQuery();
con.Close();
tmr.Stop();
Console.WriteLine("Elapsed milli: {0}",tmr.ElapsedMilliseconds);
}

private static void Method4()
{
Stopwatch tmr = new Stopwatch();
tmr.Start();
string strCon = @"Provider=VFPOLEDB;Data Source=c:\deltest";
OleDbConnection con = new OleDbConnection(strCon);
OleDbCommand cmd = con.CreateCommand();

con.Open();
string strScript = @"set safety off
use test4 exclusive
zap";
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "ExecScript";
cmd.Parameters.Add("myScript",OleDbType.Char).Value = strScript;
cmd.ExecuteNonQuery();
con.Close();
tmr.Stop();
Console.WriteLine("Elapsed milli: {0}",tmr.ElapsedMilliseconds);
}

}





Re: Performance of Foxpro OLEDB

cajin

The conclusion is, it is 'execscript' which does the magic. So I am thinking to try if I can use execscript to speed SELECT query. unfortunately,

cmd.CommandText = "execscript('SELECT * FROM mytable')"

does not return the same result set as

cmd.CommandText = "SELECT * FROM mytable"

So any advice to speed up the SELECT query Maybe the latter is already fast, but I hope I can compare if there is any difference.




Re: Performance of Foxpro OLEDB

CetinBasoz

You're trying to make execscript() more than what it's. It's not the execscript() really which does the magic. There is not a special magic but maybe I wasn't clear enough. Your select command would not be fast from wherever you do it with whatever driver (say MSSQL) if you try to get many records. It's ADO.Net which is slow in that matter.

Add parameters to your SQL so it wouldn't return too many records and it'd be fast. You may use datareader where you don't need them in a dataset etc. (dataset filling uses a DbReader but that's not what I mean).





Re: Performance of Foxpro OLEDB

Naomi Nosonovsky

It is not EXECSCIPT, that "did" magic, but the difference in the settings. Did you already try the EXECSCRIPT in multi-user scenario I've tested that Execscript uses SET('exclusive') OFF.

Cetin is correct, you need to filter your result using a parameter. Why do you need to bring all the records back to the client Is the table big Also perhaps you only need some fields, not every field





Re: Performance of Foxpro OLEDB

cajin

When I create the OLE DB connection, the connection string already includes "Mode=Share Exclusive".

I do need to return a large dataset for every query. If I query a smaller result set as your guys advised, then I have to run multiple queries to get the full result set I need.




Re: Performance of Foxpro OLEDB

CetinBasoz

Generally it's that people think they need to return a large dataset for every query. In reality they don't need. If you really need to get them all into a dataset then you have to live with what ADO.Net offers. It's slow by nature, and gets slower as dataset size increase. I don't say do multiple queries, I say query for what you need only. It's the principal with any backend.

If you say the real purpose a bit we might have alternative solutions and if no other solutions exist use VFP insteadSmile