[RESOLVED] Quick way to update datatable

[mickey_pt]

Hello

I have a table that has 9 columns, this table has about 5000 rows, but sometimes the user need to update the data in the table. The new data is in another table, with different columns names and only with 8 columns, the ninth column it's the date of the update (i know that this columns shouldn't be here...).

So for now i'm filing one datatable (the data come form a webservice that returns a dataset with this table), and after that i clean the original table and then for each row in the new datatable i call the insert command.

Is there other way, better way, to do this?

My head it's kind of messed up this morning... and i can think right.

Thanks

[Shaggy Hiker]

There may or may not be a better way to do this. At first glance, it looks like it would be better to only update the records that are new or changed, but that may not be efficient. After all, for this to work, you would have to know which records needed to be updated. If you have some easy way to know this, and the number is small, then just updating those would be simpler. If you have to check each record to determine whether or not it has changed, then the cost of checking will outweigh any possible gain.

What isn't clear to me is why you need to do this in the first place. You are getting a datatable back from the service, why can't you replace the existing with this new table. If all you have to do is add a column and populate it, this is probably still going to be faster than copying over all the records.

[mickey_pt]

Thanks for the answer.
To clarify, this table it's a table where i have the list of all materials used by the company, so i need to read all the rows, because i have 3 fields that could have changed, descriptions, quantity available and other numeric field. So the fastest solution it's to clear the original (truncate) and insert all new rows...

So what i need is, clear the table and then insert all rows.

I was trying to use an adapter to do the job, but behind the scene what it does it's calling the insert command for each row, doesnt it?
The current code that i'm using it 's a simple for each datarow in the datatable, and add the correct parameters related to the correct cell and call the executenonquery. I don't like this, doesn't look like an elegant way to do the job, and takes some time to finish... i have the sensation that the adapter may do the job faster.

[Shaggy Hiker]

It would call the Insert command if you were using the adapter to update the database table with all new records, but I thought you were just talking about a datatable. Then you mentioned the ExecuteNonQuery, which sounds like a database table again. So what are we actually talking about, a table in the database, or a datatable object in memory?

[mickey_pt]

Both...
I have a sql server database for my application, i have a form that presents the list of materials that exist in my database, and i have the web service that returns me a dataset with one datatable from the main source of the company ERP (SAP).

So why i need this, if the user doesn't find in the list of materials the item that it is looking for, the list must be outdated so the user needs to get the new list from the webservice.

Flow:
1. Get Datatable from the webservice
2. Truncate my table in the database
3. Insert all items from the data table to the database
4. Refresh the list

And that's it.

[Shaggy Hiker]

Oh. In that case, you may be doing about as well as you can. The adapter will indeed call an INSERT on each row. Is that in any way better than you doing that yourself? That's hard to say. I haven't specifically tested that, but I do note that the WHERE clause in the adapter can be really ugly if you are using a CommandBuilder to generate it automatically. If you write it yourself, then there shouldn't be any difference at all between the two methods.

[mickey_pt]

Hehehe
I have to test the two methods, and see what i got, and i write my one insert.

Thanks

[mickey_pt]

Hello
I had tested both methods, and they take aprox. the same time, 16 seconds to fill around 30k rows.

But now i take this one step further, in the original datatable all fields are strings, and in my datatable i want to put the fields in the correct format, so before setting the parameter value I use the cdec function, and now it takes around 3 minutes to do the job, if i use the cdbl it takes 5.40 minutes, and using the double/decimal parse with a string replace it takes arround 20 seconds... I could only do this with the loop version, if i use the adapter i get always a conversion error from string to decimal (the strings that i get from the original datatable use the comma to separate the units)...

Thanks

[Shaggy Hiker]

Yeah, converting strings is going to hurt. Is there any way you could avoid that first table having just strings?

[mickey_pt]

No I can't, i only have the webservice, that returns me that table...

But it's wired that the fastest way it's the last one that uses 2 operations, the replace the coma by the point and then double.parse the result?!

I only add 4 seconds to the 16 seconds of the original version, it's not that much and i think i can't get better time...

Thanks for your time and patience.

[mickey_pt]

Just to close this thread.
I managed to gain another 6/7 seconds, replacing the insert statement by a sproc. So the final time it's around 13 seconds...