My company's database application is, in all honesty, not a particularly complicated one. It's straight up client/server, we support Oracle 9i and Oracle 10g, and we use OLEDB on the Windows XP Professional clients to communicate with the database.
To this point in my career I have never had to worry about performance tuning. The client's DBAs and network analysts always took care of that for us, and it being such a relativity simple architecture and database, performance was not typically a problem.
I would like to add to my skillset at least being able to make SUGGESTIONS for improving performance - it's never my responsibility, but still, it can only help me to at least have a basic bag of tricks!
We have a simple client/server setup that accesses a database with tables called Order, OrderProduct, and Product.
All tables have a field called DBKey (a unique rowID populated by a sequence in a trigger)
The Product table also has fields like UPC, Price, Category, Name, Size etc. (our actual product table has about 400 fields)
OrderProduct has DBParentProductKey, DBParentOrderKey, Quantity and (for the time being) nothing else.
Order has Date, Place, CustomerName etc.
All tables have an index on DBKey. Product has an additional index on UPC.
Product will typically have 500,000 rows, Order will typically have 2,000 rows, OrderProduct will be approximately 40,000 rows (assuming an average of about 20 products per order).
The user is presented the list of orders, the user picks one, then the OrderProduct table is queried to get the corresponding Product DBKeys and the list of products and all of their fields is shown to the user.
We require that the client DBA create the database and the tablespaces, then our installation script creates all of the tables, views, procedures, indexes etc.
We require that the client be in charge of its network.
Obviously our app is not THIS simple but I wanted to give something for reference.
So the question I have is, if I have one client for whom certain operations are taking significantly longer than similar operations for other clients (i.e. just opening an order!), what are some suggestions I can make for attempting to improve performance?
I will be generous with points on this one for multiple answers- I'm just trying to have SOME tricks in my pocket!