Швидкі запити до PostgreSQL

Функції

PGresult *PQprepare (PGconn *conn,
                     const char *stmtName,
                     const char *query,
                     int nParams,
                     const Oid *paramTypes) ;

• conn — вказівник на дескриптор підключення до бази, створений за допомогою функції PQconnectdb;
• stmtName — ідентифікатор підготовленого запиту в якості рядка символів, за яким буде ідентифікуватись потрібний запит; якщо даний параметр пустий — буде створений неназваний підготовлений запит або замінений існуючий; якщо даний ідентифікатор вже створено в системі — функція повертає помилку;
• query — запит, який буде підготовляти PostgreSQL для множинного виконання;
• nParams — кількість параметрів, які будуть передаватись підготовленому запиту; усі параметри, як буде показано в прикладі, потрібно вказувати у формі $1..$N для параметрів під номером 1 до N у самому запиті (змінна query);
• paramTypes — типи параметрів у формі їх OID; цей параметр може бути 0 для автоматичного присвоювання їм текстового формату.

PGresult *PQexecPrepared (PGconn *conn,
                          const char *stmtName,
                          int nParams,
                          const char * const *paramValues,
                          const int *paramLengths,
                          const int *paramFormats,
                          int resultFormat) ;

• conn — які і в усіх інших функціях, вказівник на дескриптор підключення до бази, створений за допомогою функції PQconnectdb;
• stmtName — ідентифікатор необхідного підготовленого запиту в якості рядка символів;
• nParams — кількість параметрів, які передаються даному запиту;
• paramValues — значення параметрів, які передаються підготовленому запиту;
• paramLengths — довжина кожного рядка символів, які представляють значення параметрів запиту; ці значення ігноруються для параметрів NULL і текстових параметрів; даний вказівник на масив може мати значення NULL, якщо запиту не передаються бінарні значення;
• paramFromats — значення масиву на який вказує даний вказівник, пояснюють тип параметрів, які передаються підготовленого запиту: 0 — для тексту, і 1 — для бінарного значення;
• resultFormat — значення даного параметра функції вказує чи результат виконання запиту повинен бути в текстовому (0) чи в бінарному (1) форматі.

Програма

#include <iostream> /* I/O */
#include <string> /* string */
#include <libpq-fe.h> /* функції для PostgreSQL */
using namespace std ; /* використовуємо простір імен std */


/* головна функція програми */
int main (int argc, char** argv) 
{
    /* дескриптор підключення PostgreSQL  */
    PGconn * connection ; 
    
    /* інформація про підключення */
    string conn_info = "user=postgres password=MyBigSecret "
                       "dbname=pgtestdb port=5432 host=localhost" ;
                       
    /* буфер повідомлення про помилку */
    string err_msg ;
    
    /* виводимо повідомлення про спробу підключення */
    cout << "Trying to connect to database\n" ;
    
    /* підключення і створення нового дескриптору */
    connection = PQconnectdb(conn_info.c_str()) ;
    
    /* перевіряємо, чи все в порядку */
    if (PQstatus(connection)!=CONNECTION_OK)
    {
        /* якщо виникли проблеми - виводимо повідомлення */
        err_msg = PQerrorMessage (connection) ;
        cout << "Fail to connect to database: " << err_msg << endl ;
        
        /* і завершуємо виконання програми */
        return 0 ;
    }
    
    /* виводимо повідомлення про успішне підключення */
    cout << "Connected\n" ;
    
    /* змінна-дескриптор виконання запиту */
    PGresult * result ;
    
    /* створюємо ідентифікатор підготовленого запиту */
    string stmt1n = "query_one" ;
    
    /* створюємо підготовлений запит */
    result = PQprepare (connection,
                        stmt1n.c_str (),
                        "SELECT * FROM testtable ;",
                        0,
                        NULL) ;
    
    /* перевіряємо успішність виконання функції */
    if (PQresultStatus(result)!=PGRES_COMMAND_OK && 
        PQresultStatus(result)!=PGRES_TUPLES_OK)
    {
        /* якщо сталась помилка отримуємо текст помилки */
        err_msg = PQerrorMessage (connection) ;
        /* виводимо її на екран*/
        cout << "Fail to execute query:" << err_msg << endl ;
        
        /* завершуємо виконання програми */
        return 0 ;
    }
    
    /* очищуємо результат */
    PQclear (result) ;
    
    /* довжина параметрів */
    int pLen = 0 ;
    /* формат параметрів */
    int pFor = 0 ;
    
    /* виконуємо підготовлений запит */
    result = PQexecPrepared (connection,
                             stmt1n.c_str (),
                             0,
                             NULL,
                             &pLen,
                             &pFor,
                             0) ;
    
    /* перевіряємо успішність виконання запиту */
    if (PQresultStatus(result)!=PGRES_COMMAND_OK && 
        PQresultStatus(result)!=PGRES_TUPLES_OK)
    {
        /* якщо сталась помилка отримуємо текст помилки */
        err_msg = PQerrorMessage (connection) ;
        
        /* виводимо її на екран*/
        cout << "Fail to execute query:" << err_msg << endl ;
        
        /* завершуємо виконання програми */
        return 0 ;
    }
    
    /* виводимо повідомлення про те, що наступні дані - це */
    /* результат виконання запиту */
    cout << "Table rows:\n" ;
    
    /* виводимо дані полів відділяючи їх символом "|" */
    for (unsigned int iter=0; iter<PQntuples(result); ++iter)
    {
        for (unsigned int jter=0; jter<PQnfields(result); ++jter)
        {
            cout << PQgetvalue (result, iter, jter) << "|" ;
        }
        
        /* новий рядок відділяє записи */
        cout << endl ;
    }
    
    /* виводимо повідомлення про закриття підключення до PostgreSQL */
    cout << "Closing connection\n" ;
    
    /* очищаємо дескриптор виконання запиту і його дані */
    PQclear (result) ;
    
    /* закриваємо підключення до СКБД */
    PQfinish (connection) ;
 
    /* завершуємо виконання програми */   
    return 0 ;
}

