SQL Performance Tuning: Good Programming Practices Guidelines

Most of the developers are like 'accidental developers' and they identify the database performance problems only when the client reports the issues. If you give importance to these guidelines, you can standardize the performance to a stable level. I wrote this article from my own experience and knowledge. Since the Internet is a wide resource, like most of the professionals I also got such information from the contributions which I read from browsing.
The statements provided here may not be applicable to all the databases since all the features (Eg: Stored Procedures) are not available on all the database systems.

SQL Performance: Good Practices for Beginners
  1. Divide your large table to small multiple tables. (In SQL terminology, we call it as Normalization.)
  2. Use the lookup tables. This will help you reduce the overload of tables. E.g.: If you have a product-sales table and want to store 3 pictures of the same product, rather than adding three columns to the table, use one lookup table. This way, you have the freedom to add any number of photos.
  3. Also, use only necessary columns. E.g.: If you have columns A & B and you have the sum in another column C. Simply, you don't need that C since we can reproduce the same table effect with statement

  1. The performance of your database increases if the primary key is numeric or small in data width.
  2. Do not use database to store your images. A good approach is store only URL in the table and store the image file in desk itself.
  3. But sometimes the Normalization may fail if you have a large database, much calculations and frequent calls if the output table is almost static in result. (E.g.: salary details of employees for years old data). In this case, you can improve the performance by using a De-normalized table. But that too has many demerits like very large database, etc.
  4. Use the right data types and widths when defining columns. E.g.: If you want to store the 'age', then you don't need to use the VARCHAR field since a TINYINT can do the job. (TINYINT can store integer data from 0 to 255. You know no 'age' value exceeds 255.)
  5. Start! If you don't have the practice of using Primary Key, Foreign Key, etc.
  6. Use stored procedures and functions instead of writing all the messy code in the program itself. It not only gives you the performance but also a matter of security.
  7. Always try to keep one error table to log all the errors which comes from the stored procedures or functions. In T-SQL (version < 2000), you have the global variable @@ERROR to detect errors. From 2005 or greater versions, you have the freedom to use TRY/CATCH which is available in today's highlevel languages. It helps you save time and avoid tension for finding 'Where is the bug?'
  8. Use transactions to avoid loss of data while execution of a stored procedure. The possibility of failure is high. It can be a data truncation problem, network problem, etc. In MSSQL, we have BEGIN TRANSACTION, COMMIT TRANSACTION and ROLLBACK TRANSACTION available. Use this along with the error handling methods. Regarding performance, TRANSACTION is one step down when comparing with ordinary statements but when considering quality of the product it is very high.
  9. Avoid using CURSORs. Use only when no other way exists. In most cases, CURSOR consumes much time since it is a record-by-record process.
  10. Avoid using GOTOs. This is not just for SQL but for all the programming languages. GOTO statement is considered unstructured and it has the possibility of jumping out of our hands. But there are circumstances in which we cannot avoid GOTO.
  11. Avoid IF and start using CASE.
  12. Write your code readable.
    1. User proper indents.
    2. Keep all the statements in upper case.
    3. Use lowercase for data-types.
    4. Use Upper Camel notations (also known as Pascal Casing) for all user created objects. E.g.:@EmployeeCode.
    5. Use meaningful user-defined identifiers. Use only names which contain A-Z, a-Z, 0-9 and underscore character. Do not use regional special characters.
    6. Specify operation performed also in the name of stored procedure. E.g.: spEmployeeUpdate.
    7. Always use the schema also while calling SQL objects. Eg: EXEC dbo.spEmployeeUpdat.
    8. Use BEGIN and END to specify block of statements.
    9. User alias where we need operations including multiple tables.
    10. Even though the AS keyword is optional, use always.
  13. Use proper commenting. Also add the purpose, author, date, version, etc. details on top of all the procedures, functions, etc.
  14. Add test data with statements inside stored procedures and comment it. This helps you in future debugging. Also this helps another developer to get a quick start.
  15. Do proper INDEX-ing. There will be a noticeable difference when applying INDEX on very large tables.
  16. Always use column names in SELECT, INSERT, UPDATE statements. Avoid using asterisks –(*). 
  17. Avoid using dynamic SQL statements inside stored procedures, i.e., Do not use SQL statements to create SQL statements.
  18. Be careful while SELECT-ing strings with LIKE clause. If it is not used wisely, it will give rise to performance problems.



