Hello,

we have an issue using elevatdDB in Client/Server mode.

When we connect to the Server, the select is 5x slower then local access
We did build our own server, but the result is the same using the standard edbsrvr.

SELECT * FROM KLANTEN (in ElevateDB manager)

================================================================================
29515 row(s) returned in 7,39 secs
================================================================================

Local access

================================================================================
29515 row(s) returned in 1,344 secs
================================================================================

It seems that table structure is important, blob fields seem to slow down.

Another table without blob fields gives this result
Client/server
81617 row(s) returned in 3,735 secs
Local Access
81617 row(s) returned in 0,75 secs

My system to test:
Processor   12th Gen Intel(R) Core(TM) i7-12850HX   2.10 GHz
Memory           64,0 GB (63,7 GB beschikbaar)
Type systeem   64-bits besturingssysteem, x64-processor
HD: M.2 2280 1 TB, Gen 4 PCIe x4 NVMe, Solid State Drive
Windows 11 Pro

Is there a way to speed up the queries or optimize the server?

Hello,

The hardware specs you gave are for the local machine or the server?
What about the network, is it fast?
What does affect speed is not the table structure but the total amount of data that has to cross the network wires.
Also, is it a sensitive or insensitive result set? That can make a huge difference for big tables.

--
Fernando Dias
[Team Elevate]

<<  Accowin wrote:

...

Is there a way to speed up the queries or optimize the server?

>>

Fernando covered most of this already but another consideration is RemoteReadSize property of the dataset.

You want to minimize number of trips to the server and download in chunks - otherwise You will incur cost of the round-trip latency each time.

Raul

Accowin


I'm guessing that by "local access" you mean the data and application (EDBManager?) on the same PC. If so "29515 row(s) returned in 1,344 secs" from the query SELECT * FROM KLANTEN" is appalling. That query basically amounts to opening the table and should return in zero seconds.

I'm retired so don't have any decent sized tables to play with nut I just ran "select * from movies" - zero seconds return for 12140 rows

Taking Fernado's points

<<The hardware specs you gave are for the local machine or the server?>>

I'm assuming either both or local machin

<<What about the network, is it fast?>>

Will make a difference depending on what traffic but I think the local speed is the first problem

<<What does affect speed is not the table structure but the total amount of data that has to cross the network wires.>>

I have a suspicion the table structure is the root cause of the issue, especially since that can affect network traffic with actual data size, lookups, calculated fields etc

The results for the second table tends to support this - still should be zero for a simple table open

<<Also, is it a sensitive or insensitive result set? That can make a huge difference for big tables.>>

Oh yes. Sensitive zero seconds for opening, insensitive 6.848 seconds

Default for EDBManager (here at least) is sensitive and I would guess its sensitive in this case as well otherwise I'd expect a slower opening even for local.


CAN YOU POST THE TABLE STRUCTURE PLEASE

Roy Lambert

<< Accowin wrote:

Is there a way to speed up the queries or optimize the server? >>

Have you tried File I/O Buffering?

= Steve

Hello,

thanks for the answers.

I am testing onmy Portable, so client application and server are on the same machine.
I also tested from a Virtual machine on this host.

<I have a suspicion the table structure is the root cause of the issue, especially since that can affect network traffic with actual data size, lookups, calculated fields etc>


table structure:
CREATE TABLE "Klanten"
(
"ID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL,
"Referte" VARCHAR(9) COLLATE "ANSI_CI",
"Naam" VARCHAR(100) COLLATE "ANSI_CI",
"Adres" CLOB COLLATE "ANSI_CI",
"Land" VARCHAR(3) COLLATE "ANSI_CI",
"PostKode" VARCHAR(20) COLLATE "ANSI_CI",
"Gemeente" VARCHAR(100) COLLATE "ANSI_CI",
"Telefoon" VARCHAR(20) COLLATE "ANSI_CI",
"Fax" VARCHAR(20) COLLATE "ANSI_CI",
"Gsm" VARCHAR(20) COLLATE "ANSI_CI",
"Email" CLOB COLLATE "ANSI_CI",
"Taal" VARCHAR(1) COLLATE "ANSI_CI",
"Valuta" VARCHAR(3) COLLATE "ANSI_CI",
"BtwLand" VARCHAR(2) COLLATE "ANSI_CI",
"BtwNr" VARCHAR(20) COLLATE "ANSI_CI",
"BtwKode" SMALLINT,
"BtwEEG" SMALLINT,
"Grootboek" VARCHAR(9) COLLATE "ANSI_CI",
"BankRek" VARCHAR(20) COLLATE "ANSI_CI",
"KredietLimiet" INTEGER,
"BetTermijn" SMALLINT,
"BetEindeMnd" BOOLEAN,
"FaktuurAdres" CLOB COLLATE "ANSI_CI",
"FaktLand" VARCHAR(3) COLLATE "ANSI_CI",
"FaktPostKode" VARCHAR(20) COLLATE "ANSI_CI",
"FaktGemeente" VARCHAR(100) COLLATE "ANSI_CI",
"Beroep" VARCHAR(50) COLLATE "ANSI_CI",
"Opmerk" CLOB COLLATE "ANSI_CI",
"ProcKKB" FLOAT,
"DagenKKB" INTEGER,
"RSZLandKode" VARCHAR(5) COLLATE "ANSI_CI",
"NationaalNr" VARCHAR(14) COLLATE "ANSI_CI",
"DefDocLayout" INTEGER,
"AardPersoon" INTEGER,
"Iban" VARCHAR(34) COLLATE "ANSI_CI",
"BIC" VARCHAR(11) COLLATE "ANSI_CI",
"AardF28150" INTEGER,
"Actief" BOOLEAN,
"ProcKredietBep" FLOAT,
"BetWijze" INTEGER,
"Verzendwijze" INTEGER,
"LevAdres" CLOB COLLATE "ANSI_CI",
"LevLand" VARCHAR(3) COLLATE "ANSI_CI",
"LevPostKode" VARCHAR(20) COLLATE "ANSI_CI",
"LevGemeente" VARCHAR(100) COLLATE "ANSI_CI",
"ContactPersoon" VARCHAR(100) COLLATE "ANSI_CI" DEFAULT '' ,
"ProcKorting" FLOAT DEFAULT 0.00 ,
"PopupText" CLOB COLLATE "ANSI_CI",
"PopupTonen" BOOLEAN DEFAULT FALSE ,
"DefEmailLayout" INTEGER,
"DocVerzendType" INTEGER DEFAULT 0,
"RappelNrMax" INTEGER DEFAULT 4,
"EmailFact" CLOB COLLATE "ANSI_CI",
"UblInvoice" BOOLEAN DEFAULT FALSE,
"CreatedOn" TIMESTAMP,
"ModifiedOn" TIMESTAMP,
"SyncedOn" TIMESTAMP,
CONSTRAINT "PK" PRIMARY KEY ("Referte")
)
VERSION 1.00
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768