#include <iostream> /* I/O */
#include <string> /* string */
#include <libpq-fe.h> /* функції для PostgreSQL */
using namespace std ; /* використовуємо простір імен std */


/* головна функція програми */
int main (int argc, char** argv) 
{
    /* дескриптор підключення PostgreSQL  */
    PGconn * connection ; 
    
    /* інформація про підключення */
    string conn_info = "user=postgres password=MyBigSecret "
                       "dbname=pgtestdb port=5432 host=localhost" ;
                       
    /* буфер повідомлення про помилку */
    string err_msg ;
    
    /* виводимо повідомлення про спробу підключення */
    cout << "Trying to connect to database\n" ;
    
    /* підключення і створення нового дескриптору */
    connection = PQconnectdb(conn_info.c_str()) ;
    
    /* перевіряємо, чи все в порядку */
    if (PQstatus(connection)!=CONNECTION_OK)
    {
        /* якщо виникли проблеми - виводимо повідомлення */
        err_msg = PQerrorMessage (connection) ;
        cout << "Fail to connect to database: " << err_msg << endl ;
        
        /* і завершуємо виконання програми */
        return 0 ;
    }
    
    /* виводимо повідомлення про успішне підключення */
    cout << "Connected\n" ;
    
    /* змінна-дескриптор виконання запиту */
    PGresult * result ;
    
    /* створюємо ідентифікатор підготовленого запиту */
    string stmt1n = "query_one" ;
    
    /* створюємо підготовлений запит */
    result = PQprepare (connection,
                        stmt1n.c_str (),
                        "SELECT * FROM testtable ;",
                        0,
                        NULL) ;
    
    /* перевіряємо успішність виконання функції */
    if (PQresultStatus(result)!=PGRES_COMMAND_OK && 
        PQresultStatus(result)!=PGRES_TUPLES_OK)
    {
        /* якщо сталась помилка отримуємо текст помилки */
        err_msg = PQerrorMessage (connection) ;
        /* виводимо її на екран*/
        cout << "Fail to execute query:" << err_msg << endl ;
        
        /* завершуємо виконання програми */
        return 0 ;
    }
    
    /* очищуємо результат */
    PQclear (result) ;
    
    /* довжина параметрів */
    int pLen = 0 ;
    /* формат параметрів */
    int pFor = 0 ;
    
    /* виконуємо підготовлений запит */
    result = PQexecPrepared (connection,
                             stmt1n.c_str (),
                             0,
                             NULL,
                             &pLen,
                             &pFor,
                             0) ;
    
    /* перевіряємо успішність виконання запиту */
    if (PQresultStatus(result)!=PGRES_COMMAND_OK && 
        PQresultStatus(result)!=PGRES_TUPLES_OK)
    {
        /* якщо сталась помилка отримуємо текст помилки */
        err_msg = PQerrorMessage (connection) ;
        
        /* виводимо її на екран*/
        cout << "Fail to execute query:" << err_msg << endl ;
        
        /* завершуємо виконання програми */
        return 0 ;
    }
    
    /* виводимо повідомлення про те, що наступні дані - це */
    /* результат виконання запиту */
    cout << "Table rows:\n" ;
    
    /* виводимо дані полів відділяючи їх символом "|" */
    for (unsigned int iter=0; iter<PQntuples(result); ++iter)
    {
        for (unsigned int jter=0; jter<PQnfields(result); ++jter)
        {
            cout << PQgetvalue (result, iter, jter) << "|" ;
        }
        
        /* новий рядок відділяє записи */
        cout << endl ;
    }
    
    /* виводимо повідомлення про закриття підключення до PostgreSQL */
    cout << "Closing connection\n" ;
    
    /* очищаємо дескриптор виконання запиту і його дані */
    PQclear (result) ;
    
    /* закриваємо підключення до СКБД */
    PQfinish (connection) ;
 
    /* завершуємо виконання програми */   
    return 0 ;
}

