Business Intelligence (BI) has become an integral part of modern businesses. As a result, databases dealing with BI queries are experiencing an increase in workload. When queries are sent to large databases, millions of rows are returned, leading to extended query response times, especially when joining multiple tables.

Fortunately, SQL Server 2008 Enterprise Edition introduces a new feature called “Star Join Query Optimization” to address this issue. This mechanism utilizes bitmap filtering to improve the performance of certain types of queries by efficiently retrieving rows from fact tables.

Improved Query Response Times

In data warehouses, dimensionally modeled star or snowflake schemas are commonly used. These schemas consist of one or more fact tables containing transactional data and multiple dimension tables holding information such as product data, customer information, and times and dates. Foreign keys are used to maintain relationships between rows in fact tables and dimension tables.

SQL Server 2008 Enterprise Edition recognizes databases with star schemas and employs the new Star Join Query logic to process queries against these schemas more efficiently. On average, data warehouse queries run approximately 20% faster with this optimization.

Automatically Implemented Optimization

Star Join Query Optimization is automatically implemented by SQL Server and does not require any special database or application configuration. The query processor optimizes queries with medium selectivity, which refers to queries retrieving approximately 10% to 75% of rows from a fact table.

For these queries, hash joins are used to join the dimension and fact tables by utilizing foreign keys to identify matching rows. Hash tables are built for each dimension table referenced in the query, and bitmap filters are derived from these hash tables during the optimization process.

Bitmap filters identify key values from each dimension table that qualify for inclusion in the query. When scanning the fact table, these bitmap filters are applied to eliminate rows that do not qualify for the result set. The most selective bitmap filter is applied first, eliminating the highest number of rows. This process improves efficiency by avoiding unnecessary processing of eliminated rows.

Query Selectivity

Bitmap filtering enhances performance for medium selectivity queries by filtering rows before any joins are performed. This reduces the number of rows processed by each join. However, bitmap filtering is not applied to highly selective queries (returning less than 10% of rows) or non-selective queries (returning more than 75% of rows) as other join methods are more efficient in these cases.

Integer Data Types

Star join optimization is most effective when the columns used in joins have integer data types. This feature allows the bitmap filter to be applied as part of the initial table or index scan, rather than at a later stage in the query plan. Since foreign key relationships commonly use integer-based alternate key columns, most queries benefit from star join optimization.

In conclusion, SQL Server 2008 Enterprise Edition’s Star Join Query Optimization feature significantly improves query performance for data warehouses with star schemas. By utilizing bitmap filtering and optimizing join operations, query response times are reduced, resulting in faster and more efficient data retrieval.