use again 1

[newtofoxpro]

set exclusive off
use tran.dbf in 0 alias "TranEdit" index tran.cdx order 1
use tran.dbf in 0 alias "TranRead" index tran.cdx again exclusive noupdate order 1

when I have to read data I use "TranRead" and insert/change I use "TranEdit"
anybody finds speed advantage to another user in lan ?

"performance improves because Visual FoxPro does not need to test the status of record or file locks."

Link

Best Regards.

 

[Mike Lewis]

I don't think this will work. If you use a file, then use it again EXCLUSIVE, you will get a "File in use" error. Besides, you should only open a file exclusively if you are sure that no other user - or no other concurrent process in your application - will need to open it, and then only if you have a special reason to.

I doubt that you will get any significant performance advantage by opening it exclusively, or, for that matter, by opening it with NOUPDATE. But you can always test that for yourself.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[newtofoxpro]

I tried as another instance (reopen vfp) I did not find any error. I don't have lan setup on development machine.

 

[Mike Lewis]

I did not find any error. I don't have lan setup on development machine.

Hmm. That's not what I'm seeing. I did this:

* First VFP instance 
USE customer.dbf IN 0 ALIAS a1

* Second VFP instance
USE customer.dbf IN 0 ALIAS a2 EXCLUSIVE NOUPDATE AGAIN

In that case, I got a "File access is denied" error in the second instance. This is exactly what I would expect.

But is that relevant? If I understood your question correctly, you want to open the same table twice in the same instance. I don't see the point of that. In any case, it wouldn't be opened exclusively, so any performance benefits wouldn't apply.

I might have misunderstood your question, in which case my apologies.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[vgulielmus]

In any case, it wouldn't be opened exclusively

Exactly.

USE aa SHARED
USE aa IN 0 ALIAS bb EXCLUSIVE AGAIN
SELECT bb
PACK

Got the error "File must be opened exclusively"

USE aa exclusive
USE aa IN 0 ALIAS bb EXCLUSIVE AGAIN
SELECT bb
PACK

Got the error "File is opened in another work area"

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[newtofoxpro]

Please copy this 3 line code to test.prg

set exclusive off
use tran.dbf in 0 alias "TranEdit" index tran.cdx order 1
use tran.dbf in 0 alias "TranRead" index tran.cdx again exclusive noupdate order 1

Run test.prg ( do test.prg )

Open again vfp.exe (another instance)
Run test.prg

Best Regards.

 

[newtofoxpro]

No No No Sorry, Sorry for confusion.

Pls don't run PACK.

commands like packing & indexing, needs no another user should open tran.dbf file.

 

[newtofoxpro]

And one who want to pack or indexing needs pure exclusive open..
My question is not that.
Sorry again for confusion.

 

[Mike Lewis]

I think Vilhelm-Ion only included the PACK to check to see if the file was opened exclusively. (The way I do that is to open the Modify Structure window and see if allows changes.)

So, NewToFoxpro, are you OK now, or have you still got a question?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[newtofoxpro]

Please run this code as test.prg at multiple instances at a time.

IF !FILE("temp.dbf")
CREATE TABLE temp.dbf (line02c c(40))
ENDIF
IF !FILE("temp.cdx")
SET EXCLUSIVE ON
USE temp.dbf
INDEX on line02c TAG one OF temp.cdx
INDEX on DELETED() TAG two OF temp.cdx
ENDIF
CLOSE DATABASES
SET EXCLUSIVE OFF
USE temp.dbf ORDER 1 ALIAS "tmpEdit" IN 0
USE temp.dbf ORDER 1 ALIAS "tmpRead" IN 0 AGAIN EXCLUSIVE NOUPDATE
FOR ii=1 TO 100
INSERT INTO tmpEdit VALUES (SYS(2015))
WAIT WINDOW TIMEOUT 0.5 "Adding data Pls Wait "+STR(RECNO())
ENDFOR
WAIT WINDOW NOWAIT "Total "+STR(RECCOUNT("tmpRead"))+" Added"
SELECT * FROM tmpRead
RETURN

Please find 2nd last line of the above code. this is simple select sql but when running complex select sql, is "tmpread" works better then "tmpedit" on lan ?

 

[Mike Lewis]

Well, I still think that you won't notice any significant difference. But why not try it for yourself? You will need to have a lot more than your 100 rows to get a worthwhile test, but it shouldn't be difficult to do that - nor to simulate the "complex SQL".

Run some tests and let us know what results you get.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[newtofoxpro]

Thanks you for you understood my Question. Again I move to my first post/question. I have provided link.

"performance improves because Visual FoxPro does not need to test the status of record or file locks."

while select sql, tmpRead should not affect the "status of record or file locks."

In this case file-locking or net-taffic is applied to "tmpEdit" not to "tmpRead" I think so. Pls correct me if I am wrong.

Thank you & Best Regards.

 

[Olaf Doschke]

Besides EXCLUSIVE not working, while you say it is, NOUPDATE is not accelerating read access, it'll just hinder you to insert data in that alias.
Another thing is, you don't need to specify the main cdx file associated with the dbf, it's opened automatic.

