problem with updating command

[pamtru]

Hi i have a problem when running this query in shared mode or set exclu off.



update table set field=5



It is very slow in a network or mapped drive. The crazy thing is that if the mapped drive is pointing to a directory in the local computer it is very slow too. Example



use c:\table shared



update table set field=5



this go allright but now let suppose that i shared the folder where my database is and my ip is 192.168.1.1 (opening from the same computer where the database are)



use \\192.168.1.1\table shared

update table set field=5



it is very slow any idea????. This occurs in vfp6 and 9 either.



Thanks

Pamela

 

[Imaginecorp]

Update is and has always been slow.

***VFP Help:
"When updating multiple records in a table opened for shared access, SQL UPDATE uses record locking, unlike the REPLACE command. This reduces record contention in multiuser situations but might reduce performance....."

Use Replace instead... Will be extremely fast if there is an index on the field being replaced using a FOR statement

 

[pamtru]

i tried using relace too but it locks all the table even if y use replace field with 5 for closed=.t. any idea.

 

[markros]

Yes, it does that according to Help. See locking data in Help file.

 

[markros]

I'm not sure it's wise to have an index on a logical field.

 

[markros]

Mike,

I'm not sure these tests are really conclusive. We need to run these tests across the Network, with re-starting PC after each test. Also we may need to test update/replace/replace in SCAN loop to be closer to the original problem.

But binary index is a really good addition to VFP9 and perhaps it is now advisable to use them on logical fields, you're right.

 

[pamtru]

i tried and the query took me about 0.02 seconds but when i tried the scan for or the update command it takes about 10 seconds to update 20000 records (it is normal??) , i am very confused why if y run the command in the local computer

update c:\databases\dbname it goes very quickly and why if i create a mapped drive in my computer to
\\localhost\database\dbname (let call it g is very slow

update g:\dbname. even with binary indexes.

thanks pamela.

 

[Imaginecorp]

Try this:

Create a Binery index on Deleted(), if using VFP9. Example Field Name = Test, table Name = Temp. Do Not set the index.

Replace temp.test with 5 for Not deleted("temp")

For a simple replace of one field with a "5", I would not bother with a Scan...EndScan...

Mike Yearwood has given you an excellent explanation as to why a network would be slower than the local hard drive, But 20,000 records is nothing...

 

[markros]

Pamela,

If you accessing local table even through mapped (shared) drive, perhaps if you would still refer it as local, update would happen quicker. So, my idea is to convert UNC path to the actual (local) path and use this table this way.

 

[pamtru]

imaginecorp i tried what you say but the problem is that replace locks me all the table. Makros can you explain me a litte more plz. I think you want me to map the drive to c:\ either than \\server.... but what will happend with the
others clients, i think it will not work???.

Thanks to all
pamela

 

[pamtru]

Sorry now i understand. i have already tried to use that way

use \\server\dbname shared

etc
etc

but the performance is similar.

Thanks
Pamela

 

[Imaginecorp]

try this:

****field name = test
**** has an index on Deleted()
**** this will not lock the tables
SELECT 0
use \\server\dbname
Set Multilocks On
CURSORSETPROP("Buffering",5)
Replace dbname.test With 5 For Deleted() = .F.
=TABLEUPDATE(1,.t.,"dbname")

 

[markros]

loFso = createobject("Scripting.FileSystemObject")
lcPath = loFSO.GetFile(shared name).Path && It may return local path

Use the local path instead.

The idea is not tested.

 

[pamtru]

i have got any idea but i dont know if i can do it. i think that it will be better to have an executable on the server that do this kinds of job, but is there a way that if i execute

use c:\databases\dbname it refers to the server c:\ and not to the client harddisk.

thanks very much.
Pamela

 

[markros]

If the application resides on the server, it would refer to the server's disk.

 

[Olaf Doschke]

If the exe is on the server, but the clients start it, C:\ refers to the clients harddrive, if the exe is started on the server, C:\ refers to the servers hard drive.

Bye, Olaf.

 

[pamtru]

Yes but if i do

do g:\program.exe (g:\ is a disk on the server)

it will execute it on the client. Its there a way to make the server execute it???.

Thanks very much!!1.
Pamela

 

[Olaf Doschke]

Several methods. Some keywords to look up are: CreateObjectEx, Scheduled Task, Webservices. You might want to use

http://www.vfpserver.com/.

The question is, why do you need to a field to 5 for all rows of a large table? Is there perhaps simply some suboptimal design of your tables?

Bye, Olaf.

 

[pamtru]

Thanks olaf i will take a look. I dont really want to all the records be checked only records that fulfill a condition but the problem is that it takes a lot of time to update those registries. In my house it took me near 7 seconds but in the client about 30 seconds that is a lot of time an it is very difficult to take a look at the network so i will trie to make an exe on the server and execute it locally.

Greetings
Pamela

 

[NasibKalsi]

Its there a way to make the server execute it???.

You can try the following logic:

Make this option a stand alone .exe, etc.

Test it directly on the server. If there is a saving in execution, then simply run it through the Scheduler. Create a new table, where your values for update are going to be stored plus a logical field such as UPDATENOW. After the program finish the updating the main table, replace this field to False. Set your scheduler to execute every so often. So when you need to update the main table, modify the Flag and the other fields of this new table. Scheduler will run the program as necessary but your program should exit immediately if the UPDATENOW flag is False.


Nasib Kalsi