If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Large Scale Updates using C#

It's basic purpose is calculate the lat/long location using around data in 20 different columns from 3 different tables. The calculated lat/long is then updated into one of those tables.

The procedure for determining the lat/long is rather complicated involving 1000+ lines of C# code, and several calls to a .DLL that I have no control over.

The number of columns to be calculated/updated is huge.

My current noob like attempts are as follows

1) Populate a DataSet of each of the tables.
2) Iterate through them, calculate the lat/long for each row.
3) Individually update each row with a seperate update statement.

I don't think I can do much about (2), but I've played with (1) trying OracleDataReader, and DataSets, and I'd *REALLY* like to play with (3), but I don't really know how.

Currently I often run into an 'Out of Memory' error. I'm closing/disposing all connections/transactions/datasets/readers/etc. after I'm done with them, I think it is just the size of the data that I iterate through.

With Spatial option in 10g, you have the option of using GEOCODER ENGINE.
Are you using it?

Tamil

No. But it is something to look into.

The speed of the calculation isn't a concern at this point (the .DLL is certified somewhere and this is apparently a hassle, it is also quite fast), more the processing/updating of the data from the DB in a timely manner

The speed of the calculation isn't a concern at this point (the .DLL is certified somewhere and this is apparently a hassle, it is also quite fast), more the processing/updating of the data from the DB in a timely manner

Then what is the issue?

Isn't it a good idea to use the existing function/procedures already developed by Oracle?

One thing is to remember: A single row processing in Oracle is always slow, use bulk collect.