mORMot Open Source

youssef

Member

Registered: 2024-08-05

Posts: 15

Custom SQL Queries Creating New Connections

Hello everyone,

I’m facing an issue when executing custom SQL queries using methods like `.Execute()`or `.ExecuteJSon()`. Every time I run a custom query, a new connection is created to the database, which is causing performance issues and overwhelming the database (postgress) with too many open connections.

- I need to execute custom SQL queries (e.g., complex `SELECT` statements with `JOIN`, `WHERE`, etc...) that cannot be easily expressed using the standard ORM methods
- When I use `Execute`or `ExecuteJSON`, a new connection is created each time, which is not efficient.
- This results in too many open connections, impacting performance and potentially hitting the database's connection limit.

Example of My Query:
there're more complexe queries  than this one :

function TNSAServ.FetchOrderDetails(ACustomerID: RawUtf8): RawUtf8;
var
  Query: RawUtf8;
begin
  Query := FormatUtf8(
    'SELECT o.OrderID, o.OrderDate, c.CustomerName, p.ProductName, p.Price ' +
    'FROM Orders o ' +
    'JOIN Customers c ON c.CustomerID = o.CustomerID ' +
    'JOIN Products p ON p.ProductID = o.ProductID ' +
    'WHERE o.CustomerID = % ' +
    'ORDER BY o.OrderDate DESC',
    [ACustomerID]);

  // Execute the query and return the result as JSON
  Result := fServer.ExecuteJson([], Query );
end;

In this example, the query retrieves order details for a specific customer by joining three tables (`Orders`, `Customers`, and `Products`). However, each call to `ExecuteJson` creates a new connection, which is not optimal.

Last edited by youssef (2025-03-11 10:51:39)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,935

Website

Re: Custom SQL Queries Creating New Connections

There is a connection per thread used.
Just reduce the number of threads in your HTTP server.

Also note that running such complex queries will involve the SQLite3 virtual tables, which is far from optimum.
You would better directly call the DB layer, bypassing the ORM.
It would also allow you to use a lock and a single connection, if really needed less connections.

youssef

Member

Registered: 2024-08-05

Posts: 15

Re: Custom SQL Queries Creating New Connections

Thank you for your response! I understand that reducing the number of threads in my HTTP server can help manage database connections more efficiently. However, my application will be handling many simultaneous requests from a large number of users.

Would reducing the number of threads negatively impact performance in such a scenario? If so, is there a recommended way to balance the number of threads while ensuring efficient database connection usage?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,935

Website

Re: Custom SQL Queries Creating New Connections

If you still use the ORM layer for such complex queries, you will have a main lock at the SQlite3 DB layer.

Consider my proposal of bypassing the ORM.

itSDS

Member

From: Germany

Registered: 2014-04-24

Posts: 544

Re: Custom SQL Queries Creating New Connections

In MySQL we increased the number of allowed connections, may be there is an option in PostgresSQL

---

Rad Studio 12.3 Athens

youssef

Member

Registered: 2024-08-05

Posts: 15

Re: Custom SQL Queries Creating New Connections

@itSDS Limiting connections in PostgreSQL during high traffic can indeed be challenging. If we have 1,000 users and limit connections to 900, it could create a bottleneck, leaving some users unable to access the database

youssef

Member

Registered: 2024-08-05

Posts: 15

Re: Custom SQL Queries Creating New Connections

@AB Bypassing the ORM and using direct SQL queries can indeed lead to creating new connections in PostgreSQL each time a query is executed. This can result in a large number of connections being opened unnecessarily, which may overwhelm the database

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,935

Website

Re: Custom SQL Queries Creating New Connections

@AB Bypassing the ORM and using direct SQL queries can indeed lead to creating new connections in PostgreSQL each time a query is executed. This can result in a large number of connections being opened unnecessarily, which may overwhelm the database

No, this is exactly the opposite.
Please read again what I wrote above, and look how the connections are maintained in our DB client layer.

JD

Member

Registered: 2015-08-20

Posts: 123

Re: Custom SQL Queries Creating New Connections

@youssef

I also had the same problem. One option was to use a PostgreSQL connection pool utility like pgPool or pgBouncer, BUT in the end I opted for a per-thread connection pool in mORMot

DbProps := TSqlDBPostgresConnectionProperties.Create('localhost',  'postgres://:5432/' + DbName, DbUserName, DbPassword);
// ThreadSafeConnection creates a per-thread connection pool
TSqlDBPostgresConnectionProperties(DbProps).ThreadSafeConnection;

I don't use the ORM layer too because of the complexity of the queries/views, triggers and functions in my PostgreSQL database. So for instance, if I want to fetch all orders processed by a particular employee, I have the following function which is the endpoint of a REST (Interface based) request.

(* All orders processed by a particular employee *)
function TOrderService.GetOrdersByEmployeeID: ISQLDBRows;
begin
  Result := Db.DbProps.Execute(Format('select * from %s.vw_orders where id_employee=? order by order_date desc', [vParams.schema]), [vParams.id_employee]);
end;

Called by

Result := GetOrdersByEmployeeID.FetchAllAsJSON(True);

This is how I got around the connection limitation. I hope this helps a little bit.

Cheers,

JD

Last edited by JD (2025-03-15 10:12:35)