Best Practice in Database Development for Performance

Comment

Comment by Admon Phillips on January 27, 2009 at 7:28pm

Hi guys,

Help me to create a video library database.I have some difficulties creating it, as am still learning Access. Thank you in advance. Phillips

Comment by Barry on January 1, 2009 at 11:28pm

Hi Geoff,

Thanks for your response.

I will send a message to all the members to tell thme about your White Paper.

Regards

Barry

Comment by Geoff Duniam on January 1, 2009 at 11:47am

Hi Barry,

Happy New Year! All going well, I'll drop you a private mail re recent progress. Thanks for posting this, nice to see it available. I should probably mention that this paper is based around a lot of Tom Kytes design philosophies, and I heartily recommend that everyone checks out his web site if you haven't already asktom.oracle.com - full of excellent information. It's mostly Oracle specific, but the design philosophy is database independent.

Hope all's well with you, have a great new year and speak soon.

Cheers

Geoff

Comment by Barry on December 30, 2008 at 10:10am

You can download Geoff's White Paper in Word document format from my Database Answers Web Site :-
http://www.databaseanswers.org/Geoff_Duniams_Paper_on_Database_Design_for_Performance.doc

I look forward to your comments !

Barry

Comment by Barry on December 30, 2008 at 9:07am

This is a White Paper contributed by Geoff Duniam, Senior DBA at PriceWaterhouseCoopers in London, England, who is Member 1076.


