1. Aleksandar Spasojevic
  2. Valentina Database ADK
  3. Monday, May 21 2018, 01:56 PM
  4.  Subscribe via email
Hello everyone.

I found something very strange while I was trying to Update few thousand records through loop.

While using table.UpdateRecord in XOJO, updating vary from 20 - 300 ms ( table has approx. 90.000 records )
Using mDatabase.SQLExecute with Update query for single record, takes 1 - 8 ms in same table.

Bellow is part of code which I'm using in XOJO,



dim startAPI as integer = Microseconds / 1000

//ADK UPDATE
tabelaAsset.RecID = idfound
tabelaAsset.BooleanField("found").Value = True
tabelaAsset.BooleanField("enbl").Value = True
tabelaAsset.UpdateRecord

dim krajAPI as Integer = Microseconds / 1000
System.DebugLog("- UPDATE API : " + Str(krajAPI - startAPI) + " ms.") // getting 20 - 300 ms

query = "UPDATE asset SET found = 'True', enbl = 'True' WHERE RecID = '" + Str(idfound) + "'"
dim sqlStartMS as Integer = Microseconds / 1000

call App.mDatabase.SqlExecute(query)

dim sqlKrajMS as Integer = Microseconds / 1000
System.DebugLog("- UPDATE SQL : " + Str(sqlKrajMS - sqlStartMS) + " ms.") // getting 1 - 8 ms



Can anyone help me understand this? I thought using VTable class should be faster than using SQL.
Comment
There are no comments made yet.
Ruslan Zasukhin Accepted Answer
Hi Aleksandar,

1) The first question to check is: if you enable V4RB DebugLog?
You could do this with Valentina.DebugLevel = kLogParams

2) I guess for API way, you have used loop around that code?

3) You can extract before loop
dim fldBoolean as VBoolean = tabelaAsset.BooleanField("found";).Value

4) DB is local or under remote VServer?
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 1
Hello Ruslan,

1) I disabled logs completely on VServer since I caught few times logs after 2-3 days reaching 40+ GB .

2) Yes, I used For Next loop for iterating through array and updating changed records.

3) Yes, I know I can extract field, but how would it help if I still have to use tabelaAsset.UpdateRecord which is slow?

4) It depends of situation, can be local and remote, but currently, its under remote VServer.

As I saw from testing, this is how it works on remote VServer . API - slowest, SQL-API - just a little bit faster, Pure SQL - The Fastest.
Should I completely go with Pure SQL to get best performance?


Additional question. I opened ticket on Bug tracker maybe 2 years ago, about Huge queries getting complete DB unresponsive. Is there any progress with that?
With this table ( ~95.000 records now ), if I execute query like ''SELECT ** FROM asset'', time to execute it is about 18 seconds ( first time after connecting on VServer, same query takes over 45 seconds ).
Maybe its okay to wait that time for this kind of queries ( but still it is to much, or I'm doing something wrong), but currently, I'm afraid of situation where I send new, the smallest possible query, it will wait until first query is finished and then execute new one.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 2
  • Page :
  • 1


There are no replies made for this post yet.
However, you are not allowed to reply to this post.