How to Enable/Disable Indexes?
Disable Index
ALTER INDEX [IndexName] ON TableName DISABLE GO
Enable Index
ALTER INDEX [IndexName] ON TableName REBUILD
What is max number of columns in index?
32
How many maximum columns in partitions can be used?
16
What are Synonyms?
Synonyms give you the ability to provide alternate names for database objects. You can alias object names; for example, using the Employee table as Emp. You can also shorten names. This is especially useful when dealing with three and four part names; for example, shortening server.database.owner.object to object.
What is the Difference between Update Lock and Exclusive Lock?
When Exclusive Lock is on any process, no other lock can be placed on that row or table. Every other process have to wait till Exclusive Lock completes its tasks.
Update Lock is a type of Exclusive Lock, except that it can be placed on the row which already has Shared Lock on it. Update Lock reads the data of the row which has the Shared Lock as soon as the Update Lock is ready to change the data it converts itself to the Exclusive Lock.
What is a Surrogate Key?
A surrogate key is a substitution for the natural primary key. It is just a unique identifier or number for each row that can be used for the primary key to the table. The only requirement for a surrogate primary key is that it should be unique for each row in the table. It is useful because the natural primary key can change and this makes updates more difficult. Surrogated keys are always integer or numeric.
Design the Database process which can handle 1 million requests at same time and What should be DB consideration?
All the major databases and operating systems will work in 1 million requests at same time; provided sufficient hardware with sufficient bandwidth to memory, disk, and network are provided.
All databases are built for this kind of scenario -- that is, where you need to update lots of rows from different clients all the time. This is what indexes and primary keys are used for, and the database is optimized for updating in this manner. (i.e., UPDATE your_table SET your_column=your_value where your_key=12)
The hardware requirements are going to be your biggest issue, and I suspect you will need to think about quite a lot here, including:
- Failover (what happens when your main server fails?)
- Clustering (You may simply need to have more than one database server to handle your load)
- Processors (2? 4? 8? 16? Multi-core? Does the db provide multi-core support that is well optimized?)
- Memory (The faster the better, but your chipset must be able to handle the bandwidth as well)
- Disk (Faster I/O the better. eSATA/SATA or Fiber, etc.)
- Network (You'll need lots of bandwidth to handle this kind of traffic)
- Virtualization (Does it make sense to build this as real hardware? Why not as virtual servers in the cloud? Amazon / Rackspace / etc.?)
Thankfully a good majority of the scaling issues are handled either at the hardware or db level. That is, you can start your database now on a slow server and as traffic increases you can adjust accordingly with minimal changes to your code or your db structure. What will change is your hardware and your db configuration. Most database servers will support failover/clustering with little to no change to your existing code or structures. (But be sure to research possible costs and the efficiency thereof first)
Database Query Optimisation tips with JDBC:
Use prepared statement pooling
Database supports prepared statement pooling for pooled connections, as discussed in the JDBC 3.0 specification, through the TimesTenObservableConnectionDS class. Note that statement pooling is transparent to an application. Use of the PreparedStatement object, including preparing and closing the statement, is no different.
Enable prepared statement pooling and specify the maximum number of statements in the pool by calling the ObservableConnectionDS methodsetMaxStatements(). A value of 0, the default, disables prepared statement pooling. Any integer value greater than 0 enables prepared statement pooling with the value taken as the maximum number of statements. Once set, this value should not be changed.
Prepared statements or callable statements will be pooled at the time of creation if the pool has not reached its capacity. In Java 6, you can remove a prepared statement or callable statement from the pool by calling setPoolable(false) on the statement object. After the statement is closed, it will be removed from the pool.
Important:
With prepared statement pooling, JDBC considers two statements to be identical if their SQL (including comments) is identical, regardless of other considerations such as optimizer settings. Do not use prepared statement pooling in a scenario where different optimizer hints may be applied to statements that are otherwise identical. In this scenario, a statement execution may result in the use of an identical statement from the pool with an unanticipated optimizer setting.
Use arrays of parameters for batch execution
You can improve performance by using groups, referred to as batches, of statement executions, calling the addBatch() and executeBatch() methods forStatement or PreparedStatement objects.
A batch can consist of a set of INSERT, UPDATE, DELETE, or MERGE statements. Statements that return result sets, such as SELECT statements, are not allowed in a batch. A SQL statement is added to a batch by calling addBatch() on the statement object. The set of SQL statements associated with a batch are executed through the executeBatch() method.
For PreparedStatement objects, a batch consists of repeated executions of a statement using different input parameter values. For each set of input values, create the batch by using appropriate setXXX() calls followed by the addBatch() call. The batch is executed by the executeBatch() method.
Oracle recommendation the following batch sizes for Release 11.2.1:
- 256 for INSERT statements
- 31 for UPDATE statements
- 31 for DELETE statements
- 31 for MERGE statements
Example 5-1 Batching statements
// turn off autocommit
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
stmt.addBatch("INSERT INTO employees VALUES (1000, 'Joe Jones')");
stmt.addBatch("INSERT INTO departments VALUES (260, 'Shoe')");
stmt.addBatch("INSERT INTO emp_dept VALUES (1000, 260)");
// submit a batch of update commands for execution
int[] updateCounts = stmt.executeBatch();
conn.commit ();
Example 5-2 Batching prepared statements
// turn off autocommit
conn.setAutoCommit(false);
// prepare the statement
PreparedStatement stmt = conn.prepareStatement
("INSERT INTO employees VALUES (?, ?)");
// first set of parameters
stmt.setInt(1, 2000);
stmt.setString(2, "Kelly Kaufmann");
stmt.addBatch();
// second set of parameters
stmt.setInt(1, 3000);
stmt.setString(2, "Bill Barnes");
stmt.addBatch();
// submit the batch for execution. Check update counts
int[] updateCounts = stmt.executeBatch();
conn.commit ();
For either a Statement or PreparedStatement object, the executeBatch() method returns an array of update counts (updateCounts[] in Example 5-1 andExample 5-2 above), with one element in the array for each statement execution. The value of each element can be any of the following:
- A number indicating how many rows in the database were affected by the corresponding statement execution.
- SUCCESS_NO_INFO, indicating the corresponding statement execution was successful, but the number of affected rows is unknown.
- EXECUTE_FAILED, indicating the corresponding statement execution failed.
Once there is a statement execution with EXECUTE_FAILED status, no further statement executions will be attempted.
For more information about using the JDBC batch update facility, refer to the Javadoc for the Statement interface, particularly information about theexecuteBatch() method, at the following location: http://download.oracle.com/javase/1.5.0/docs/api/
Bulk fetch rows of data
Oracle provides an extension that allows an application to fetch multiple rows of data. For applications that retrieve large amounts of data, fetching multiple rows can increase performance greatly. However, when using Read Committed isolation level, locks are held on all rows being retrieved until the application has received all the data, decreasing concurrency. For more information on this feature, see "Fetching multiple rows of data".
Use the ResultSet method getString() sparingly
Because Java strings are immutable, the ResultSet method getString() must allocate space for a new string in addition to translating the underlying C string to a Unicode string, making it a costly call.
In addition, you should not call getString() on primitive numeric types, like byte or int, unless it is absolutely necessary. It is much faster to call getInt()on an integer column, for example.
Avoid data type conversions
TimesTen instruction paths are so short that even small delays due to data conversion can cause a relatively large percentage increase in transaction time.
Use the appropriate getXXX() method on a ResultSet object for the data type of the data in the underlying database. For example, if the data type of the data is DOUBLE, to avoid data conversion in the JDBC driver you should call getDouble(). Similarly, use the appropriate setXXX() method on thePreparedStatement object for the input parameter in an SQL statement. For example, if you are inserting data into a CHAR column using aPreparedStatement, you should use setString().
Tuning JMS/XLA applications with Database
In the JMS model an object is instantiated and each record is presented one at a time in a callback to the MessageListener method onMessage(). High performance applications can use some tuning to overcome some of this overhead.
Reduce frequency of calls to Client Acknowledge
Calls to Client Acknowledgement move the bookmark and involve updates to system tables, so one way to increase throughput is to wait until several transactions have been detected before issuing the call. The reader application must have some tolerance for seeing the same set of records more than once. Moving the bookmark can be done manually using the Session object CLIENT_ACKNOWLEDGE mode when instantiating a session:
Session session = connection.createSession (false, Session.CLIENT_ACKNOWLEDGE);
For many applications, setting this value to 100 is a reasonable choice.
Handling high event rates
The synchronous interface is suitable only for applications with low event rates and for which AUTO_ACKNOWLEDGE or DUPS_OK_ACKNOWLEDGE acknowledgment modes are acceptable. Applications that require CLIENT_ACKNOWLEDGE acknowledgment mode and applications with high event rates should use the asynchronous interface for receiving updates. They should acknowledge the messages on the callback thread itself if they are using CLIENT_ACKNOWLEDGEMENTas acknowledgment mode.
