Monday, 31 March 2008

INDEXES IN SQL Neeraj Nathani Smart Bridge Trading Solutions

INDEXES IN SQL

Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Simply put, an index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book.

For example, if you want to reference all pages in a book that discuss a certain topic, you first refer to the index, which lists all topics alphabetically, and are then referred to one or more specific page numbers.

An index helps speed up SELECT queries and WHERE clauses, but it slows down data input, with UPDATE and INSERT statements. Indexes can be created or dropped with no effect on the data.

Creating an index involves the CREATE INDEX statement, which allows you to name the index, to specify the table and which column or columns to index, and to indicate whether the index is in ascending or descending order.

Indexes can also be unique, similar to the UNIQUE constraint, in that the index prevents duplicate entries in the column or combination of columns on which there's an index.

The CREATE INDEX Command:

The basic syntax of CREATE INDEX is as follows:

CREATE INDEX index_name ON table_name;

Single-Column Indexes:

A single-column index is one that is created based on only one table column. The basic syntax is as follows:

CREATE INDEX index_name

ON table_name (column_name);

Unique Indexes:

Unique indexes are used not only for performance, but also for data integrity. A unique index does not allow any duplicate values to be inserted into the table. The basic syntax is as follows:

CREATE INDEX index_name

on table_name (column_name);

Composite Indexes:

A composite index is an index on two or more columns of a table. The basic syntax is as follows:

CREATE INDEX index_name

on table_name (column1, column2);

Whether to create a single-column index or a composite index, take into consideration the column(s) that you may use very frequently in a query's WHERE clause as filter conditions.

Should there be only one column used, a single-column index should be the choice. Should there be two or more columns that are frequently used in the WHERE clause as filters, the composite index would be the best choice.

Implicit Indexes:

Implicit indexes are indexes that are automatically created by the database server when an object is created. Indexes are automatically created for primary key constraints and unique constraints.

The DROP INDEX Command:

An index can be dropped using SQL DROP command. Care should be taken when dropping an index because performance may be slowed or improved.

The basic syntax is as follows:

DROP INDEX index_name;

You can check INDEX Constraint chapter to see actual examples on Indexes.

When should indexes be avoided?

Although indexes are intended to enhance a database's performance, there are times when they should be avoided. The following guidelines indicate when the use of an index should be reconsidered:

Indexes should not be used on small tables.
Tables that have frequent, large batch update or insert operations.
Indexes should not be used on columns that contain a high number of NULL values.
Columns that are frequently manipulated should not be indexed.

How are Indexes used?

Indexes have three main uses:

To quickly find specific rows by avoiding a Full Table Scan

We've already seen above how a Unique Scan works. Using the phone book metaphor, it's not hard to understand how a Range Scan works in much the same way to find all people named "Gallileo", or all of the names alphabetically between "Smith" and "Smythe". Range Scans can occur when we use >, <, LIKE, or BETWEEN in a WHERE clause. A range scan will find the first row in the range using the same technique as the Unique Scan, but will then keep reading the index up to the end of the range. It is OK if the range covers many blocks.

To avoid a table access altogether

If all we wanted to do when looking up Gallileo in the phone book was to find his address or phone number, the job would be done. However if we wanted to know his date of birth, we'd have to phone and ask. This takes time. If it was something that we needed all the time, like an email address, we could save time by adding it to the phone book.

Oracle does the same thing. If the information is in the index, then it doesn't bother to read the table. It is a reasonably common technique to add columns to an index, not because they will be used as part of the index scan, but because they save a table access. In fact, Oracle may even perform a Fast Full Scan of an index that it cannot use in a Range or Unique scan just to avoid a table access.

To avoid a sort

This one is not so well known, largely because it is so poorly documented (and in many cases, unpredicatably implemented by the Optimizer as well). Oracle performs a sort for many reasons: ORDER BY, GROUP BY, DISTINCT, Set operations (eg. UNION), Sort-Merge Joins, uncorrelated IN-subqueries, Analytic Functions). If a sort operation requires rows in the same order as the index, then Oracle may read the table rows via the index. A sort operation is not necessary since the rows are returned in sorted order.

Partitioned Tables And Indexes

Maintenance of large tables and indexes can become very time and resource consuming. At the same time, data access performance can reduce drastically for these objects. Partitioning of tables and indexes can benefit the performance and maintenance in several ways.

Partition independance means backup and recovery operations can be performed on individual partitions, whilst leaving the other partitons available.
Query performance can be improved as access can be limited to relevant partitons only.
There is a greater ability for parallelism with more partitions.

