Devart Forums

Hi all,

I am currently working on a project, where I write a mapper from an old BTrieve Database to MSSQL. My customer has a lot of tools written in Delphi 5 which use BTrieve TTable like components to access a proprietary ERP database. The newer version of the ERP now uses MSSQL. In order to be able to migrate my customer's tools, I wrote a custom TTable component with an interface like BTrieve, which uses TMSTable objects internally to do the mapping and "behave" like the Btrieve components, but uses MSSQL.

It works quite well, however, when using larger tables (40000 lines and above) it gets sluggish. It seems when using the default settings (CursorType = ctDefaultResultSet, FetchAll=true) TMSTable fetches the whole table into memory on startup, which takes too long, and uses crazy amounts of memory.

Partial progress could be made when setting FetchAll to false. Startup time was now acceptable, but unfortunately, when setting the IndexFieldNames property (which I have to do in order to emulate BTrieve) it seemed like there was again a fetch of the complete data.

When using CursorType=ctStatic, memory usage of the App was as expected, but still the SQLServer hat to sweat and (as I understand it) had to cache the whole result set on the serverside, which will kill it, once the apps are deployed to 20 Workstations.

How can I prevent TMSTable from reading the whole thing when setting IndexFieldNames?

Version Info:
Delphi 5
SDAC 3.55.0.17

Thank you very much!

The IndexFieldNames property performs local ordering. This requires all records to be fetched from the server. Probably server-side ordering will be better in your case. Use SetOrderBy for that.

Thank you for your reply.
We changed the code to not set IndexFieldNames, but set the OrderFields property, which gives much better results. I suppose setting OrderFields is the same as calling SetOrderBy. Is that true?

We now see the records fetched on demand, but still, one problem persists. It seems like when calling Last, for setting the cursor to the last record in the table, it again fetches all records, and not just the last n records. Is there any way to stop it from doing that?

Thank you.

Yes, OrderFields is the same. But this property persists only in TMSTable (not in TMSQuery).

In order to obtain last records from the requested record set, SDAC must fetch all previous records. There is no possibility to change this behaviour. You can try to decries requested records count by adding conditions to the FilterSQL property of TMSTable.
As a rule requesting large record sets to the client for browsing is in not a good approach.