Hello. Please tell me how best to connect and work with the database (MySQL)? For example, if the speed of the Internet connection is 1.7 Mb / s, then the program constantly hangs for a few seconds until the data from the database comes. Logs and data that only need to be sent, I do in a separate stream - there is no problem with that.

Is it possible to arrange communication with the database not through components that work with the database directly (for example, zeos), but via web api? And to be more precise, will this design work faster? If not, how can you still organize the connection and work with the database?

The site and the database are located on the same server.

    2 answers 2

    Delphi always seemed to be able to create three-tier applications.

    Web api can be a special case of this architecture. And surely among the components there are those that work through the web.

    Logs all there are easily transferred to the server and do not chase on the Internet.

    As for the data.
    No api will help to speed up the Internet connection with the database. What is the difference how api (sql, web, something else) to upload query results from the server? They do not become smaller with any api! And more can easily become.

    You can think of paging data in one way or another.

    I don’t remember anymore, but maybe Delphi itself loads into the buffers exactly as many records as can fit on the screen at a given time. At least when the database supports cursors or when working through its three-link components.
    You just need to close all the data sets that are not needed at the moment. So that they do not try to update inadvertently and do not load in the background from the server again and again.

    • How then can you log actions? For example, authorization - verification of the entered login / password with the data in the database. Is there any function procedure (feature in the end) that keeps track of a particular query and adds the result of this query to a particular table? - GinTR1k
    • one
      You yourself compose the program and the client and server and do everything there that you consider necessary. At components the mass of events is provided. The event handlers for the necessary events are logged. I can’t say anything definite about the login, but all actions with the base are tracked. Look for materials on the three-tier, multi-tier delphi technology. - Sergey

    If your problem is that you have a gui freeze when executing queries, then this is solved by asynchronous execution of these very queries. The components through which communication with the database takes place usually have special methods / modes / methods for this.

    For example, in ADO in TExecuteOptions, you can specify eoAsyncExecute and get all the data by an event. At the same time, after the start of the request and before receiving the data, the main stream will not be blocked and the user can make some indication that data is being received and even give the opportunity to cancel the request.

    An article on how this is done: Asynchronous data loading using ADO to Delphi .

    • My problem is that I have a program with almost every action that communicates with the database. With the Internet connection speed in question, every request that is not in a separate thread is executed for a few seconds. Such requests I can not make in a separate thread, because Based on the results of the queries, I already display this or that information and perform the manipulations. I am interested in how you can "speed up" communication with the database. - GinTR1k 7:27 pm
    • one
      First, you are greatly mistaken, believing that these requests cannot be executed in a separate thread. It does not interfere with outputting information and manipulating data. - zed
    • one
      Secondly, it is possible to speed up data exchange with the database by speeding up the communication channel and / or reducing the amount of transmitted data. Regarding the communication channel, it’s not yet a fact that you are utilizing it 100% in your current implementation. The reduction in the amount of data transferred may be due to optimization of the database schema and queries to it, the use of data compression and caching on the client side, etc. To fine-tune the process of exchanging data with the database, you will probably have to write your server with your own exchange protocol. This is a very difficult and wide field for action, but you need to understand what you are doing. - zed
    • one
      Those. I fully agree, with a friend above - if you want maximum optimization, you need to build a three-tier (multi-tier) architecture. But we must understand that even having built such an architecture, it’s not a fact that everything will “fly” with you. No one has yet canceled the laws of the real world and it is impossible to transmit instantly infinite amounts of data. - zed
    • one
      Yes, but not the program, but the application server: mylektsii.ru/1-90057.html Faster may be due to the fact that less data will be transmitted (in bytes) between the user and the server and the communication channel will be used to the maximum. For example, due to the fact that when transferring over the SQL protocol, data compression may not be used (depending on the settings and the database), by including this compression itself, you will increase the speed by an order of magnitude. - zed