CPU vs IO, a Performance Primer.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A small notebook explaining the pitfalls of algorithmic compexities and the difference of complexity and actual runtime."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First, let's define a few mockup functions whose performance we want to analyze."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# mockup for a function with constant runtime:\n",
    "# it simply performs a multiplication on the input parameters\n",
    "# this function has constant runtime,\n",
    "# basically just the costs for one multiplication\n",
    "def foo(i,j):\n",
    "    return i*j\n",
    "\n",
    "# mockup for a function with linear runtime:\n",
    "# this function calls function <func> n times\n",
    "def loop(n, func):\n",
    "    sum = 0\n",
    "    for i in range(n):\n",
    "        sum += func(i,i)\n",
    "    return sum\n",
    "\n",
    "# mockup for a function with quadratic runtime:\n",
    "# this function calls function <func> n*n times\n",
    "def nestedLoop(n, func):\n",
    "    sum = 0\n",
    "    for i in range(n):\n",
    "        for j in range(n):\n",
    "            sum += func(i,j)\n",
    "    return sum\n",
    "\n",
    "# mockup for a function with n*log n runtime:\n",
    "# this function calls function <func> n*ceil(log_2 n) times\n",
    "import math\n",
    "def loopLog(n, func):\n",
    "    sum = 0\n",
    "    _log = math.ceil(math.log2(n))\n",
    "    for i in range(n):\n",
    "        for j in range(_log):\n",
    "            sum += func(i,j)\n",
    "    return sum"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('09 Zusammenfassung.pdf', 13174793), ('x9 Datenjournalismus (Teil 1).pdf', 16935169), ('00 Jupyter.ipynb', 3889160), ('01 IMDb (Teil 1).pdf', 12137353), ('x10 Datenjournalismus (Teil 2).pdf', 8853176), ('05 Anfrageoptimierung (Teil 1).pdf', 7176671), ('07 Handel, Banken, Ticketsystem (Teil 1).pdf', 33598178), ('Welcome.pdf', 6486308), ('02 IMDb (Teil 2).pdf', 4143455)]\n"
     ]
    }
   ],
   "source": [
    "# list files in current directory greater than 1MB\n",
    "# we do this to simulate I/O operations to play with:\n",
    "import os\n",
    "files = [(f,os.stat(f).st_size) for f in os.listdir('.') if os.path.isfile(f) and os.stat(f).st_size >1024*1024]\n",
    "print(files)\n",
    "\n",
    "# a mockup function performing a pseudo-random read to simulate I/O:\n",
    "def foo_IO(i,j):\n",
    "    # pick a file pseudo-randomly from the pre-computed list of files:\n",
    "    pseudo_random = i*i % len(files)\n",
    "    with open(files[pseudo_random][0], 'rb') as f:\n",
    "        # seek to random offset in that file:\n",
    "        f.seek(files[pseudo_random][1]//2)\n",
    "        # read 4096 bytes and return the last byte read:\n",
    "        first_bytes = f.read(4096)\n",
    "        # return the bytes read:\n",
    "        return first_bytes[min(4095,len(first_bytes)-1)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's run performance measurements on these mockup functions:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 4/4 [01:09<00:00, 17.28s/it]\n"
     ]
    }
   ],
   "source": [
    "import time\n",
    "from tqdm import tqdm\n",
    "# combination of methods to benchmark:\n",
    "# syntax: (outer-function, inner-function)\n",
    "methods=[ (loop,foo), (loop, foo_IO), (loopLog, foo), (nestedLoop, foo)]\n",
    "results = []\n",
    "for m in tqdm(methods):\n",
    "    # vary n:\n",
    "    for n in range(100, 10000, 500):\n",
    "        start = time.time()\n",
    "        # call the function combination:\n",
    "        m[0](n, m[1])\n",
    "        elapsedTime = time.time() - start\n",
    "        # append measured times to measurement data:\n",
    "        results.append((m[0].__name__, m[1].__name__, n, elapsedTime))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's display the performance results visually:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "<div id=\"altair-viz-b2264a44e5c248098d3e862c4e8a45d5\"></div>\n",
       "<script type=\"text/javascript\">\n",
       "  (function(spec, embedOpt){\n",
       "    let outputDiv = document.currentScript.previousElementSibling;\n",
       "    if (outputDiv.id !== \"altair-viz-b2264a44e5c248098d3e862c4e8a45d5\") {\n",
       "      outputDiv = document.getElementById(\"altair-viz-b2264a44e5c248098d3e862c4e8a45d5\");\n",
       "    }\n",
       "    const paths = {\n",
       "      \"vega\": \"https://cdn.jsdelivr.net/npm//vega@5?noext\",\n",
       "      \"vega-lib\": \"https://cdn.jsdelivr.net/npm//vega-lib?noext\",\n",
       "      \"vega-lite\": \"https://cdn.jsdelivr.net/npm//vega-lite@4.8.1?noext\",\n",
       "      \"vega-embed\": \"https://cdn.jsdelivr.net/npm//vega-embed@6?noext\",\n",
       "    };\n",
       "\n",
       "    function loadScript(lib) {\n",
       "      return new Promise(function(resolve, reject) {\n",
       "        var s = document.createElement('script');\n",
       "        s.src = paths[lib];\n",
       "        s.async = true;\n",
       "        s.onload = () => resolve(paths[lib]);\n",
       "        s.onerror = () => reject(`Error loading script: ${paths[lib]}`);\n",
       "        document.getElementsByTagName(\"head\")[0].appendChild(s);\n",
       "      });\n",
       "    }\n",
       "\n",
       "    function showError(err) {\n",
       "      outputDiv.innerHTML = `<div class=\"error\" style=\"color:red;\">${err}</div>`;\n",
       "      throw err;\n",
       "    }\n",
       "\n",
       "    function displayChart(vegaEmbed) {\n",
       "      vegaEmbed(outputDiv, spec, embedOpt)\n",
       "        .catch(err => showError(`Javascript Error: ${err.message}<br>This usually means there's a typo in your chart specification. See the javascript console for the full traceback.`));\n",
       "    }\n",
       "\n",
       "    if(typeof define === \"function\" && define.amd) {\n",
       "      requirejs.config({paths});\n",
       "      require([\"vega-embed\"], displayChart, err => showError(`Error loading script: ${err.message}`));\n",
       "    } else if (typeof vegaEmbed === \"function\") {\n",
       "      displayChart(vegaEmbed);\n",
       "    } else {\n",
       "      loadScript(\"vega\")\n",
       "        .then(() => loadScript(\"vega-lite\"))\n",
       "        .then(() => loadScript(\"vega-embed\"))\n",
       "        .catch(showError)\n",
       "        .then(() => displayChart(vegaEmbed));\n",
       "    }\n",
       "  })({\"config\": {\"view\": {\"continuousWidth\": 400, \"continuousHeight\": 300}}, \"data\": {\"name\": \"data-6cc806285fe884f746052ab4ebbca5d5\"}, \"mark\": \"line\", \"encoding\": {\"color\": {\"type\": \"nominal\", \"field\": \"cat\", \"title\": \"Algorithm\"}, \"x\": {\"type\": \"quantitative\", \"field\": \"n\"}, \"y\": {\"type\": \"quantitative\", \"field\": \"time\", \"title\": \"Time [sec]\"}}, \"selection\": {\"selector001\": {\"type\": \"interval\", \"bind\": \"scales\", \"encodings\": [\"x\", \"y\"]}}, \"transform\": [{\"calculate\": \"datum.method + ' using ' + datum.foo\", \"as\": \"cat\"}], \"$schema\": \"https://vega.github.io/schema/vega-lite/v4.8.1.json\", \"datasets\": {\"data-6cc806285fe884f746052ab4ebbca5d5\": [{\"method\": \"loop\", \"foo\": \"foo\", \"n\": 100, \"time\": 1.0728836059570312e-05}, {\"method\": \"loop\", \"foo\": \"foo\", \"n\": 600, \"time\": 5.435943603515625e-05}, {\"method\": \"loop\", \"foo\": \"foo\", \"n\": 1100, \"time\": 9.942054748535156e-05}, {\"method\": \"loop\", \"foo\": \"foo\", \"n\": 1600, \"time\": 0.00014662742614746094}, {\"method\": \"loop\", \"foo\": \"foo\", \"n\": 2100, \"time\": 0.0002551078796386719}, {\"method\": \"loop\", \"foo\": \"foo\", \"n\": 2600, \"time\": 0.00024199485778808594}, {\"method\": \"loop\", \"foo\": \"foo\", \"n\": 3100, \"time\": 0.0002918243408203125}, {\"method\": \"loop\", \"foo\": \"foo\", \"n\": 3600, \"time\": 0.0004029273986816406}, {\"method\": \"loop\", \"foo\": \"foo\", \"n\": 4100, \"time\": 0.0004487037658691406}, {\"method\": \"loop\", \"foo\": \"foo\", \"n\": 4600, \"time\": 0.00048804283142089844}, {\"method\": \"loop\", \"foo\": \"foo\", \"n\": 5100, \"time\": 0.0004830360412597656}, {\"method\": \"loop\", \"foo\": \"foo\", \"n\": 5600, \"time\": 0.0005612373352050781}, {\"method\": \"loop\", \"foo\": \"foo\", \"n\": 6100, \"time\": 0.0006563663482666016}, {\"method\": \"loop\", \"foo\": \"foo\", \"n\": 6600, \"time\": 0.0006463527679443359}, {\"method\": \"loop\", \"foo\": \"foo\", \"n\": 7100, \"time\": 0.0006740093231201172}, {\"method\": \"loop\", \"foo\": \"foo\", \"n\": 7600, \"time\": 0.0007252693176269531}, {\"method\": \"loop\", \"foo\": \"foo\", \"n\": 8100, \"time\": 0.0007646083831787109}, {\"method\": \"loop\", \"foo\": \"foo\", \"n\": 8600, \"time\": 0.0008444786071777344}, {\"method\": \"loop\", \"foo\": \"foo\", \"n\": 9100, \"time\": 0.0008955001831054688}, {\"method\": \"loop\", \"foo\": \"foo\", \"n\": 9600, \"time\": 0.0009348392486572266}, {\"method\": \"loop\", \"foo\": \"foo_IO\", \"n\": 100, \"time\": 0.03452658653259277}, {\"method\": \"loop\", \"foo\": \"foo_IO\", \"n\": 600, \"time\": 0.13176274299621582}, {\"method\": \"loop\", \"foo\": \"foo_IO\", \"n\": 1100, \"time\": 0.19992351531982422}, {\"method\": \"loop\", \"foo\": \"foo_IO\", \"n\": 1600, \"time\": 0.30564355850219727}, {\"method\": \"loop\", \"foo\": \"foo_IO\", \"n\": 2100, \"time\": 0.4044971466064453}, {\"method\": \"loop\", \"foo\": \"foo_IO\", \"n\": 2600, \"time\": 0.5048646926879883}, {\"method\": \"loop\", \"foo\": \"foo_IO\", \"n\": 3100, \"time\": 0.5697422027587891}, {\"method\": \"loop\", \"foo\": \"foo_IO\", \"n\": 3600, \"time\": 0.6620967388153076}, {\"method\": \"loop\", \"foo\": \"foo_IO\", \"n\": 4100, \"time\": 0.7855203151702881}, {\"method\": \"loop\", \"foo\": \"foo_IO\", \"n\": 4600, \"time\": 0.8658318519592285}, {\"method\": \"loop\", \"foo\": \"foo_IO\", \"n\": 5100, \"time\": 0.9474542140960693}, {\"method\": \"loop\", \"foo\": \"foo_IO\", \"n\": 5600, \"time\": 1.0271046161651611}, {\"method\": \"loop\", \"foo\": \"foo_IO\", \"n\": 6100, \"time\": 1.1257355213165283}, {\"method\": \"loop\", \"foo\": \"foo_IO\", \"n\": 6600, \"time\": 1.2134268283843994}, {\"method\": \"loop\", \"foo\": \"foo_IO\", \"n\": 7100, \"time\": 1.3121287822723389}, {\"method\": \"loop\", \"foo\": \"foo_IO\", \"n\": 7600, \"time\": 1.412837028503418}, {\"method\": \"loop\", \"foo\": \"foo_IO\", \"n\": 8100, \"time\": 1.489161729812622}, {\"method\": \"loop\", \"foo\": \"foo_IO\", \"n\": 8600, \"time\": 1.5887274742126465}, {\"method\": \"loop\", \"foo\": \"foo_IO\", \"n\": 9100, \"time\": 1.6821928024291992}, {\"method\": \"loop\", \"foo\": \"foo_IO\", \"n\": 9600, \"time\": 1.76186203956604}, {\"method\": \"loopLog\", \"foo\": \"foo\", \"n\": 100, \"time\": 7.104873657226562e-05}, {\"method\": \"loopLog\", \"foo\": \"foo\", \"n\": 600, \"time\": 0.0005147457122802734}, {\"method\": \"loopLog\", \"foo\": \"foo\", \"n\": 1100, \"time\": 0.0010089874267578125}, {\"method\": \"loopLog\", \"foo\": \"foo\", \"n\": 1600, \"time\": 0.0015347003936767578}, {\"method\": \"loopLog\", \"foo\": \"foo\", \"n\": 2100, \"time\": 0.0020494461059570312}, {\"method\": \"loopLog\", \"foo\": \"foo\", \"n\": 2600, \"time\": 0.003158092498779297}, {\"method\": \"loopLog\", \"foo\": \"foo\", \"n\": 3100, \"time\": 0.0037822723388671875}, {\"method\": \"loopLog\", \"foo\": \"foo\", \"n\": 3600, \"time\": 0.003549814224243164}, {\"method\": \"loopLog\", \"foo\": \"foo\", \"n\": 4100, \"time\": 0.004529476165771484}, {\"method\": \"loopLog\", \"foo\": \"foo\", \"n\": 4600, \"time\": 0.004831075668334961}, {\"method\": \"loopLog\", \"foo\": \"foo\", \"n\": 5100, \"time\": 0.005237102508544922}, {\"method\": \"loopLog\", \"foo\": \"foo\", \"n\": 5600, \"time\": 0.006033658981323242}, {\"method\": \"loopLog\", \"foo\": \"foo\", \"n\": 6100, \"time\": 0.0066912174224853516}, {\"method\": \"loopLog\", \"foo\": \"foo\", \"n\": 6600, \"time\": 0.007192134857177734}, {\"method\": \"loopLog\", \"foo\": \"foo\", \"n\": 7100, \"time\": 0.0074253082275390625}, {\"method\": \"loopLog\", \"foo\": \"foo\", \"n\": 7600, \"time\": 0.008605241775512695}, {\"method\": \"loopLog\", \"foo\": \"foo\", \"n\": 8100, \"time\": 0.009444236755371094}, {\"method\": \"loopLog\", \"foo\": \"foo\", \"n\": 8600, \"time\": 0.010869264602661133}, {\"method\": \"loopLog\", \"foo\": \"foo\", \"n\": 9100, \"time\": 0.010828733444213867}, {\"method\": \"loopLog\", \"foo\": \"foo\", \"n\": 9600, \"time\": 0.011426925659179688}, {\"method\": \"nestedLoop\", \"foo\": \"foo\", \"n\": 100, \"time\": 0.0008561611175537109}, {\"method\": \"nestedLoop\", \"foo\": \"foo\", \"n\": 600, \"time\": 0.028378963470458984}, {\"method\": \"nestedLoop\", \"foo\": \"foo\", \"n\": 1100, \"time\": 0.09735894203186035}, {\"method\": \"nestedLoop\", \"foo\": \"foo\", \"n\": 1600, \"time\": 0.20671296119689941}, {\"method\": \"nestedLoop\", \"foo\": \"foo\", \"n\": 2100, \"time\": 0.3561441898345947}, {\"method\": \"nestedLoop\", \"foo\": \"foo\", \"n\": 2600, \"time\": 0.5475709438323975}, {\"method\": \"nestedLoop\", \"foo\": \"foo\", \"n\": 3100, \"time\": 0.7752246856689453}, {\"method\": \"nestedLoop\", \"foo\": \"foo\", \"n\": 3600, \"time\": 1.032026767730713}, {\"method\": \"nestedLoop\", \"foo\": \"foo\", \"n\": 4100, \"time\": 1.3458671569824219}, {\"method\": \"nestedLoop\", \"foo\": \"foo\", \"n\": 4600, \"time\": 1.6951260566711426}, {\"method\": \"nestedLoop\", \"foo\": \"foo\", \"n\": 5100, \"time\": 2.0965874195098877}, {\"method\": \"nestedLoop\", \"foo\": \"foo\", \"n\": 5600, \"time\": 2.534797191619873}, {\"method\": \"nestedLoop\", \"foo\": \"foo\", \"n\": 6100, \"time\": 2.9603023529052734}, {\"method\": \"nestedLoop\", \"foo\": \"foo\", \"n\": 6600, \"time\": 3.485043525695801}, {\"method\": \"nestedLoop\", \"foo\": \"foo\", \"n\": 7100, \"time\": 3.973540782928467}, {\"method\": \"nestedLoop\", \"foo\": \"foo\", \"n\": 7600, \"time\": 4.570703029632568}, {\"method\": \"nestedLoop\", \"foo\": \"foo\", \"n\": 8100, \"time\": 5.3797667026519775}, {\"method\": \"nestedLoop\", \"foo\": \"foo\", \"n\": 8600, \"time\": 5.93502140045166}, {\"method\": \"nestedLoop\", \"foo\": \"foo\", \"n\": 9100, \"time\": 6.633058786392212}, {\"method\": \"nestedLoop\", \"foo\": \"foo\", \"n\": 9600, \"time\": 7.320724725723267}]}}, {\"mode\": \"vega-lite\"});\n",
       "</script>"
      ],
      "text/plain": [
       "alt.Chart(...)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import altair as alt\n",
    "alt.renderers.enable('default')\n",
    "import pandas as pd\n",
    "\n",
    "# display all methods:\n",
    "# convert the <results> array to a pandas dataframe (a table-like structure)\n",
    "resultsPD = pd.DataFrame(results, columns=['method', 'foo', 'n', 'time'])\n",
    "\n",
    "# map the columns of resultsPD to x, y, and color\n",
    "alt.Chart(resultsPD).transform_calculate(\n",
    "    cat=\"datum.method + ' using ' + datum.foo\"\n",
    ").mark_line().encode(\n",
    "    x='n',\n",
    "    y=alt.Y('time', title='Time [sec]'),\n",
    "    color=alt.Y('cat:N', title='Algorithm'),\n",
    ").interactive()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>method</th>\n",
       "      <th>foo</th>\n",
       "      <th>n</th>\n",
       "      <th>time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>loop</td>\n",
       "      <td>foo</td>\n",
       "      <td>100</td>\n",
       "      <td>0.000011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>loop</td>\n",
       "      <td>foo</td>\n",
       "      <td>600</td>\n",
       "      <td>0.000054</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>loop</td>\n",
       "      <td>foo</td>\n",
       "      <td>1100</td>\n",
       "      <td>0.000099</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>loop</td>\n",
       "      <td>foo</td>\n",
       "      <td>1600</td>\n",
       "      <td>0.000147</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>loop</td>\n",
       "      <td>foo</td>\n",
       "      <td>2100</td>\n",
       "      <td>0.000255</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>nestedLoop</td>\n",
       "      <td>foo</td>\n",
       "      <td>7600</td>\n",
       "      <td>4.570703</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>nestedLoop</td>\n",
       "      <td>foo</td>\n",
       "      <td>8100</td>\n",
       "      <td>5.379767</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <td>nestedLoop</td>\n",
       "      <td>foo</td>\n",
       "      <td>8600</td>\n",
       "      <td>5.935021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>nestedLoop</td>\n",
       "      <td>foo</td>\n",
       "      <td>9100</td>\n",
       "      <td>6.633059</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>nestedLoop</td>\n",
       "      <td>foo</td>\n",
       "      <td>9600</td>\n",
       "      <td>7.320725</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>80 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        method  foo     n      time\n",
       "0         loop  foo   100  0.000011\n",
       "1         loop  foo   600  0.000054\n",
       "2         loop  foo  1100  0.000099\n",
       "3         loop  foo  1600  0.000147\n",
       "4         loop  foo  2100  0.000255\n",
       "..         ...  ...   ...       ...\n",
       "75  nestedLoop  foo  7600  4.570703\n",
       "76  nestedLoop  foo  8100  5.379767\n",
       "77  nestedLoop  foo  8600  5.935021\n",
       "78  nestedLoop  foo  9100  6.633059\n",
       "79  nestedLoop  foo  9600  7.320725\n",
       "\n",
       "[80 rows x 4 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# display the data as a table:\n",
    "resultsPD"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "feel free to ignore the following..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# helper to be able to tun SQL against pandas data frames:\n",
    "import pandasql as pdsql\n",
    "pysql = lambda q: pdsql.sqldf(q, globals())\n",
    "import sqlparse\n",
    "\n",
    "def queryFrame(q, _display=False):\n",
    "    result = pysql(q)\n",
    "    if _display:\n",
    "        display(result)\n",
    "    return result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>n</th>\n",
       "      <th>improvementFactor</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100</td>\n",
       "      <td>3218.111111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>600</td>\n",
       "      <td>2423.916667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1100</td>\n",
       "      <td>2010.887290</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1600</td>\n",
       "      <td>2084.491057</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2100</td>\n",
       "      <td>1585.592523</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2600</td>\n",
       "      <td>2086.262069</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>3100</td>\n",
       "      <td>1952.346405</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>3600</td>\n",
       "      <td>1643.215976</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>4100</td>\n",
       "      <td>1750.643464</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>4600</td>\n",
       "      <td>1774.089888</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>5100</td>\n",
       "      <td>1961.456565</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>5600</td>\n",
       "      <td>1830.071793</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>6100</td>\n",
       "      <td>1715.102434</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>6600</td>\n",
       "      <td>1877.344522</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>7100</td>\n",
       "      <td>1946.751680</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>7600</td>\n",
       "      <td>1948.017094</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>8100</td>\n",
       "      <td>1947.613658</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>8600</td>\n",
       "      <td>1881.311688</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>9100</td>\n",
       "      <td>1878.495208</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>9600</td>\n",
       "      <td>1884.668452</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       n  improvementFactor\n",
       "0    100        3218.111111\n",
       "1    600        2423.916667\n",
       "2   1100        2010.887290\n",
       "3   1600        2084.491057\n",
       "4   2100        1585.592523\n",
       "5   2600        2086.262069\n",
       "6   3100        1952.346405\n",
       "7   3600        1643.215976\n",
       "8   4100        1750.643464\n",
       "9   4600        1774.089888\n",
       "10  5100        1961.456565\n",
       "11  5600        1830.071793\n",
       "12  6100        1715.102434\n",
       "13  6600        1877.344522\n",
       "14  7100        1946.751680\n",
       "15  7600        1948.017094\n",
       "16  8100        1947.613658\n",
       "17  8600        1881.311688\n",
       "18  9100        1878.495208\n",
       "19  9600        1884.668452"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# calculate performance difference of different methods as a facor:\n",
    "improvementFactor = queryFrame(\"\"\"\n",
    "WITH justfoo AS (\n",
    "    SELECT *\n",
    "    FROM resultsPD\n",
    "    WHERE method='loop' AND foo='foo'),\n",
    "justRead AS (\n",
    "    SELECT *\n",
    "    FROM resultsPD\n",
    "    WHERE method='loop' AND foo='foo_IO')\n",
    "\n",
    "SELECT L.n, R.time/L.time as improvementFactor\n",
    "FROM justfoo L JOIN justRead R\n",
    "ON L.n==R.n\n",
    ";\n",
    "\"\"\", True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "<div id=\"altair-viz-dcfed7ba9090438c85a831b3db32fb76\"></div>\n",
       "<script type=\"text/javascript\">\n",
       "  (function(spec, embedOpt){\n",
       "    let outputDiv = document.currentScript.previousElementSibling;\n",
       "    if (outputDiv.id !== \"altair-viz-dcfed7ba9090438c85a831b3db32fb76\") {\n",
       "      outputDiv = document.getElementById(\"altair-viz-dcfed7ba9090438c85a831b3db32fb76\");\n",
       "    }\n",
       "    const paths = {\n",
       "      \"vega\": \"https://cdn.jsdelivr.net/npm//vega@5?noext\",\n",
       "      \"vega-lib\": \"https://cdn.jsdelivr.net/npm//vega-lib?noext\",\n",
       "      \"vega-lite\": \"https://cdn.jsdelivr.net/npm//vega-lite@4.8.1?noext\",\n",
       "      \"vega-embed\": \"https://cdn.jsdelivr.net/npm//vega-embed@6?noext\",\n",
       "    };\n",
       "\n",
       "    function loadScript(lib) {\n",
       "      return new Promise(function(resolve, reject) {\n",
       "        var s = document.createElement('script');\n",
       "        s.src = paths[lib];\n",
       "        s.async = true;\n",
       "        s.onload = () => resolve(paths[lib]);\n",
       "        s.onerror = () => reject(`Error loading script: ${paths[lib]}`);\n",
       "        document.getElementsByTagName(\"head\")[0].appendChild(s);\n",
       "      });\n",
       "    }\n",
       "\n",
       "    function showError(err) {\n",
       "      outputDiv.innerHTML = `<div class=\"error\" style=\"color:red;\">${err}</div>`;\n",
       "      throw err;\n",
       "    }\n",
       "\n",
       "    function displayChart(vegaEmbed) {\n",
       "      vegaEmbed(outputDiv, spec, embedOpt)\n",
       "        .catch(err => showError(`Javascript Error: ${err.message}<br>This usually means there's a typo in your chart specification. See the javascript console for the full traceback.`));\n",
       "    }\n",
       "\n",
       "    if(typeof define === \"function\" && define.amd) {\n",
       "      requirejs.config({paths});\n",
       "      require([\"vega-embed\"], displayChart, err => showError(`Error loading script: ${err.message}`));\n",
       "    } else if (typeof vegaEmbed === \"function\") {\n",
       "      displayChart(vegaEmbed);\n",
       "    } else {\n",
       "      loadScript(\"vega\")\n",
       "        .then(() => loadScript(\"vega-lite\"))\n",
       "        .then(() => loadScript(\"vega-embed\"))\n",
       "        .catch(showError)\n",
       "        .then(() => displayChart(vegaEmbed));\n",
       "    }\n",
       "  })({\"config\": {\"view\": {\"continuousWidth\": 400, \"continuousHeight\": 300}}, \"data\": {\"name\": \"data-63073c303570dffa2fa4a9f23e41d52c\"}, \"mark\": \"line\", \"encoding\": {\"x\": {\"type\": \"quantitative\", \"field\": \"n\"}, \"y\": {\"type\": \"quantitative\", \"field\": \"improvementFactor\", \"title\": \"improvement factor\"}}, \"$schema\": \"https://vega.github.io/schema/vega-lite/v4.8.1.json\", \"datasets\": {\"data-63073c303570dffa2fa4a9f23e41d52c\": [{\"n\": 100, \"improvementFactor\": 3218.1111111111113}, {\"n\": 600, \"improvementFactor\": 2423.9166666666665}, {\"n\": 1100, \"improvementFactor\": 2010.8872901678658}, {\"n\": 1600, \"improvementFactor\": 2084.491056910569}, {\"n\": 2100, \"improvementFactor\": 1585.592523364486}, {\"n\": 2600, \"improvementFactor\": 2086.2620689655173}, {\"n\": 3100, \"improvementFactor\": 1952.3464052287582}, {\"n\": 3600, \"improvementFactor\": 1643.215976331361}, {\"n\": 4100, \"improvementFactor\": 1750.643464399575}, {\"n\": 4600, \"improvementFactor\": 1774.0898876404494}, {\"n\": 5100, \"improvementFactor\": 1961.4565646594274}, {\"n\": 5600, \"improvementFactor\": 1830.071792693288}, {\"n\": 6100, \"improvementFactor\": 1715.1024337086815}, {\"n\": 6600, \"improvementFactor\": 1877.3445223164883}, {\"n\": 7100, \"improvementFactor\": 1946.7516802263883}, {\"n\": 7600, \"improvementFactor\": 1948.017094017094}, {\"n\": 8100, \"improvementFactor\": 1947.6136576239476}, {\"n\": 8600, \"improvementFactor\": 1881.3116883116884}, {\"n\": 9100, \"improvementFactor\": 1878.4952076677316}, {\"n\": 9600, \"improvementFactor\": 1884.668451925529}]}}, {\"mode\": \"vega-lite\"});\n",
       "</script>"
      ],
      "text/plain": [
       "alt.Chart(...)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# draw the improvement factor:\n",
    "# this graph shows the \"slow donw\" of the method without I/O over the one with I/O\n",
    "alt.Chart(improvementFactor).mark_line().encode(\n",
    "    x='n',\n",
    "    y=alt.Y('improvementFactor', title='improvement factor')\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}