Is there a way to put an index on the business object's data table so that a Seek() command executes more quickly? 

I have a relatively small dataset (225,000) records each of which only has 3 fields of data (so the memory footprint is rather small).  The problem is that I'm validating over a million records against this dataset.  If the seek takes a second (which is about how long it takes) that is 277 minutes or 4.6 hours of validations. 

My assumption was that if I loaded up the entire table in the business object that the seek would be almost immediate.

Here is the seek string:

string seekString = String.Format("wnf_name = '{0}' AND wnf_lat = {1} AND wnf_long = {2}", fixName.Trim(), latitude, longitude);

 

wnf_name is a String, wnf_lat is a Double and wnf_long is also a Double ... I'm betting it is the Doubles that are causing the slow down.  I wonder if I converted the Double to String that the seek would be faster?

Thanks!

Charles T. Blankenship
Senior Consultant
Novant Consulting, Inc.
704.975.7152
http://www.novantconsulting.com

Hi Charles,

Please post the whole code if you don't mind.

So you have over a million records (where? in another BO?) which you are then looping to see if you find it on your BO with 225,000 records?

Anyway, when dealing with so many records, it is always better to do your process in the MS-SQL Server database with an stored procedure and that would effeminately will improve your process to seconds or couple of minutes instead of hours.

I do very complicated calculations importing over 700,000 records from a CSV file and then validate these against a warehouse table with millions of records.  You will need to have a strong TSQL knowledge and data manipulation with .Net SQL Bulk Copy command to speed up the whole process.

Edhy Rijo

I cannot use a stored procedure as I'm parsing out aeronautical Fix information out of a text file and then validating the Latitude and Longitude coordinates so that I can get accurate great circle distances when needed.

That said, I found the "problem" ... I was loading up the 229,923 records within the validation loop ... which means I was an idiot.  As you can imagine my performance has drastically increased!

The only reason I'm admitting to such a foolish error in a public forum is to clarify that in a situation like this that loading up all 229,923 records and then seeking against that data set results in more than reasonable performance, and, since the actual amount of data is very small one string and two doubles, it doesn't take too long to load and doesn't take up much memory either. 

The whole validation takes place in roughly 30 seconds now.

C. T.

Charles T. Blankenship
Senior Consultant
Novant Consulting, Inc.
704.975.7152
http://www.novantconsulting.com

Hi Charles,

Glad you found the problem and 30 seconds is pretty good anyway.

Just to satisfy my curiosity, the 229,923 records are the one you are importing or the lookup records you are using to compare your text data?

Edhy Rijo