4/562 Databake Off @ 3:00

RSVP (limited space available) to participate

A note on optimization...

Lots of interesting strategies used in Checkpoint 3

• Pre-parsing
• Column Stores
• Cost-based Opt
• Hyper-optimize the slowest query

• A stream of inserts, deletes, updates, and queries.
• No restarts.
• Answer queries as fast as possible.
• Make sure query results account for DDL effects.

Stage 0

10 minutes of prep

Stage 1

Inserts only

Stage 2

Inserts + Deletes

Stage 3

Inserts + Deletes + Updates

No restarts.

Classical Databases

Problem 1: More indexes = Slower writes (bad for OLTP)

Problem 2: Fewer indexes = Slower reads (bad for OLAP)

What if you have both OLAP and OLTP workloads?

Idea: Weekly / Nightly / Hourly dump
from OLTP System to OLAP system.

(Index the data while dumping)

Problem: Not seeing the freshest data!

Better Idea: OLTP DB + OLAP DB.

OLTP DB has few indexes, but only stores recent updates.

OLAP DB has many indexes, and stores everything except recent updates.

Periodically migrate updates into OLAP DB.

(Lambda Architecture)

Checkpoint 4

Suggested Approach: Lambda-Lite

              INSERT INTO FOO(A, B, C) VALUES (1, 2, 3);
    

      SELECT COUNT(*) FROM lineitem WHERE mktsegment = 'BUILDING';
    

                  DELETE FROM FOO WHERE A > 5;
    

... but that's not quite how SQL Delete works.

                      DELETE FROM FOO WHERE A > 5;
    

                      DELETE FROM Orig WHERE Something;
    

    INSERT INTO lineitem(...) VALUES (...);
    INSERT INTO lineitem(...) VALUES (...);
    DELETE FROM lineitem WHERE shipdate BETWEEN date(1997-10-01) 
                                            AND date(1997-10-30);
    SELECT COUNT(*) FROM lineitem WHERE mktsegment = 'BUILDING';
    

            UPDATE Foo SET A = 1, B = 2 WHERE C = 3;
    

            UPDATE Foo SET A = 1, B = 2 WHERE C = 3;
    

            UPDATE Foo SET A = 1, B = 2 WHERE C = 3;
    

      SELECT CASE WHEN C = 3 THEN 1 ELSE A END AS A,
             CASE WHEN C = 3 THEN 2 ELSE B END AS B,
             C AS C
      FROM Foo;