The number of records is quite large (about 100k records). Indexes are in place and the query executes within about 5 seconds inside Access, so the time is getting used filling the BO.

This is a read-only view. The fill routine is just "select * from blah" (blah is a view). Can anyone suggest a way to speed the fill routine up?

Thanks!

I thought I was going to have to use ADO.NET directly. I'll try it out. I know working with this many records is not typical, so maybe I need to consider other ways? I will eventually need to "touch" all of these records for this functionality.

I'm pretty new to .NET programming (I'm an old-school VB dev). Is there a way to asychonously grab the data and begin working with it while the table is getting filled?

Kevin

There are a number of ways you could optimize this; some that spring to mind would be pretty funky and esoteric threading alternatives so, maybe we shouldn't go down that route

It seems that the problem is the query/load time coupled with the additional process time. By far the fastest way to read the data is by using an ADO.Net DataReader.

I have attached a sample.txt file with my attempt at VB (I am a VFP/C# guy) so, it may be buggy but you should get the gist of it. Also, I have referred to SQL as the dataprovider so, you should replace these references with the appropriate ones that you currently use for Access.

The solution is that you essentially "For Each" the DataReader instance and get an IDataRecord. You then cast this to a DataRow. The DataRow can be processed in whatever way you want and then after processing it, you simply add the row to the CurrentDataTable of your BO.

So, in this way, you process the record as you read it (and the DataReader is extremely fast at reading your data in the first place). I'm sorry, I haven't actually tried/tested this code but, I am pretty certain that, bad syntax apart, it should work for you.

FYI, the CurrentDataTable property of the BO is simply a reference to the inner DataTable. Also in the sample code, I have included a way to access the "default" connection string used by StrataFrame so, you don't have to worry about re-specifying it.

Let me know if you are confused with any of this and I will try and explain further.

HTH

Best

-=Gary

Just wanted you to know that I have ready your reply. I've actually been working on 3 different projects and haven't had a chance to give your suggestions a try, but I will...

I'll keep you posted. Thanks again for the help!

Kevin

Gary has a wealth of experience in StrataFrame.  He has been with us from the beginning and has a lot of good ideas.  I just thought I would tell you a little about his background on StrataFrame.

it takes a little more coding because I can't take advantage of the strong typing the BOs provide, but it is working just peachy.,..

Thanks again, Gary.

I'm glad the datareader trick worked out for you!

Best

-=Gary