Improving ODBC Query Performance

[smedvid]

Just a shot in the dark... I have an Access database that contains several Oracle linked tables. Some of these linked tables are huge (15 Million records).

Well... My user is running an import routine that seems to take forever... I have a nice interface that shows start/end time so the user can benchmark the amout of time required. The strange thing is that the process take 20 minutes on my PC a P3 w/192RAM. The user has a P2 w/192 RAM and the process takes well over an hour + to complete.

Is processor speed that important to performance? Would more RAM help? If there is any document of these facts it could justify getting new hardware.

My other idea was to check how the network is segmemented...

Any ideas appreciated... Steve Medvid
"IT Consultant & Web Master"

 

[JimHorton]

Steve,
Are you importing into Oracle via Access, ie some text file on the pc or Access table that gets pushed into Oracle? Or bringing data from Oracle to Access locally?
First, I doubt the Processor is a factor here, memory probably not either, unless the procedure is written such that it takes alot of these resources. Usually simple import routines are just disk and network intensive, not processor/memory intensive. The network bandwidth and how this is being imported are probably most of the issue.

If you're moving data into Oracle from an Access table or text file, and if the native Oracle sql loader facilities aren't feasible, there is no getting around the fact that 100% of the data is going to have to travel through the pipe. Depending on how much data is moved at one time, and how often, there are a few oracle-side things that help, such as disabling/re-enabling constraints, dropping/rebuilding indexes, etc. If you can guarantee on the Access side that the records fit the constraints, etc, then this is most recommended, just run a script to rebuild the indexes and constraints when the import is done, if that's feasible.

I think you're probably on the right track with the network segmenting, though.
--Jim