Question about Key vs Key+Link in Analysis

Startbeitrag von GuenterP am 16.07.2017 07:28

Hi,

just a question: looking at performance, is a simple key in a big file (10 - 50 gigabytes) much better than a key with a link to another file? And if, at about which percentage would you estimate is the latter being worse?

I'm pondering the idea of not to make seldom-used links (just for statistics) and update any changed values in the file just before running the statistics. Is it worth it? Would you recommend to do so?

Antworten:

Hi Guenter,

first, it probably depends on the DB used.

Second, I have never have a performance problem due to links, so maybe if you gives us more details on the problem, we could help more easily.

Best regards

von Fabrice Harari - am 16.07.2017 20:24
Hi Fabrice,

it's HFSQL C/S. While adding new file items to an existing analysis and application I thought about the luxury to add links between this file and a few descriptive files for the newly added file items. Would that pose a noticeable influence on the big file's behavior or not?

von GuenterP - am 17.07.2017 05:10
Hi again

I cannot say for sure that it would or wouldn't. I can only say that I have never noticed a difference and never had a problem with something like that...

And probably (what that is theoretical) NO, because :
- when writing, the possible overhead would be infinitesimal (because you are writing one record at a time, mostly)
- when reading, the link are already there and are used or not to optimize access

But the only way to be sure would be to test and compare the two solutions. So let us know your results if you can take the time to do it.

Best regards

von Fabrice Harari - am 17.07.2017 12:02
The main problem can be with the integrity constraints.
If you have a lot of constraint when you update/delete one records the db engine must check if it's possible to delete/modify the record or if it must delete/modify the corresponding records in the other files.

In some situations like batch update of large databases you can disable the integrity checks to speed the process using the HSetIntegrity function.

You must decide what is more important, integrity or performance.
If the database is only updated by your apps you can control the integrity by code but if it's updated by other apps it's better to priorize the integrity.

von Paulo Oliveira - am 17.07.2017 13:29
Hi Paulo,

thank you! I took it for granted that there would be a degradation of performance by adding links. My question still is: by how much? OK, I will add these links and look what our customers report. If they complain about a noticeable slow-down I'll try to blame their hardware first :D and if that doesn't work, I'll remove the links and add the necessary code to get the file up to date before any statistics are run.

von GuenterP - am 17.07.2017 14:05
Hi guenter,

It's difficult to say how much just with this info. The only way i know is by testing.

In the read operation the links should not influence at all, for the update/delete it depends on how many links you have in the files, how are they defined and the number of records in the files.

von Paulo Oliveira - am 17.07.2017 14:23
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.