Account Payables,1,Advance Table In OAF,1,AME,1,AOL Concepts,19,AP Holds,1,Apps Initialization,1,Blanket Purchase Orders,1,Blanket Releases,1,Careers,9,Common Errors and Solutions,2,Concurrent Programs,10,Contract Purchase Orders,1,Corporate Culture,3,Corporate Ladder,3,Customer Interface,3,Customization,1,De-Normalization,1,EAM,1,EBS Tutorials,10,Education,16,Emterprise Asset Management,1,English,1,Enterprise Asset Management,1,Entrepreneur,1,Error Fixes,1,Executables,2,Extension,1,fnd_user,1,FNDLOAD,1,Global Agreements,1,Guest Posts,7,Handy Scripts,63,Health Tips,1,Important Apps Queries,1,Infographic,3,Interview Preparation,62,Interview Q/A,68,Inventory Org ID,1,Iproc,4,Iprocurement,4,IT,3,jDeveloper,1,Job Search,1,Key Tables In Oracle Apps,5,Learning English,1,MDS,1,Metadata Tables,2,Multi Org Architecture,2,Normalization,1,O2C,1,OAF Interview,50,OAF Personalization,1,OAF Tutorials,52,OCI,1,Operating Unit,1,Oracle 10g,1,Oracle 11g,1,Oracle Access Manager,1,Oracle ADF,1,Oracle Alerts,2,Oracle AP,12,Oracle Apex,1,Oracle APIs,4,Oracle Application Object Library,19,Oracle Applications,10,Oracle Approvals Management,1,Oracle Apps,4,Oracle Apps DBA,2,Oracle Apps Interviews,36,Oracle Apps Tips,9,Oracle Apps Tutorials,98,Oracle AR,18,Oracle Assets,2,Oracle BAM,1,Oracle BI Publisher,1,Oracle BPEL,1,Oracle BPM,1,Oracle Cloud,1,Oracle Cloud Infrastructure,1,Oracle Dabase Structure,2,Oracle Database Architecture,2,Oracle Database Key Tables,3,Oracle EAM,2,Oracle EBS,5,Oracle Financials,2,Oracle Forms And Reports,17,Oracle Forms Interview Questions,6,Oracle Forms Tutorials,6,Oracle GL,4,Oracle Holds,1,Oracle HRMS,10,Oracle Human Resources,1,Oracle Inventory,4,Oracle Payables,1,Oracle Payroll,1,Oracle POTutorials,1,Oracle Purchasing,7,Oracle Reports Interview,9,Oracle Reports Tutorials,3,Oracle SOA,1,Oracle System Administrator,4,Oracle Workflow,4,Order Management,2,Organization ID,3,Organizations,3,Performance Tuning,2,Personalization,1,PL/SQL Block,1,PL/SQL Interview Questions,11,PL/SQL Scripts,44,PL/SQL Tutorials,53,Planned Purchase Orders,1,PO,1,PO Action History,1,PO_ACTION_HISTORY,1,POracle Purchasing,1,PR,1,Procedures And Functions,2,Profile Options,2,Purchase Orders,1,Purchase Requisition,2,Receipts,1,Request Groups,1,Responsibility,1,Shell Script,1,SOA Tutorials,4,Social Media,1,Software Industry,2,Software Tools,1,SQL And PL/SQL Interview,38,SQL Interview,52,SQL Scripts,38,SQL Tutorials,43,Standard Purchase Orders,1,Troubleshooting,1,Unexpected error,1,Unix/Linux Commands,3,User,1,Value Sets,1,Weblogic,1,XML Publisher,4,
Oracle Apps Guy: SQL Performance Tuning: Good Programming Practices Guidelines
SQL Performance Tuning: Good Programming Practices Guidelines
Oracle Apps Guy
Loaded All Posts Not found any posts VIEW ALL Readmore Reply Cancel reply Delete By Home PAGES POSTS View All RECOMMENDED FOR YOU LABEL ARCHIVE SEARCH ALL POSTS Not found any post match with your request Back Home Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sun Mon Tue Wed Thu Fri Sat January February March April May June July August September October November December Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec just now 1 minute ago $$1$$ minutes ago 1 hour ago $$1$$ hours ago Yesterday $$1$$ days ago $$1$$ weeks ago more than 5 weeks ago Followers Follow THIS PREMIUM CONTENT IS LOCKED STEP 1: Share to a social network STEP 2: Click the link on your social network Copy All Code Select All Code All codes were copied to your clipboard Can not copy the codes / texts, please press [CTRL]+[C] (or CMD+C with Mac) to copy Table of Content