Akiban Use Cases - Code-Generated SQL

In many applications SQL is generated by ORM frameworks, embedded code or via form-based user interaction. Programmatically, it's simpler to write subqueries than to correctly write joins. In practice, however, subqueries cause some database systems to perform millions of extraneous database operations! When your application needs to process large volumes of data, or to satisfy many concurrent users, this sort of suboptimal access quickly leads to contention and errors.

SELECT    A0.unit_price,
          A0.shipment_part,
          A0.book_id,
          A0.discount,
          A0.item_status,
          A0.quantity,
          A0.item_id,
          A0.tax,
          A0.color,
          A0.last_updated,
          A0.order_id		 Individual columns define the data elements required by the application

FROM      items A0
WHERE     A0.order_id
IN       (SELECT    B0.order_id 
          FROM       orders     BO 
          INNER JOIN handling_instructions    B1 
          ON         B0.order_id = B1.order_id
          WHERE      B1.additional_instructions  
                      = 'ID0000000SA81'
          AND        B0.order_priority = 'Standard')
AND       A0.last_updated >= '2007-08-15 00:00:00' 
AND       ( A0.item_status = 'N'
          OR  A0.shipment_part = 1 )

 

A subquery is used to filter the data instead of writing a join.

 

Predicates (filters) applied

 
ORDER BY  A0.order_id, A0.last_updated; Sort the results

 

Akiban versus Traditional Relational Systems

A subquery is used to filter the data instead of writing a join. MySQL has to execute the subquery once for every row returned for the attendance table:

Akiban Server recognizes that the subquery can be internally rewritten as a join. This saves one subquery execution for every row returned by the outer query. Akiban’s ability to create an ordered index on a table-group allows the optimizer to avoid a temporary table and filesort, and table-grouping turns three table accesses and their associated joins into a single group access.

Business Value In Context

Program-generated code without performance risk

More efficient execution leads to better scalability

Reduced resource contention maximizes hardware potential

Stable architecture leads to more agile development

Akiban table grouping brings game changing query acceleration and scalability to SQL queries. By decreasing your customers’ time to interaction, and in providing them valuable real-time context, your business yields significant benefits. The graph reflects it. For this customer Akiban Server’s throughput is 17x that of MySQL. MySQL executes this query in 42 seconds, whereas Akiban Server executes the query in 2.5 seconds.