In term of performance is mormot rest connection is better than Oracle direct connection?

Question

I work in a company on a huge application made with Delphi 10 Seattle (VCL), using direct connection to Oracle database server (2-tier).

One of the experts proposed to migrate to the 3-tier architecture using mormot (or another library/components).

One of his arguments that the new architecture (3-tier) will provide more performance (time of exchange data), because https is faster than direct oracle connection and json objects are much lighter (without using a cache policy on the rest) and we can after that make web clients.

I didn't understand:

• Why is https faster than oracle connection protocol? (if it's true, why oracle doesn't use https and json as protocol for exchange data?).
• Isn't a security problem if we let all the functions and queries on the client side (even if we will do web clients)?

Cordially

Answer 1

In the context of any n-Tier framework, remote SOA access via a REST solution could have performance benefits.

The mORMot documentation tries to introduce all those concepts (SOA, REST, MVC, n-Tier, Clean Architecture, DDD...) and, then only, speaks about performance impact. Don't take a documentation sentence in isolation. Consider the whole picture, and the use-cases.

Why is https faster than oracle connection protocol?

HTTPS is not "faster" in se. What was meant is that it could be more efficient for a remote connection, especially over the Internet.

The Oracle Connection protocol was designed to run on a local network, whereas HTTP is a query/answer model.

The main PRO of the Oracle protocol is that it is more complete (complex?) than a simple query/answer model: it can dialogue with the Oracle server to cache statements and data, it can have real-time notifications, it can prepare the data in binary form ready to be encoded.

In terms of performance, the main CON of the Oracle protocol is that it requires more round-trips other the wire: it was designed to work on local network, with a low latency. Over an Internet connection, it will be much slower, and, for security reasons, is very likely to be encapsulated into a VPN - reducing even more the performance.

We should not speak of "performance" in an abstract way. There are several ways to skin a cat... If you want raw query performance, use another kind of database, like Redis.

But for business applications, the main "performance" point is perhaps more about scaling. And here, the Oracle protocol has a bigger cost in its database connections. Maintaining a connection, especially maintaining transactions, can be very demanding. You can maintain up to a few dozen/hundredths of simultaneous DB connections on a typical Oracle server. Whereas it is very easy to have a REST server maintaining thousands of simultaneous clients. And even if you currently expect only a few clients, how could you imagine your future? All serious applications expect a REST-like interface, nowadays. And keep the database connection local on the server side.

Isn't a security problem if we let all the functions and queries on the client side (even if we will do web clients)?

Security is another concern, and here a REST Web client has known and proven strategy, with proper audit methodology. You will never "let all functions on the client", in a REST interface. The framework offers several ways of authentication and authorization - check the documentation, from URI-signature to JWT.

Opening an Oracle endpoint to the whole Internet is not a good idea, in terms of security and scalability. Even Oracle is offering dedicated solutions for proper networking.

Anyway, a framework like mORMot was designed to offer REST, SOA, ORM and web MVC in a single package, and performance was driven from the ground-up - as a bonus. If you expect to design a RAD VCL/FMX application, go with direct database connection, and be data-centric. If you want something more open and maintainable, consider SOA, and be service-centric. Today, I develop all my SOA solutions as Micro-Services, with stand-alone databases, and mORMot as tooling, with huge performance - up to million of data items per second.

Answer 2

Sounds like a vague story to me, and I'd have a hard time believing this unless I got well documented proof. And even then, it's easy to compare apples and oranges when comparing the performance of such different ways of architecture.

I don't think that https in general is faster than a direct connection, but it depends on a lot of variables.

Moreover, mORMot itself needs to connect to the database as well, for which it can use some Direct Oracle Access (I assume the same as the one you compare it with), or OleDB (aka ADO) which is in general the same or slower than DOA, so there is no gain there. There is only the extra overhead of mORMot. See software architecture of mORMot.

So, how can it be better?

If the middle tier uses connection pooling when the client cannot, for instance. In that case, having a middle tier for pooling connections can lead to better performance, because no new connection needs to be established on every request. This can save a lot of overhead.

The same goes for caching. If you want to build a web site or web app, having a middle tier can greatly improve caching, if query results can be cached regardless of user. If you have client side caching, you cache it for that user/browser/session only, while in the middle tier, you can cache some data for all your visitors, which is a great benefit.

If there is a lot of processing needed before the database is involved. This can be faster on a high end middle tier. Then again, if you have lots of users, you might run out of hardware capacity (or cloud budget), and you might consider doing part of the legwork on the client.

So there are all kinds of benefits to 3-tier. whosrdaddy already named scalability, portability and redundance. Performance can be one of the benefits as well, if you tick any point like the ones listed above, but in general it's not a main reason for going from 2-tier to N-tier.