Oracle Performance

I am using TTables to hold a vendor table in Oracle. Everything was fine until the user dumped 55000 records into it. Now it takes 75 seconds to post my purchase orders. (was <5 seconds) I don't understand the connection. The vendor is simply a field in the purchase order table. Anyway, is there anyway to tweek the database so posting is faster?

(One example was to include not null on fields that indexes during table creatation. Apparently, Oracle will ignore the index if it finds a null.)
LVL 2
CalvinDayAsked:
Who is Participating?
 
kretzschmarConnect With a Mentor Commented:
hi cal,

first,
you can use queries also in a master/detail relationship. to keep it updateable, place a TUpdateSQL component as UpdateObject to your query.
(i can send you sample how to configure it)

second,
i guess you are using the native oracle driver with the bde, therefore  it could be that the bde-cache is to little now. You can raise the maxbufsize in the bde admin by configuration|Init, which should solve your problem temporary (until the rec-amount used all cache)

third,
if you plan to develop your apps primary for oracle-database, then i would recommend to use this commercial-bde-replacement:

http://www.allroundautomations.nl/doa.html 

(there is also a trial available)
these component are 2x-5x faster than a properly configured native-bde-connection, and providing also oracle.specific feature like packaged-prcedures or the oracle-qbe

meikl
0
 
JoeBoothCommented:
You should use tQuery instead of tTable when you are accessing an ORACLE server.

Are you familar with SQL?

0
 
CalvinDayAuthor Commented:
I've heard that before. And, I am familar with SQL. However, I seem to have problem with it being updateable.

Also, the table are connected in master/detail situation. Could this be a problem?
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
JoeBoothCommented:
When you design a query, you should assume it will not be updatable.  I typically have a query called WORKQUERY which I use for all my UPDATE and INSERT statements.

When a change needs to be recorded, write the SQL in the Workquery and use the ExecSQL method, rather than relying on the table to update it.

The master detail could be the problem, it depends on how the Oracle schema is defined.  If you have a cascading updates option, what appears to be a single row update could actually be updating a large number of records.
0
 
CalvinDayAuthor Commented:
Good comments.
0
 
CalvinDayAuthor Commented:
BTW

>(i can send you sample how to configure it)

please do send > calvin@data-trak.com
0
 
kretzschmarCommented:
hi cal,
sorry, didn't forget you,
sample comes in ~9 hours
(must search it first)
meikl
0
All Courses

From novice to tech pro — start learning today.