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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

kretzschmarCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.