Android Question Seeking Guidance on Optimizing Database Access for Scaling B4X App

[amarnath]

I'm currently using B4J with JdbcSQL to connect directly to a remote MySQL database:

Public mysql As JdbcSQL
Public driver As String = "com.mysql.jdbc.Driver"
Public jdbcUrl As String = "jdbc:mysql://207.1*.2*.2*:3306/amarnq4g_dairy?useSSL=False&characterEncoding=utf8"
Public Username As String = "amarg_amarnath"
Public Password As String = "Amarnath@@1234"

However, saving data takes over 30 seconds—too slow for my growing user base (projected >1000 clients).

Would switching to RDC (Remote Database Connector) or using a custom API (e.g., api.php) significantly improve performance and scalability?

Given your expertise, which approach would you recommend for a high-performance B4X mobile app serving thousands of users?

Thank you for your time and guidance.

 

[aeric]

For jRDC2, please check the following threads:
1. https://www.b4x.com/android/forum/threads/project-template-jrdc2-server.165712/
2. https://www.b4x.com/android/forum/threads/project-template-jrdc2-client-b4x-b4j-b4a-b4i.166069/
3. https://www.b4x.com/android/forum/threads/jrdc2-server-with-login-session.166903/

 

[hibrid0]

Check this template ready to use from the master Aeric. Why you don't use the search function?
Have a lot of examples.

[Project Template] jRDC2 Server

Version: 3.30 Project template based on jRDC2 version 2.23 with sample database. GitHub: https://github.com/pyhoon/jrdc2-server-template-b4j Template: JRDC Server (3.30).b4xtemplate Supported databases: SQLite MySQL MS SQL Firebird Postgresql DBF Depends on: JavaObject jRandomAccessFile...

www.b4x.com

And this is a mod for Jrdc2 for multiple dbs, json answers and more performance.

jRDC2 MultiDB Server (JSON, HikariCP)

1. Introduction and Architecture This project is a modified version of the original jRDC2 Server. The multi-database support is based on Erel's JDC2 Multiple Database modification, and includes three main structural changes: HikariCP Migration: The connection pool was migrated from C3P0 to...

www.b4x.com

 

[amarnath]

Check this template ready to use from the master Aeric.

Thank you for your patience.
I’ve decided to fully switch to jRDC2—since I have a VPS and want to stay 100% in the B4X ecosystem.

This will help me avoid common pitfalls and move forward smoothly.


Thanks again for all your support!

 

[fungms]

I'm currently using B4J with JdbcSQL to connect directly to a remote MySQL database:

Public mysql As JdbcSQL
Public driver As String = "com.mysql.jdbc.Driver"
Public jdbcUrl As String = "jdbc:mysql://207.1*.2*.2*:3306/amarnq4g_dairy?useSSL=False&characterEncoding=utf8"
Public Username As String = "amarg_amarnath"
Public Password As String = "Amarnath@@1234"

However, saving data takes over 30 seconds—too slow for my growing user base (projected >1000 clients).

Would switching to RDC (Remote Database Connector) or using a custom API (e.g., api.php) significantly improve performance and scalability?

Given your expertise, which approach would you recommend for a high-performance B4X mobile app serving thousands of users?

Thank you for your time and guidance.

Well, maybe client isn't so important as well optimized queries and indexes. If those two are done, then you can limit records to transfer.

If you joining some tables with many records, please filter to minimum start records in the first table, then join next tables ON indexed columns (the best are ints).

Every column in WHERE clause should be indexed. If you can not index column you should think about change data types.

The fastest indexes are numbers (integers), a little bit dates. The slowest are strings, a specially a long varchars (or blobs).

If query runs slow on server - on client will be slower of course (time for transfer data)

 

[amarnath]

Well, maybe client isn't so important as well optimized queries and indexes. If those two are done, then you can limit records to transfer.

If you joining some tables with many records, please filter to minimum start records in the first table, then join next tables ON indexed columns (the best are ints).

Every column in WHERE clause should be indexed. If you can not index column you should think about change data types.

The fastest indexes are numbers (integers), a little bit dates. The slowest are strings, a specially a long varchars (or blobs).

If query runs slow on server - on client will be slower of course (time for transfer data)

I’m moving to jRDC2 and rewriting all my database queries using it.
Thanks for the advice—I’ll make sure every query is optimized with proper indexes (especially on integer columns) and minimal data transfer.

 

[TILogistic]

I suggest using a RESTful API. This will keep the data layers separate from your client apps, which can be developed in other languages. In other words, your applications can be written in Java, JavaScript, .NET, etc., and still access your data.

Most web hosting providers come with Node and Apache pre-installed, giving you the option to choose what to use for your RESTful API.

Example:
Node + Express (https://expressjs.com/)
Apache + PHP + Slim (https://www.slimframework.com/)


Both are easy to implement and support multiple database types (Oracle, MySQL, MariaDB, SQL Server, etc.).

Note:

One observation is that both frameworks handle security and data transfer layers.

There are other RESTful APIs that are simpler or that you can develop yourself, but they only transfer and retrieve data, without security layers, and these frameworks include them.

 

[TILogistic]

This can help you make requests to your RESTful API from your B4X client apps.

[B4X] PrettyURL: Generating pretty URLs and making http requests.

Generate pretty URLs and make requests. EDIT: Private Sub SetPrettyURL(URL As String, Parameters() As Object) As String Dim Output As StringBuilder Output.Initialize Output.Append(URL.Trim) If Parameters.Length = 0 Then Return Output.ToString For Each s As String In...

www.b4x.com

 

[Bladimir Silva Toro]

Hello @amarnath

Hi, I also recommend using jRDC2. I have at least 100 users connected to an MS SQL Server 2019 database and it works perfectly. I have a mobile invoicing app in B4A and a point-of-sale application for Windows desktop in VB.NET.

Note: jRDC2 uses HikariCP as its connection pool library, and the key parameter is:

MaximumPoolSize → the maximum number of simultaneous connections to the database.

If you use 50 simultaneous connections, you could use more than 500 users.

The server for MySQL and jRDC2 should have this hardware:

16 vCPUs, 32 GB RAM, NVMe SSD, 64-bit Linux

After this, I don't know how much the performance would improve for saving data; it shouldn't take 20, 30, or 50 seconds.

I recommend testing with jRDC2 initially, as it's easier to do and not as complex to implement.

 

[amarnath]

Hello @amarnath

Hi, I also recommend using jRDC2. I have at least 100 users connected to an MS SQL Server 2019 database and it works perfectly. I have a mobile invoicing app in B4A and a point-of-sale application for Windows desktop in VB.NET.

Note: jRDC2 uses HikariCP as its connection pool library, and the key parameter is:

MaximumPoolSize → the maximum number of simultaneous connections to the database.

If you use 50 simultaneous connections, you could use more than 500 users.

The server for MySQL and jRDC2 should have this hardware:

16 vCPUs, 32 GB RAM, NVMe SSD, 64-bit Linux

After this, I don't know how much the performance would improve for saving data; it shouldn't take 20, 30, or 50 seconds.

I recommend testing with jRDC2 initially, as it's easier to do and not as complex to implement.

Thank you for the detailed advice!


I’ve already started migrating everything to jRDC2 and rewriting all my queries accordingly.
It’s reassuring to know it handles 100+ users smoothly—that gives me real confidence.


I’m not an expert, so I’d really appreciate your support as I finalize the migration. I’m rewriting every query in jRDC2 format and aim to complete the full app within one month.


If I run into issues with query structure, performance, or deployment on my VPS, I may reach out again for guidance—thank you in advance for your help!


Thanks again!