HOLISTIC OPTIMIZATION OF DATABASE APPLICATIONS Karthik Ramachandra IIT

HOLISTIC OPTIMIZATION OF DATABASE APPLICATIONS Karthik Ramachandra, IIT Bombay Joint work with R Guravannavar (IIT Hyderabad), M Chavan & S Sudarshan (IIT Bombay) The Problem • Applications often invoke Database queries/Web Service requests • repeatedly (with different parameters) • synchronously (blocking on every request) • Naive iterative execution of such queries: inefficient • No sharing of work (eg. Disk IO) • Network round-trip delays The Problem is Ø not within the database engine Ø in the way queries are invoked from the application Query optimization: time to think out of the box! Soln 1: Batching Soln 2: Asynchronous Submission • Repeated invocation of a query automatically replaced by a single invocation of its batched form • Enables use of efficient set-oriented query execution plans • Repeated synchronous invocation of a queries automatically replaced by asynchronous submission • Application can perform other work while query executes • Multiple queries could be issued concurrently Approach • Analyze application programs along with embedded queries • Transform programs using equivalence rules and semantics preserving reordering • Rewrite queries using decorrelation, APPLY operator, etc. • DBridge: Implements these ideas on Java/JDBC code, using Soot framework • Program rewrite using DBridge API identical for both solutions Program Transformation S 1: while (category != null) { qt. bind(1, category); S 2: int count = execute. Query(qt); S 3: sum = sum + count; S 4: category = get. Parent(category); } Parsing S 2 Statement Reorder S 3 Data Dependence Graph(DDG) Input program S 1: while (category != null) { S 5: int temp = category; S 4: category = get. Parent(category); qt. bind(1, temp); S 2: int count = execute. Query(qt); sum = sum + count; S 3: } Data flow analysis Handles complex programs with • Conditional branching • Nested Loops Reordered program before Loop Splitting S 4 Performance gains (upto 7 x)! Construct/ Update DDG Split Loop Decompile Output program after Loop Splitting Threads Submit Q DB Result array Asynchronous mode Loop. Context. Table lct = new Loop. Context. Table(); while(!category != null){ Loop. Context ctx = lct. create. Context(); stmt. set. Int(1, category); ctx. set. Int(”category”, category); category = get. Parent(category); stmt. add. Batch(ctx); } stmt. execute. Batch(); for (Loop. Context ctx : lct) { category = ctx. get. Int(”category”); Result. Set rs = stmt. get. Result. Set(ctx); rs. next(); int count = rs. get. Int(”count"); sum += count; print(category + ”: ” + count); } Parameter Batch (temp table) Query rewrite DB Experiment 1: DB Query invocation Only Asynchronous Submission applies Batching not possible here as there is no set -oriented interface exposed by the web service Set of Result. Sets Batching mode Identical API interface for Batching and Asynchronous approach Experiment 2: Web Service invocation