Hi andrew.

>What you are saying about the file server behaviour is probably true. But how come the updates are slow *only* if they involve nested select (at least it's my observation)?
>If the issue is with write and read caching, then shouldn't it also be slow for the following:
>DELETE FROM resource WHERE "whatever_condition_on_resource_table_only" ???

Unfortunately I can't answer this one as I really don't know without trying. Maybe the DELETE command issues record locking (slow) on one construction and file locking on another (Fast). The filemon utility can answer this question for you. It takes a while to find out what the output is telling you, but if you get the hang of it, it is invaluable for analyzing and solving performance problems related to data. Use the utility and you'll find a new friend against you battle agains slownesses.

Walter,


>I assume Filemon should be running on the File Server. If yes, then unfortunately I can't use it. At least not now.

No it should run on the client. BTW, it is just an EXE file, not something you need to install, so if you don't have administrator rights you can still run it.




>--Andrew
>
>
>>Hi andrew,
>
>>This is actually normal behaviour. When only one instance is using the tables, the file >server can use read and write buffering on the tables.
>
>>When a second instance is opening the same tables, the server is going to disable at >least the write cache. As soon you are going to write something to the table it is also >going to disable the read chache. This file server mechanism is called opportunistic >locking and is part of the file server redirector implementation.
>
>>Question. Is there any referential integrity in effect (cascading deletes perhaps)?
>
>>You can download filemon from www.sysinternals.com to see what messages go accross the >network and you can find out what is taking so long. Also you can see what information >comes from the cache and what is read from/written to the network.
>
>>Walter