-
Large Scale Updates using C#
I have a rather unwieldy piece of code that I'm trying to optimize.
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.
Any thoughts?
Thanks in advance.
-
Are you using SQL-Server?
Tamil
-
Originally Posted by tamilselvan
Are you using SQL-Server?
Tamil
Nope Oracle 10g.
-
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.
With Spatial option in 10g, you have the option of using GEOCODER ENGINE.
Are you using it?
Tamil
-
Originally Posted by tamilselvan
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
-
Originally Posted by Gnolaum
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
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.
Tamil
-
Originally Posted by tamilselvan
One thing is to remember: A single row processing in Oracle is always slow, use bulk collect.
Precisely what I want, but not quite sure how to go about.
How, in C#, since this is where I am doing the calculation, can I update a table using a bulk process?
Last edited by Gnolaum; 06-08-2006 at 04:58 PM.
-
If ur using Oracle 10.2 on Windows machine, then U can use .NET Stored Procedure.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|