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

 

[GriffMG]

I wouldn't even use a SQL for that - so long as there is an index on MASTLINK in your myItems table.

If think if there is VFP will use rushmore to do this anyway,but my code would look like this

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 MYITEMS
SET ORDER TO MASTLINK && presumed index tag
SEEK (m.MASTLINK)
COPY TO (TEMPFILE) WHILE MYITEMS.MASTLINK = m.MASTLINK
SELECT 0
USE (TEMPFILE) EXCLUSIVE
INDEX ON MYORDER TAG MYORDER1

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

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

There is no place like G28 X0 Y0 Z0
​

 

[GriffMG]

You could add error checking for no matching items...

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

There is no place like G28 X0 Y0 Z0
​

 

[Tore Bleken]

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.

 

[GriffMG]

Alternatively, you could do this and add a progress bar if you liked...

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 MYITEMS
COPY TO (TEMPFILE) STRU
SELECT 0
USE (TEMPFILE) EXCLUSIVE ALIAS TMPFILE
INDEX ON MYORDER TAG MYORDER1
SELECT MYITEMS
SET ORDER TO MASTLINK && presumed index tag
SEEK (m.MASTLINK)
DO WHILE .NOT. EOF() .AND. MYITEMS.MASTLINK = m.MASTLINK
	SCATTER TO TMPFIELDS MEMO
	SELECT TMPFILE
	APPEND BLANK
	GATHER FROM TMPFIELDS MEMO
	SELECT MYITEMS
	SKIP
ENDDO
SELECT TMPFILE
GO TOP

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

There is no place like G28 X0 Y0 Z0
​

 

[Chris Miller]

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

Then Sys(3) just generates a stem of a filename, no path, so you just create a table in whatever is the current directory. If that's on the LAN it also slows down things, temp files should be created local, in %TEMP%, and for that matter you could use INTO CURSOR and that would not only automatically use %TEMP%, it may even not create a file at all and keep the dat of the query result in RAM memory.

SELECT * FROM MYITEMS WHERE MASTLINK=m.mastlink ORDER BY ;
  MYORDER INTO CURSOR LinkedItems

Griff's idea also needs the index, so you see how important indexing data is. Besides his non-SQL solution you could then also just filter MYITEMS or set a relation between MASTER and MYITEMS. Or SET KEY To m.MASTLINK after you SET ORDER TO MASTLINK in the MYTÌTEMS workarea, which again needs an index on MASTLINK.

Chriss

 

[GriffMG]

Did you know sys(3) and seconds() are closely related?

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

There is no place like G28 X0 Y0 Z0
​

 

[Steve-vfp9user]

Griff, Tore and Chris

Thank you for the prompt posts. I'll check them all out and post back soon.

Much appreciated.

Thank you

Steve Williams
VFP9, SP2, Windows 10

 

[Chris Miller]

...sys(3) and seconds()...

Now that you say it. Seconds() is pretty much a legal file name with extension

? sys(3), seconds()

They only say it's a legal file name, not that it is a useful file name.

Chriss

 

[GriffMG]

Sys(2015) would probably be better than sys(3) legal file - not necessarily a legal alias

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

There is no place like G28 X0 Y0 Z0
​

 

[Mike Lewis]

I'm inclined to disagree with Griff. Doing it in native Foxpro code (as opposed to SQL) is not likely to speed it up (although it might be worth a try). What's much more important is to make sure there are indexes on the MASTLINK and MYORDER fields. That could make a very big difference.

I don't think that using INTO CURSOR rather than INTO TABLE will make a huge difference, but it might help a bit. And I would not worry too much about the difference between SYS(3) and SYS(2015) (in this case), which in any case are less important with INTO CURSOR.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Mike,

Well, suppose for using MASTER with Steve's first command USE MASTER SHARED the current directory is the mapped drive or share having the master.dbf of the central database, then a query INTO TABLE whatever.dbf will create that new dbf file on the LAN side by side with the datbase DBFs, not on a local drive or TEMP. So, alone that will slow down the query. You write the result back to where you query it from to then finally also use that for a grid to display. That means, all records of the result are going through the network at least 3 times forth and back. That's simply wrong.

As Steve mentioned some notebook is specifically slower, I guess it has a worse network connection, maybe WLAN with an older hardware that can't get 1GBit, perhaps not even 100MBit. And in that situation the slower LAN together with the forth and back of transporting records through the LAN means it's much slower than better connected clients. With INTO CURSOR the network traffic is now only fetching the result once. REduces that part of the query to 33%.

If you don't have an index, it means in the first place all data is scanned, that's of course adding much more to the network traffic than the final result records, which likely are only a small portion. So the indexing can reduce the load much better than not creating LAN files, that's true. It's still just wrong to create DBFs for local usage, we have cursors for that. Something that is for your form/display/rport, doesn't need to be stored on the LAN nor on a local drive, you only have the central database on a LAN share as there is simply no other way of sharing the data, not because it's a better or ideal storage. It's no good idea to make one client the database location, though that's also done often when it all stems in a workgroup, then that one client has the data local to it. But only that client has that benefit. And since Windows OSes for desktop clients are not meant to be used in the file server role, you just make the experience even worse for everyone else.