Порівняння швидкості виконання запитів

#include <iostream> /* I/O */
#include <string> /* string */
#include <libpq-fe.h> /* функції для PostgreSQL */
#include <functional>
#include <numeric>
#include <algorithm>
#include <sys/time.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <math.h>
#include <vector>
#include <fstream>
using namespace std ; /* використовуємо простір імен std */

/* кількість перевірок кожного з інтерфейсів */
const unsigned int TIMES = 10000 ;

/* Функція яка повертає секунди разом з мікросекундами */
/* 1 c = 1.0 * 10+E6 мс*/
double get_secs () ;

/* перевірити запит query times разів і записати час виконання в delta_times 
** за допомогою простої функції PQexec */
int check_pqexec (PGconn* connection, 
                  const char* query, 
                  unsigned int times,
                  vector <double>& delta_times) ;

/* перевірити запит query times разів і записати час виконання в delta_times 
** за допомогою складного інтерфейсу libpq  */
int check_pqprepeared (PGconn* connection, 
                       const char* query, 
                       unsigned int times,
                       vector <double>& delta_times) ;
                       
/* функція, яка зберігає результати тестів в файлі "result.html"
** в зручному табличному представленні */
void save_result (vector<double>& simple, vector<double>& complex) ;

/* головна функція програми */
int main (int argc, char** argv) 
{
    /* дескриптор підключення PostgreSQL для простого інтерфейсу */
    PGconn * cfsimple ;
    /* дескриптор підключення PostgreSQL для складного інтерфейсу */
    PGconn * cfcomplex ;
    
    /* інформація про підключення */
    string conn_info = "user=postgres password=MyBigSecret "
                       "dbname=pgtestdb port=5432 host=localhost" ;
                       
    /* буфер повідомлення про помилку */
    string err_msg ;
    
    /* вектори, в яких будемо утримувати час роботи кожного з інтерфейсу */
    vector<double> dt_simple, dt_complex ;
    
    /* резервуємо пам'ять для TIMES перевірок і результатів */
    dt_simple.reserve  (TIMES) ;
    dt_complex.reserve (TIMES) ;
    
    string query = "SELECT * FROM testtable ;" ;
    
    /* виводимо повідомлення про спробу підключення */
    cout << "Trying to connect to database for simple interface\n" ;
    
    /* підключення і створення нового дескриптору */
    cfsimple  = PQconnectdb (conn_info.c_str()) ;
    cfcomplex = PQconnectdb (conn_info.c_str()) ;
    
    /* перевіряємо, чи все в порядку */
    if (PQstatus(cfsimple)!=CONNECTION_OK)
    {
        /* якщо виникли проблеми - виводимо повідомлення */
        err_msg = PQerrorMessage (cfsimple) ;
        cout << "Fail to connect to database: " << err_msg << endl ;
        
        /* і завершуємо виконання програми */
        return 0 ;
    }

    if (PQstatus(cfcomplex)!=CONNECTION_OK)
    {
        /* якщо виникли проблеми - виводимо повідомлення */
        err_msg = PQerrorMessage (cfcomplex) ;
        cout << "Fail to connect to database: " << err_msg << endl ;
        
        /* і завершуємо виконання програми */
        return 0 ;
    }
    
    cout << "Connected\n" ;
    
    /* перевіряємо простий інтерфейс */
    cout << "Checking simple...\n" ;
    if (check_pqexec (cfsimple, query.c_str(), TIMES, dt_simple)!=0)
    { return 0 ; }
    
    /* перевіряємо складний інтерфейс */
    cout << "Checking complex...\n" ;
    if (check_pqprepeared (cfcomplex, query.c_str(), TIMES, dt_complex)!=0)
    { return 0 ; }
    
    cout << "Saving result\n" ;
    
    /* зберігаємо результат */
    save_result (dt_simple, dt_complex) ;
    
    cout << "Finish\n" ;
    
    /* закриваємо підключення до СКБД */
    PQfinish (cfsimple) ;
    PQfinish (cfcomplex) ;
 
    /* завершуємо виконання програми */   
    return 0 ;
}

