Skip to main content

How to speed up SQL execution?

The most expensive step in the SQL preparation process is the generation of the execution plan, particularly when dealing with a query with multiple joins. When RDBMs evaluates table join orders, it must consider every possible combination of tables. For example, a six-way table join has 720 (permutations of 6, or 6 * 5 * 4 * 3 * 2 * 1 = 720) possible ways that the tables can be joined together.

In Oracle, optimizer_search_limit and optimizer_max_permutations parameters work together to place an upper limit on the number of permutations the optimizer will consider. But setting these values will not guarantee the best results.

Hints for Your SQL Statement
Hints are instructions that you include in your SQL statement for the optimizer. Using hints, you can specify join orders, types of access path, indexes to be used, and the intended optimization goals. You must place the hints within /*+ <hint> */, and you should place them after the SELECT key word.

The following statement returns the rows as soon as it finds a few:

SELECT /*+ FIRST_ROWS */ distinct customer_name
FROM customer

Where as the following query waits until all the rows are retrieved and sorted before returning them to the client:

SELECT /*+ ALL_ROWS */ distinct customer_name
FROM customer ORDER BY customer_name

Index Hint
Indexes play a very important role in SQL tuning. Indexes allow the table data to be indexed and organized, which in turn enables faster retrieval. Merely creating an index does not speed up the query execution. You must make sure that the query's execution plan uses the hinted index. In the following query, when the optimizer uses the index hint, it will be forced to use the specified index for the search on last_name:

SELECT /*+ index(last_name_indx) */
distinct author_names

FROM authors

WHERE  last_name ='SAN%'

When you analyze the execution plan on this query, you will see the optimizer using this index. You can also instruct the optimizer to choose between a subset of indexes using /*+ index( indx1, indx2) */.

http://psoug.org/reference/hints.html

Comments

Popular posts from this blog

SAAS Simple Maturity Model

There are two architectural models – commonly referred as SAAS Maturity models- that describe the transition of a service to what is called Multi-tenant efficient, highly scalable application. The SAAS Maturity model described by Microsoft has four distinct stages and is illustrated below. Another similar well-known model for SaaS-maturity is known as Forrester-model but adds another stage known as "Dynamic Business Apps-as-a-service". The three key Attributes of a SAAS Architecture: Configurability: Metadata used to configure the way the application behaves for customers Multi-tenant Efficiency : Maximizing the sharing of resources across tenants Scalability: Maximizing concurrency, resource efficiency SAAS Simple Maturity Model (Microsoft, 2006) SaaS Maturity Model (Forres...

CXF Example –Web Service Using Spring and Maven

Apache CXF is an open source services framework. CXF helps you build Web Services using frontend programming APIs, like JAX-WS and JAX-RS. These services can speak a variety of protocols such as SOAP, XML/HTTP, RESTful HTTP, or CORBA and work over a variety of transports such as HTTP, JMS or JBI. Support for bottom up approach and top down approach. Support for Standards JAX-WS, JSR-181, SAAJ, JAX-RS SOAP 1.1, 1.2, WS-I BasicProfile, WS-Security, WS-Addressing, WS-RM, WS-Policy WSDL 1.1 MTOM Building Web Services – Example 1 Develop a simple Web Service using CXF framework. The example in this case is an InterestRate Service. Tools / technologies Version CXF 2.1 Maven 2.0 Tomcat apache-tomcat-6.0.24 JDK java version 1.6.0_20       The Application Scope The "Interest Rate Service" application demonstrates how easily you can develop a Web Service using CXF frame work. The WSDL service definition defines three operations. Below is the InterestRateService interface.   @WebS...

CXF – Webservice Example – Contract first

We will use the same WSDL document that we used in our earlier example. The contract is given below: Step: 1 Run the wsdl2java tool to generate the requires service class, JAXB input output message classes. wsdl2java.bat -d src -ant -impl -server rateService.wsdl This command will generate: JAXB Input and Output message classes, Service Interface, Service Implementation class, Standalone Server class and an ant build file to build the service. If you open up these files in the editor, then you see various JAXB annotations on classes and methods which are used to map a Java class to XML. It will also generate Request and Response Wrapper classes as with the JAX-WS specification. It also generate Request and Response Wrapper classes as with the JAX-WS specification ...