--- Software Labs team

Indexing Strategies to Improve Query Performance

If you are running an E-Commerce site, it is important that end-user searches return data quickly. Slow performance may push the potential customer away from your site. Indexing a database can improve query performance. In this article, Dr. Pradeep Tapadiya will cover some indexing strategies. Read More >

Announcements

xFusion Studio v3.5 Released

We are pleased to announce the release of xFusion Studio v3.5. This new version provides additional functionality for the SAP user community, adds validation rules to prevent bad data uploads, and lets users receive email notifications on data export results.

Free xFusion Templates

Do you need to know inventory status, see a proposed commission report for a given sales employee, or see a quick list of sales invoices for all the business partners in a given territory?. Our new xFusion templates provide out-of-the-box functionality across a number of subject areas, including:

• xFusion Insight Packs -- provide fast query and export of important business information from SAP to Excel files, all in a single mouse click.
• xFusion Data Quality Packs -- provide valuable information to help you improve the quality of data residing in SAP. The files contain a number of queries that help to identify duplicate, missing, or incorrect data so that you can decide if it should be corrected.
• xFusion Database Admin Packs -- provides useful tasks for database backup, creating restore points, and file backup.
• xFusion Acceleration Packs -- enables you to quickly upload and download important data between Excel / Text and SAP, and also compare for changes.
  

Latest News

• BizJournals.com selects Software Labs as "100 Fastest Growing Companies" in 2008. Read More >
  
• Leading SAP partner,OxygenExpress delivers 30% faster ERP deployment. Read More >
  
• Recent Partner reports suggest data management industry heating up. Read More >
  

New Partners

We are happy to announce that Bluekey (South Africa), Crossroads, Logo Chairs, Lorge (South Africa), and SoftEngine have joined as customers and partners of Software Labs. These companies are experts in their respective industries, and valuable additions to our customer community.

Did You Know?

xFusion can load pictures from file and store them in database. Read More >

From the Support Desk

Support Questions:

Dear Support,

I want to use the Lookup transform function for cross referencing. But as I understand from the syntax of lookup function, the function takes in one key for mapping and returns one column. Please let me know how I can use this function for multi key based lookup? Also I want to return multiple columns while I see lookup function just returns one column.
Please help.

Thanks

Dear Customer,

You are correct that Lookup function takes just a single column for looking up. However, there is a simple workaround for handling multiple keys. You would need to create a temporary column by concatenating the keys. In the following transform query, the source table is A and the lookup table is B. The keys are FirstName and LastName:

$a = AddColumn([[A]],
     [A.FirstName] + [A.LastName] as FullName)

$b = AddColumn([[B]],
     [B.FirstName] + [B.LastName] as FullName)

$c = Select(
     Lookup( [$a.FullName], [$b.FullName],
     [$b.ZipCode], null) as ZipCode,
     Lookup( [$a.FullName], [$b.FullName],
     [$b.City], null) as City )

$c will return you the right zip code and city from table B for every entry in table A based on First name and the last name together.

Note that you would need to concatenate keys in the source table as well as the lookup table.

Hope this helps.

Best regards,
Software Labs Support.

Indexing Strategies to Improve Query Performance

Dr. Pradeep Tapadiya

A database index is an auxiliary data structure that allows for faster retrieval of data stored in the database. They are keyed off a specific column so that queries like "give me all people with last name 'Parker'" are fast. However, before you create indexes on tables, you must thoroughly understand the types of queries that will be run against those tables. In general, indexing must be considered on all columns that are frequently accessed by WHERE, ORDER BY, GROUP BY, TOP and DISTINCT clauses.

Note that while the performance for SELECT queries may get improved, the performance for INSERT, DELETE and UPDATE is adversely affected. This is because each time the actual record is updated, the auxiliary data for indexing needs to be updated as well. The more indexes you have, the slower the update performance will be. Balance the number of indexes between fetch and update queries. Static tables (those that change very little or not at all) can be more heavily indexed than dynamic tables (those that change frequently).

Once you have identified the columns to index, you need to decide if you should create a clustered index or a non-clustered index. A clustered index stores the actual data and a non-clustered index stores a pointer to the actual data. As is obvious, a clustered index is faster than a non-clustered index. However, there can be only one clustered index per table. So use your clustered index judiciously. In most cases, the primary key is the ideal column for clustered index.

Indexes come in many types. The most common type is based on B-tree+ algorithm. This is the default type for most databases. Hash indexing is also supported by many databases. Hash indexes are faster than B-tree+. However, they can deal with equality but not with inequality. For example, a query to show all people whose age is greater than 30 is not efficient using a Hash index. Other index types include R-tree indexes (used for querying spatial data) and Bitmap indexes. Bitmap indexes work exceptionally well for data that has small number of distinct values (such as gender).

Finally, be aware that most databases allow multiple indexes on the same column as long as the index name is different. Be careful not to inadvertently add index on the same column twice. This may happen, for example, with a primary key. Keep in mind that a primary key is always automatically indexed.

Hope this is a good introduction to indexing strategies. Database vendors such as Microsoft, Oracle, IBM, etc. add their own indexing optimizations. More information on database-specific indexing is usually available on the vendor's technical website.