bde_2_fd FireDAC performances

[mario.arosio@gmail.com 0]

Hi all, I recently migrated from D7 BDE (SqlServer) to D11 FireDac.
All is working just fine, but the performance are getting worse.
Have you got any advice about the better set up? Most of my query are read only select, aming to popolate DbGrid or to save data to other kind of structure.

Thanks in advance

[Uwe Raabe 2057]

Are you fetching the whole result set at once?

[mario.arosio@gmail.com 0]

Hi Uwe, thanks for your reply.
No, I use 50 records paging, as suggested on most of discussion found on the net.

[mario.arosio@gmail.com 0]

But mooving from BDE (ANSI) to FD on SqlServer, if I'm not wrong, the new driver forces me to use UTF16, that is to physically double the amount of data transferred.
Is there a way to use FD and MSSQL (or maybe ODBC) with UTF8 ?

Edited October 20, 2022 by mario.arosio@gmail.com

[Lajos Juhász 293]

I am using the Infx driver (based on ODBC) and in that case the data is moved over the network using the db locale (ANSI or UTF-8) and at the FD or ODBC converted to UTF-16, when writing back unfortunately in case of ANSI database the db locale isn't used (instead the windows ANSI code page for non-unicode language and that can corrupt the data).

 

I expect to be similair in case of MSSQL.

[Uwe Raabe 2057]

1 hour ago, mario.arosio@gmail.com said:

No, I use 50 records paging, as suggested on most of discussion found on the net.

Fine, but there are other means to make the query fetch the complete result set. A simple Last call or setting a local index different to the query order can have the same effect.

[Lars Fosdal 1792]

We went from ADO to FireDAC and significantly improved the performance.

However - we do NOT use db aware components and inserts and updates are done through stored procedures.

 

We systematically use
   FetchOptions.Mode := fmAll;
   FetchOptions.Unidirectional := Unidirectional;
 

For stored procs, we add

  FetchOptions.Items := FetchOptions.Items - [fiMeta];

 

We also avoid MARS concurrency by ensuring that every query gets a connection of its own.

[Anders Melander 1782]

5 hours ago, Lars Fosdal said:

We went from ADO to FireDAC and significantly improved the performance.

Ditto. And ditto on your options.

 

20 hours ago, mario.arosio@gmail.com said:

Have you got any advice about the better set up?

Before trying to solve this problem you should determine where your bottleneck is instead of guessing. Is it the DB server, the network, the middleware (FireDAC), or your own code?

Examine the CPU, network usage, and DB server (using the activity monitor) during execution. Who's waiting on what? Profile your application or, if you don't know how to do that, just repeatedly pause your application in the debugger and examine the call stack. Chances are that the bottleneck will be somewhere in the call stack. It's a poor man's sampling profiler.

[Tom Chamberlain 47]

Do not forget to check the indexes on the M$/SQL server tables; missing indexes or where's and join's on fields without indexes are also performance killers.  If you have blob/varchar(max) fields, never do a Select * From. If you have any tables with millions of records and lots of indexes rebuilding the indexes could help a little but that can take time, best done in off-hours.  You may also want to look at the database Properties -> Options -> Legacy Cardinality Estimation value, if it is ON try turning it OFF and see if that makes any difference. (in test of course)

 

[PhilPlus 6]

Hello

As you need  dataset 'to save data to other kind of structure' paging is not a good idea, just try  FetchOptions.Mode := fmAll; and compare timings.

Now it would be usefull to have more informations about your software : table size, Query size...   

[mario.arosio@gmail.com 0]

Find out a brand new clue: I have a Select on 3 tables, and returns 21k rows.
Executing this query on BDE, on FD and SQLServer takes about 1 second.

Then, I decided to add a Group By clause to the query, and the result set is now about 2.5k records.
Unfortunately, it takes more or less the same time on BDE and SqlServer, but more than 3 minutes on FD (!!!)

What's wrong?

Edited March 9, 2023 by mario.arosio@gmail.com

[programmerdelphi2k 237]

post your SQL text ( not need all Fields ... just fields used to create the "joins" between tables ) ... how is it?

 

[Anders Melander 1782]

On 10/21/2022 at 1:55 PM, Anders Melander said:

Before trying to solve this problem you should determine where your bottleneck is instead of guessing. Is it the DB server, the network, the middleware (FireDAC), or your own code?

Examine the CPU, network usage, and DB server (using the activity monitor) during execution.

Did you do any of this?

[mario.arosio@gmail.com 0]

21 hours ago, programmerdelphi2k said:

post your SQL text ( not need all Fields ... just fields used to create the "joins" between tables ) ... how is it?

 

... from t1 as b 
left join t2 as r on r.codet=b.codice  
left join t3 as a  on r.codart=a.codice
left join t4 on t4.codice=b.t4 and b.clf=t4.tipo 
join t5 as o on b.ordine=o.codice 
join t6 as p on b.codpag=p.codice
where ...

.. group by ...

As this is a aumatical generated code, there is no an aggregate function on the Select statement, so I can take off the Group by statement and put a Distinct clause, but this not increase the perfoermance

[programmerdelphi2k 237]

many "JOINs" for sure...

did try use a "VIEW" instead create your SQL all time?

using a "VIEW" (on DB definition) your data "always" it's ready for use any time! then, you use this "VIEW" as you table as usual... but none space it's really used on DB!!!

Quote

SELECT xx, xx, xxx, FROM MyViewInMyDB WHERE  xx = yy  ORDER BY  ww  etc. etc.. etc...

Tip: maybe you need "re-think" your way not?

Edited March 10, 2023 by programmerdelphi2k

[mario.arosio@gmail.com 0]

The issue is that on SqlServer it takes just one second, and using the BDE as well.

Using FD it takes 3 minutes and more, but if I take off the grouping function, it takes just one second. It seems like FD execute the query on server side, and I don't know why.

Connectiong to a local server, I was able to see on Task Manager that it's working serverside 

[programmerdelphi2k 237]

look, to "GROUP all data" it's necessary a while... index, find, group, etc... etc.. etc..  if this is done using a "VIEW" on DB (before any usage), then, this time can "is gone" not? did you try if it's possible or not? 

Edited March 10, 2023 by programmerdelphi2k

[Anders Melander 1782]

1 hour ago, mario.arosio@gmail.com said:

It seems like FD execute the query on server side, and I don't know why.

Where else would it execute it? Did you expect it to execute the query locally?

 

If you execute the exact same query remotely via FireDAC and remotely via SQL Server Management Studio and they don't perform almost the same, then the problem is most likely the way the data is fetched by the client.

 

I solved a similar performance problem at one time by fiddling with some connection parameters... Here are the screenshots I took to document the difference in performance:

 

Query executed via MSSQL MS:

 

Same query executed via TFDQuery:

 

Try to compare the network bandwith usage of FireDAC vs SQL Server Management Studio when the query execute/fetches, like I did above. Again, they should be almost identical.

 

In the above case, I solved the problem by disabling MARS on the connection.

 

Query executed via TFDQuery on a connection with MARS disabled: