1. Aleksandar Spasojevic
  2. Valentina Database ADK
  3. Montag, Mai 21 2018, 01:56 PM
  4.  Abonnieren via E-Mail
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

tabelaAsset.RecID = idfound
tabelaAsset.BooleanField("found").Value = True
tabelaAsset.BooleanField("enbl").Value = True

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.
There are no comments made yet.
Ruslan Zasukhin Akzeptierte Antwort
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?
There are no comments made yet.
Aleksandar Spasojevic Akzeptierte Antwort
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.
There are no comments made yet.
  • Seite :
  • 1

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


Announcements & News
  1. 0 subcategories
Valentina Studio
  1. 2 subcategories
Valentina Server
  1. 4 subcategories
Valentina Database ADK
  1. 0 subcategories
Valentina Reports ADK
  1. 0 subcategories
Other Discussions
  1. 2 subcategories
BETA Testing
  1. 0 subcategories
Education & Research
  1. 0 subcategories