Slow Data Access Problems

Slow Data Access Problems

Old forum URL: forums.lhotka.net/forums/t/7961.aspx


NickTaylor posted on Saturday, November 07, 2009

Hi,

I wonder if anyone can throw any light on this problem please ? Its causing me some real frustration so any offers of help would be most welcome.

I am currently converting a legacy FoxPro application to dotnet and csla. As the old application is still live I have to access data stored in FoxPro ver 2.6 free tables via the VFPOLEDB driver. For business objects returning a single record I dont have any real data access issues, but I am now writing some list objects which have to perform a small amount of processing as part of their DataPortal_Fetch code. In cases where I wish to return 100 records from a table containing 500K records, the data access appears to be painfully slow ( i.e. 8-seconds or more ). I cant seem to fathom out what is actually causing the problem, and I have even tried writing some DLL based code which performs the query directly in FoxPro and then returns XML data to dotnet, but this doesn't seem to offer any real performance advantage.

If we look at the following code ( located in the DataPortal_Fetch method ):-

// Open the connection

OleDbConnection connection = KELplanAPP.Connection;

connection.Open();

// Create command object

OleDbCommand command = new OleDbCommand(selectCMD, connection);

OleDbDataReader dr = command.ExecuteReader();

connection.Close();

This takes about 5-seconds to execute, but if I repeat the code twice i.e.:-

// Open the connection

OleDbConnection connection = KELplanAPP.Connection;

connection.Open();

// Create command object

OleDbCommand command = new OleDbCommand(selectCMD, connection);

OleDbDataReader dr = command.ExecuteReader();

connection.Close();

// Open the connection

connection = KELplanAPP.Connection;

connection.Open();

// Create command object

command = new OleDbCommand(selectCMD, connection);

dr = command.ExecuteReader();

connection.Close();

The second time around takes only about 1-second to complete!

I dont understand why this should be, but somehow I need to speed up the access to the data - why should it consistently take different lengths of time to complete the same query ? I'm not sure if anyone out there has experienced problems like this, but I would welcome any suggestions on offer...

Many thanks,

Nick  

 

Copyright (c) Marimer LLC