Postgres Pro
Downloads
Home   >   mailing lists

Adapter update. - Mailing list pgsql-general

From Murali Maddali
Subject Adapter update.
Date
Msg-id 76758090F8686C47A44B6FF52514A1D308C9CA40@hermes.uai.int
Whole thread Raw
Responses Re: Adapter update.  (Richard Huxton <dev@archonet.com>)
List pgsql-general
Tree view
Hello Group,
 
I have asked this question already on the NpgSql forum, but didn't get a response so far. Sorry for cross posting, but want to check if any one had any suggestions for my problem.
 
I am trying to do my updates through NpgsqlDataAdapter (I also tried with Odbc driver with no luck) by passing in a Datatable with changes in it, this would take forever to do the updates.
 
This is what I am doing, I am reading the data from SQL Server 2005 and dumping to out to Postgresql 8.2 database.
 
            using (SqlCommand cmd = new SqlCommand(t.SourceSelect, conn))
            {
                using (SqlDataReader r = cmd.ExecuteReader())
                {
                    DataSet ds = new DataSet("postgis");
                    NpgsqlDataAdapter adp = new NpgsqlDataAdapter(t.DestinationSelect, destConn);
                    NpgsqlCommandBuilder cmdBld = new NpgsqlCommandBuilder(adp);
                    adp.Fill(ds, t.DestinationTable);
                    DataTable destTbl = ds.Tables[t.DestinationTable];
                    
                    DataTable srcTblSchema = r.GetSchemaTable();
                    adp.FillSchema(ds, SchemaType.Mapped, t.DestinationTable);
                    
                    // My save method will check if the row exists or not and would add or update accordingly to the datatable (destTbl). The whole process
                    // of comparision is done under 2 mins on 60,000 records.
                    while (r.Read())
                        _save(r, srcTblSchema, destTbl, destConn); 
                    r.Close();
 
                    // This is the where my application goes into lala land. If I call this update in my while loop above, it took about two hours to process
                    // the whole thing
                    adp.Update(destTbl);
                }
            }
 
I have around 60000 records. I also have a geometry field on my table.
 
I have couple of questions.
 
1) What do I do to speed up the process? Any database configuration changes, connection properties, .....
2) When I call the adapter.update does NpgsqlDataAdapter checks to see if the column value really changed or not? I believe SQLDataAdapter does this validation before it actually writes to the database.
 
Any suggestions and comments are greatly appreciated. Right now I am in dead waters and can't get it to work on large datasets.
 
Thank you all.
 
Regards,
Murali K. Maddali
UAI, Inc.
 
"Always bear in mind that your own resolution to succeed is more important than any one thing." - Abraham Lincoln
 
This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the sender. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail.This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the sender. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail.

pgsql-general by date:

Previous
From: "Phoenix Kiula"
Date:
Subject: Re: Postgres, fsync and RAID controller with 100M of internal cache & dedicated battery
Next
From: "Dmitry Koterov"
Date:
Subject: Re: Postgres, fsync and RAID controller with 100M of internal cache & dedicated battery