query_performance_test_script.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "af8d32d4-9c88-4bb2-9e08-2e3adb1a920b",
   "metadata": {},
   "source": [
    "# TPCDS: Query Performance Test Script"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "b008de62-98a8-4de9-a4cd-003e80e6f7e8",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sys, os\n",
    "import psycopg2\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "from psycopg2 import Error\n",
    "from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT\n",
    "from datetime import datetime\n",
    "from IPython.display import clear_output"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "f1c703bb-49b5-46fc-b585-5eb778a9f272",
   "metadata": {},
   "outputs": [],
   "source": [
    "# scale factor being tested\n",
    "sf = 'sf_1'\n",
    "\n",
    "# set up connection variables\n",
    "db_host = \"localhost\"\n",
    "db_port = \"5432\"\n",
    "db_user = \"postgres\"\n",
    "db_pass = \"password\"\n",
    "db_name = \"tpcds\"\n",
    "\n",
    "# function to connect with postgres\n",
    "def connect_postgres(db_host, db_port, db_user, db_pass, db_name):\n",
    "    try:\n",
    "        # Connect to an existing database\n",
    "        connection = psycopg2.connect(host = db_host,\n",
    "                                      port = db_port,\n",
    "                                      user = db_user,\n",
    "                                      password = db_pass,\n",
    "                                      database = db_name)\n",
    "        # Set auto-commit\n",
    "        connection.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT);\n",
    "        # Create a cursor to perform database operations\n",
    "        cur = connection.cursor()\n",
    "        # Print PostgreSQL details\n",
    "        print(\"PostgreSQL server information\")\n",
    "        print(connection.get_dsn_parameters(), \"\\n\")\n",
    "        # Executing a SQL query\n",
    "        cur.execute(\"SELECT version();\")\n",
    "        # Fetch result\n",
    "        record = cur.fetchone()\n",
    "        print(\"You are connected to - \", record, \"\\n\")\n",
    "\n",
    "    except (Exception, Error) as error:\n",
    "        print(\"Error while connecting to PostgreSQL\", error)\n",
    "    else:\n",
    "        return cur"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "1e736ce3-98e4-4d55-b26e-feae085ecd89",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "PostgreSQL server information\n",
      "{'user': 'postgres', 'dbname': 'tpcds', 'host': 'localhost', 'port': '5432', 'tty': '', 'options': '', 'sslmode': 'prefer', 'sslcompression': '0', 'gssencmode': 'prefer', 'krbsrvname': 'postgres', 'target_session_attrs': 'any'} \n",
      "\n",
      "You are connected to -  ('PostgreSQL 14.5, compiled by Visual C++ build 1914, 64-bit',) \n",
      "\n"
     ]
    }
   ],
   "source": [
    "# connect to postgres\n",
    "cur = connect_postgres(db_host, db_port, db_user, db_pass, db_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "3e7a51e5-6f5c-4cdf-ac2e-4b47a8e5b54d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "C:\\Users\\ahmad\\Desktop\\tpcds_git\\tpc-benchmark\\all_queries\\optimized_queries_final\n"
     ]
    }
   ],
   "source": [
    "# get dir path\n",
    "\n",
    "path = os.getcwd() + '\\\\all_queries\\\\optimized_queries_final'\n",
    "files = os.listdir(path)\n",
    "print(path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "1afcff78-df73-4fdd-b842-80c875e7e80f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# function to get full abosolute path files in directory\n",
    "\n",
    "def get_absolute_path(d):\n",
    "    return [os.path.join(d, f) for f in os.listdir(d)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "d93031fe-af21-4094-9037-048a3e7ff5cd",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total files: 99\n",
      "First few files...\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "['C:/Users/ahmad/Desktop/tpcds_git/tpc-benchmark/all_queries/optimized_queries_final/query-01.sql',\n",
       " 'C:/Users/ahmad/Desktop/tpcds_git/tpc-benchmark/all_queries/optimized_queries_final/query-02.sql',\n",
       " 'C:/Users/ahmad/Desktop/tpcds_git/tpc-benchmark/all_queries/optimized_queries_final/query-03.sql',\n",
       " 'C:/Users/ahmad/Desktop/tpcds_git/tpc-benchmark/all_queries/optimized_queries_final/query-04.sql',\n",
       " 'C:/Users/ahmad/Desktop/tpcds_git/tpc-benchmark/all_queries/optimized_queries_final/query-05.sql']"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get full abosolute path files in directory\n",
    "\n",
    "files_abs_path = [p.replace('\\\\', '/') for p in get_absolute_path(path)]\n",
    "print(\"Total files:\", len(files_abs_path))\n",
    "print(\"First few files...\")\n",
    "files_abs_path[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "bab192fd-3d39-4160-86ae-d2ea2c282f25",
   "metadata": {},
   "outputs": [],
   "source": [
    "# setup dataframe for recording query execution run times\n",
    "\n",
    "query_name_list = []\n",
    "\n",
    "for i in range(len(files)):\n",
    "    query_name_list.append(\"Q\" + files[i][-6:-4])\n",
    "    \n",
    "query_name_dict = {'query':query_name_list}\n",
    "exec_details_df = pd.DataFrame(query_name_dict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "31cfb8dd-64c3-4809-b52a-56febc2c9f0b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Overall Run Start: 22/10/2022 20:55:01\n"
     ]
    }
   ],
   "source": [
    "# get the date-time before all 99 queries have run (with iterations if chosen)\n",
    "\n",
    "run_start_default = datetime.now()\n",
    "# dd/mm/YY H:M:S\n",
    "run_start = run_start_default.strftime(\"%d/%m/%Y %H:%M:%S\")\n",
    "print(\"Overall Run Start:\", run_start)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "52809ed8-270c-4678-aaa2-65031ba3c523",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Iteration 3\n",
      "\n",
      "Q01: Success, Execution Time: 0.16s\n",
      "Q02: Success, Execution Time: 0.47s\n",
      "Q03: Success, Execution Time: 0.03s\n",
      "Q04: Success, Execution Time: 45.72s\n",
      "Q05: Success, Execution Time: 0.75s\n",
      "Q06: Success, Execution Time: 0.19s\n",
      "Q07: Success, Execution Time: 0.75s\n",
      "Q08: Success, Execution Time: 0.20s\n",
      "Q09: Success, Execution Time: 1.80s\n",
      "Q10: Success, Execution Time: 5.62s\n",
      "Q11: Success, Execution Time: 55.13s\n",
      "Q12: Success, Execution Time: 0.03s\n",
      "Q13: Success, Execution Time: 0.38s\n",
      "Q14: Success, Execution Time: 60.31s\n",
      "Q15: Success, Execution Time: 0.14s\n",
      "Q16: Success, Execution Time: 1.14s\n",
      "Q17: Success, Execution Time: 0.30s\n",
      "Q18: Success, Execution Time: 0.22s\n",
      "Q19: Success, Execution Time: 0.22s\n",
      "Q20: Success, Execution Time: 0.05s\n",
      "Q21: Success, Execution Time: 0.53s\n",
      "Q22: Success, Execution Time: 5.84s\n",
      "Q23: Success, Execution Time: 10.61s\n",
      "Q24: Success, Execution Time: 0.05s\n",
      "Q25: Success, Execution Time: 0.22s\n",
      "Q26: Success, Execution Time: 0.34s\n",
      "Q27: Success, Execution Time: 0.63s\n",
      "Q28: Success, Execution Time: 1.27s\n",
      "Q29: Success, Execution Time: 0.16s\n",
      "Q30: Success, Execution Time: 10.30s\n",
      "Q31: Success, Execution Time: 6.84s\n",
      "Q32: Success, Execution Time: 0.13s\n",
      "Q33: Success, Execution Time: 0.27s\n",
      "Q34: Success, Execution Time: 0.17s\n",
      "Q35: Success, Execution Time: 0.86s\n",
      "Q36: Success, Execution Time: 0.52s\n",
      "Q37: Success, Execution Time: 0.03s\n",
      "Q38: Success, Execution Time: 1.77s\n",
      "Q39: Success, Execution Time: 5.61s\n",
      "Q40: Success, Execution Time: 0.13s\n",
      "Q41: Success, Execution Time: 0.72s\n",
      "Q42: Success, Execution Time: 0.20s\n",
      "Q43: Success, Execution Time: 0.35s\n",
      "Q44: Success, Execution Time: 0.15s\n",
      "Q45: Success, Execution Time: 0.09s\n",
      "Q46: Success, Execution Time: 0.30s\n",
      "Q47: Success, Execution Time: 1.63s\n",
      "Q48: Success, Execution Time: 0.44s\n",
      "Q49: Success, Execution Time: 0.11s\n",
      "Q50: Success, Execution Time: 0.06s\n",
      "Q51: Success, Execution Time: 1.29s\n",
      "Q52: Success, Execution Time: 0.20s\n",
      "Q53: Success, Execution Time: 0.44s\n",
      "Q54: Success, Execution Time: 0.24s\n",
      "Q55: Success, Execution Time: 0.25s\n",
      "Q56: Success, Execution Time: 0.97s\n",
      "Q57: Success, Execution Time: 0.89s\n",
      "Q58: Success, Execution Time: 0.09s\n",
      "Q59: Success, Execution Time: 0.71s\n",
      "Q60: Success, Execution Time: 2.79s\n",
      "Q61: Success, Execution Time: 0.06s\n",
      "Q62: Success, Execution Time: 0.19s\n",
      "Q63: Success, Execution Time: 0.46s\n",
      "Q64: Success, Execution Time: 0.39s\n",
      "Q65: Success, Execution Time: 0.75s\n",
      "Q66: Success, Execution Time: 0.28s\n",
      "Q67: Success, Execution Time: 1.57s\n",
      "Q68: Success, Execution Time: 0.22s\n",
      "Q69: Success, Execution Time: 0.55s\n",
      "Q70: Success, Execution Time: 0.77s\n",
      "Q71: Success, Execution Time: 0.17s\n",
      "Q72: Success, Execution Time: 0.38s\n",
      "Q73: Success, Execution Time: 0.19s\n",
      "Q74: Success, Execution Time: 43.30s\n",
      "Q75: Success, Execution Time: 1.07s\n",
      "Q76: Success, Execution Time: 0.34s\n",
      "Q77: Success, Execution Time: 0.38s\n",
      "Q78: Success, Execution Time: 2.39s\n",
      "Q79: Success, Execution Time: 0.22s\n",
      "Q80: Success, Execution Time: 0.30s\n",
      "Q81: Success, Execution Time: 8.48s\n",
      "Q82: Success, Execution Time: 0.05s\n",
      "Q83: Success, Execution Time: 0.11s\n",
      "Q84: Success, Execution Time: 0.05s\n",
      "Q85: Success, Execution Time: 0.09s\n",
      "Q86: Success, Execution Time: 0.17s\n",
      "Q87: Success, Execution Time: 1.79s\n",
      "Q88: Success, Execution Time: 1.32s\n",
      "Q89: Success, Execution Time: 0.24s\n",
      "Q90: Success, Execution Time: 0.13s\n",
      "Q91: Success, Execution Time: 0.03s\n",
      "Q92: Success, Execution Time: 0.06s\n",
      "Q93: Success, Execution Time: 0.16s\n",
      "Q94: Success, Execution Time: 0.11s\n",
      "Q95: Success, Execution Time: 0.27s\n",
      "Q96: Success, Execution Time: 0.19s\n",
      "Q97: Success, Execution Time: 0.57s\n",
      "Q98: Success, Execution Time: 0.20s\n",
      "Q99: Success, Execution Time: 0.31s\n",
      "\n",
      "SF_1, Iteration 3, Total run time for the 99 queries: 0.08hr\n"
     ]
    }
   ],
   "source": [
    "# run all 99 queries in sequence, and multiple iterations if chosen\n",
    "# save query result table output\n",
    "# save query execution run time (for all iterations)\n",
    "\n",
    "q_errors = 0\n",
    "exec_details = []\n",
    "# choose number of iterations to run\n",
    "n_iterations = 3\n",
    "\n",
    "for i in range(1, n_iterations + 1):\n",
    "    \n",
    "    clear_output(wait = True)\n",
    "    print(f'Iteration {i}\\n')\n",
    "    q_index = 0\n",
    "    exec_details = []\n",
    "    iteration_start = datetime.now()\n",
    "    for sql_script in files_abs_path:\n",
    "\n",
    "        exec_start = datetime.now()\n",
    "        try:\n",
    "            cur.execute(\n",
    "                open(sql_script, \"r\").read()\n",
    "            )\n",
    "        except Exception as e:\n",
    "            q_errors += 1\n",
    "            outcome = \"Error\"\n",
    "        else:\n",
    "            outcome = \"Success\"\n",
    "\n",
    "        exec_end = datetime.now()\n",
    "        exec_run_time = \"{:.2f}\".format((exec_end - exec_start).total_seconds())\n",
    "        query_num = query_name_list[q_index]\n",
    "        print(f'{query_num}: Success, Execution Time: {exec_run_time}s')\n",
    "        exec_details.append(exec_run_time)\n",
    "        \n",
    "        # load table output to csv file (on first iteration only)\n",
    "        if i == 1:\n",
    "            df = pd.DataFrame(cur.fetchall(), columns = [desc[0] for desc in cur.description])\n",
    "            df.to_csv(f'performance_test/{sf}/{query_num}.csv', index = False)\n",
    "        else:\n",
    "            pass\n",
    "        \n",
    "        q_index += 1\n",
    "        \n",
    "    iteration_end = datetime.now()    \n",
    "    iteration_run_time = \"{:.2f}\".format(((iteration_end - iteration_start).total_seconds()) / 3600)\n",
    "    print(f'\\n{sf.upper()}, Iteration {i}, Total run time for the 99 queries: {iteration_run_time}hr')\n",
    "    \n",
    "    # append iteration execution details to dataframe\n",
    "    exec_details_df[f'exec_time_iter_{i}'] = np.array(exec_details)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "64668b17-7249-4548-adef-57abb20006a6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "We have a total of 0 queries with error\n"
     ]
    }
   ],
   "source": [
    "# check total amount of query errors\n",
    "\n",
    "print(f\"We have a total of {q_errors} queries with error\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "1819485d-62df-45c1-b091-075091f943d4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Overall Run End (with 3 iterations): 22/10/2022 21:10:25\n"
     ]
    }
   ],
   "source": [
    "# get the date-time after all 99 queries have run (with iterations if chosen)\n",
    "\n",
    "run_end_default = datetime.now()\n",
    "# dd/mm/YY H:M:S\n",
    "run_end = run_end_default.strftime(\"%d/%m/%Y %H:%M:%S\")\n",
    "print(f\"Overall Run End (with {n_iterations} iterations):\", run_end)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "f217c0fd-d1ed-4a30-b372-2be45fd7172a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total run time for the 99 queries (with 3 iterations): 0.26hr\n"
     ]
    }
   ],
   "source": [
    "# get the total run time (in hours) for all 99 queries to complete (with iterations if chosen)\n",
    "\n",
    "total_run_time = \"{:.2f}\".format(((run_end_default - run_start_default).total_seconds()) / 3600)\n",
    "print(f'Total run time for the 99 queries (with {n_iterations} iterations): {total_run_time}hr')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "f910ef6d-0070-4503-946f-0a7426d28cf6",
   "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>query</th>\n",
       "      <th>exec_time_iter_1</th>\n",
       "      <th>exec_time_iter_2</th>\n",
       "      <th>exec_time_iter_3</th>\n",
       "      <th>avg_exec_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Q01</td>\n",
       "      <td>0.64</td>\n",
       "      <td>0.16</td>\n",
       "      <td>0.16</td>\n",
       "      <td>0.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Q02</td>\n",
       "      <td>0.71</td>\n",
       "      <td>0.47</td>\n",
       "      <td>0.47</td>\n",
       "      <td>0.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Q03</td>\n",
       "      <td>0.27</td>\n",
       "      <td>0.03</td>\n",
       "      <td>0.03</td>\n",
       "      <td>0.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Q04</td>\n",
       "      <td>49.63</td>\n",
       "      <td>45.04</td>\n",
       "      <td>45.72</td>\n",
       "      <td>46.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Q05</td>\n",
       "      <td>0.82</td>\n",
       "      <td>0.72</td>\n",
       "      <td>0.75</td>\n",
       "      <td>0.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>Q95</td>\n",
       "      <td>0.25</td>\n",
       "      <td>0.27</td>\n",
       "      <td>0.27</td>\n",
       "      <td>0.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>Q96</td>\n",
       "      <td>0.17</td>\n",
       "      <td>0.16</td>\n",
       "      <td>0.19</td>\n",
       "      <td>0.17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>Q97</td>\n",
       "      <td>0.58</td>\n",
       "      <td>0.58</td>\n",
       "      <td>0.57</td>\n",
       "      <td>0.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>Q98</td>\n",
       "      <td>0.20</td>\n",
       "      <td>0.22</td>\n",
       "      <td>0.20</td>\n",
       "      <td>0.21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>Q99</td>\n",
       "      <td>0.33</td>\n",
       "      <td>0.31</td>\n",
       "      <td>0.31</td>\n",
       "      <td>0.32</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>99 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   query exec_time_iter_1 exec_time_iter_2 exec_time_iter_3  avg_exec_time\n",
       "0    Q01             0.64             0.16             0.16           0.32\n",
       "1    Q02             0.71             0.47             0.47           0.55\n",
       "2    Q03             0.27             0.03             0.03           0.11\n",
       "3    Q04            49.63            45.04            45.72          46.80\n",
       "4    Q05             0.82             0.72             0.75           0.76\n",
       "..   ...              ...              ...              ...            ...\n",
       "94   Q95             0.25             0.27             0.27           0.26\n",
       "95   Q96             0.17             0.16             0.19           0.17\n",
       "96   Q97             0.58             0.58             0.57           0.58\n",
       "97   Q98             0.20             0.22             0.20           0.21\n",
       "98   Q99             0.33             0.31             0.31           0.32\n",
       "\n",
       "[99 rows x 5 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# full details on query execution times (including iterations & average)\n",
    "# load execution details to csv\n",
    "\n",
    "exec_details_df['avg_exec_time'] = np.round(exec_details_df.iloc[:, 1:].apply(pd.to_numeric).mean(axis = 1), 2)\n",
    "exec_details_df.to_csv(f'performance_test/{sf}/exec_time_details_{sf}.csv', index = False)\n",
    "exec_details_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "ba54f295-b4df-4eae-87ce-5cb19774b69d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# close connection to db\n",
    "\n",
    "cur.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "23cf3748-16e4-4621-8e52-174008329d3b",
   "metadata": {},
   "source": [
    "#### End of script."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}