double get_secs ()
{
    static struct timeval value ;
    
    /* обнуляємо пам'ять структури */
    memset ((void*)&value, 0, sizeof (timeval)) ;

    /* отримуємо значення секунд і мікросекунд */
    if (gettimeofday (&value, NULL)==0)
    {
        /* повертаємо значення мікросекунд */
        return  ((double)value.tv_sec) +  (((double)value.tv_usec) / 1000000.0) ;
    }
    else
    {
        /* в разі помилки */
        perror("get_secs FAIL : getttimeofday()") ;
        return -1 ;
    }

    /* сюди ніколи не доберемось */
    return -1 ;
}

int check_pqexec (PGconn* connection, 
                  const char* query, 
                  unsigned int times,
                  vector <double>& delta_times)
{
    /* змінна-дескриптор виконання запиту */
    PGresult * result ;
    
    /* час перед виконанням запиту */
    double prev = 0.0 ;
    /* час після виконання запиту */
    double curr = 0.0 ;
    
    /* буфер повідомлення про помилку */
    string err_msg ;
    
    for (unsigned int iter=0; iter<times; ++iter)
    {
        prev = get_secs () ;
        
        /* виконуємо SQL-запит */
        result = PQexec (connection, query) ;
        
        curr = get_secs () ;
        
        
        /* перевіряємо чи все в порядку */
        if (PQresultStatus(result)!=PGRES_COMMAND_OK && 
            PQresultStatus(result)!=PGRES_TUPLES_OK)
        {
            /* якщо сталась помилка отримуємо текст помилки */
            err_msg = PQerrorMessage (connection) ;
            /* виводимо її на екран*/
            cout << "Fail to execute SQL query:" << err_msg << endl ;
            
            /* завершуємо виконання програми */
            return -1 ;
        }
        
        delta_times.push_back (curr - prev) ;
    }
    
    return 0 ;
}

