The TDS_FDW fetches the data very slowly from SQL Server

[mmorris-sd]

Issue report

The following information is very important in order to help us to help you. Omission of the following details cause delays or could receive no attention at all.

Operating system

On recent GNU/Linux distributions, you can provide the content of the file /etc/os-release

NAME="Red Hat Enterprise Linux"
VERSION="8.5 (Ootpa)"
ID="rhel"
ID_LIKE="fedora"
VERSION_ID="8.5"
PLATFORM_ID="platform:el8"
PRETTY_NAME="Red Hat Enterprise Linux 8.5 (Ootpa)"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:redhat:enterprise_linux:8::baseos"
HOME_URL="https://www.redhat.com/"
DOCUMENTATION_URL="https://access.redhat.com/documentation/red_hat_enterprise_linux/8/"
BUG_REPORT_URL="https://bugzilla.redhat.com/"

REDHAT_BUGZILLA_PRODUCT="Red Hat Enterprise Linux 8"
REDHAT_BUGZILLA_PRODUCT_VERSION=8.5
REDHAT_SUPPORT_PRODUCT="Red Hat Enterprise Linux"
REDHAT_SUPPORT_PRODUCT_VERSION="8.5"

Version of tds_fdw

From a psql session, paste the outputs of running \dx

If you built the package from Git sources, also paste the output of running git log --source -n 1 on your git clone from a console

16399   tds_fdw 16397   2200    true    2.0.2   NULL    NULL

Version of PostgreSQL

From a psql session, paste the output of running SELECT version();

PostgreSQL 13.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit

Version of FreeTDS

How to get it will depend on your Operating System and how you installes FreeTDS

From a console:

• On RPM based systems: rpm -qa|grep freetds
• On Deb based systems: dpkg -l|grep freetds
• _If you built your own binaries from source code, then go to the sources, and run: grep 'AC_INIT' configure.ac_

tsql -C
Compile-time settings (established with the "configure" script)
                            Version: freetds v1.3.3
             freetds.conf directory: /etc
     MS db-lib source compatibility: yes
        Sybase binary compatibility: yes
                      Thread safety: yes
                      iconv library: yes
                        TDS version: auto
                              iODBC: no
                           unixodbc: yes
              SSPI "trusted" logins: no
                           Kerberos: yes
                            OpenSSL: no
                             GnuTLS: yes
                               MARS: yes

Logs

Please capture the logs when the error you are reporting is happening, as well as commands with their outputs if you are reporting a problem build or installing

_For problems using tdsfdw on PostgreSQL how to do it will depend on your system, but if your PostgreSQL is installed on GNU/Linux, you will want to use tail -f with the log of the PostgreSQL cluster

For MSSQL you will need to use the SQL Server Audit Log

Replace this with the commands and outputs

Sentences, data structures, data

This will depend on the exact problem you are having and data privacy restrictions

However the more data you provide, the more likely we will be able to help

As a bare minimum, you should provide

• The SQL sentence that is failing
• The data structure on the PostgreSQL side and on the MSSQL side

Our Postgres database is installed on a VM on ExtremeIO where our copy of SQL Server is also installed. This uses a SAN SSD array to eliminate IO concerns. I can run queries from tsql on the Postgres machine and the data returns in msecs. But executing the exact same query from Postgres (where the same table was imported), the query takes up to several minutes. You can see the log saying it is fetching records. The query does not have a WHERE clause so there is no push down issue. 

From the Postgres documentation they mention fetch_size, but I cannot set that option for a foreign server created with tds_fdw. Is there any option to speed up the fetch of the data by tds_fdw? Any help would be greatly appreciated.