vsbabu.org

Tuning Oracle backed ASP application

Last week, I worked on some one else’s code to "clean up, speed up and make it work". This was an application written in ASP (using VBScript) with Oracle 8i database. I thought I’ll record my findings here so that when I grow old, I can come back and look at it!

• Design an Oracle application using Oracle. This particular application was designed using MS-Access and then exported to Oracle and code changed here and there to make it work.
  • So, there was no DDL (create table, views etc) scripts available
  • All the tables were created with default storage parameters. This made it quickly run out of extents in 2 days of operation
  • People who design in Access use *ID columns as primary keys. You can also have composite primary keys that enforce the business logic.
• When you have primary keys, create it in the beginning. Don’t wait to create it till data is populated.
• When you’ve foreign keys and referential integrity constraints, create these at the beginning.
• Create indexes as required. Not having indexes is really bad.
• Don’t write code in the typical ASP style with Record Sets and loops. In the initial code there were six record sets with 3-4 select-all from tables. This was straight forward and out of many tutorials out there, but doesn’t make for production quality code. After I rewrote it from scratch, it came down to one query with joins. And WHERE clauses instead of If-Then conditions in ASP. Full table scans are BAD for performance! Speed was improved from 3 minutes to less than a second.
• There was one join. When you join, in the FROM clause, put the tables in the order of decreasing row count (after the WHERE filtering). Example can be FROM orderlines, orders instead of the basic instinct to write it as FROM orders, orderlines.
• In the WHERE clause, try not to put column names on both sides of an operator, as much as possible. Example can be WHERE orders.order_date=sysdate AND orderlines.order_date = sysdate instead of WHERE orders.order_date=sysdate AND orderlines.order_date = orders.order_date
• Don’t use OR or IN in WHERE clause. Use EXISTS or NOT EXISTS as much as possible.
• In ASP, you better clean up all your objects (ADO, RECORDSET etc) before you end the scripts. In this applications there were quite a few RESPONSE.REDIRECT in the middle and the cleaning up was all towards the end. When the redirects happened, the objects where not destroyed and it quickly ate up the memory. This is really stupid implementation of ASP engine, but hey, unless you know how to use better technology, use it with care.
• When you design a database, don’t ever think of it in a horizontal way. For example, don’t design it as like ORDERS.ORDERQUANTITIES where this column has values like 3,1,5,9 and then parse the CSV. Put it as a detail table with 3, 1, 5 and 9 all going to distinct records - horizontal way. Makes it lot more easy to query. I’d have thought that this is basic database design principle.
• Elementary programming wisdom asks you to cleanly write the code using functions and procedures. Do that! This particular application was so full of If-Then-Else and repeating code that I couldn’t fix the scripts any further in the given time. I had to write it from scratch. One long winded program with patches all over the place is what happens when you don’t use functions or procedures.
• Use pseudo-columns in Oracle where possible. In this particular application, current day was painfully formatted in VBScript and passed into an INSERT statement! Using SYSDATE in the query makes it that much simpler and puts the time of the database machine rather than web server’s time!
• Have scripts for initial data load, DDL creation/modification etc. Using TOAD to create table and fill in the data, and then export/import database to production systems is not a good idea.
• If you don’t need some code, delete it out! In some include files, I ended up deleting around 600 lines. Blind copy-paste and not knowing what each line of code is not at all good. Someone else would’ve written it doesn’t mean that it is right or it is required.

I wrote about working on this last week too.

1. jfdskf

   Posted by: Anupam Mazumdar on June 7, 2004 02:07 PM