H2 Database Linked table Performance

[Chavya Khanna]

Hello,

I have general question around Linked Tables of H2 Database:

1. How does Linked Table work? When i create a Linked Table in H2 Database - does it continuously monitor the actual database table that it is linked to - for changes? and if changes are identified does it deletes all records and insert them again or does it updates only new /or updated entry?

2. What performance impact it would have on original database table? as it would be continuously monitoring original database table that it's linked to for changes?

Hope to hear soon.

[Evgenij Ryazanov]

Hello.

 

1. How does Linked Table work?

It simply redirects all queries and data modification commands to the target database.

 

When i create a Linked Table in H2 Database - does it continuously monitor the actual database table that it is linked to - for changes?

No.

 

and if changes are identified does it deletes all records and insert them again or does it updates only new /or updated entry?

They aren't detected, but all your attempts to do something with a linked table are passed to the target database and it will obliviously know the current data in the table, because it its own real table.

 

2. What performance impact it would have on original database table?

It depends on performance of calls into target database and complexity of your queries. H2 tires to read information about indexes from the target database to optimize complex queries better, but in some cases execution plan may be far from being perfect, optimizer of H2 is not very advanced.

[Chavya Khanna]

Thanks Evgenij!

One last question: Does it have a cost involved for creating a Linked Table? Like $ value per call Redirected to Target Table?

[Evgenij Ryazanov]

If you need to know their performance, you need to measure it by yourself with your real queries and your real data.

But in all or almost all cases linked tables are slower than own regular tables. They aren't about performance, they are for access to data stored in other database.

[Chavya Khanna]

Got it!

However,  Does it have a cost involved for creating a Linked Table? Like $ value per call Redirected to Target Table?