[SOLVED] Reason for my app's slow down lazarus / 16-Databases ---------------------------------------------------------------------- egsuh · 2021-03-16 08:50 I'm comparing DB connections between TIBConnection plus TSQLQuery, etc., which is provided as default by FPC/Lazarus, and IBX (TIBDatabase, TIBQuery, etc.) from MWA software. I'm testing IBX because it provides some more components like for stored procedure, etc., but I found IBX are a little slower than default SQL products. Has anybody experienced the same thing?  I cannot compare directly for now because I'm modifying other things at the same time, but before I change the IBX components to FPC components I'm looking for opinions from experienced users. ---------------------------------------------------------------------- tonyw · 2021-03-16 22:33 Could you please be more explicit about "little slower". What tests did you run and what were the results. ---------------------------------------------------------------------- egsuh · 2021-03-17 00:36 QuoteCould you please be more explicit about "little slower". What tests did you run and what were the results. No formal test. I'm using it for web-server. I can feel it is late at browser responses --- when run in CGI or FCGI mode. As there are other changes as well, it's not sure whether the DB components caused the slowdown. In httpserver mode, the response is quite prompt. (I ran both server and browser on the same machine.) ---------------------------------------------------------------------- tonyw · 2021-03-17 11:53 Quote from: egsuh on March 17, 2021, 01:36:54 amQuoteCould you please be more explicit about "little slower". What tests did you run and what were the results. No formal test. I'm using it for web-server. I can feel it is late at browser responses --- when run in CGI or FCGI mode. As there are other changes as well, it's not sure whether the DB components caused the slowdown. In httpserver mode, the response is quite prompt. (I ran both server and browser on the same machine.) The Firebird server is a complex environment and with lots of caching and threading. It can return variable response times depending on operating conditions. When testing IBX I have found that you need a very controlled test and to run it several times - and take an average over each run - to get any idea of how performance compares. I am genuinely interest to hear of any performance issues with IBX as I try to take a lot of care over things like path lengths and buffer management in order to try and make sure that it is as fast as possible. So any feedback is useful. A known issue is with the handling of very large datasets (> 100,000 rows). The original Borland code allocates a single memory area for the dataset buffer pool and when this becomes exhausted, it has to be reallocated as a single bigger block requiring a complete in memory copy to expand the size of the buffer pool. The IBX dataset property BufferChunks can be used to control the size of the initial memory pool and the expansion factor used when reallocating the pool. This is documented in section 6.2.1 of the IBX User Guide and is an important tuning factor for handling large datasets. Improving on this algorithm is on my "to do" list but is not a quick fix otherwise I would have done it already. To do this properly requires a rewrite of the buffer management code. Another workaround for large datasets is to set the dataset property "UniDirectional" to true as this avoids the need to cache each record and works well if all you need to do is to pass through a dataset in sequence rather than randomly processing rows. ---------------------------------------------------------------------- egsuh · 2021-03-17 13:51 @tonyw My expression is not exact. I don't think that IBX has any performance issue. Actually I'm sharing (and comparing) the same webmodule between CGI, FCGI, and HttpServer applications, and in HttpServer, I don't see the slow-down. What I'm looking for finally is the reason that my new application (w/ IBX) is gotten slowed down. As I changed many parts of the application, I'd like to remove possibilities one by one. The first thought is IBX, because it is the most noticeable change. If IBX is not reason of slow down, then I have to check other parts one by one. For example, I moved much part which used to be done within pascal code to stored procedure. Will this slow down the operation? Both pascal code and stored procedure run on the same machine. Also I have changed the client side --- javascript part. It does not necessarily have to mean performance problem. One issue, I experienced, is that "prepare" works in TSQLQuery without active transaction, but TIBQuery does not. This may mean IBX does more strict examination before any operation, and these may slow down the whole operation if there are many database operations. But we cannot say that this is bad performance. Or it may be related with initial connection to DB and related initializations. In most situations, this would not be an issue, but my application is web server, and I cannot keep the DB connected. In CGI mode, database is re-connected at every request. I'm trying to solve this by using FastCGI, but currently testing on CGI.  AFAIK, Firebird is much talkative database --- which means much communications, so not very good choice if there have to be many connections. I'd like to listen to your thoughts. I want to know whether anybody have had any experience or ideas on those issues. My database is not so large... it is rather small. ---------------------------------------------------------------------- tonyw · 2021-03-17 14:23 Quote from: egsuh on March 17, 2021, 02:51:06 pm@tonyw My expression is not exact. I don't think that IBX has any performance issue. Actually I'm sharing (and comparing) the same webmodule between CGI, FCGI, and HttpServer applications, and in HttpServer, I don't see the slow-down. What I'm looking for finally is the reason that my new application (w/ IBX) is gotten slowed down. As I changed many parts of the application, I'd like to remove possibilities one by one. The first thought is IBX, because it is the most noticeable change. If IBX is not reason of slow down, then I have to check other parts one by one. For example, I moved much part which used to be done within pascal code to stored procedure. Will this slow down the operation? Both pascal code and stored procedure run on the same machine. Also I have changed the client side --- javascript part. Actually, a stored procedure is executed server side. It is an SQL code fragment, saved in the database, and then executed using an execute query. It may slow things down if the overhead of executing a remote query exceeds the cost of doing the same thing locally. Recall that you have both the network overhead plus the server execution overhead to consider. Quote from: egsuh on March 17, 2021, 02:51:06 pmIt does not necessarily have to mean performance problem. One issue, I experienced, is that "prepare" works in TSQLQuery without active transaction, but TIBQuery does not. This may mean IBX does more strict examination before any operation, and these may slow down the whole operation if there are many database operations. But we cannot say that this is bad performance. The transaction is a Firebird artefact and both IBX and TSQLQuery have to start a Firebird transaction before preparing a query - IBX just makes this more visible to you and allows you to control better the transaction parameters and when it commits/rollsback. This may allow you to optimise performance rather than the other way around. Quote from: egsuh on March 17, 2021, 02:51:06 pmOr it may be related with initial connection to DB and related initializations. In most situations, this would not be an issue, but my application is web server, and I cannot keep the DB connected. In CGI mode, database is re-connected at every request. I'm trying to solve this by using FastCGI, but currently testing on CGI.  AFAIK, Firebird is much talkative database --- which means much communications, so not very good choice if there have to be many connections. I'd like to listen to your thoughts. PHP caches connections which why I would tend to prefer it for server side functions. If you have to use CGI then have you considered using a local database attachment. To do this you have to read/write access to the database (from the web server) and the connect string is just the path to the database (Firebird 3 and later) i.e. no "localhost:" prefix. Quote from: egsuh on March 17, 2021, 02:51:06 pmI want to know whether anybody have had any experience or ideas on those issues. My database is not so large... it is rather small. ---------------------------------------------------------------------- egsuh · 2021-03-21 10:55 I'm writing this because based on my tests, IBX has nothing to do with my application's slow down. I applied several changes to my new version of application as listed below, and my app was slowed down noticeably at web browser -- even without any strict measurements. 1)  Changing Lazarus's default TIBConnection / TSQLQuery to IBX's components 2)  Changing javascript at the webbrowser end 3)  Separating query components and database component into different modules --- they were within the same module before. 4)  Rewriting internal procedures I have checked first and second changes, and they do not seem to have caused the slowdown. I have to check third and fourth points. I hope one of my hypotheses is not perceived as a result of comparable test. ---------------------------------------------------------------------- egsuh · 2021-03-22 09:06 I found the reason.  The second app was using different database (but the structures, etc. are almost the same between the two databases), just one of its tables had much more records (300,000 records vs. 10,000 records). And I searched with unindexed keys. Just adding another index to the table solved slow-down problem.