none
VFPOLEDB prodiver access files on the network performance RRS feed

  • Question

  • I am working on a C# app to replace an existing VFP app and am having a problem with the performance of the VFPOLEDB provider. He is a piece of test code that i ran to get some idea of what was going on.
    Code Snippet

    Stopwatch sw = new Stopwatch();
    sw.Start();
    string vfpConnectionString = "Provider=VFPOLEDB.1;Data Source=S:\\ddtest";
    OleDbConnection vConn = new OleDbConnection(vfpConnectionString);
    vConn.Open();
    sw.Stop();
    Console.WriteLine("Opening took {0} ms",sw.ElapsedMilliseconds);
    string cmdText = " SELECT adid FROM adrchur where adid=1111111132";
    sw.Reset();
    sw.Start();
    OleDbCommand dCmd = new OleDbCommand(cmdText, vConn);
    OleDbDataReader odrd = dCmd.ExecuteReader();
    sw.Stop();
    Console.WriteLine("Query took {0} ms", sw.ElapsedMilliseconds);
    sw.Reset();
    odrd.Close();
    dCmd.CommandText = "SELECT min(adid) FROM adrchur";
    sw.Start();
    odrd = dCmd.ExecuteReader();
    sw.Stop();
    Console.WriteLine("2nd Query took {0} ms", sw.ElapsedMilliseconds);


    The results of that code are:

    Opening took 43 ms
    Query took 15 ms
    2nd Query took 53446 ms

    They both return the same amount of data, and are working on the same file.

    I have also tried the second Select command with "where adid>1" to make it fully rushmore optimizeable, it did not make a difference. It is a gigabit network and the file is only about 450,000 reocrds and there is an index on the field adid. I moved the database to my local harddrive and the results were.

    Opening took 23 ms
    Query took 5 ms
    2nd Query took 8433 ms


    Both of those querries execute instantly in VFP's command window. So it looks to me like the VFPOLEDB driver is not using rushmore when using a sql command (top min max). Is this by design or a bug. Is there something I am doing wrong?

    Any help would be appreciated. Thanks,

    Doug D

    Friday, July 11, 2008 8:46 PM

All replies

  • How you are connecting to your data? What provider are you using?

     

    If you migrate the application but keep the database, there are differences on how each provider manages the data, so your mileage can vary. That being said, if you migrate the data from FoxPro to SqlServer, for example, and you have the right indexes, the queries should be almost instantaneous, as you expect.

     

    How you index adchur? Do you have an index over adid?

     

     

    Sunday, July 13, 2008 3:35 AM
  • The data must remain in VFP.  I cannot migrate the data.  I am using the VFP 9 SP2 OLEDB driver.  ADID is indexed and is the primary key.
    Wednesday, July 16, 2008 6:31 PM
  • Had u Solve it ?

    I use VFPOLEDB provider On network.
    It word slowly.
    If i open connection on te second pc it word VERY VERY VERY small

    i use Mode=Shared dany Mode  ??   It 's right

    With to me  daniele_at_levon_dot_it_nosapamm
    Thanks
    Wednesday, October 21, 2009 9:27 PM