I am trying to retrieve data from MS SQL Server (about 40000 records) and copy them to TDictionary as fast as possible. My problem is that it is slow and some setting changes give strange results. What I do is:
1. I open TMSQuery object MSQArt, it takes 0,1 s (FetchAll = false) or 0,9 s (FetchAll = true), in both cases ReadOnly=true
2. I iterate through it:
- if MSQArt.FindFirst then
- Repeat
- Until not MSQArt.FindNext;
This loop executes for 12 s (FetchAll = false) or 30 s (FetchAll = true). First strange thing is that I expected it to be faster when FetchAll = true, why is it this way? The second thing is that even 12 s seems to be a bit long, how to do that faster?
Data set is not connected to any datasources or visual controls, no calculated fields or anything else unusual. SQL server is a local server.
I need to do exactly the same thing with mySQL server (remote) with TMyQuery, result is similar, it opening takes 2,5-3 s, iteration through all records takes about 60 s while FetchAll=true.
I expected all records to be fetched on Open when FetchAll=true and iteration to be much faster.
Tell me please what can I do to speed it up.
Delphi 10.2 + SDAC 8.2.8 + myDAC 9.3.8