Uploaded image for project: 'Qt'
  1. Qt
  2. QTBUG-81828

Returning query results in binary format for PostgreSQL

    XMLWordPrintable

Details

    Description

      PostgreSQL supports sending query results in text or binary format:

      https://www.postgresql.org/docs/current/protocol-overview.html#PROTOCOL-FORMAT-CODES

      Currently, the QSQL plugin uses a text format that is less efficient than the binary format. Other libraries like Npgsql for C# use binary format and run much faster than QPSQL.

      For example, getting the SQL query result:

      SELECT seq.num::boolean AS bval, 
                      seq.num::integer AS intval, 
                      seq.num::double precision * 0.1 AS dblval,
                      'żółwik' || seq.num AS strval, 
                      '2000-01-01 00:00'::timestamp + (seq.num || ' second')::interval AS tmval 
      FROM (SELECT generate_series(0, 5000000) AS num) seq;

      for MSVC2015 + Qt 5.12.6 it takes:
      24786 msecs

      and for .NET Framework 4.7.2 + Npgsql 4.1.2:
      12897 msecs

      The tests I did show that changing the implementation from a text to binary format would give similar results as C #:

      benchmarkSequence_Libpq_binaryFormat ():
      12608 msecs
      benchmarkSequence_Libpq_textFormat ():
      19828 msecs
      benchmarkSequence_QSqlQuery ():
      24786 msecs

      I think it would be worth considering the binary format for QPSQL in Qt6.

      Below I describe what should be done to support the binary format:

      • parse binary formats for different data types (see *send i *recv functions in PostgreSQL src\backend\utils\adt directory and https://www.npgsql.org/doc/dev/type-representations.html)
      • split SQL queries into individual SQL commands and use the PQsendQueryParams () function with the resultFormat = 1 parameter instead of PQsendQuery ()
      • drop support for PostgreSQL 7.3, because it does not support binary format

       

      Attachments

        1. Program.cs
          2 kB
          Robert Szefner
        2. tst_qsqlqueryformat.cpp
          7 kB
          Robert Szefner
        No reviews matched the request. Check your Options in the drop-down menu of this sections header.

        Activity

          People

            mabrand Mark Brand
            amiart Robert Szefner
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:

              Gerrit Reviews

                There are no open Gerrit changes