SET ORDER vs USE.... 1

[brigmar]

Real quick question:

I see code like this:

SELECT 0
USE table1
SET ORDER TO id

.. which I, in my thrifty ways would write as:

USE table1 IN 0 ORDER id

So I ask why is the first form so prevalent (version compatibility aside)?

I've been told the first form is faster. Why would that be so ?

 

[mkrausnick]

AFAIK the two statements are identical. The first form might be coded by less experienced programmers. Personally, I use the form

use table1 in (select("table1")) order id

that automatically prevents 'already in use' errors.

Mike Krausnick
Dublin, California

 

[TamarGranor]

The difference is that the more verbose version leaves you in the work area for the newly opened table. The shorter version does not.

Tamar

 

[GriffMG]

Which logical record are you left on in each case?

I suspect that the verbose one would need a GO TOP to be certain of which record was current?

Regards

Griff
Keep [Smile]ing

 

[alvechurchdata]

The difference is that the more verbose version leaves you in the work area for the newly opened table.

Good lord yes. The number of maintenance bugs caused by people who write:

close data all
use customer in 0 order cust_id

and then proceed coding on the assumption that they are in the customer table. This works if there are no other tables open. If they later change the code to open another table:

close data all
use spares in 0 order spares_id
use customer in 0 order cust_id

then the code is broken because they're in the spares table and they've opened customer in another area.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[mkrausnick]

Regarding what record is current using each method:

Using the first method -

SELECT 0
USE table1
SET ORDER TO id

the current record is record 1, even after the SET ORDER. You'd have to GO TOP or INDEXSEEK to get to the desired starting record.

In the second case -

USE table1 in 0 ORDER id
select table1

The current record is the top record within the specified order - as if a GO TOP was present after the SELECT.

Not the result I expected when I started the test, but there you are.

And, yes, I've bitten myself enough times by forgetting to select the area that I now always select the area right before starting any SCAN loop. It's also good documentation - there's no question what table the SCAN loop is operating on because it's explicitly indicated right before the SCAN statement.

Mike Krausnick
Dublin, California

 

[brigmar]

Well, what prompted me to bring the question was seeing the 3-liner, and some longtime FoxPro guys saying that that method was faster (although unable to quantify why).

The only things I can think of are:
1) Because the workarea is new, you then have to explicitly add a SELECT alias command.
2) As the 2nd form automatically performs a "GO TOP" (as per Mike's post) that this would make that statement appear slower.

But then those two points cancel each other out, as the programmer would have to explicitly add a GO TOP command??

Mike,
Generally the first thing I do after opening tables and setting relations is a SCAN, and like you do, I always precede a SCAN with an explicit SELECT command for readability/documentation (ALOT of the previous code here is written as DO WHILE NOT EOF() with all the baggage that entails).

 

[Mike Lewis]

Brigmar,

... some longtime FoxPro guys saying that that method was faster

Maybe it is, but any difference in speed is likely to be pretty negligible -- unless you were executing the code many millions of times within a loop. But you'd be unlikely to do that with code that opens a table.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[GriffMG]

Mike,

Perhaps Brigmar meant that the SCAN approach was quicker than the DO WHILE one?

I have seen that issue argued (by me for one) here - the DO WHILE being, for some reason, a bit quicker under some circumstances...

But it's all a bit nebulous, rather like arguing that all replaces should be done using a single statement...

replace a with x, b with y, c with z

which is undoubtably faster than ...

replace a with x
replace b with y
replace c with z

(particularly if there is any scope beyond the current record involved!)

But, which is more likely to be more difficult to maitain later - if you're consistent you can then search for 'replace y with' should it be necessary. Much harder, using the standard tools, to search for code references starting with 'replace' which have 'x' and 'with' on the same line (or a continuation line)...

Just blathering...





Regards

Griff
Keep [Smile]ing

 

[Mike Lewis]

Griff,

Perhaps Brigmar meant that the SCAN approach was quicker than the DO WHILE one?

Fair comment, although that's not how I read it.

Just blathering...

Feel free <g>.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[brigmar]

Mike,
Nope, just in the order of 10 tables containing up to 1.2 million rows. Frankly, I was a little dubious as to the speed claim, as I couldn't see a tangible difference nor see why there would be a difference. Hence throwing it out here.

Griff,
Nope, that's not how I meant it. I was responding to Mike Krausnick's suggestion to precede each SCAN with an explicit SELECT for readability. I just mentioned DO WHILE as the same longtime guy writes his loops thus:

SELECT thetable
GO TOP
DO WHILE !EOF()
  IF condition
     ...
     commands
     ...
  ENDIF

  SELECT thetable
  SKIP
ENDDO

... and again, in my thrifty ways, I end up recoding as ..

SELECT thetable
SCAN FOR condition
  ...
  commands
  ...
ENDSCAN && thetable

 

[TamarGranor]

For going through a whole table, SCAN is faster than DO WHILE and, of course, less error-prone since you don't need SKIP and thus can't accidentally omit it.

Tamar

 

[craigber]

USE <table>
SET ORDER TO <MyTag>

is faster than

USE <table> SET ORDER TO <MyTag>



Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com

Author, CrysDev: A Developer's Guide to Integrating Crystal Reports&quot;

 

[Dan Freeman]

And the first one doesn't cause a syntax error, either.

*gd&r*

 

[brigmar]

craigber,
That is what I have been told, but like your post there was no explanation as to why, nor as to how much faster.

Mike,
Same sort of numbers I see, but I ran a slightly different test (IN 0). I fail to see any tangible advantage to the '3 line form' in my situation.

 

[craigber]

When you open a table, VFP positions the record pointer at the top or "first record". So, if you set the order when you USE the table, the record pointer has to be moved to the top of the table, based on the index.

When you USE <table>, the record pointer is simply put at record 1. If you then issue a separate SET ORDER TO, the record pointer isn't moved...only the controlling tag is changed.

How long the first method takes depends on the size of the table. I've seen it take several seconds. Long enough that users noticed so I changed my code to USE, followed by a separate SET ORDER TO. The application load time was signficantly faster and the users were happy.



Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com

Author, CrysDev: A Developer's Guide to Integrating Crystal Reports&quot;

 

[craigber]

Mike,

Not necessarily. Only if you want to guarantee you're at the first record.

Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com

Author, CrysDev: A Developer's Guide to Integrating Crystal Reports&quot;

 

[brigmar]

Craigber,
Your explanation goes along with what I was sorta thinking -might- be the reason.. ie, that 'USE xxx ORDER yyy' has an implicit 'GO TOP'.
But like Mike says, when opening and setting the order, I'd want to be at the first record according to the index. I think the only time I'd not care is if I'm using the table as a child in a relation.
Thank you both.

 

[craigber]

Brigmar,

Based on my experience, I think it will still be faster to have separate USE, SET ORDER, and GO TOP commands.

Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com

Author, CrysDev: A Developer's Guide to Integrating Crystal Reports&quot;

 

[Mike Lewis]

I don't disagree with any of the good advice given in this thread, but keep in mind that you are probably never going to use a USE statement more than a few times in a given sesssion. Even if you saved as much as 500 ms each time you call it, the total potential saving is going to be fairly negligible.

By all means, go for the fastest option, but, when it comes to optimisation, I would've thought there are better uses for your energy.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk