SAP IQ Developer Center in SAP Developer CenterWhere is this place located?

Log in to follow, share, and participate in this community.

Overview

sm_competitive2.jpg

Build big-data analytics solutions with the market leader. SAP IQ
is more powerful and efficient than traditional systems for extreme-scale data warehousing, advanced analytics, and business intelligence applications.

Top Liked Content

Refresh this widget

You can't create discussions here, but as discussions appear they might have answers you need.

You can't create documents here, but watch for documents with information you can use.

You can't post to a blog here, but watch blog posts for new insights and ideas.

You can't create polls here, but watch for polls you can vote on.

Test Drive IQ Today!

Download

SAP IQ 16 Express Edition or SAP IQ 16 Evaluation Edition

IQ_Download.jpg

Formatted Text

Featured Links for SAP IQ:

Formatted Text

Popular Blog Posts

Are you new to SAP Sybase IQ?  Have you used the product but are still not clear on what indexes to use or when to use them?  Have you just migrated to SAP Sybase IQ 16 and are wondering if there are any indexing changes?  This blog will discuss indexes and strategies for SAP Sybase IQ.

 

Basic Indexes

When creating a table in IQ, a default index is placed on every column.  This index is referred to as a Fast Projection (FP) index.  In order to enable optimized FP indexes, one must set the Minimize_Storage option to ON.

set option public.minimize_storage=’on’;

 

From this point, all new tables and columns will use n-bit encoding for highly optimized and compressed data storage and access.  Each FP index will be encoded as 1 to 31 bits depending on the data cardinality (2^1 to 2^31 distinct values).

 

Advanced Indexes

In addition to the basic FP indexes, IQ supports nine other indexes:

  • High Group (HG) – used for join columns and searchable columns whose data has more than 2,000 distinct values
  • Low Fast (LF) – used for searchable columns whose data has fewer than 2,000 distinct values
  • High Non Group (HNG) – used for range searches (except for date, time, and datetime data types) and for single column aggregations
  • Word (WD) – Use for whole word searching of character based data
  • Text (TEXT)– Use for full text searching of character based data
  • Date (DATE) – Use for fields that have a DATE data type (ranges and datepart queries)
  • Time (TIME) – Use for fields that have a TIME data type(ranges and datepart queries)
  • Datetime (DTTM) – Use for fields that have a DATETIME/TIMESTAMP data type (ranges and datepart queries)
  • Compare (CMP) – Use for ore-computing the comparison of two columns in the same table

 

When are indexes used?

In most relational database engines, the optimizer chooses the best single index for a given table in the query.  SAP Sybase IQ takes the optimization process to the next level by looking not only at each column in the query, but at all joins and search arguments.  For example, assume that a date column is used in a join and to search for the month datepart.  The optimizer would prefer to use the HG index on the date field for the join.  It would also prefer to use the DATE index to search on the month datepart.

 

In short, IQ will look at each join, where clause, aggregation, etc. to determine the best index for each operation.  Not just one index per table and not just one index per column.  But rather, one index for each use of each column in the query!

 

Should we be concerned about over indexing?

The short answer is an emphatic NO!  As mentioned previously, typical relation database engines pick the best single index to access data in a table.  Second, when adding indexes to most RDBMS engines the load times tend to slow down due to how data is processed.  Third, most database engines store data and indexes in different structures so that as more indexes are added the table size swells.  All of these lead to trying to minimize the total number of indexes on a table in most engines.

 

SAP Sybase IQ is not prone to these limitations.  We pick as many indexes as needed to process the entire query.  Indexes in IQ are not as large as the data being indexed.  Each of the indexes employs various levels of vectors, bitmaps, data representations, and compression to dramatically decrease the database size.  Finally, when loading data, IQ can consume all CPU resources.  We currently released a fully audited benchmark in which SAP Sybase IQ loaded data at over 34.3 TB/hour on an 80 core Linux system (http://scn.sap.com/community/developer-center/analytic-server/blog/2013/05/30/sap-sybase-iq-16-sets-guinness-world-record-with-343tb-per-hour-for-big-data-loading).

 

 

All of these SAP Sybase IQ features lead to an engine that can handle as many indexes as necessary for all query (canned or ad hoc) activity.  It is quite common to have 30-60% of all columns indexed with the advanced indexes (and in some cases multiple indexes per column).

 

When to use Indexes?

Now that we have some basics about indexes, the big question is when to use them? Here are some simple guidelines that will help cover every situation.  The general idea is to ask a series of questions about each column.  The answers to the questions will dictate whether or not indexes are needed, and if needed which one(s) are best.

 

  • Will this column be used as a JOIN clause?
    • NO: Continue to next question
    • YES: Use an HG index
  • Will this column be used as a search column in the WHERE clause?
    • NO: Continue to next question
    • YES: Use an HG index if the column has more than 2,000 distinct values.  If less than 2,000 distinct values use an LF.
  • Is the column data type a DATE, TIME, TIMESTAMP, or DATETIME?
    • NO: Continue to next question
    • YES: Use a DATE, TIME, or DTTM index.  All date/time/datetime fields should have this index as systems quite frequently use date fields for searching, filtering, and aggregations.
  • Will whole word searching be done on the character data?
    • NO: Continue to next question
    • YES: Use WD (WORD) index
  • Will text searching be done on the character data?
    • NO: Continue to next question
    • YES: Use a TEXT index
  • Will this column be compared to another column in the same table?
    • NO: Continue to next question
    • YES: Use a CMP (compare) index that includes both columns
  • Will this column be used for range searches?
    • NO: Continue to next question
    • YES: Use an HNG (high non group) index
  • Will this column be used in aggregations with no other columns?
    • NO: Continue to next question
    • YES: Use an HNG (high non group) index

 

There are two types of indexes in IQ: those that have detailed statistics and those that have minimal statistics.  The HG, LF, TEXT, and WD indexes contain detailed statistics.  The HNG, CMP, DATE, TIME, and DTTM indexes contain minimal statistics.  After walking through the above checklist, you may find that an index with minimal statistics was all that was warranted for the column.  Should that situation arise, it is also recommended that an index with detailed statistics is placed on that column.  Use an HG or LF index for these detailed statistics.  The choice will depend on the current or expected cardinality of data in the column.

 

The process to choose indexes may seem daunting especially if the data model has a significant number of tables and columns.  The process outlined above assumes that very little is known about the application and its use of the data.  This is, generally, not the case as most DBAs and data modelers do know more about the data and its use.

 

Should you know more about the model and data, we can simplify the process.  I prefer to look at columns that are known to be used in WHERE clauses of most queries and reports.  Indexes on join and search criteria are the single biggest factor in performance, so focusing solely on those will allow IQ to be indexed for nearly every situation.

 

Certainly some columns and indexes will be missed with this approach.  However, since the focus was on columns that are heavily used, the impact of missing a few minor, or less frequently used, columns should be relatively low.

 

Lastly, know that IQ provides a safety net for us!  IQ has an index advisor that can be enabled by setting the option Index_Advisor to ON.

set option public.Index_Advisor=’on’;

 

Once on, IQ will print index advice to query plans (if they are on) as well as to a system table that can be accessed via the stored procedure sp_iqindexadvice.  The index advisor output will note under what circumstance the index is needed.  The stored procedure will also aggregate the number of times the advice was made.  Should that procedure be used to trend data over a period of time (days, weeks, even months) the number of times the same advice was made can help focus efforts on the most recommended advice.

You have thought about it. Freedom from clunky hardware, easier scaling, automatic upgrades, and the ability to pay for only what you need.  Well here is your chance to tell us what you think about a future offering of a Data Warehouse in the Cloud powered by SAP IQ.

 

We want your use cases and your challenges so that we can develop a solution that meets your needs, helping your business run better and letting you spend more time on what matters most to you. And what could be better than that?

 

So we invite you as our customers to take the survey. It is anonymous and can be completed in less than 15 minutes, and all responses are kept confidential. However if you would like to be contacted for a follow-up we’d be more than happy to speak with you. Just leave us your contact information on the form.

 

Your response is invaluable to us and we greatly appreciate your feedback and time! All comments received will go directly toward shaping an offering of IQ in the cloud.

 

The survey can be found at the link below and will be available until March 24, 2014.  But why wait? Tell us what you think right now and help shape the future!


https://www.surveymonkey.com/s/SapIQCloudDW

 

Many thanks to Maria Stoianova, member of the SAP IQ product management team, for putting this survey together.

 

Kind regards,

Courtney Claussen, SAP IQ Product Management

Hi Everyone -

Happy New Year! To kick-off 2014 SAP is integrating the Sybase products, technologies, engineering, and marketing more fully with the other SAP data management, data movement, data quality and data modeling assets along with our go-to-market strategies. To reflect this integration, SAP now has a full suite for managing applications across a spectrum of use cases, and SAP has named this SAP Data Management.

 

Specifically for EDW and analytics, SAP now offers SAP IQ, which, along with other assets from SAP, including SAP HANA, SAP ESP, SAP Rep Server, SAP BW and extensive Hadoop integration, now provide from SAP a logical architecture for all data warehousing and analytic scenarios.

 

We are excited for 2014 and looking forward to SAP IQ continuing to make great strides!

Many of our customers have asked for more "how to" videos on SAP Sybase IQ enablement and specifically on the new features in IQ 16. Well you asked for it and we heard you!

 

We will be producing a number of technical videos for our customers and partners. The videos will focus on best practices, new feature functionality and your basic how do I do that? Our first release of new videos has been developed by Diana Healy and focus on SAP Sybase IQ 16 and Sybase Control Center.

 

Stay tuned as we add more technical videos and webinars from our product team for in-depth technical sessions on the new IQ 16 features and functions.

 

We hope you find these valuable and as always we would love to hear your feedback.

SAP and its business partners, BMMsoft, HP and Red Hat, have generated a new world record for loading and indexing Big Data with SAP Sybase IQ 16, the latest version of SAP Sybase IQ, which implements a new, fully parallel data loading capability  and a next generation column store. The audited result of 34.3 TB/hour greatly surpasses the previous world record of 14 TB/hour, also achieved by the same team using a previous version of SAP Sybase IQ.

 

This new record also easily eclipses published data loading results by companies such as Oracle and EMC. It demonstrates the ability of this solution to efficiently handle extreme-scale EDWs and Big Data analytics by providing high speed data loading, indexing, immediate access and  deep analysis of both structured data (i.e. database records) and unstructured data such as emails, documents and multi-media files. SAP Sybase IQ makes it simpler and more cost-effective to exploit the value of massive amounts of data at the speed of business, helping to transform companies through new insights that were previously impossible or impractical.

 

Way to go team!

Formatted Text

Formatted Text

12-month Leaderboard