All the examples shown here use the users tablespace for all partitions. In a real situation it is likely that these partitions would be assigned to different tablespaces to reduce device contention.

Saturday, 29 March 2008

Neeraj Nathani Smart Bridge Trading Solutions PERFORMANCE BOOSTERS FOR ETL PROGRAMS:

PERFORMANCE BOOSTERS FOR ETL PROGRAMS:

Challenges

ETL processes can involve considerable complexity, and significant operational problems can occur with improperly designed ETL systems.

The range of data values or data quality in an operational system may exceed the expectations of designers at the time validation and transformation rules are specified. Data profiling of a source during data analysis can identify the data conditions that will need to be managed by transform rules specifications. This will lead to an amendment of validation rules explicitly and implicitly implemented in the ETL process.

Data warehouses are typically assembled from a variety of data sources with different formats and purposes. As such, ETL is a key process to bring all the data together in a standard, homogeneous environment.

Design analysts should establish the scalability of an ETL system across the lifetime of its usage. This includes understanding the volumes of data that will have to be processed within service level agreements. The time available to extract from source systems may change, which may mean the same amount of data may have to be processed in less time. Some ETL systems have to scale to process terabytes of data to update data warehouses with tens of terabytes of data. Increasing volumes of data may require designs that can scale from daily batch to multiple-day microbatch to integration with message queues or real-time change-data capture for continuous transformation and update

Performance

ETL vendors benchmark their record-systems at multiple TB (terabytes) per hour (or ~1 GB per second) using powerful servers with multiple CPUs, multiple hard drives, multiple gigabit-network connections, and lots of memory. The fastest ETL record is currently held by Syncsort,^[1] Vertica and HP at 5.4TB in under an hour which is more than twice as fast as the earlier record held by Microsoft and Unisys.

In real life, the slowest part of an ETL process usually occurs in the database load phase. Databases may perform slowly because they have to take care of concurrency, integrity maintenance, and indices. Thus, for better performance, it may make sense to employ:

Direct Path Extract method or bulk unload whenever is possible (instead of querying the database) to reduce the load on source system while getting high speed extract
most of the transformation processing outside of the database
bulk load operations whenever possible.

Still, even using bulk operations, database access is usually the bottleneck in the ETL process. Some common methods used to increase performance are:

Partition tables (and indices). Try to keep partitions similar in size (watch for null values which can skew the partitioning).
Do all validation in the ETL layer before the load. Disable integrity checking (disable constraint ...) in the target database tables during the load.
Disable triggers (disable trigger ...) in the target database tables during the load. Simulate their effect as a separate step.
Generate IDs in the ETL layer (not in the database).
Drop the indices (on a table or partition) before the load - and recreate them after the load (SQL: drop index ...; create index ...).
Use parallel bulk load when possible — works well when the table is partitioned or there are no indices. Note: attempt to do parallel loads into the same table (partition) usually causes locks — if not on the data rows, then on indices.
If a requirement exists to do insertions, updates, or deletions, find out which rows should be processed in which way in the ETL layer, and then process these three operations in the database separately. You often can do bulk load for inserts, but updates and deletes commonly go through an API (using SQL).

Whether to do certain operations in the database or outside may involve a trade-off. For example, removing duplicates using distinct may be slow in the database; thus, it makes sense to do it outside. On the other side, if using distinct will significantly (x100) decrease the number of rows to be extracted, then it makes sense to remove duplications as early as possible in the database before unloading data.

A common source of problems in ETL is a big number of dependencies among ETL jobs. For example, job "B" cannot start while job "A" is not finished. You can usually achieve better performance by visualizing all processes on a graph, and trying to reduce the graph making maximum use of parallelism, and making "chains" of consecutive processing as short as possible. Again, partitioning of big tables and of their indices can really help.

Another common issue occurs when the data is spread between several databases, and processing is done in those databases sequentially. Sometimes database replication may be involved as a method of copying data between databases - and this can significantly slow down the whole process. The common solution is to reduce the processing graph to only three layers:

Sources
Central ETL layer
Targets

This allows processing to take maximum advantage of parallel processing. For example, if you need to load data into two databases, you can run the loads in parallel (instead of loading into 1st - and then replicating into the 2nd).

Of course, sometimes processing must take place sequentially. For example, you usually need to get dimensional (reference) data before you can get and validate the rows for main "fact" tables.

Parallel processing

A recent^[update] development in ETL software is the implementation of parallel processing. This has enabled a number of methods to improve overall performance of ETL processes when dealing with large volumes of data.

ETL applications implement three main types of parallelism:

