Однажды мне потребовалось забирать регулярно относительно большие объемы данных в MS SQL из PostgreSQL. Неожиданно выяснилось, что самый очевидный способ, через Linked Server на родные ODBC к PostgreSQL, очень медленный.
История вопроса
На этапе прототипирования все было хорошо. Просто потому, что протипировалось всего несколько тысяч записей. Как только перешли к разработке, сразу возникло подозрение, что с производительностью что-то не то:
SET STATISTICS TIME ON DECLARE @sql_str nvarchar(max) DROP TABLE IF EXISTS #t CREATE TABLE #t ( N int, T datetime, S varchar(256) ) SELECT @sql_str=' SELECT N, T, md5((R*100000*random())::text) S FROM generate_series(1,1000,1) N CROSS JOIN generate_series($$2020-01-01$$::timestamp, $$2020-12-31$$::timestamp, $$1 day$$::interval) T CROSS JOIN generate_series(1,100,1) R' INSERT #t (N, T) EXEC (@sql_str) AT LINKED_SERVER_TO_POSTGRES
Такой простейший пример выборки 36,6 миллионов записей оказался жутко медленным:
SQL Server Execution Times: CPU time = 927640 ms, elapsed time = 1705275 ms.
Решение
В первую очередь, захотелось исключить самый подозрительный элемент - ODBC. К тому времени MS уже предоставлял утилиту bcp для Linux. Поэтому bcp был установлен на сервер, где работал PostgreSQL и проведен следующий тест:
SET STATISTICS TIME ON DECLARE @sql_str nvarchar(max), @proxy_account sysname='proxy_account', @proxy_password sysname='111111' DROP TABLE IF EXISTS ##t CREATE TABLE ##t ( N int, T datetime, S varchar(256) ) SELECT @sql_str=' COPY ( SELECT N, T, md5((R*100000*random())::text) S FROM generate_series(1,1000,1) N CROSS JOIN generate_series($$2020-01-01$$::timestamp, $$2020-12-31$$::timestamp, $$1 day$$::interval) T CROSS JOIN generate_series(1,100,1) R ) TO PROGRAM $pgm$ tmp_file=$'+'(mktemp /tmp/pgsql_bcp_to_mssql.XXXXXXXXX); ' +'cat > $tmp_file; /opt/mssql-tools/bin/bcp ''##t'' ' +'in $tmp_file -S '+REPLACE(@@SERVERNAME,'','\') +' -U '+@proxy_account+' -P ''' +@proxy_password+''' -c -b 10000000 -a 65535; ' +'rm $tmp_file $pgm$ NULL $nil$$nil$;' EXEC (@sql_str) AT LINKED_SERVER_TO_POSTGRES
Результат сразу порадовал, причем сильно:
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 132794 ms.
Реализация
Не сложно заметить, что такой подход требует явного указания логина и пароля. Причем, bcp для Linux до сих пор не умеет авторизоваться через Kerberos. Поэтому использовать его можно только указывая кредентиалы в командной строке.
Вторая проблема в том, что в обычную временную таблицу bcp записать не может. Он ее просто не увидит. Значит нужно использовать постоянную таблицу или глобальную временную.
Давать права пользователю, кредентиалы которого открытым текстом видны в SQL запросе, на таблицы своей БД совершенно не хочется. Тем более на запись. Поэтому остается только вариант с глобальной временной таблицей.
В связи с тем, что процессы на сервере могут запускаться асинхронно и одновременно, использовать фиксированное имя глобальной временной таблицы опасно. Но тут нас опять спасает динамический SQL.
Итоговое решение следующее:
DECLARE @sql_str nvarchar(max), @proxy_account sysname='proxy_account', @proxy_password sysname='111111' SELECT @sql_str=' DROP TABLE IF EXISTS ##proxy_table_'+CONVERT(nvarchar(max),@@SPID)+' CREATE TABLE ##proxy_table_'+CONVERT(nvarchar(max),@@SPID)+' ( N int, T datetime, S varchar(256) )' EXEC (@sql_str) SELECT @sql_str=' COPY ( SELECT N, T, md5((R*100000*random())::text) S FROM generate_series(1,1000,1) N CROSS JOIN generate_series($$2020-01-01$$::timestamp, $$2020-12-31$$::timestamp, $$1 day$$::interval) T CROSS JOIN generate_series(1,100,1) R ) TO PROGRAM $pgm$ tmp_file=$'+'(mktemp /tmp/pgsql_bcp_to_mssql.XXXXXXXXX); ' +'cat > $tmp_file; /opt/mssql-tools/bin/bcp ''##proxy_table_''' +CONVERT(nvarchar(max),@@SPID)+' ' +'in $tmp_file -S '+REPLACE(@@SERVERNAME,'\','\\') +' -U '+@proxy_account+' -P ''' +@proxy_password+''' -c -b 10000000 -a 65535; ' +'rm $tmp_file $pgm$ NULL $nil$$nil$;' EXEC (@sql_str) AT LINKED_SERVER_TO_POSTGRES
Пояснения
В PostgreSQL команда COPY может писать в файл или на стандартный ввод вызываемой ей программы. В данном случае вместо программы использован скрипт на sh. Вывод COPY, поступающий на стандартный ввод, записывается во временный файл с уникальным именем, формируемым mktemp. К сожалению, bcp не умеет читать данные со стандартного ввода, поэтому приходится ему создавать файл.
Для совместимости формата, формируемого командой COPY и формата, ожидаемого bcp, обязательно следует указывать в COPY параметр NULL $nil$$nil$
Остальные параметры bcp:
-c - символьный формат, так как бинарный формат PostgreSQL не совместим с бинарным форматом MS SQL и мы вынуждены использовать только символьный;
-b - количество записей, вставляемых одной транзакцией. В моей конфигурации десять миллионов оказалось оптимальным значением. В иной конфигурации это число, скорее, может потребоваться уменьшить, чем увеличить;
-a - размер пакета. В нашем случае лучше указывать сразу максимальный. Если сервер не поддерживает указанную длину пакета, то просто будет использована максимальная длина пакета, поддерживаемая сервером.
Если кто-то знает более быстрый способ получения данных на MS SQL из PostgreSQL - буду очень рад увидеть описание этого способа в комментариях.
Сравнение с SSIS
DTSX
<?xml version="1.0"?> <DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:refId="Package" DTS:CreationDate="4/23/2021 10:48:39 PM" DTS:CreationName="Microsoft.Package" DTS:CreatorComputerName="server" DTS:CreatorName="DOMAIN\user" DTS:Description="SSIS Package Description" DTS:DTSID="{9AF1F924-8357-4B7E-9766-2A78BB9E063D}" DTS:ExecutableType="Microsoft.Package" DTS:LastModifiedProductVersion="15.0.1900.63" DTS:LocaleID="1049" DTS:MaxErrorCount="0" DTS:ObjectName="Package" DTS:ProtectionLevel="0" DTS:VersionGUID="{92B9BB2D-B8C6-4C73-BB9E-A2ADFFE8A8C0}"> <DTS:Property DTS:Name="PackageFormatVersion">8</DTS:Property> <DTS:ConnectionManagers> <DTS:ConnectionManager DTS:refId="Package.ConnectionManagers[DestinationConnectionOLEDB]" DTS:CreationName="OLEDB" DTS:DTSID="{33E470E0-8AB7-4D9B-B0B2-53C9411BB976}" DTS:ObjectName="DestinationConnectionOLEDB"> <DTS:ObjectData> <DTS:ConnectionManager DTS:ConnectionString="Data Source=ms-sql;Initial Catalog=db;Provider=SQLNCLI11;Integrated Security=SSPI;Auto Translate=false;" /> </DTS:ObjectData> </DTS:ConnectionManager> <DTS:ConnectionManager DTS:refId="Package.ConnectionManagers[SourceConnectionAdoNET]" DTS:CreationName="ADO.NET:System.Data.Odbc.OdbcConnection, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" DTS:DTSID="{2840A792-1CF7-495A-A55F-875331D032BB}" DTS:ObjectName="SourceConnectionAdoNET"> <DTS:ObjectData> <DTS:ConnectionManager DTS:ConnectionString="Dsn=PostgreSQL;" /> </DTS:ObjectData> </DTS:ConnectionManager> </DTS:ConnectionManagers> <DTS:Variables /> <DTS:Executables> <DTS:Executable DTS:refId="Package\Data Flow Task 1" DTS:CreationName="Microsoft.Pipeline" DTS:DelayValidation="True" DTS:DTSID="{A38FAC70-DCF8-4AD6-9416-E6204931F4FA}" DTS:ExecutableType="Microsoft.Pipeline" DTS:FailPackageOnFailure="True" DTS:LocaleID="-1" DTS:ObjectName="Data Flow Task 1" DTS:TaskContact="Performs high-performance data extraction, transformation and loading;Microsoft Corporation; Microsoft SQL Server; (C) Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;1"> <DTS:Variables /> <DTS:ObjectData> <pipeline defaultBufferSize="3145728" version="1"> <components> <component refId="Package\Data Flow Task 1\Destination - Query3" componentClassID="Microsoft.OLEDBDestination" contactInfo="OLE DB Destination;Microsoft Corporation; Microsoft SQL Server; (C) Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;4" description="OLE DB Destination" name="Destination - Query3" usesDispositions="true" validateExternalMetadata="False" version="4"> <properties> <property dataType="System.Int32" description="The number of seconds before a command times out. A value of 0 indicates an infinite time-out." name="CommandTimeout">0</property> <property dataType="System.String" description="Specifies the name of the database object used to open a rowset." name="OpenRowset">[dbo].[Query3]</property> <property dataType="System.String" description="Specifies the variable that contains the name of the database object used to open a rowset." name="OpenRowsetVariable"></property> <property dataType="System.String" description="The SQL command to be executed." name="SqlCommand" UITypeEditor="Microsoft.DataTransformationServices.Controls.ModalMultilineStringEditor"></property> <property dataType="System.Int32" description="Specifies the column code page to use when code page information is unavailable from the data source." name="DefaultCodePage">1251</property> <property dataType="System.Boolean" description="Forces the use of the DefaultCodePage property value when describing character data." name="AlwaysUseDefaultCodePage">false</property> <property dataType="System.Int32" description="Specifies the mode used to access the database." name="AccessMode" typeConverter="AccessMode">3</property> <property dataType="System.Boolean" description="Indicates whether the values supplied for identity columns will be copied to the destination. If false, values for identity columns will be auto-generated at the destination. Applies only if fast load is turned on." name="FastLoadKeepIdentity">false</property> <property dataType="System.Boolean" description="Indicates whether the columns containing null will have null inserted in the destination. If false, columns containing null will have their default values inserted at the destination. Applies only if fast load is turned on." name="FastLoadKeepNulls">false</property> <property dataType="System.String" description="Specifies options to be used with fast load. Applies only if fast load is turned on." name="FastLoadOptions">TABLOCK,CHECK_CONSTRAINTS</property> <property dataType="System.Int32" description="Specifies when commits are issued during data insertion. A value of 0 specifies that one commit will be issued at the end of data insertion. Applies only if fast load is turned on." name="FastLoadMaxInsertCommitSize">2147483647</property> </properties> <connections> <connection refId="Package\Data Flow Task 1\Destination - Query3.Connections[OleDbConnection]" connectionManagerID="Package.ConnectionManagers[DestinationConnectionOLEDB]" connectionManagerRefId="Package.ConnectionManagers[DestinationConnectionOLEDB]" description="The OLE DB runtime connection used to access the database." name="OleDbConnection" /> </connections> <inputs> <input refId="Package\Data Flow Task 1\Destination - Query3.Inputs[Destination Input]" errorOrTruncationOperation="Insert" errorRowDisposition="FailComponent" hasSideEffects="true" name="Destination Input"> <inputColumns> <inputColumn refId="Package\Data Flow Task 1\Destination - Query3.Inputs[Destination Input].Columns[n]" cachedDataType="i4" cachedName="n" externalMetadataColumnId="Package\Data Flow Task 1\Destination - Query3.Inputs[Destination Input].ExternalColumns[n]" lineageId="Package\Data Flow Task 1\Source - Query.Outputs[ADO NET Source Output].Columns[n]" /> <inputColumn refId="Package\Data Flow Task 1\Destination - Query3.Inputs[Destination Input].Columns[t]" cachedDataType="dbTimeStamp" cachedName="t" externalMetadataColumnId="Package\Data Flow Task 1\Destination - Query3.Inputs[Destination Input].ExternalColumns[t]" lineageId="Package\Data Flow Task 1\Source - Query.Outputs[ADO NET Source Output].Columns[t]" /> <inputColumn refId="Package\Data Flow Task 1\Destination - Query3.Inputs[Destination Input].Columns[s]" cachedDataType="nText" cachedName="s" externalMetadataColumnId="Package\Data Flow Task 1\Destination - Query3.Inputs[Destination Input].ExternalColumns[s]" lineageId="Package\Data Flow Task 1\Source - Query.Outputs[ADO NET Source Output].Columns[s]" /> </inputColumns> <externalMetadataColumns isUsed="True"> <externalMetadataColumn refId="Package\Data Flow Task 1\Destination - Query3.Inputs[Destination Input].ExternalColumns[n]" dataType="i4" name="n" /> <externalMetadataColumn refId="Package\Data Flow Task 1\Destination - Query3.Inputs[Destination Input].ExternalColumns[t]" dataType="dbTimeStamp" name="t" scale="6" /> <externalMetadataColumn refId="Package\Data Flow Task 1\Destination - Query3.Inputs[Destination Input].ExternalColumns[s]" dataType="nText" name="s" /> </externalMetadataColumns> </input> </inputs> <outputs> <output refId="Package\Data Flow Task 1\Destination - Query3.Outputs[OLE DB Destination Error Output]" exclusionGroup="1" isErrorOut="true" name="OLE DB Destination Error Output" synchronousInputId="Package\Data Flow Task 1\Destination - Query3.Inputs[Destination Input]"> <outputColumns> <outputColumn refId="Package\Data Flow Task 1\Destination - Query3.Outputs[OLE DB Destination Error Output].Columns[ErrorCode]" dataType="i4" lineageId="Package\Data Flow Task 1\Destination - Query3.Outputs[OLE DB Destination Error Output].Columns[ErrorCode]" name="ErrorCode" specialFlags="1" /> <outputColumn refId="Package\Data Flow Task 1\Destination - Query3.Outputs[OLE DB Destination Error Output].Columns[ErrorColumn]" dataType="i4" lineageId="Package\Data Flow Task 1\Destination - Query3.Outputs[OLE DB Destination Error Output].Columns[ErrorColumn]" name="ErrorColumn" specialFlags="2" /> </outputColumns> <externalMetadataColumns /> </output> </outputs> </component> <component refId="Package\Data Flow Task 1\Source - Query" componentClassID="Microsoft.ManagedComponentHost" contactInfo="Consumes data from SQL Server, OLE DB, ODBC, or Oracle, using the corresponding .NET Framework data provider. Use a Transact-SQL statement to define the result set. For example, extract data from SQL Server with the .NET Framework data provider for SQL Server.;Microsoft Corporation; Microsoft SQL Server; © Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;4" description="Consumes data from SQL Server, OLE DB, ODBC, or Oracle, using the corresponding .NET Framework data provider. Use a Transact-SQL statement to define the result set. For example, extract data from SQL Server with the .NET Framework data provider for SQL Server." name="Source - Query" usesDispositions="true" version="4"> <properties> <property dataType="System.String" description="Specifies the SQL statement used by the component to extract data." expressionType="Notify" name="SqlCommand" UITypeEditor="Microsoft.DataTransformationServices.Controls.ModalMultilineStringEditor">SELECT N, T, md5((R*100000*random())::text) S FROM generate_series(1,1000,1) N CROSS JOIN generate_series($$2020-01-01$$::timestamp, $$2020-12-31$$::timestamp, $$1 day$$::interval) T CROSS JOIN generate_series(1,100,1) R</property> <property dataType="System.Int32" description="The number of seconds before a command times out. A value of 0 indicates an infinite time-out." name="CommandTimeout">0</property> <property dataType="System.Boolean" description="Indicates whether to allow non-string external columns to be implicitly converted to strings at the output columns." name="AllowImplicitStringConversion">true</property> <property dataType="System.String" description="The Source database table name." expressionType="Notify" name="TableOrViewName" UITypeEditor="Microsoft.DataTransformationServices.Controls.ModalMultilineStringEditor"></property> <property dataType="System.Int32" description="Specify the mode to retrieve external column information" name="AccessMode" typeConverter="Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter+EnumToStringConverter">2</property> <property dataType="System.String" name="UserComponentTypeName">Microsoft.DataReaderSourceAdapter</property> </properties> <connections> <connection refId="Package\Data Flow Task 1\Source - Query.Connections[IDbConnection]" connectionManagerID="Package.ConnectionManagers[SourceConnectionAdoNET]" connectionManagerRefId="Package.ConnectionManagers[SourceConnectionAdoNET]" description="Managed connection manager" name="IDbConnection" /> </connections> <outputs> <output refId="Package\Data Flow Task 1\Source - Query.Outputs[ADO NET Source Output]" name="ADO NET Source Output"> <outputColumns> <outputColumn refId="Package\Data Flow Task 1\Source - Query.Outputs[ADO NET Source Output].Columns[n]" dataType="i4" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" externalMetadataColumnId="Package\Data Flow Task 1\Source - Query.Outputs[ADO NET Source Output].ExternalColumns[n]" lineageId="Package\Data Flow Task 1\Source - Query.Outputs[ADO NET Source Output].Columns[n]" name="n" truncationRowDisposition="FailComponent" /> <outputColumn refId="Package\Data Flow Task 1\Source - Query.Outputs[ADO NET Source Output].Columns[t]" dataType="dbTimeStamp" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" externalMetadataColumnId="Package\Data Flow Task 1\Source - Query.Outputs[ADO NET Source Output].ExternalColumns[t]" lineageId="Package\Data Flow Task 1\Source - Query.Outputs[ADO NET Source Output].Columns[t]" name="t" truncationRowDisposition="FailComponent" /> <outputColumn refId="Package\Data Flow Task 1\Source - Query.Outputs[ADO NET Source Output].Columns[s]" dataType="nText" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" externalMetadataColumnId="Package\Data Flow Task 1\Source - Query.Outputs[ADO NET Source Output].ExternalColumns[s]" lineageId="Package\Data Flow Task 1\Source - Query.Outputs[ADO NET Source Output].Columns[s]" name="s" truncationRowDisposition="FailComponent" /> </outputColumns> <externalMetadataColumns isUsed="True"> <externalMetadataColumn refId="Package\Data Flow Task 1\Source - Query.Outputs[ADO NET Source Output].ExternalColumns[n]" dataType="i4" name="n" /> <externalMetadataColumn refId="Package\Data Flow Task 1\Source - Query.Outputs[ADO NET Source Output].ExternalColumns[t]" dataType="dbTimeStamp" name="t" /> <externalMetadataColumn refId="Package\Data Flow Task 1\Source - Query.Outputs[ADO NET Source Output].ExternalColumns[s]" dataType="nText" name="s" /> </externalMetadataColumns> </output> <output refId="Package\Data Flow Task 1\Source - Query.Outputs[ADO NET Source Error Output]" isErrorOut="true" name="ADO NET Source Error Output"> <outputColumns> <outputColumn refId="Package\Data Flow Task 1\Source - Query.Outputs[ADO NET Source Error Output].Columns[n]" dataType="i4" lineageId="Package\Data Flow Task 1\Source - Query.Outputs[ADO NET Source Error Output].Columns[n]" name="n" /> <outputColumn refId="Package\Data Flow Task 1\Source - Query.Outputs[ADO NET Source Error Output].Columns[t]" dataType="dbTimeStamp" lineageId="Package\Data Flow Task 1\Source - Query.Outputs[ADO NET Source Error Output].Columns[t]" name="t" /> <outputColumn refId="Package\Data Flow Task 1\Source - Query.Outputs[ADO NET Source Error Output].Columns[s]" dataType="nText" lineageId="Package\Data Flow Task 1\Source - Query.Outputs[ADO NET Source Error Output].Columns[s]" name="s" /> <outputColumn refId="Package\Data Flow Task 1\Source - Query.Outputs[ADO NET Source Error Output].Columns[ErrorCode]" dataType="i4" lineageId="Package\Data Flow Task 1\Source - Query.Outputs[ADO NET Source Error Output].Columns[ErrorCode]" name="ErrorCode" specialFlags="1" /> <outputColumn refId="Package\Data Flow Task 1\Source - Query.Outputs[ADO NET Source Error Output].Columns[ErrorColumn]" dataType="i4" lineageId="Package\Data Flow Task 1\Source - Query.Outputs[ADO NET Source Error Output].Columns[ErrorColumn]" name="ErrorColumn" specialFlags="2" /> </outputColumns> <externalMetadataColumns /> </output> </outputs> </component> </components> <paths> <path refId="Package\Data Flow Task 1.Paths[ADO NET Source Output]" endId="Package\Data Flow Task 1\Destination - Query3.Inputs[Destination Input]" name="ADO NET Source Output" startId="Package\Data Flow Task 1\Source - Query.Outputs[ADO NET Source Output]" /> </paths> </pipeline> </DTS:ObjectData> </DTS:Executable> <DTS:Executable DTS:refId="Package\Preparation SQL Task 1" DTS:CreationName="Microsoft.ExecuteSQLTask" DTS:DTSID="{1B2F804A-9E69-4B6A-A509-B50A15DC0779}" DTS:ExecutableType="Microsoft.ExecuteSQLTask" DTS:FailPackageOnFailure="True" DTS:LocaleID="-1" DTS:ObjectName="Preparation SQL Task 1" DTS:ThreadHint="0"> <DTS:Variables /> <DTS:ObjectData> <SQLTask:SqlTaskData SQLTask:Connection="{33E470E0-8AB7-4D9B-B0B2-53C9411BB976}" SQLTask:SqlStatementSource="CREATE TABLE [dbo].[Query3] (
[n] int,
[t] datetime2(6),
[s] ntext
)
GO
" xmlns:SQLTask="www.microsoft.com/sqlserver/dts/tasks/sqltask" /> </DTS:ObjectData> </DTS:Executable> </DTS:Executables> <DTS:PrecedenceConstraints> <DTS:PrecedenceConstraint DTS:refId="Package.PrecedenceConstraints[{55D4743D-F3F0-4EA4-ABFD-A394E4D51C48\}]" DTS:CreationName="" DTS:DTSID="{55D4743D-F3F0-4EA4-ABFD-A394E4D51C48}" DTS:From="Package\Preparation SQL Task 1" DTS:LogicalAnd="True" DTS:ObjectName="{55D4743D-F3F0-4EA4-ABFD-A394E4D51C48}" DTS:To="Package\Data Flow Task 1" /> </DTS:PrecedenceConstraints> </DTS:Executable>
Started: 22:52:26
Finished: 23:11:25
Elapsed: 1138.16 seconds
Итого в 8.5 раз медленней, чем через ramfs/tmpfs и BCP.
