Hi Walter,
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" ???
No, there is no ref integrity going on.
I assume Filemon should be running on the File Server. If yes, then unfortunately I can't use it. At least not now.
--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