Difference performance levels for diff users with seemingly the same set-up?! (1 Viewer)

[Jibbadiah]

Afternoon Folks,

I have a database that multiple users have access to.
We all have new pcs of exactly the same spec.
We all have the same oracle ODBC, which is linking to tables in an oracle database on a Sun F15k Server.
The linked tables are set-up using my oracle account with saved password.
We are using the same network.
We are all using Access 97.

Queries that are run in this database are returning results in just a couple of seconds for other users, and 20-30 seconds for me.

Can anyone suggest any factors that may be causing this discrepancy?
Or things that I can run/do to improve performance?

Cheers in advance, James

 

[jamesWP]

Have you compared the memory usage in task manager for each machine?

 

[Jibbadiah]

It looks like cpu usage is at 99% on both pc's when running query.
I have tried running this on my machine with no other applications open and it is still very poor performance-wise.

Incidentally, I have logged on to another machine with the same specs and it runs poorly on the other machine as well... so it must some how be related to my profile. Any suggestions in light of this new info?

J.

 

[jamesWP]

Another possibilty is the way that you're comparing 'spec', both machine's may have 512mb of RAM and a 1.5gh processor but they may have different latencies, technologies etc. Are they the same brand hardware etc?

 

[Jibbadiah]

Yep, all the same brand, specs, software, etc.

I haven't changed any settings

It will work on another user's pc for them... but not for me.
So presume that my profile is doing something odd that their's isn't.

 

[jamesWP]

Are you trapping any errors or anything? That may be causing it to be slow and inresponsive, it's probably better to sort the errors out if you are just using something like on error resume next, if you're not using VBA then just ignore this post.

 

[Jibbadiah]

The error trapping would be the same for all users though?! We are running exactly the same VB and queries using a command button on a form. I have set up these queries originally. They work for all users, but there is a major difference in the speed with which the results are returned for me. I am thinking that there is some sort of Open Oracle connection that I experimented with months ago that has become part of my profile. Is this a possibility?!

 

[jamesWP]

I suppose you could make sure you have the latest Oracle software on each machine, although by the sounds of things you already have.

 

[Jibbadiah]

New office, New pc's, new software on client, all the same... no differences.

Here is a function that I ran once upon a time... is there any chance that this type of vb code could be stored in my profile and run everytime I open an Access Database?! Note: This code doesn't exist in the database that I have been referring to above.


Public Function Refresh_Oracle_ODBC()
On Error GoTo 0
Dim mydb As Database
Dim aoracletable As TableDef
Dim attachtable As Recordset
Set mydb = CurrentDb()
Dim tablecount As Integer
Dim i As Integer
Dim connectstr As String
Dim SourceTableName As String
Dim connect As String
Dim counter As Integer
counter = 0
Dim dummy
Dim strMessage As String

dummy = SysCmd(acSysCmdSetStatus, "Attaching Oracle tables")
tablecount = mydb.TableDefs.Count

For i = 0 To tablecount - 1
Set aoracletable = mydb.TableDefs(i)
connectstr = aoracletable.connect

If connectstr Like "ODBC*" Then
counter = counter + 1
End If

Next

For i = 0 To tablecount - 1
Set aoracletable = mydb.TableDefs(i)
connectstr = aoracletable.connect
If connectstr Like "ODBC*" Then
SourceTableName = aoracletable.SourceTableName
connect = "ODBC;DSN=w;UID=x;PWD=y;SERVER=z;"
dummy = SysCmd(acSysCmdSetStatus, "Connecting Oracle table " & SourceTableName)
aoracletable.connect = ""
aoracletable.connect = connect
strMessage = "Table " & SourceTableName & " Can't be refresh linked. Connect: " & aoracletable.connect
aoracletable.RefreshLink
End If

Next

On Error GoTo 0
dummy = SysCmd(acSysCmdClearStatus)
Exit Function

ErrorHandler3:
MsgBox Error
Resume Next

End Function

 

[jamesWP]

Have you tried compacting the database(Tools>Options>General>Compact on Close)? I'm afraid I can't answer if the code's locked into your profile or not, I've never heard of this happening.

 

[Jibbadiah]

I don't think that you can do a compact on close in '97?!

.. .but I regularly compact/housekeep this database anyway, so that wouldn't cause problems.

 

[jamesWP]

Where's the database stored?

 

[Jibbadiah]

On a shared network drive

 

[jamesWP]

Just as a test, try running it locally.

 

[Jibbadiah]

Good idea... but unfortunately it doesn't help

 

[jamesWP]

Damn, sounds very odd what with you having identical setups.

 

[Jibbadiah]

Hence the reason that I thought something odd might be happening with my Access set-up or historic vb code. Maybe I failed to clear down a recordset and set it to nothing... but this should be covered by compacting the database. Perhaps the code I just showed you is running every time I open an Access Session... I really don't know. But I would love to have the bloody thing run as fast for me as it does for everyone else!!

Oh well... guess I will just have to forget it. :-(

Thanks for offering your suggestions.

 

[jrjr]

Would it be possible to delete your profile and recreate it? Stranger things have happened!

 

[Jibbadiah]

Resolution to this problem

Hey folks,

Just thought that I would post the fix to this problem in case someone experiences similar problems. Basically I had an NT profile that had got it's knickers in a twist. I asked my IT people to fully delete and recreate the profile and alakazam... my problem was solved.

J.

 

[Jibbadiah]

Just noticed... I should be thanking jrjr for the suggestion

Cheers buddy!!

J.