So always keep in mind you only put data on a LAN because you have no other choice, VFP is always best with local data and so everything you only need at one client, like query results, should never be created central. And cursors are there for local usage, even exclusive. And no need to care for purging temp files, too.

Chriss

 

[GriffMG]

Hi Mike

I wasn't really saying it would necessarily be faster, just that it would likely be about the same for the copy to ... while case.

My do while examples are certainly how I would do it personally as that's what I'm used to doing things, you can do a lot of things
in a loop where the indexes have been designed to help.

Others have made sensible suggestions about cursors and the like too - and to that I could add that the .exe should be local
not dragged across the network during runtime.

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

There is no place like G28 X0 Y0 Z0
​

 

[Trento777]

@Steve Williams

Is the laptop actually connected to the server by cable?
If using just wifi connection... Ups BIG PROBLEMO, kiddo.


Grettings.

 

[Chris Miller]

Steve,

as it seems to be your first networked data experience, you also have to get used to the fact that the LAN is a slow bottleneck and you can't get the same experience as with local data anyway. Fifteen seconds is a long time, though, but it depends on the amount of data, whether that is slow or not. I think you're alarmed not mainly by that number but by differences from client to client.

You can hardly do any detail analysis on a query, as there is no server side component logging what happens in the file system there while the query executes. There's no connection timing, no service getting a request and responding, everything is done from the VFP client via the network to the file queried. The only method of analysis you have within VFP is Sys(3054) - look up the details in the help - telling what indexes Rushmore uses for the query optimization, it reports used indexes, in some case reports creating temp indexes or temp results, such things point out missing indexes, of course.

In case you actually have an index on MASTLINK but Sys(3054) tells it doesn't use that, that would be a reason to investigate further in how Rushmore misses that index. It could be a wrong index type. If that's all right look in detail how large the data is you get, only a last resort is using network monitoring tools, for example.

Rule of thumb always is there are starting problems when something new is introduced, like a network, at some point everything is configured ideally and works and then problems arise when such an infrastructure ages. It could also just be a bad network configuration contributing to this. You have no more detailed sql analysis like SQL servers can offer.

One thing you can do completely unrelated to VFP is measure the network throughput by metering how long it takes to get the same large file from the different clients, for example, that could of course also uncover any irregularities and point out weak points.

Chriss

 

[Chris Miller]

Hi Steve,

how's it going? Or are you stuck with it?

As you have that performance trouble I assume you're not familiar with indexes, so just one more note on that. You only need to index a table once, usually already when it's created as part of its structure. The table designer offers a section for indexes, too. And the simplest form of index, which you need in this case for both the MASTLINK and the MYORDER field can also be created in simple code:

USE MASTER EXCLUSIVE
INDEX ON MASTLINK TAG MASTLINK
[s]INDEX ON MYORDER TAG MASTLINK[/s]
INDEX ON MYORDER TAG MYORDER
USE

There are other index types as primary, candidate, binary, unique indexes. All of which are not needed in this case and some of which are not used by rushmore query optimization anyway.

Obviously, as it needs exclusive access, you don't do this in the application code, that's just a preparation step you do once in the lifetime of this table. If VFP has an index created like this, it will be in a CDX file with the same table file stem nane, i.e. master.cdx. And such a cdx index file contains all index tags, just a fancy term for index name, in this one file, that is always automatically associated with the table. So to set order to an index tag you only need to SET ORDER TO tagname and VFP knows the index is found in master.cdx. Also Rushmore knows for query optimization purposes it can find indexes in the master.cdx. Even old VFP versions already know the cdx file type, previously FoxPro also had (and still has) idx files which only contain a single index. Those have one big disadvantage, you have to explicitly open them so they get updated whenever data changes in the dbf. So if that's not done they can get out of sync with the data. Rushmore also doesn't take them into account when they are not explicitly opened. But with a main CDX index file it's just as simple as creating indexes once and having them available.

Your query stay as is, the indexes make it faster. And Tore is right don't just Select * - all fields - if yo don't really need all fields. That's just what adds to the amount of data going through the network. Indexes make the most part of the performance gain, as they usually limit the number of records you actually load from the DBF file (and FPT in case of memos and Blob/General etc fields). Otherwise the SQL engine needs to load data just to decide by here clause and join whether to include it in the result or not, and that's too late and costs most of the time.

Chriss

 

[Dan Olsson]

INDEX ON MYORDER TAG MASTLINK

You really did mean something else, right?

 

[Chris Miller]

Dan, correct, thank you. I have fixed my post.

Chriss

 

[Steve-vfp9user]

Hi Chris

Haven't forgotten, I will be looking into this early part of next week.

Thank you

Steve Williams
VFP9, SP2, Windows 10

 

[vaxman9]

Hi Steve,

I will reiterate what Trento777 said above - make sure the workstations are direct connected, wifi connections do not seem to be able to perform. I have a RDS server setup for remote users and wifi users.

Thanks
MSC