Using dbfs exclusive in a single user application is really a thing accelerating write access. Some validations and locking and unlocking needed in case of shared access can be skipped.

You can verify that with a little loop:

Cd c:\users\public
Erase test.*
Create Table test (id I)
Use
Use test Shared
t0 =  Seconds()
For n = 1 to 1000
   Insert into test values (n)
   Replace id with n+1
   Update test set id=n where id = n+1
EndFor
t1 =  Seconds()
Use Dbf() Exclusive
Set Safety Off
Zap in test
For n = 1001 to 2000
   Insert into test values (n)
   Replace id with n+1
   Update test set id=n where id = n+1
EndFor
t2 = Seconds()
Use

? "shared:",t1-t0
? "exclusive:",t2-t1

In my case the duration factor is about 2/3. Exclusive access just needs 2/3 of the time needed with shared access. And that is because each single replace and update creates a lock and unlocks it, if access only is shared, with exclusive access, this is skipped. But you can't use exclusive for applications with shared access.

Bye, Olaf.

 

[DSummZZZ]

You won't get an error opening one table shared and the same table AGAIN exclusive as long as it is the same instance of VFP. VFP is 'smart' enough to handle it. But you of course can't do that in different instances or apps or quite possibly, even different data sessions.
Opening a table exclusive is the only option for packing, indexing, zapping, etc. And yes, it is much faster to have a table open exclusive if you need to say, append a bunch of records from another table, text file, whatever. But there is no reason to for normal operations or app usage, for updating records.


-Dave Summers-

Even more Fox stuff at:

http://www.dave-summers.net/foxprolinks.htm

 

[Olaf Doschke]

> VFP is 'smart' enough to handle it.
Only halfways. You only get the File access is denied in another user/IDE session.
But as Vilhelm-Ion Praisach showed, the table isn't opened exclusive, really.

Anyway, as you say there is no reason for normal operations to use a table exclusive. It can only help in case of single user apps. And it also only makes write operations. Your hdd, RAM, etc. doesn't get faster reading in exclusive mode, so the idea to open a table EXCLUSIVE NOUPDATE is not helping at all. It'll only fail delayed, if that table is in use already from another client/user/IDE session.

Bye, Olaf.

 

[newtofoxpro]

I don't find any error, is this the reason ?

SET MULTILOCKS ON
SET REPROCESS TO AUTOMATIC

As per recent post, Pure Exclusive use table has speed advantage for write purpose. Because vfp does not consider locking etc.,. I agree.

 

[Olaf Doschke]

NewToFoxpro,

you still haven't understood what Vilhelm-Ion points out. While both USEs don't error, you don't get exclusive access. You assume you have exclusive access because you don't get an error, but that's your wrong assumption.

Halfway going back to your original post code:

set exclusive off
use Labels.dbf in 0 alias "LabelsEdit"
use Labels.dbf in 0 alias "LabelsRead" again exclusive noupdate
? IsExclusive("LabelsEdit")
? IsExclusive("LabelsRead")

I just put in another table foxpro finds in HOME() for everyone to check and I removed the order clauses, which are irrelevent for showing this and not present in labels or other DBFs in HOME().

You see? You don't get an error, we all don't get an error directly. But if using that with mutliple users they'll get errors and it doesn accelerate read access. You only used EXCLUSIVE for your read alias, that's useless.

I can't really tell you why foxpro doesn't error, but it's not like Dave assumed, that foxpro is smart enough to handle it. Then it would need to change the exclusive access for the LabelsEdit alias, too. VFP simply ignores your wish to get exclusive access.

And what Vilhelm-Ion found out is that. And he used PACK to demonstrate you don't have exclusive access, though you have no error.

Bye, Olaf.

 

[Olaf Doschke]

Your other sample code also is working in multiple instances, as you see your code never uses a table exclusive.

So finally, if you always use a table shared before exclusive, you never open a table exclusive, you get no problems with other sessions, but you get no exclusive access. Your code acts in the same way as without the EXCLUSIVE clause.

Your measurement of SQL-Select speed is gaining from cached data. Whatever you do last is faster. If you measure speed of something, alsways do that twice, once in reversed order, too. Or do it twice, one query only at a time and then restarting to not measure cache speed.

Overall you think you discovered something, which you didn't. I've been through similar discoveries.

Bye, Olaf.

 

[newtofoxpro]

Yes, this is what I am looking for. But I wish to check myself.

Thank you & Best Regards.

 

[Olaf Doschke]

Another triple post. No reason to worry, though.

There is another good example already in this thread about how to ensure fair measurements: I introduced the ZAP in my share/exclusive write test for getting the truth, while the code also works without and I also started without it - therefore the second insert lop starts at 1001. I got an unexpected result.

With ZAP both 1000 inserts will start on an empty table, you may now change the llop going from 1 to 1000, too, but it won't matter. If you stay with 1001 and only remove the ZAP, the inserts and the replaces and updates with shared access are faster than exclusive. Why? Not because of caching, but mainly because the speed of UPDATE statements degrades, as there is no index on id. The Updates of records 1001 to 2000 are each scanning the full additional 1000 records, if they are not zapped. If introducing INDEX ON id TAG id the difference with and without ZAP gets neglectable.

Bye, Olaf.