Proposal: allow to run multiple EZ query methods using same connection string

Third Party Coronium
#1

I just read that EZ query methods manage connection string automatically. I need to run multiple queries per api call, so with current solution I feel that I need to replace all my mysql methods calls with dbQuery to use same connection and avoid performance issues in future.

So few ideas came to my mind:

  1. We already need to pass database name to method, like core.mysql.select(db_name, select_tbl). So can it be variational parameter: db_name or db_connection (string or table).

  2. Coronium can work in this way (don’t know how difficult it is to implement with current architecture):

    a) request started, have an ez_db_connections lua table initialized
    b ) core.mysql.select(db_name, select_tbl) called, inside it cheks if ez_db_connections already contains db_name then just use opened connection, otherwise create connection and put it into ez_db_connections
    c) after api method ended close all connections in ez_db_connections

  1. Advanced question: if I will use one db connection for multiple queries how many transactions do I have? I guess it would be transaction per query and I guess any serious project will require transactions in some places. 
#2

Hi,

I may not understand all the questions perfectly, so correct me where I am missing something.

If you are using EZ queries on the server-side, you don’t really need to worry too much. Multiple EZ query calls in the same API method use a connection pool so that the speed is very fast. The issue is when you are calling multiple EZ queries from the client-side.

To answer #1, EZ queries can take a database name, or connection table already (it’s just not well documented). But a connection table is generally used for connecting to remote databases.

To answer #2, as long as your EZ queries are called on the server-side in the same method, something like you describe takes place, through the use of a keep-alive connection pool.

The dbQuery methods are for developers who want to write all the queries by hand. To be honest, in my tests, the speed difference is minimal. Again, the main issue with connections is when using the EZ query methods from the client-side.

I recommend that you always do your MySQL work on the server-side. If you need to gather data from multiple sources, run your EZ queries and collect the results, then send them back down to the client in the same call.

I hope that helps. If you need more clarity, let me know.

-dev

#3

Hi, thanks for clarifying!

I saw note about connections it in server-side docs: https://develephant.github.io/coronium-core-docs/server/modules/mysql/#advanced-methods.

So my last question is about transactions: I need to write SQL script if I want to execute multiple queries in one transaction, right?

#4

Hi,

The docs need a little updating, that was put in before I activated the connection pool.

As far as your question. If a group of EZ queries wont work for what you need, then yes, use the dbQuery.

-dev

#5

Hi,

I may not understand all the questions perfectly, so correct me where I am missing something.

If you are using EZ queries on the server-side, you don’t really need to worry too much. Multiple EZ query calls in the same API method use a connection pool so that the speed is very fast. The issue is when you are calling multiple EZ queries from the client-side.

To answer #1, EZ queries can take a database name, or connection table already (it’s just not well documented). But a connection table is generally used for connecting to remote databases.

To answer #2, as long as your EZ queries are called on the server-side in the same method, something like you describe takes place, through the use of a keep-alive connection pool.

The dbQuery methods are for developers who want to write all the queries by hand. To be honest, in my tests, the speed difference is minimal. Again, the main issue with connections is when using the EZ query methods from the client-side.

I recommend that you always do your MySQL work on the server-side. If you need to gather data from multiple sources, run your EZ queries and collect the results, then send them back down to the client in the same call.

I hope that helps. If you need more clarity, let me know.

-dev

#6

Hi, thanks for clarifying!

I saw note about connections it in server-side docs: https://develephant.github.io/coronium-core-docs/server/modules/mysql/#advanced-methods.

So my last question is about transactions: I need to write SQL script if I want to execute multiple queries in one transaction, right?

#7

Hi,

The docs need a little updating, that was put in before I activated the connection pool.

As far as your question. If a group of EZ queries wont work for what you need, then yes, use the dbQuery.

-dev