int check_pqprepeared (PGconn* connection, 
                       const char* query, 
                       unsigned int times,
                       vector <double>& delta_times)
{
    /* змінна-дескриптор виконання запиту */
    PGresult * result ;
    
    /* буфер повідомлення про помилку */
    string err_msg ;
    
    string stmt1n = "query_one" ;
    
    /* підготовлюємо запит */
    result = PQprepare(connection,
                       stmt1n.c_str (),
                       query,  
                       0,
                       NULL) ;
                       
    /* перевіряємо успішність виконання функції */
    if (PQresultStatus(result)!=PGRES_COMMAND_OK && 
        PQresultStatus(result)!=PGRES_TUPLES_OK)
    {
        /* якщо сталась помилка отримуємо текст помилки */
        err_msg = PQerrorMessage (connection) ;
        
        /* виводимо її на екран*/
        cout << "Fail to execute query:" << err_msg << endl ;
        
        /* завершуємо виконання програми */
        return -1 ;
    }
    
    /* очищуємо дескриптор результату */
    PQclear (result) ;
    
    int pLen = 0 ;
    int pFor = 0 ;

    /* час перед виконанням запиту */
    double prev = 0.0 ;
    /* час після виконання запиту */
    double curr = 0.0 ;
    
    for (unsigned int iter=0; iter<times; ++iter)
    {
        /* отримуємо системний час перед виконанням запиту */
        prev = get_secs () ;
        
        /* виконуємо запит */
        result = PQexecPrepared (connection,
                                 stmt1n.c_str(),
                                 0,
                                 NULL,
                                 &pLen,
                                 &pFor,
                                 0) ;
                                 
        /* отримуємо системний час після виконанням запиту */
        curr = get_secs () ;
        
        /* перевіряємо успішність виконання запиту */
        if (PQresultStatus(result)!=PGRES_COMMAND_OK && 
            PQresultStatus(result)!=PGRES_TUPLES_OK)
        {
            /* якщо сталась помилка отримуємо текст помилки */
            err_msg = PQerrorMessage (connection) ;
            
            /* виводимо її на екран*/
            cout << "Fail to execute query:" << err_msg << endl ;
            
            /* завершуємо виконання програми */
            return -1 ;
        }
        
        /* зберігаємо різницю часу (час виконання запиту) */
        delta_times.push_back (curr - prev) ;
        
        /* очищаємо дескриптор виконання запиту і його дані */
        PQclear (result) ;
    }
    
    /* сигналізуємо успішність виконання */
    return 0 ;
}

void save_result (vector<double>& simple, vector<double>& complex)
{
    /* файлова змінна */
    fstream file ;
    
    /* відкриваємо файл */
    file.open ("results.html", fstream::out | fstream::trunc) ;

    /* перевіряємо успішність відкриття файлу */
    if (!file.is_open ())
    {
        /* в разі помилки виводимо повідомлення */
        cout << "Fail to open file \"results.html\"" << endl ;
        
        return ;
    }

    /* заголовок html сторінки */
    file << "<head>" ;
    file << "<style>\ntable { border-collapse: collapse; border: 1px solid #000; padding:5px; }\n "
         << "table td { border-collapse: collapse; border: 1px solid #000; padding:5px; }"
         << "</style>\n</head>" ;

    file << "<center>\n<h1>Test results</h1>\n<table>" ;

    /* заголовок таблиці */
    file << "<tr><td><b>Iteration<b></td><td><b>Simple</b></td>" 
         "<td><b>Complex</b></td></tr>" ;

    /* виводимо в файл час виконання запитів */
    for (int i=0; i<TIMES; ++i)
    {
        file << "<tr>" ;
        
        /* номер ітерації, перше поле */
        file << "<td>" << i + 1 << "</td>" ;
        /* для першої URL */
        file << "<td>" << simple[i] << "</td>" ;
        /* для другої URL */
        file << "<td>" << complex[i] << "</td>" ;
        
        file << "</tr>" ;
    }

    /* виводимо в файл статистику */

    /* мінімальний елемент */
    file << "<td><b>Minimal</b></td>" ;
        
    file << "<td>"
         << (double)*(min_element(simple.begin(), simple.end()))
         << "</td>" ;
    file << "<td>"
         << (double)*(min_element(complex.begin(), complex.end()))
         << "</td>" ;
    file << "</tr>" ;
 
     /* максимальний елемент */
    file << "<td><b>Maximum</b></td>" ;
    file << "<td>"
         << (double)*(max_element(simple.begin(), simple.end()))
         << "</td>" ;
    file << "<td>"
         << (double)*(max_element(complex.begin(), complex.end()))
         << "</td>" ;
        
    file << "</tr>" ;

    /* обчислюємо середні значення */
    file << "<tr>" ;
    file << "<td><b>Average</b></td>" ;
        
    /* середнє арифметичне значення для першої URL */
    file << "<td>"
         << (double)(accumulate(simple.begin(), simple.end(), 0.0) / (double) simple.size ())
         << "</td>" ;
        
    /* для другої URL */
    file << "<td>"
         << (double)(accumulate(complex.begin(), complex.end(), 0.0) / (double) complex.size ())
         << "</td>" ;
        
    file << "</tr>" ;    
 
    file << "</table>\n</center>\n" ;
 
    /* закриваємо файл */
    file.close() ;
}