Large Scale Updates using C#
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Large Scale Updates using C#

  1. #1
    Join Date
    Jun 2006
    Posts
    4

    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.

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Are you using SQL-Server?

    Tamil

  3. #3
    Join Date
    Jun 2006
    Posts
    4
    Quote Originally Posted by tamilselvan
    Are you using SQL-Server?

    Tamil
    Nope Oracle 10g.

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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

  5. #5
    Join Date
    Jun 2006
    Posts
    4
    Quote 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

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Quote 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

  7. #7
    Join Date
    Jun 2006
    Posts
    4
    Quote 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.

  8. #8
    Join Date
    Sep 2005
    Posts
    278
    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
  •  



Click Here to Expand Forum to Full Width