Mike Yearwood
Toronto, Ontario, Canada
Information générale
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
Versions des environnements
>Hi All,
>
>Been continuing to test. Having a difficult time. Tests seem inconclusive due to multiple users, etc.
>
>Went back to this test:
>
>I am on the server. I see 3 users working in my app from their workstations.
>
>I have everyone exit my app.
>
>From the server, I can see all tables are closed. No users using my app.
>
>I am on the phone with one of my users.
>I have this ONE user (user1) log into my app.
>User1 performs a function that pulls a lot of records. Time to pull recs=15 seconds.
>I have them do this again.
>User1 performs a function that pulls a lot of records. Time to pull recs=15 seconds.
>I have user2 log into my app. They are not doing anything. Just sitting in my app doing nothing. The DBC would be open and several tables are open but user2 is doing nothing.
>User1 performs a function that pulls a lot of records. Time to pull recs=2 minutes.
>I have them do this again.
>User1 performs a function that pulls a lot of records. Time to pull recs=2 minutes.
>I have user2 exit my app. Now we are back to only 1 user (user1). I wait and ensure user2 has no open tables. The DBC is not open either. User2 is completely out.
>User1 performs a function that pulls a lot of records. Time to pull recs=2 minutes.
>I have the only user (user1) exit my app.
>I have user1 log into my app.
>User1 performs a function that pulls a lot of records. Time to pull recs=15 seconds.
>
>I feel confident the above can be repeated all day with the exact same results.
That is the classic behavior of the oplocks. It starts out giving the only user "exclusive" access. When the second user joins, the first users exclusive changes to shared. Then all users are equally slow. THAT is the normal. Exclusive use is an exceptional case and you will never get that performance across the shared users. The best thing is to disable oplocks and optimize for shared access. 15 seconds may be something you can really cut down on.
For example, query lookup tables to local cursors and then run your main query joining with those.
>
>Summary: As long as only 1 user has my app open everything is fast. If a 2nd user logs into my app, everything is slow.
>In addition, for user1 to see improvement after another user has logged in, user1 must exit and re-login to see the improvement.
>
>I should also mention, it is a bit harder to test but I have the new server installed and nothing seems to have changed. It is Server2008 R2 whereas the old server was XP.
>
>I should also mention, just in case it matters, when my app starts up, refresh is set to 0,0 so my app does not do any refreshing by itself.
>
>Hope this makes sense to someone.
>
>Thanks,
>John
Précédent
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement