1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

# DISK STATS
rpm = 7200
avg_seektime_ms = 15
transfer_rate_sec_mb = 100
block_size_byte = (1024 * 4)  # 4KB blocks

# DERIVED STATS
avg_rotational_delay_ms = 0.5 * (float(1) / (rpm / (60 * 1000))) # translate rpm into
block_size_mb = float(block_size_byte) / float(1024 * 1024)
transfer_rate_ms_mb = transfer_rate_sec_mb / 1000
transfer_one_block_ms = block_size_mb / transfer_rate_ms_mb

# RANDOM AND SEQUENTIAL ACCESS TIMES
random_access_block = round(avg_seektime_ms + avg_rotational_delay_ms + transfer_one_block_ms,3)
sequential_access_block = round(transfer_one_block_ms,3)
speed_up = round(random_access_block / sequential_access_block,3)

print(f"random access (ms per block):        {random_access_block:9.3f}\nsequential access (ms per block):    {sequential_access_block:9.3f}\nspeed-up sequential:                 {speed_up:9.3f}")

random access (ms per block):           19.206
sequential access (ms per block):        0.039
speed-up sequential:                   492.462

1
2
3

SELECT c * c AS D
FROM (SELECT a + b AS c, e
      FROM R(a,b,e)) sub

• pullup subqueries

1
2

SELECT (a+b) * (a+b) AS D
FROM R

• what about side effects (we have to be careful as we may change the number of times the function is executed)

  1 2 3

  SELECT c * c AS D FROM (SELECT send_email(a,b) AS c, e FROM R(a,b,e)) sub

• in worst case, we get an exponential blowup

1
2
3
4

SELECT an-1 * an-1 AS an
FROM (SELECT an-2 * an-2 AS an-1
     ...
        SELECT a1 * a1 AS a2 FROM R

1
2
3
4
5
6

CREATE FUNCTION f(x,y) {
   RETURN x + y
};

SELECT f(a,c)
FROM R;

1
2

SELECT fresult
FROM R, LATERAL (SELECT a + c AS fresult) AS f;

1
2

SELECT a + c AS fresult
FROM R;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

CREATE FUNCTION divide(x,y) AS (
  IF y = 0
  THEN
     RETURN NULL
  ELSE
     RETURN x / y;
)

-- ->
SELECT CASE WHEN y = 0 THEN NULL ELSE x / y;

1
2
3

CREATE FUNction
   FOR x in (SELECT * FROM ..)
      loop_body

• in general this is hard as in general optimizing beyond the "function boundary" would require lifting the function to the declarative / batch level of relational operations (such as joins) which is akin to automatic parallelization of programs (an undecidable computational problem)

  • the net result is that functions are "optimization boundaries", e.g., if your UDF runs a query with a join and you call your UDF from another query with joins, there will be no join reordering between these two

• there are some research prototypes that translate procedural code into declarative SQL:

  • Froid: Optimization of Imperative Programs in a Relational Database. Karthik Ramachandra, Kwanghyun Park, K. Venkatesh Emani, Alan Halverson, César A. Galindo-Legaria, Conor Cunningham. PVLDB, vol. 11 (4), 432–444, 2017.
  • Blackmagic: Automatic Inlining of Scalar Udfs into SQL Queries with Froid. Karthik Ramachandra, Kwanghyun Park. PVLDB, vol. 12 (12), 1810–1813, 2019.
  • One with RECURSIVE Is Worth Many GOTOs. Denis Hirn, Torsten Grust. SIGMOD '21: International Conference on Management of Data, Virtual Event, China, June 20-25, 2021, pp. 723–735 (2021).
  • Dear User-Defined Functions, Inlining Isn'T Working out so Great for Us. Let'S Try Batching to Make Our Relationship Work. Sincerely, SQL. Kai Franz, Samuel Arch, Denis Hirn, Torsten Grust, Todd C. Mowry, Andrew Pavlo. 14th Conference on Innovative Data Systems Research, CIDR 2024, Chaminade, HI, USA, January 14-17, 2024 (2024).

1
2
3
4
5
6
7

SELECT name
FROM orders o,
    (SELECT max(amount) AS ma, i.cust
    FROM orders i
    GROUP BY i.cust) sub
WHERE sub.cust = o.cust
      AND o.amount = sub.ma

1
2
3
4

SELECT name
FROM (Select max(amount) OVER (PARTITION BY cust) AS ma, amount, name
      FROM orders)
WHERE ma = amount

Enhanced Subquery Optimizations in Oracle. Srikanth Bellamkonda, Rafi Ahmed, Andrew Witkowski, Angela Amor, Mohamed Zait, Chun-Chieh Lin. Proceedings of the VLDB Endowment, vol. 2 (2), 1366–1377, 2009.