We have an extra index
CREATE INDEX "IX_Naam" ON "Klanten" ("Naam" COLLATE "ANSI_CI")



<<Also, is it a sensitive or insensitive result set? That can make a huge difference for big tables.>>

In the elevateDB manager his seems to make a big difference
In server mode

Result Set
---------
The result set was insensitive and read-only
The result set consisted of zero or more rows
================================================================================
29515 row(s) returned in 9,203 secs
================================================================================

Result Set
----------
The result set was sensitive
The result set consisted of zero or more rows
The result set was ordered using the index PK
================================================================================
29515 row(s) returned in 0 secs
================================================================================

So Sensitive is faster.
When "Request Sensitive Rsult Set" is on in the elevate DB Manager, I notice that an index is used.

Normaly, the query is used to show a selection list in a grid.
So, if I understand well, I have to get a sensitive result set and set the grid to Read-only.

Are there other properties of TEdbquery involved?

Hello,

The time difference is because an insensitive result set is behind the scenes a temporary table containing all the result set that has to be copied from the original database table (or tables, in case of a join). A sensitive result set is in fact a cursor on the database table itself.

The issue is that sometimes it's not possible to get a sensitive result set. To make things simple, it's only possible if you request it and if there is an index on the order you are requesting and there is only one table involved in the query (no joins).  It's also possible in some other cases, but for now lets keep things simple

So yeah, if you want the result set to be read only, just set the UI components to read only, or the query itself - set TEDBQuery.Readonly to True. A TEDBQuery (or any other component that return db data) can return a sensitive result set and yet be set to read only.

--
Fernando Dias
[Team Elevate]

Accowin


Here's another thought for you since you're using queries - restrict the query to just those columns you want in your lookup. Tim has been pretty smart about transferring only the data needed over the network  it may be worth doing on a large table.

As an example with one of my tables selecting insensitive the time taken for "select * from movies" took 4.7 seconds while "select _ID, _Title from movies" too 0.062 seconds.

If you try something like this run the query a few times so that buffering is sort of removed from the test.

Roy Lambert

Hello,

I was able to increase the speed by setting the property "RequestSensitive" of the query to true.
I have also added an additional index.
Because there were only 3 fields from the joined table, I replaced the JOIN in the query with (SELECT.... so that the query got a sensitive result.
(See source below, old source in comment with --)

--t.TotDebEUR AS Deb,
--t.TotCredEUR AS Cred,
--t.TotDebEUR-t.TotCredEUR AS Saldo
(SELECT t.TotDebEUR  FROM KlantTot t WHERE (klanten.Referte, :werkJaar, 9999) = (t.Referte,t.Jaar, t.Periode)) AS Deb,
(SELECT t.TotCredEUR FROM KlantTot t WHERE (klanten.Referte, :werkJaar, 9999) = (t.Referte,t.Jaar, t.Periode)) AS Cred,
(SELECT t.TotDebEUR-t.TotCredEUR FROM KlantTot t WHERE (klanten.Referte, :werkJaar, 9999) = (t.Referte,t.Jaar, t.Periode)) AS Saldo
FROM klanten
--LEFT JOIN KlantTot AS t ON (klanten.Referte, :werkJaar, 9999) = (t.Referte,t.Jaar, t.Periode)
WHERE Actief = True

Thanks for the replies, you have been extra helpful to me