1 Best Practice in Database Development for Performance
1.1 Introduction
This is aimed at people with some experience in Database Administration.
It is intended to provide a comprehensive review of problems and solutions.
1.2 Successful database applications
• An Application built around (and dependant) on the database… will succeed or fail based on how it uses the database.
• Applications come, applications go. The data, however, lives forever. In the long term, the goal is not about building applications, it is really about using the data underneath these applications.
• A development team needs at its heart a core of database savvy developers who are responsible for ensuring the database logic is sound and the system is built to perform from day one. Tuning after the fact (tuning after development) typically means you did not give serious thought to these concerns during development
1.3 Database design – Indexes, Referential Integrity and Statistics
1.3.1 Enforce your business model at the database level
Your business model should be implemented and enforced at the database level. The initial application analysis will identify the logical data objects and their relationship to each other, even before you choose your database platform. Not every application will require a data model in Third Normal Form, however when designing your physical data model from your logical data model, the logical relationships should be enforced in the physical model using referential integrity constraints – Primary keys and foreign keys.
1.3.1.1 Business logic
Business rules = data rules. For example, “you can only be an employee if you are in a department” should be instantiated as a mandatory foreign key on an employee table to the primary key on a department table. Not only does this enforce the integrity of your data, it will have a knock on effect to the performance of your queries by giving the database optimiser the right information to determine the most effective query paths.
1.3.1.2 Application logic
Application specific logic not enforced in the database.
1.3.2 Indexing and index types
Indexes enable faster searching and (depending on design) faster updates. Be aware that differing database structures will require differing index strategies, and these decisions should be made in the initial design phase.
You will also need to be aware of the differing types of index that are available to you, and when it would be appropriate (and just as importantly, when it would not be appropriate) to use them.
1.3.3 Statistics and their place in query optimisation
Indexes, primary and foreign keys, domain and unique constraints do more than enforce the relationship of data objects to each other. The database kernel needs information to optimise execution paths of your queries, procedures, functions and packages, as well as efficiently allocate database and system resources. Usually, this information is in the form of database statistics, held within the databases’ data dictionary. Statistics may have to be manually gathered, or you may be able to generate them automatically. Your DBA will advise you here, however it is important to understand that unless your database has up-to date and complete statistics for each object in your database, the optimiser will be unable to calculate the most effective execution plan for your application queries.
1.3.4 Explain plans and benchmarking
Explain plans are reports on how your query is executed. They will contain information regarding what indexes were used, what tables were scanned, how many blocks and rows were accessed to return your result sets, and an indication of what effort your query cost the database in terms of CPU seconds, IO operations and waits. They are a very effective tool to tune your applications, so take the time to learn how to generate and read explain plans on your database platform.
1.4 Writing SQL Statements
At its most basic level, interaction with a database is easy. To retrieve information, READ or SELECT. To CREATE, CREATE or INSERT. To change, UPDATE – and to remove, DELETE. Create Read Update and Delete. Point these statements at database tables or views; use generic code generators application development tools and the data manipulation procedures are done. If we’re not sure what tables we want to access, or what fields we want until we get some input from the end user, it’s very simple to build the query into a string and then execute the string through a JDBC (or equivalent) call.
Unfortunately, that design approach is fundamentally flawed on a number of levels, the most important being security, scalability and performance.
For every database application the mechanics of executing a query need to be understood, even if you are using a generic code generator - because the SQL code that is generated from these tools will probably not be optimised at best and can potentially be dangerously inefficient at worst. You need to be able to SEE the generated code and then decide if it’s appropriate. Code generators should not be used as a screen to hide SQL ignorance behind. Bad SQL is just that, regardless of who or what generates it, and it’s up to you to ensure that every piece of code you write or are responsible for is fit for purpose, and that goes way beyond just the immediate CREATE/INSERT, READ/SELECT, UPDATE or DELETE operation you want to perform.
1.5 Bind Variables for efficiency and security
1.5.1 Query parsing, binding and execution - overview
After syntax checking, the query is parsed into memory and the database looks to see if the query already exists in memory. If it doesn’t, it has to be security checked, optimised, an execution plan for the query has to be generated in memory, then the query parameters are bound and the query is executed. This is a hard parse, and it’s a lot of work, it consumes a lot of resources - and more importantly locks objects to prevent database corruption while this work is being done, preventing other session accessing these objects. Database instance memory and resources are finite, and we do not want to find ourselves in a situation when we run out of memory, or locking resources because of badly designed SQL. And be aware that it can take only one badly written SQL statement to cause a problem – regardless of the fact that YOUR queries are 100% optimised and well written.
As an application developer, you do not want to find yourself in a situation that every query you issue from your application has to be hard parsed. Parsing is an expensive operation for the database and is a performance and scalability killer. What you want to see is your application using the existing execution paths and query plans already in the database memory as much as possible, i.e. soft parsing. So you need to design your SQL code to be as universal as possible, and that means binding your variables to your SQL Statements at execution time, regardless of where the query originates from. The way this is done will vary from database to database, and application to application, but the one constant across the varying technology is that refusing to use bind variables will potentially not only open your application up to quite severe security risks, it will also be a prime cause of your application not being able to scale or perform under load.
Application developers and Project Managers should make sure that the technology being used to interact with any RDBMS system supports the ability to issue queries with bind variables. Unbound queries can destroy the ability of your application to scale and run efficiently. And even though this should be obvious, it bears repeating - string substitution in an SQL query is not binding variables.
1.5.2 Bind variable example
For example, to retrieve the details of employee # 123 from the employee table, you could
Select * from emp where empno = 123;
Alternatively, you could
Select * from emp where empno = :empno
With a variation of “using (123)” to bind the variable.
The first query will have to be hard parsed every time you issue the query with a different employee number – and if this query is going to be executed many times with different parameters, you will flood your database’s memory area with multiple copies of the same statement. Once this occurs, then every new query will have to be hard parsed, as well as possibly (depending on the database platform) the unwelcome side affect of causing your database to start page swapping into your virtual memory with all the unwelcome IO operations that entails.
However, the second query will only have to be checked, optimised and parsed once and once only.
1.5.3 Bind Variables prevent SQL Injection
You have a procedure with two parameters you wish to call from your Java or .Net application.
You set up the statement like this –
Stmt = “begin p(‘” & input1 & “’, ‘” & input2 & “’ ); end;”
Your user types in
Input1 = x’. ‘y’); execute immediate ‘drop table all_salaries’; end; --
Input2 =
Your application then runs stmt on your database, which is this –
Begin p(‘x’, ‘y’ ); execute immediate ‘drop table all_salaries’; end; --
Now you have to explain to your line manager, director and reporting partner why the salary table has been dropped, and unless you have auditing functions turned on, this will be very hard to trace.
As an exercise, I suggest you Google “sql injection”. The importance of this cannot be overstated.
Programmatic constructs like this
Stmt = “begin p(‘” & input1 & “’, ‘” & input2 & “’ ); end;”
and their variations are not binding variables. Use your technologies variation of the Java PreparedStatement.
PreparedStatement pstat =
Conn.prepareStatement
(“begin p(?, ?); end;”);
Pstat.setString(1, );
Pstat.setString(2, );
Bind variables are not just about performance; they are also about security. This is particularly important if you are using a code generator, or dealing with third party software.
1.6 Query hints
Some database platforms support the use of “hints”. These are directives to the optimiser to change the execution plan of the query.
Unless you are intimately acquainted with the underlying architecture of your database platforms optimiser, and you have extensively benchmarked your changes, and you have discrete, quantifiable and definite performance reasons to use hints, and you fully understand the internal mechanics of what the hint will do to your execution paths under load, leave them well alone. Under almost all circumstances, the database optimiser will have come up with the optimal query execution plan for your given query.
This of course presumes that your database design and indexing strategy is valid. A hint is not a quick fix for poor database design.
1.7 Transaction Design
Transactional design will have implications on how your application will scale and perform. Issues and design questions you will have to answer include
• How do we prevent “lost updates”?
• How do we guarantee transactional integrity, and the underlying integrity of our data?
• How do we guarantee a consistent view of data to the end users of our applications?
• What locking model and isolation levels are available in the particular database we are using – and they are different between different databases – and how will this affect our design? and
• How to design appropriate methods and rules to cater for this?
The most efficient way is to package your transactions into stored procedures in SQL Server and packages in Oracle, and use static SQL as much as possible. Certainly within an Oracle instance, the first time a package is called in your session, it is pinned into the shared pool, and the static SQL statements are parsed and the explain plans written, so when the packages are called again, they just execute – no need for a hard or soft parse. This is one of the most pressing arguments for the usage of properly designed stored procedures and static SQL – “parse once, execute many”. A good philosophy to adopt when designing database applications and the associated SQL transactions is -
• Do it in one SQL statement if you possibly can; think in terms of set processing, not row-by-row - especially for bulk processes.
• If you can’t do it in one SQL statement, then a little database native procedural code (ie. PL/SQL on Oracle, or T-SQL in SQL Server) wrapped around well designed SQL statements will almost certainly solve most problems – but remember to think in terms of processing “sets” of data. Keep your procedural programs small and explicit, and designed to solve one specific problem.
• If for whatever reason, native procedural code (and this includes native Java libraries in both Oracle and SQL Server) won’t give you what you need – and this should RARELY be necessary – then an external call to a Java library or C/C++/C#/Assembler dll could be justified.
• If that won’t do it, you probably shouldn’t be trying it in the first place, or your design is fundamentally flawed
• AVOID dynamic SQL unless you have absolutely no other choice; dynamic SQL is very easy to code, but it can be complicated to code properly and if you don’t code it properly, you run the risk of flooding the instance memory area with queries that have to be hard parsed every time and that can bring a database to its knees.
Further, using database packages and procedures, you can make use of the appropriate methods to lock you records for INSERTS and UPDATES depending on your database, and elegantly handle any waits or exceptions you may encounter.
AUTOCOMMIT should always be turned off to preserve transactional integrity. Let’s say you have a process that updates records in five tables, and because of the design of the data, you need to perform this process in three separate SQL Statements. The first statement executes without error, as does the second. The third fails for some reason (maybe it violates a unique constraint) – so the entire transaction should be rolled back, but if the separate prior statements have been AUTOCOMMITed, you have a problem. As an application designer, it is up to you to decide the success or failure of a transaction and when to explicitly commit or rollback.
1.8 Database locking
Pragmatically, you also need to have a very good idea of the data usage your application will need so you can decide what approach to take - what’s not so important in a reporting database with few users becomes critical in a large OLTP system with a high number of concurrent users. But regardless of whether or not record locking will be an issue for your application, you must know how a particular database locks, what the implications are and how best to design your transactions. This is not really something that can be done generically (and it really shouldn’t be handled at the application level, either) – every database is different in the way it handles locks and latches, and no one approach will work effectively across all database platforms.
1.9 Database native SQL extensions
DB2, SQLServer and Oracle all have a rich set of SQL extensions that go far beyond ANSI standard SQL, and as a good developer you should know what they are because if you do, it will make you job much, much easier. You should also have a good working knowledge how your queries will be parsed and the mechanisms your database uses to optimise your queries, so you can write and tune efficient SQL. Remember, you should be designing your applications for scalability and performance from the beginning of your project lifecycle.
1.10 Dynamic SQL
Properly designed dynamic SQL can be as efficient as static SQL if it’s written properly, but it is very easy to write bad dynamic SQL. The various implementations of dynamic SQL will vary widely across database platforms, so if you do have to use dynamic SQL make sure you know what’s available, how and why it works as it does and what the implications are - because one method may be appropriate for one set of circumstances, another method may be appropriate for another.
For example, in Oracle databases, version 8.1.6 and above, there are two methods for generating dynamic SQL - Native Dynamic SQL and the DBMS_SQL API package which was introduced in version 7.1. The differences between these two methods are quite pronounced, even though they both provide a similar service, so it would be important to choose the correct method depending on what type of transaction you wanted to perform.
1.11 Dynamic SQL guidelines with Java and .Net
If you absolutely MUST generate a dynamic SQL statement from within your Java classes or .Net applications ALWAYS use a method that allows you to bind variables (eg. for Java, use PreparedStatement not Statement. PreparedStatement supports the use of binds. Statement does not. Refer back to section 1.4.2).
A better approach would be to make a call to a properly designed database package that will build the (optimised) dynamic statement for you, and return a resultset or value set that you can then manipulate as you see fit.
Make sure your web services/Java Beans/ORBs/J2EE/.Net apps are binding; this is particularly important with 3rd party software you didn’t develop, or when using a design tool that generates the SQL for you. At the very least, you must have the ability to examine the actual SQL that has been generated. Once again, if the tools you have been provided with do not support binding, or the ability to call a database package, flag this as a risk to your Project Manager.
1.12 Achieving true Database independence.
Enterprise standard databases exist for one purpose and one purpose only. To store, validate, secure and manipulate data in the most efficient way possible. No matter how sophisticated a middle layer technology is, it WILL NOT match the native performance and scalability of the underlying database to securely and consistently process data related transactions – so long as the underlying processes have been properly designed. Anybody who says differently is trying to sell you something. After the years and billions of dollars of R&D IBM, Microsoft, SAP and Oracle have put into their database products, no third party middle tier layer can match the performance, security, ability to scale, ability to manipulate data of any of these Enterprise standard RDBMS systems.
The trade off for this brilliant functionality and performance is that you, the developer and your development team, have the responsibility to know and understand what tools are available to you across all aspects of your project, but particularly so within your database. Have a serious think in the design and architecture phase about what you want your application database to do, and how best to interact with it. It is the Project Managers responsibility to ensure that the team has a Database Administrator available as well as a Data Architect to assist with the choices of technology available from the database.
Your database structures should be implemented from a properly designed data models, with the appropriate referential integrity, primary and unique key constraints and the proper index strategies in place. It’s important to remember this – applications and technology come and go. C# / .Net / J2EE / CORBA / RMI / WebServices / Applet / Servlet / Client-Server applications may not be around in five years, as new technologies emerge. Your data, however, has a much longer lifecycle and is far more valuable. Your database may be only supporting one application initially, but data is an extremely valuable asset; other applications may want to utilise your data and the variety of platforms and technologies vary enormously.

