Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (4)

Resolved How long does it take for Datastore.update() to execute?

  1. How-to
0
Votes
Undo
  1. Saul Erhmy
    Saul Erhmy
  2. SnapDevelop
  3. Friday, 7 October 2022 03:45 AM UTC

Hi,

So i am trying to update Oracle by using Datastore.Update() but I can't tell if its supposed to take a long time or if something in my code is not working. For reference, I think I am updating about 400k rows and each row has like 46 columns. That being said, my assumption is it's supposed to take long. I've been waiting around 15-20 minutes though, is that too long to wait or has still not enough time passed?

  1. C#
Generate PDF from C# API Rest Call
What is the equivalent of GetItemNumber in C#?
Accepted Answer
René Ullrich
René Ullrich Accepted Answer Pending Moderation
  1. Friday, 7 October 2022 04:40 AM UTC
  2. SnapDevelop
  3. # Permalink

Hi Saul,

It depends on different things how long such huge update takes: network, database server, ...

For example: I had a refresh of a lot of catalog data (round about 100K SQL statements). This needs 7 minutes for a database in a local network. After database migration to a remote server it needs 30 minutes because of network latency.

400k rows are quite a lot. If you want to do it in 15 minutes you will have > 400 statements per second!

In Powerbuilder you can activate the database trace. Do some few updates. In trace you can see how long it needs for each statement. 

HTH,

René

 

Comment
Load more comments
Saul Erhmy
  1. Saul Erhmy
  2. Friday, 7 October 2022 19:19 PM UTC
@Armeen It might have been 1 row per second because I was printing out to Console after each update and that usually slows things down. But anyways I noticed I had very high ram usage on machine. I got that fixed and now i'm up to 20 rows/second when updating 10k rows. Still seems too slow since Rene is doing about 55 rows/second after switching to a remote server



So I am running my app on a VM, and the Database is around 2000 miles away. However we have a 10GB link between the two datacenters. My VM seems to be connected through ethernet but I'm not sure if there is a VPN behind the ethernet connection.



I'm not sure how to check the DataContext config. I have a ODataContex.cs file if thats what you mean but it was left as default.
  1. Helpful
René Ullrich
  1. René Ullrich
  2. Monday, 10 October 2022 05:46 AM UTC
If your database is far away I guess the latency would be the problem. Your 10GB link makes it fast to transport a lot of data in a stream. But if it needs some milliseconds to transport a SQL statement from A to B and the result back to A and you have a lot of such statements it makes it slow. The difference: If you send much data the network sends more packages of data parallel without waiting for a feedback. But if Powerbuilder sends a lot of updates it sends one update and waits for the result, then it sends the next update, ... The statements are small, so it would be no big difference how much GB you can send in a second. It is much more important how fast it transports a single package.
  1. Helpful
Armeen Mazda @Appeon
  1. Armeen Mazda @Appeon
  2. Monday, 10 October 2022 17:19 PM UTC
Yes, the problem isn't DataStore performance... it is network problem between your Web API and the database. The servers need to be in same LAN if you care about good performance, and this is just general issue you will run into no matter what technology you use to develop the Web API.
  1. Helpful
There are no comments made yet.
Responses (4)
mike S
mike S Accepted Answer Pending Moderation
  1. Saturday, 8 October 2022 10:51 AM UTC
  2. SnapDevelop
  3. # 1

Are there triggers and/or referential integrity on the table that you are 'updating'.  That can slow things down a lot.

Are you doing actual sql updates, or are you doing inserts or deletes?

if you are doing updates, are you updating data that didn't change?  (check your original value against current and change the column status back to notmodified).

 

 

Comment
There are no comments made yet.
Sivaprakash BKR
Sivaprakash BKR Accepted Answer Pending Moderation
  1. Friday, 7 October 2022 06:00 AM UTC
  2. SnapDevelop
  3. # 2

Batch commit is the better option.

Commit every n rows, eg 10k rows.  [ increase or decrease it depending on the load to the server ]

HTH

Happiness Always
BKR Sivaprakash

 

Comment
There are no comments made yet.
Saul Erhmy
Saul Erhmy Accepted Answer Pending Moderation
  1. Friday, 7 October 2022 03:51 AM UTC
  2. SnapDevelop
  3. # 3

I will probably test it with a few rows in the morning but for now i'll let it run through the night 

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.
We use cookies which are necessary for the proper functioning of our websites. We also use cookies to analyze our traffic, improve your experience and provide social media features. If you continue to use this site, you consent to our use of cookies.