Hans J. Ellingsgaard

The Ryzen 5 processor is more than capable of running VM's on your PC. If it's not for gaming, go for a business PC, they have better hardware quality.

Have you checked if your interbase service is running? You can try to connect to it with tke IB Consol that comes with the Interbase installation.

Could it be this problem you have run into? https://www.ideasawakened.com/post/rad-studio-10-4-2-is-now-available-with-possible-cure-for-the-blue-dot-problem

Check in your project folders to see if there is a dbgrid.pas or dbgrid.dcu file and remove it, if it's there.

Is there anything that prevent you from indexing all the fields in the join and where clause? There is a good chance that it would give you a significiant boost on your query performance.

If you make a select count query on an indexed field, it should only take a ms to execute. The resultset will only have one record. If your table only have 1500 records, the dataset.RecordCount should also respond quckly (not as quick as the record count thoug). There must be something else that goes wrong. What database are you using? Have you tried to run the queries directly in a db manager?

A count(*) should always be done an an index field, or the point of using it is gone in the first place.

To fetch all the records from a table to get the record count is in most cases a bad praxis. If it's a table with a lot of records, you will just put a lot unnessisary strain on the database server. A count(*) query, on the other hand, has a very limited impact on the server.

You can use an extra query with a count(*) to get the number of records.

Have you tried IBExpert?

You can analyze your queries in IBExpert, but you need the paid version to be able to see all the details.

You will need a vpn connection between the datasnap server and the local network. SQL queries over an internet line is very slow, but if it's only a few records, maybe you can live with it. You would be better of writing some REST services to communicate between the two hosts.

I'm no expert in threads, but I'm shure that a LoadFromFile should be synchronized. As stated above the try/finally is pointless. If you only have these two commands in the thread, and both will be synchronized, there will be no point in putting them in a thread. It will probably just slow your program down.

Firebird is free. With Interbase you will have to pay for a license. I'm shure that both will serve you well, with RDBMS or as embedded.

But you would get rid of all the trouble of sync‘ing the data, if you kept the data on the server. With a REST service you will probably be able to load the data much faster, and save a lot of bandwith - at least if you are on a slow network.

MS SQL server can have deadlocks, just like any other database.

You can minimize the deadlock problem, if you call starttransaction just before the ExecSQL, and call CommitTransaction right after ExecSQL. Then you also can make a controlled RollBack in case of an error.

For Interbase the default username and password has been SYSDBA and masterkey, has it changed in later versions?

Yes you'r right. I was thinking about a single user client with multiple datamodules.

You could have just one FDConnection, and let all the other datamodules use that connection. You would then have just one place to set up all your connection logic, And by the way... Some SQL servers have licens restrictions on how many simultanious connections you can have, and if your are using one connection for each datamodule, you could easily end up being unable to connect to the SQL server.

Why are you not using ApplyUpdates to delete the marked records from the database?

If you can call the Generator directly, like in Interbase/Firebird and other db's, I would prefer that approach. I can't find any documentation that this is possible with Pervasive db's, but you can check it out. Create a query that calls the Generator and wrap it in a function like this. (The example is made in Interbase syntax). function GetGenID(GeneratorName: string): integer; begin with FDQGetGenID do begin SQL.Clear; SQL.ADD('SELECT GEN_ID ('+GeneratorName+', 1) from rdb$database'); Open; Result := Fields[0].AsInteger; Close; end; end; Then when you insert new data you will call the GetGenID function. FDQuery.insert; FDQueryID.Value:=GetGenID('GeneratorName'); FDQueryMASTERDATA.AsString:= 'Some value'; FDQuery.Post; You can also place the call to GetGenID in the FDQuery events, like OnNewRecord or BeforeInsert. --- If this approach is not possible with Pervasive, then this approach could work. It is at least working with Interbase. In this example I have used a DBGrid and A DataSource component to connect to the FDQuery. And the query has two fields called "ID" and "MASTERDATA". Set the AutoGenerator parameter value of ID field to arAutoInc (if you've not already done so). In the OnDataChange Event of the DataSource put the following code: if (FDQMasterID.Value < 0) and (FDQMaster.State in [dsInsert]) and not FDQMasterMASTERDATA.IsNull then begin FDQMaster.Post; FDQMaster.Refresh; end; You need to check if at least one of the fields has a value apart from the ID field, or you will get an error on the Post command, if the user adds to empty records.

You need to close and open the query every time you change the parameter values, if it has a result set.

You can use FDMemTables, they can load and save from streams, files and other datasets.

Yes, I can see that from your link. It came as a surprice to me, as I am mostly used to work with IB and Firebird databases. There is at NOLOCK isolationlevel, that will minimize risk of locking, but it will not be suitable, if there is a risk of data being changed during the execution of the query. Another thing to do is to make sure that all the fields in the where clause and the joins are indexed to avoid tablescans.