Hallo,
We are using MyDAC components with DevExpress TcxGrid for years now but we have just discovered significant performance difference between MyDAC (Delphi Tokyo 10.2.2) and TFDConnection. TFDConnection is about 100% faster when using DevExpress TcxGrid (only TDataset.Open): 4100ms with FDQuery1.open and up to 9000ms with MyQuery1.open. You can download Delphi Project and dump file at https://www.sendspace.com/file/dk6unq . Or maybe we are doing something wrong ?
Note: run test for several times to see full difference.
Best regards,
Živanović Predrag
MyDAC and DevExpress TcxGrid performance problem
Re: MyDAC and DevExpress TcxGrid performance problem
To solve the defined task, you can set the FetchAll property to False. The TMyQuery component has the FetchAll and FetchRows properties, which default values are True and 25, respectively. If FetchAll = False, only the number of rows specified in the FetchRows property will be read by the TMyQuery.Open method. The rest of the rows will be fetched as necessary. As you scroll down a DBGrid, the next portion of rows will be fetched. You can learn more details about it in the MyDAC documentation: https://devart.com/mydac/docs/?devart.m ... tchall.htm
In your example, TMyQuery.FetchAll is set to True, hence the all rows are read when opening a dataset. To get correct results, you can add FDQuery1.Last и MyQuery1.Last to both examples. Also, you can set the TMyQuery.FetchAll property to False.
If this doesn't help, the issue might be caused by the third-party component (cxGrid).
Please try replacing cxGrid with the standard DBGrid, and let us know the result.
In your example, TMyQuery.FetchAll is set to True, hence the all rows are read when opening a dataset. To get correct results, you can add FDQuery1.Last и MyQuery1.Last to both examples. Also, you can set the TMyQuery.FetchAll property to False.
If this doesn't help, the issue might be caused by the third-party component (cxGrid).
Please try replacing cxGrid with the standard DBGrid, and let us know the result.
Re: MyDAC and DevExpress TcxGrid performance problem
Thank you for reply. I don't think that FetchAll is issue because there is no performance difference when using standard TDBGrid component. I tried to go to last record right after Open, as you suggested and here is results:
- TFDQuery = 7303ms
- TMyQuery = 11795ms
I first contacted DevExpress Customer Support and after some testing they answer " ... we think that MyDAC sends some unnecessary notifications that other data set components do not send". I found solution using TcxGrid's Begin/EndUpdate and implement it to forms where we expect to have large datasets. Anyway I still think that many of MyDAC customers use DevExpress Grid and that this is significant performance difference so I am suggesting you to try to identify problem in future MyDAC builds.
Best regards.
- TFDQuery = 7303ms
- TMyQuery = 11795ms
I first contacted DevExpress Customer Support and after some testing they answer " ... we think that MyDAC sends some unnecessary notifications that other data set components do not send". I found solution using TcxGrid's Begin/EndUpdate and implement it to forms where we expect to have large datasets. Anyway I still think that many of MyDAC customers use DevExpress Grid and that this is significant performance difference so I am suggesting you to try to identify problem in future MyDAC builds.
Best regards.
Re: MyDAC and DevExpress TcxGrid performance problem
e've executed your example with your DB data and the standard DBGrid. The results are as follows:
- your code example. FetchAll = False without MyQuery1.Last and FDQuery1.Last
MyDAC - 104 ms
FireDAC - 1311 ms
- your code example with MyQuery1.Last and FDQuery1.Last
MyDAC - 2385 ms
FireDAC - 2448 ms
- your code example. FetchAll = False without MyQuery1.Last and FDQuery1.Last
MyDAC - 104 ms
FireDAC - 1311 ms
- your code example with MyQuery1.Last and FDQuery1.Last
MyDAC - 2385 ms
FireDAC - 2448 ms
Re: MyDAC and DevExpress TcxGrid performance problem
Problem persist only when using DevExpress TcxGrid component. Everything is OK on standard TDBGrid. My timings in for TcxGrid only.