Data: By splitting a single sequential file into smaller data files to provide parallel access.
Pipeline: Allowing the simultaneous running of several components on the same data stream. For example: looking up a value on record 1 at the same time as adding two fields on record 2.
Component: The simultaneous running of multiple processes on different data streams in the same job, for example, sorting one input file while removing duplicates on another file.

All three types of parallelism usually operate combined in a single job.

An additional difficulty comes with making sure that the data being uploaded is relatively consistent. Because multiple source databases may have different update cycles (some may be updated every few minutes, while others may take days or weeks), an ETL system may be required to hold back certain data until all sources are synchronized. Likewise, where a warehouse may have to be reconciled to the contents in a source system or with the general ledger, establishing synchronization and reconciliation points becomes necessary.

Source: Wikipedia.

j ntf`>09mes New Roman"; mso-fareast-language:EN-IN'>2010 group revenue (US $mil)

Walmart

$421,849

Carrefour

France

$121,519

Tesco

$94,244

Metro AG

Germany

$89,311

Kroger

$82,189

Operations

Retail pricing

The pricing technique used by most retailers is cost-plus pricing. This involves adding a markup amount (or percentage) to the retailer's cost. Another common technique is suggested retail pricing. This simply involves charging the amount suggested by the manufacturer and usually printed on the product by the manufacturer.
In Western countries, retail prices are often called psychological prices or odd prices. Often prices are fixed and displayed on signs or labels. Alternatively, when prices are not clearly displayed, there can be price discrimination, where the sale price is dependent upon who the customer is. For example, a customer may have to pay more if the seller determines that he or she is willing and/or able to. Another example would be the practice of discounting for youths, students, or senior citizens..

Staffing

Because patronage at a retail outlet varies flexibility in scheduling is desirable. Employee scheduling software is sold which, using known patterns of customer patronage, more or less reliably predicts the need for staffing for various functions at times of the year, day of the month or week, and time of day. Usually needs vary widely. Conforming staff utilization to staffing needs requires a flexible workforce which is available when needed but does not have to be paid when they are not, part-time workers; as of 2012 70% of retail workers in the United States were part-time. This may result in financial problems for the workers, who while they are required to be available at all times if their work hours are to be maximized, may not have sufficient income to meet their family and other obligations.^[3]

Transfer mechanisms

There are several ways in which consumers can receive goods from a retailer:

Counter service, where goods are out of reach of buyers and must be obtained from the seller. This type of retail is common for small expensive items (e.g. jewelry) and controlled items like medicine and liquor. It was common before the 1900s in the United States and is more common in certain countries like India.^[^which?^]
Delivery, where goods are shipped directly to consumer's homes or workplaces. Mail order from a printed catalog was invented in 1744 and was common in the late 19th and early 20th centuries. Ordering by telephone is now common, either from a catalog, newspaper, television advertisement or a local restaurant menu, for immediate service (especially for pizza delivery). Direct marketing, including telemarketing and television shopping channels, are also used to generate telephone orders. started gaining significant market share in developed countries in the 2000s.
Door-to-door sales, where the salesperson sometimes travels with the goods for sale.
Self-service, where goods may be handled and examined prior to purchase

Source: Wikipedia.

Wednesday, 26 March 2008

Neeraj Nathani Smart Bridge Trading Solutions TELECOM BILLING

TELECOM BILLING

Billing is the aggregation of all non-recurring, periodic, and chargeable events, on an account by account basis. It is also the calculation of all outstanding charges and available discounts and bonuses.

The output from billing process is a stream of tagged bill data that can be used to create a bill on paper, disk, or any other media. Billing Engine which is part of the Billing System performs creates invoices.

Bill Process:

The following diagram shows the basic diagram of the Billing Engine and associated functions:

Billing Engine picks up an account due to generate a billing and following associated information to generate invoice data:

All the rated CDRs for the customer with-in the month of invoice.
All type of charges ( initiation, installation, periodic, suspension, termination etc.) applicable for the customer's product and services.
If there is any refund or any other charges applicable.
Total outstanding from previous bills.
Total payments made by the customer in the given month.
Total adjustment passed in favor of the customer or against the customer.
Total discount given to the customer.
Total taxes applicable on customer usage and rental charges.
Billing configuration parameter required to run the Billing Engine, for example payment due date etc.

Above mentioned information is just an indicative and may vary from billing system to billing system and operator to operator.

Billing Engine produce raw data having all the information required to generate a final bill and this raw data can be used to generate a final invoice to be sent to the end customer.

Bill Cycles:

When a customer is added into the Billing System, system assigns the customer a predefined Bill Cycle. A bill cycle is a date on while Billing Engine runs and produces bill for a set of customers.

If there are many customers, then they are divided in to different billing cycles. For example, a group of customers can have billing data as 1st of every month; another can have the bill date of 15th of every month.

If customer is assigned to run a bill on 1st of the month, this would be called customer's nominal bill date. But because of various reasons, many times bill run becomes belay and actual bill gets generated on a later date, this would be called actual bill date.

Bill Types:

There could be various types of bill available for a user. Few of them may not be supported by some Billing System.

Bill Type	Description
Initiation bill	Normally only requested as the first bill on an account. Includes product charges and adjustments, but no events.
Periodic bill	Produced at regular intervals. Includes all periodic charges, events, and adjustments.
Interim bill	An extra bill that contains charges due to events processed for the account since the last bill. Includes all events and adjustments, but no periodic charges.
Suspension bill	Sent when an account has been suspended. Includes all periodic charges, events, and adjustments.
Final bill	Sent when an account has been terminated to bill all outstanding charges that are due. Includes all periodic charges, events, and adjustments, along with any refunds, for example, the return of a deposit.
Post-final bill	Sent when a terminated account has receivables outstanding after the production of a final bill. Includes any post-termination events and adjustments, but no periodic charges.
Credit note	An extra bill that contains all adjustments in the customer.s favor generated since the last bill.
Summary Statements	A summary statement can be produced for a customer-driven billing hierarchy. It can summarize all bills produced by all accounts below the customer it is associated with. Optionally, they can also concatenate all the bills into a single statement.

Bills are produced either automatically, or on request from a customer.

Billing Modes:

A Billing System can generate bills in two modes, for example:

Test (what if?) billing mode . This mode if used to produce formatted test bills whilst leaving the database unchanged. These bills are useful to make sure that system is working fine and test after making changes to bill templates or tariffs.

When running the Billing Engine in test mode, commits are not made to the database. So there would not be any impact on customer's profile even after running test billing many number of time.

Test bills are usually run for a sample set of customers. If you are satisfied with the test bills then you can proceed for production bills.

Production (live) billing mode . This mode is used to produce normal production bills. Most of the time, this is the default mode for the Billing Engine.

Once a production bill is generated, Billing Engine updates customer's profile in the database with the total outstanding balance to be paid by the customer, and next bill date etc.

Billing Engine assigns different invoice numbers to all the production bills which helps in keeping track of different payments made against the invoice.

Bill Suppression:

There may be a situation when it is not worth to generate a bill, and better to suppress the bill. Following are such type of situations:

Suppressing bills for accounts with zero (zero activity bills) or very little value (small bills).
A particular type of bill can also suppressed if multiple bill types are requested/scheduled at the same time and therefore preventing unnecessary bills from being sent to the customer.

A small bill is a bill that falls between the range defined by the minimum positive bill amount and the maximum negative bill amount exceptional bill conditions. Small valued bills are produced and then removed from the billing process, so that they are not sent out to customers.

Exceptional Bills:

Examples of possible exceptional bills are unusually high bills or bills which exceed the account's credit limit by a set multiplier. The Billing Engine performs some basic checks on the bill data that it produces. These involve testing the total being billed to ensure that the following conditions are met:

The bill total is greater than the minimum negative bill amount.
The bill total is less than the maximum positive bill amount.
The bill total is less than account's credit limit multiplied by the credit limit multiplier.

All the above conditions vary from billing system to billing system and operator to operator and they are called exceptional bills conditions.

Bill Itemization:

By default, all the invoices provide a detail summary of product and services charges along with usage charges. But it does not provide a detail on all the calls made by the customer.

An itemized bill means giving complete detail of all the calls made by the customer. This needs more number of papers to be printed. Recent trend is to send itemized bill through electronic email and summary statement is sent using a physical copy of the bill.

Bill Formatting:

There are Billing Systems who provide Billing Formatting utilities which can be used to generate final formatted bills.

Bill formatters take the output data produced by the Billing Engine and usually generate either Post Script file or a PDF file which can be used by the Bill Printing Company.

If Billing System is not capable enough to generate formatted bills then system generates a set of tags files alongwith billing information and any external Bill Formatter can use those tagged information to generate a well formatted invoice.

No matter, if Billing System generates formatted invoice or we use any external tool to generate these formatted invoice using raw data generated by the billing engine, finally these invoice are sent to the bill printing company who takes care of generating final copy of generating invoice. I will explain it in detail in subsequent chapter "Invoice Generation".

Source: Wikipedia.