15/66
\begin{frame}
  \frametitle{Dynamic SQL: Optimising Applications}
  
  \begin{goal}{Improving Performance of Applications}
  \begin{itemize}
    \pause\smallskip
    \item \emph{Connection pooling}: 
      \begin{itemize}
        \item keep DB connection open, reduces latency
      \end{itemize}
    \pause\smallskip
    \item \emph{Prepared statements}:
      \begin{itemize}
        \item SQL calls that are repeated often
        \item allows driver to optimise queries (precompiled by DBMS) 
        \item in JDBC created with \emph{Connection.prepareStatement()}
        \item allows parameters: \sql{select * from products where id = ?}
      \end{itemize}
    \pause\smallskip
    \item \emph{Stored procedures} to reduce \#query roundtrips
      \begin{itemize}
        \item written in DB-specific language, not portable \error
        \item in JDBC accessed with \emph{Connection.prepareCall()}
      \end{itemize}
    \pause\smallskip
    \item Use a \emph{driver} that is \emph{bulk}-transfer optimised
      \begin{itemize}
        \item when retrieving large result sets
        \item driver can send several tuples in a single network packet
      \end{itemize}
  \end{itemize}
  \end{goal}
\end{frame}