I've created a simple test that illustrates my problem. I would be very happy if someone can try this out on their system.
1. Create a shared folder on the server.
2. Copy this code into a program in the shared folder and run it (make sure you CD to that folder):
CLEAR
CREATE DATABASE test
CREATE TABLE Customers;
(CustPK I ,;
custname C(40))
INDEX ON custpk TAG custpk
CREATE TABLE Invoices;
(InvPK I ,;
InvCustFK I,;
InvDate T)
INDEX ON invpk TAG invpk
INDEX ON invcustfk TAG invcustfk
WAIT WINDOW NOWAIT "generating customers"
FOR I = 1 TO 1000000
m.CustName = SYS(2015)
m.custpk = m.i
INSERT INTO Customers FROM MEMVAR
ENDFOR
WAIT WINDOW NOWAIT "generating invoices"
SELECT Customers
m.invpk = 0
SCAN
m.InvCustFK = Customers.CustPK
m.InvDate = DATETIME()
FOR i = 1 TO 3
m.invpk = m.invpk + 1
INSERT INTO Invoices FROM MEMVAR
ENDFOR
ENDSCAN
CLOSE DATABASES
WAIT CLEAR 3. Copy this code into another program in the shared folder and run it:
m.Start = DATETIME()
SYS(3054,12)
select *;
FROM Invoices ;
INNER JOIN Customers ON Invoices.InvCustFK = Customers.CustPK ;
ORDER BY invPK DESC ;
INTO CURSOR c_invoices NOFILTER
SYS(3054,0)
m.end = DATETIME()
?m.end - m.start
?_TALLY
4. Note the time it took (I don't know why rushmore isn't kicking in, but I'm too tired to work it out right now).
5. Issue a CLEAR ALL.
6. On another PC open the Customers table shared.
7. On the first PC, run the query code again and note the time it took.
In my tests, the first time returned was 16 seconds, then the second time (after closing all the tables and opening the Customers table on the other PC) the time was 33 seconds.
Does anybody see similar results?