Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
7
Erster Beitrag:
vor 9 Monaten, 4 Wochen
Letzter Beitrag:
vor 9 Monaten, 3 Wochen
Beteiligte Autoren:
Yogi Yang, Arie, GuenterP, Art Bonds, Fabrice Harari

[WX] - How to UPSERT -OR- INSERT or REPLACE in HFSQL

Startbeitrag von Yogi Yang am 27.12.2016 11:28

Hello,

In a software I am trying to sync the local data by importing remote data using a web service.

But as the number of records have increased by many folds the process is painstakingly slow.

Is there any means like (as available in many other main stream RDBMS) UPSERT -OR- INSERT or REPLACE in HFSQL?

TIA

Yogi Yang

Antworten:

Hi,

I just checked the official definition of upserting, and I'm wondering: are you talking about hModify?

Best regards

von Fabrice Harari - am 27.12.2016 14:52
Correct me if I am wrong...

UPSERT will either add the record to the DB if the record does not exist, or modify a current record if it does exist. One action.

With hModify you check to see if a record exists, if it does you assign fields then hModify, if it does not exist you hAdd.

Seems the difference between the two is the checking if the record exists, which takes time. UPSERT just finds the key and overwrites if it is there or inserts if no. Seems UPSERT would be faster.

JMO, I am not a DB guru by any mean.

von Art Bonds - am 27.12.2016 15:43
Hi Art,

if it is that then hSave(..) should do the job just fine.

von GuenterP - am 27.12.2016 17:02
Quote
Fabrice Harari
I just checked the official definition of upserting, and I'm wondering: are you talking about hModify?

No not really.

using hModify would mean first finding (try to find) the record and then if it exist update it and if it does not exist add it.

I will have to build this logic and will have to code it. I have already done this. But it is taking a tell of a time at present with over 300000 records.

But in case of UPSERT the RDBMS engine will do the necessary grunt work at its core level so it would be very fast and error free also.

TIA

Yogi Yang

von Yogi Yang - am 28.12.2016 10:58
Quote
GuenterP
if it is that then hSave(..) should do the job just fine.


It is near to what I am expecting from HFSQL.

But in my case the data comes form MySQL and is saved in HFSQL. I have not use it but I think it will take more time then UPSERT would take.

TIA

Yogi Yang

von Yogi Yang - am 28.12.2016 11:02
Yes; if the hfsql engine could check if a record exist at a core level, that would be the fastest way. But afaik it is not supported.

The help says hSave() uses the newrecord property of a record.
This property is set by h-command like HReset() and HReadSeekFirst and so on (see the help)
This means you still need a first action to determine if a record already exists.

Another way would be if yoiu have access to the mysql source database you mentioned. There you know if a record is new or modified. You could create some kinf of logfile or journal to keep track of this.

You can also speed up the proces on the destination side. By first reading all record in memory, which is much faster than record-by-record. And search the copy memory to see if data already exists. The memory could be either a simple query, so you can use the H-commands as you do now. Or put the data in an array of structures. Searching an array is very fast.

von Arie - am 28.12.2016 12:16
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.