FireDAC Postgresql performance vs ADO SQL/Server performance

[Martyn Spencer 0]

My experience with SQL server is very limited and I generally use Postgresql or Oracle with very good results. I am part way through a project for a client where the existing system uses SQL server/ADO and I am moving to a Postgresql database and FireDAC. The system was created by a different developer and his approach was very different to mine. Having replaced old dynamically created queries with appropriate parametrised and more optimal queries, I find that the performance is not what I would expect. The existing SQL server database has a pretty poor design and no indexes. The Postgresql database is better designed, has appropriate indexes, and when accessed natively offers better performance to running equivalent queries on the SQL server database. With this in mind, I do not think that the issue is related to server performance, or the connection to the servers.

 

The existing system makes extensive use of DevExpress tables and relies heavily on filtering provided by the grids. I generally do not work this way, preferring to provide more tailored functions depending on the specific work flow, etc. I will start by saying I think that the root of the problem is related to the amount of data being fetched from the database, potentially including metadata retrieval. Both databases are operating in a data centre and accessed over the Internet. This is not my preference, but it does demonstrate a significant performance difference between the two systems. Running against a local database shows little difference between the two. Experience tells me that the ADO based system is probably fetching records on demand, whereas I think FireDAC is fetching all rows and possibly metadata as well. Having read the FireDAC documentation, it does appear that I have more control but I would be interested in what settings would give comparative performance to the ADO based system, if any. My ideal is to only fetch data when the user pages through a grid. In the long-term, this will be less of a problem because I will be more specific in my queries, but I have a transitionary period where I would like to allow the users to make use of the existing grids with the lowest possible performance penalty.

[Attila Kovacs 629]

58 minutes ago, Martyn Spencer said:

and relies heavily on filtering provided by the grids

...

My ideal is to only fetch data when the user pages through a grid

 

I have no idea how should both work at the same time.

Does the grid filters per sql and refetch?

What "fetchmode" setting will be used?

Are there keyfields in the tables? (primary key)

Can you monitor the traffic to find bottlenecks? (TFDMonitor? I don't know it's name)

 

 

Edited March 3, 2020 by Attila Kovacs

[Alexander Elagin 143]

ExpressQuantumGrid by default (in the Bound mode) fetches all data from the database table and then uses it for the fast local filtering / sorting / etc functions without further database operations (unless an update is required). There is another mode (Server mode) where data is fetched as needed but in this mode local grid filtering is limited.

[Martyn Spencer 0]

Hi both. Thanks for your comments. It would appear that based on the settings identified, the grids are fetching all data so that the user can manipulate it based on the various grid functions (sorting, filtering etc). To answer the first question that @Attila Kovacs raises, 90+% of the time, the users view the top 10-20 rows in any given grid, but the original system design fetches anything from 2-10K records back "in case" the user wants to start applying filtering etc. This is not a work flow that I feel works well with any client/server system and would typically change the system so that it fetches relevant data based on pre-specified conditions and in the first instance only fetches records that are in view, with appropriate pagination. So, in reply, I would say that unless the user has specified any custom sorting, filtering etc, it would have been good to carry out just a partial fetch. Ultimately, I will be replacing these grids so I am looking for a short-term "fix".

 

Quote

Does the grid filters per sql and refetch?

It looks as though all records are fetched. I am concerned with the initial fetch and was hoping to reduce the quantity of data. I was further hoping that filtering and sorting would result in modifications to the underlying query, rather than relying on a complete database fetch then client-based sorting/filtering. I think this is answered by what @Alexander Elagin suggests, I could try the "server mode" and see if this works more efficiently without restricting the user.

 

Quote

What "fetchmode" setting will be used?

I am asking what the nearest equivalent to ADO is with FireDAC, so am not sure. The default is fmAll, which clearly fetches everything. I believe that there is an on demand setting, but am not sure how well it works with the DevExpress grids. I am looking for the best option to return only visible records unless the user starts filtering or sorting. I appreciate that this may reduce functionality (as suggested above).

 

Quote

Are there keyfields in the tables? (primary key)

Yes and testing demonstrates that this is not the issue. It really does appear to be the quantity of data returned by the initial fetch. I also note that when there are master detail relationships, ADO performance does seem to be better than FireDAC for some reason, even given that the ADO/SQL server based tables have no indexes and the Postgresql tables do (and are being used). Differences in the database engines, based on testing, do not appear to account for this. It just seems as though FireDAC has a greater overhead.

 

Quote

Can you monitor the traffic to find bottlenecks?

It was monitoring that highlighted the number of fetches being made by FireDAC. I do not know of an equivalent way of monitoring ADO so any suggestions would be welcome.