Browse Prior Art Database

Method and System for Optimizing Query Performance while Utilizing an ETL Tool

IP.com Disclosure Number: IPCOM000234739D
Publication Date: 2014-Jan-31
Document File: 4 page(s) / 73K

Publishing Venue

The IP.com Prior Art Database

Abstract

A method and system is disclosed for providing an improvement to an Extract, Transform and Load (ETL) computer program product by integrating index analysis with source database managers from where data is being extracted. The method and system is helpful in recommending/ building additional indices for improving performance and reducing system workload in an efficient manner.

This text was extracted from a PDF file.
This is the abbreviated version, containing approximately 52% of the total text.

Page 01 of 4

Method and System for Optimizing Query Performance while Utilizing an ETL Tool

Usually, query performance degrades over time as the more data gets accumulated in a database. Extract, Transform and Load (ETL) applications run the same query in batch cycles frequently against large databases. As the data mix changes along with the volumes, tuning of the database is needed. Tuning requires changes to the underlying indices. Currently, a Database Administrator (DBA) must manually run an index analysis for any query that needs to be tuned and in most cases, the tuning does not take place.

The method and system disclosed herein provides an improvement to an ETL computer program product by integrating index analysis with source database managers from where data is being extracted.

Fig. 1-4 illustrates an exemplary implementation of the method and system disclosed herein.

Fig. 1

1


Page 02 of 4

Fig. 2

2


Page 03 of 4

Fig. 3

Fig. 4

As illustrated, an option to run index analysis is added to the object that is used to run a

3


Page 04 of 4

query. Additionally, an option is provided to modify index or send a warning message. The run option allows temporal values such as, but not limited to 1st Monday or 4th day of every month. In case the option to modify the index is chosen, the index analysis is run prior to the data extraction option. In case the option of a warning message is chosen, the disclosed method and system performs both the options in parallel.

Moving on, the method and system passes the query to the index analyzer if analysis is requested by the user. The query is passed to the index analyzer in case it fits in the temporal range. In case the analysis recommends no changes, then the process continues as it would with the current technology. Further,...