Instead of building and executing SELECT, UPDATE, DELETE or INSERT statements in your application – perhaps even “invisible” statements generated by a generic code generator - you should be making calls to publicly visible transactional components on the database – like
• FIRE_EMPLOYEE,
• HIRE_EMPLOYEE,
• GET_EMPLOYEE_DETAILS,
• GIVE_ME_A_PROMOTION.
These can be package, procedure or function calls, calls to updateable views or TABLE calls to pipelined functions. What you should be trying to achieve are transaction APIs. Note – this does not mean Table APIs.
The advantage of this is that your transactions can be re-used by pretty much any other application, and you have a much tighter ability to secure access to your data. Your selects, inserts, updates and deletes have been optimised for your data structures and RDBMS product and version, you know that the queries have been optimised against your particular schema (and be aware that query design and optimisation will probably change during your load testing phase), if appropriate the correct record locking design has been incorporated and you know your application will scale securely.
It also simplifies maintenance. If you have a package/stored procedure API where you discover a bug in the SQL, it is very easy to extract the SQL, apply the bug fix and tune the SQL, zap the stored procedure and the change is immediately and automatically propagated to every application system that uses the stored procedure.
If however, the SQL is propagated in a number of Java classes or .Net objects, the problem is exacerbated by the necessity of having to locate each instance of the object and apply the fix.
In fact, you should give very serious thought to not allowing your applications access to the underlying tables at all – the application interacts with the data only through carefully designed packages, procedures, views and functions and these database objects are the only objects the application can see.
1.13 In Summary
1. Data manipulation should be done in the database, so
a. Encapsulate your logic in a stored procedure or package
b. Do it in a single SQL statement if at all possible
c. If you can’t, use as little procedural code as possible
d. If that doesn’t work, (unlikely in about 99% of cases), use an external procedure call. But unless your application is cutting – or bleeding – edge, this is potentially an alarm bell that either your design is flawed or you’re trying to do something you shouldn’t.
2. Test and benchmark EVERYTHING; there’s always a better and simpler way of doing something – actively seek it out and make your code smaller, tighter and faster.
3. Be aware of the new features on new database releases, these can make your life a lot easier.
4. Business logic should be enforced in the database. Application logic should be enforced in the application.
5. Code generators can be problematic, as generic SQL statements generated will rarely be optimised for the database platform you are developing for. If your code generator doesn’t support binding variables, and you don’t have the ability to see and tune what SQL the code generator is creating, then you should give very serious thought to replacing it. Unbound code presents a severe security risk because of SQL Injection, and your Project Manager should be made aware of that. If you don’t, and your application doesn’t perform on deployment to production, you’ll be running the risk of the production DBAs telling you there’s nothing wrong with the database instance and configuration, and there is nothing they can do to help you.
6. When you’re manipulating data, think in sets, particularly during data loads. Row-by-row processing is usually inefficient and unnecessary, except in OLAP transaction systems, and even then set based updates should be applied if possible. Looping selects and updates over a network can result in severe performance degradation. Do it all in a single statement if you can.
7. NEVER, EVER issue a COMMIT in a FOR loop. There may be circumstances when you do need to use a FOR loop and bulk processing on an update, insertion or deletion, but COMMITs in any kind of FOR loop are dangerous because they break your transactional integrity. COMMITs in for loops can also be very inefficient and generate a considerable amount more redo than single transactions. One senior DBA puts it very succinctly-
“Using any form of autocommit is one of the safest routes to an unscalable and error prone application. First of all, you shouldn’t split up a logical transaction into many physical transactions. If any of these physical transactions fail, in what hybrid state is your database? How do you restart the process?...There are many ways to stay out of hell. Doing things properly is one. Not committing inside a loop is another. Start doing things properly and stop hacking”
8. XML – if there are alternatives to processing XML, try these methods first. Large XML documents or volumes are processor intensive and can be very slow to process. Smaller documents shouldn’t prove problematic, though, unless the transaction volumes are high.
9. Design your resultsets to return just the information you want over the network. It’s a waste of resources to SELECT * FROM [table] when all you want is one or two fields.
10. Dynamic SQL can be a trap. Either learn to do it very well, or leave it well alone.
11. As a general rule, BIND EVERY VARIABLE. Yes there are exceptions, but your database specific DBA will advise you of these and when they are appropriate.
12. Know your database – what features it has, how it locks, how it handles transactions. Every one will be different.
13. Try as far as possible to encapsulate all known transactions within your application into database stored procedures. By creating a set of database transaction API’s, you will simplify access to your system, as well as enhance performance, scalability and security.
14. Talk to Technical Data Architects and other experienced developers in other disciplines; they may be able to help you solve problems in your own discipline.