SQL statement, is there a quicker way? 6

[Steve-vfp9user]

The below code is used to retrieve items that are linked to a MASTER table. There are several hundred items in the table MYITEMS.

USE MASTER SHARED
GO m.recno  && Record number
m.mastlink=MASTLINK  && This stores the actual linked number from MASTER to MYITEMS

tempfile=SYS(3)

SELECT * FROM MYITEMS WHERE MASTLINK=m.mastlink ORDER BY ;
  MYORDER INTO TABLE tempfile+'.dbf'

* The linked items in tempfile+'.dbf' are then displayed in a grid on a form

I have been able to set up a small network in our office so when you run the program where the main files are stored it's quick, but when you run it from a laptop on the network it can take up to fifteen seconds to gather the information.

It all works but I was just wondering if the experts know of any quicker way to extract the information for those wishing to display it?

Thank you

Steve Williams
VFP9, SP2, Windows 10

 

[Steve-vfp9user]

GriffMG:

We went with your suggestion and this has now speeded up the process. I've also changed the SYS(3) to SYS(2015). Many thanks

Trento777: "Is the laptop actually connected to the server by cable?"

We tried it over a wireless connection and via ethernet cable, the latter was quicker and yes, we knew using it over a wireless connection wasn't the best idea! Thank you

Tore:

"Use a cursor instead of a table for the result. And make sure that Myitems has an index tag on Mastlink and on Myorder. And last, if you don't need all the fields, only select the fields you really need."

There are only about fifteen fields in this table so not sure it would make much difference just selecting the seven we need for the forms grid. Mike Lewis suggested using a cursor wouldn't make to much difference but we had to stay with a tempfile as this also forms part of a grid on a form where the record source is the tempfile. We could change it, but as its working, we're going to leave it. Thank you

Chris:

"Do you have an index on the MASTLINK and MYORDER fields? Because that will speed up the query." Yes on both. Thank you

I appreciate the other posts on this thread, apologies if I've not mentioned the others.

Thank you

Steve Williams
VFP9, SP2, Windows 10