I'm using polybase to load data from PostgreSQL and have some problem The PostgreSQl database and SQL server (for my case is SQL server 2022) are in same machine. I create an external table in SQL server to loading data from PostgreSQL database by Polybase, It's took at least over 1s even the table is empty and meanwhile i ran it in Pgadmin it run ok. I already Select only id column but it was same result.Here is some information:
- When i run in pgadmin
- run with Linked Server
As you can see it took only 329 ms - I create table in SQL server
CREATE EXTERNAL TABLE [dbo].[SaleorApp]
(
[id] [int] NOT NULL,
[private_metadata] [nvarchar](max) NULL,
[metadata] [nvarchar](max) NULL,
[name] [nvarchar](60) NOT NULL,
[created_at] [datetime2](6) NOT NULL,
[is_active] [nvarchar](5) NOT NULL,
[about_app] [nvarchar](max) NULL,
[app_url] [nvarchar](200) NULL,
[configuration_url] [nvarchar](200) NULL,
[data_privacy] [nvarchar](max) NULL,
[data_privacy_url] [nvarchar](200) NULL,
[homepage_url] [nvarchar](200) NULL,
[identifier] [nvarchar](256) NULL,
[support_url] [nvarchar](200) NULL,
[type] [nvarchar](60) NOT NULL,
[version] [nvarchar](60) NULL,
[manifest_url] [nvarchar](200) NULL
)
WITH (DATA_SOURCE = [PostgreSQL35W],LOCATION = N'"saleor"."public"."app_app"')
After that i ran it in SQL server 2022
SET STATISTICS TIME ON;
SELECT [id]
,[private_metadata]
,[metadata]
,[name]
,[created_at]
,[is_active]
,[about_app]
,[app_url]
,[configuration_url]
,[data_privacy]
,[data_privacy_url]
,[homepage_url]
,[identifier]
,[support_url]
,[type]
,[version]
,[manifest_url]
FROM [Pastaxi].[dbo].[SaleorApp]
SET STATISTICS TIME OFF;
And it ruturned :
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 940 ms.
I already checked in Polybase log and here is result:
12/3/2022 10:15:54 AM [Thread:2304] [PdwTdsNativeToManagedInterop:InformationEvent] (Info, Normal): Incoming TDS connection, Client TDS version: 7 (TDS74). 12/3/2022 10:15:54 AM [Thread:2304] [EngineInstrumentation:TdsAddConnectionEvent] (Info, Low): Starting TDS connection: 1929345925184 Client: 192.168.1.22:63040, isFromDataSecurityProxy: False, isVnetAddress: False, clientAddressSafe: 192.168.1.22:63040 12/3/2022 10:15:54 AM [Thread:2304] [DataClassificationConfig:InformationEvent] (Info, Normal): Returning FeatureSwitch DataClassificationCoreEnabled status: False [Session.SessionId:SID3170][Session.IsTransactional:False] 12/3/2022 10:15:54 AM [Thread:2304] [EngineInstrumentation:ServerStartSessionEvent] (Info, Low): Started new session, in reset connection: False. [Session.SessionId:SID3170][Session.IsTransactional:False] 12/3/2022 10:15:54 AM [Thread:2304] [TdsRequestProcessor:InformationEvent] (Info, Normal): Processing login, authentication scheme: "SqlAuthentication", client application name: "C39A9D00-5206-85DF-3907-338474E48E80" 12/3/2022 10:15:54 AM [Thread:2304] [PdwTdsNativeToManagedInterop:InformationEvent] (Info, Normal): Data Classification TDS Extension Requested=False, FS Enabled=False, Enabled=False. [Session.SessionId:SID3170][Session.IsTransactional:True][Query.QueryId:QID3294] 12/3/2022 10:15:54 AM [Thread:1940] [EngineInstrumentation:TdsAddConnectionEvent] (Info, Low): Starting TDS connection: 1929427025984 Client: , isFromDataSecurityProxy: False, isVnetAddress: False, clientAddressSafe: 12/3/2022 10:15:54 AM [Thread:13664] [EngineInstrumentation:EngineStopSessionBeginEvent] (Info, Low): Stopping session, in reset connection: False. [Session.SessionId:SID3170][Session.IsTransactional:False] 12/3/2022 10:15:54 AM [Thread:13664] [EngineInstrumentation:TdsRemoveConnectionEvent] (Info, Low): Stopping TDS connection: 1929427025984 12/3/2022 10:15:54 AM [Thread:2304] [TdsRequestProcessor:InformationEvent] (Info, Normal): CancelBatch request started [Session.SessionId:SID3170] 12/3/2022 10:15:54 AM [Thread:2304] [TdsRequestProcessor:InformationEvent] (Info, Normal): CancelBatch request started [Session.SessionId:SID3170] 12/3/2022 10:15:54 AM [Thread:3860] [EngineInstrumentation:EngineCancelQueryBeginEvent] (Info, Low): Cancel requested. [Session.SessionId:SID3170][Session.IsTransactional:False] 12/3/2022 10:15:54 AM [Thread:2288] [EngineInstrumentation:EngineCancelQueryBeginEvent] (Info, Low): Cancel requested. [Session.SessionId:SID3170][Session.IsTransactional:False] 12/3/2022 10:15:54 AM [Thread:13664] [EngineInstrumentation:TdsRemoveConnectionEvent] (Info, Low): Stopping TDS connection: 1929345925184 12/3/2022 10:15:54 AM [Thread:13664] [EngineInstrumentation:EngineStopSessionBeginEvent] (Info, Low): Stopping session, in reset connection: False. [Session.SessionId:SID3170][Session.IsTransactional:False] 12/3/2022 10:15:54 AM [Thread:2304] [PdwTdsNativeToManagedInterop:InformationEvent] (Info, Normal): Incoming TDS connection, Client TDS version: 7 (TDS74). 12/3/2022 10:15:54 AM [Thread:2304] [EngineInstrumentation:TdsAddConnectionEvent] (Info, Low): Starting TDS connection: 1929345925184 Client: 192.168.1.22:63041, isFromDataSecurityProxy: False, isVnetAddress: False, clientAddressSafe: 192.168.1.22:63041 12/3/2022 10:15:54 AM [Thread:2304] [DataClassificationConfig:InformationEvent] (Info, Normal): Returning FeatureSwitch DataClassificationCoreEnabled status: False [Session.SessionId:SID3171][Session.IsTransactional:False] 12/3/2022 10:15:54 AM [Thread:2304] [EngineInstrumentation:ServerStartSessionEvent] (Info, Low): Started new session, in reset connection: False. [Session.SessionId:SID3171][Session.IsTransactional:False] 12/3/2022 10:15:54 AM [Thread:2304] [TdsRequestProcessor:InformationEvent] (Info, Normal): Processing login, authentication scheme: "SqlAuthentication", client application name: "C39A9D00-5206-85DF-3907-338474E48E80" 12/3/2022 10:15:54 AM [Thread:2304] [PdwTdsNativeToManagedInterop:InformationEvent] (Info, Normal): Data Classification TDS Extension Requested=False, FS Enabled=False, Enabled=False. [Session.SessionId:SID3171][Session.IsTransactional:True][Query.QueryId:QID3295] 12/3/2022 10:15:54 AM [Thread:1940] [EngineInstrumentation:TdsAddConnectionEvent] (Info, Low): Starting TDS connection: 1929427025984 Client: , isFromDataSecurityProxy: False, isVnetAddress: False, clientAddressSafe: 12/3/2022 10:15:54 AM [Thread:2304] [ServerInterface:InformationEvent] (Info, Normal): Incoming Query: SID3171:QID3296 [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296] 12/3/2022 10:15:54 AM [Thread:3860] [ServerInterface:InformationEvent] (Info, Normal): Starting processor ExecuteMemoProcessor. [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296] 12/3/2022 10:15:54 AM [Thread:3860] [OptimizedStatement:InformationEvent] (Info, Normal): Memo compilation time: 2.0026 ms [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296] 12/3/2022 10:15:54 AM [Thread:3860] [OptimizedStatement:InformationEvent] (Info, Normal): Sql Server Optimization Clock: 0.001 s, CPU: 0.001 s. [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296] 12/3/2022 10:15:54 AM [Thread:3860] [OptimizedStatement:InformationEvent] (Info, Normal): SQL Server XML generation Clock : 0.001 s, CPU: 0.001 s [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296] 12/3/2022 10:15:54 AM [Thread:3860] [NativeOdbcConnection:InformationEvent] (Info, Normal): NativeOdbcConnection.Open, ConnectionString: Dsn=PostgreSQL35W;uid=saleor;server=localhost;port=5432, CreateOptions: ReadExternal, PacketSize: 0, EnableConnectionPooling: True, TransactionPropagationToken: null, ResourceGroup: null [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296] 12/3/2022 10:15:54 AM [Thread:3860] [NativeOdbcConnection:InformationEvent] (Info, Normal): NativeOdbcConnection.Open, ConnectionString: Dsn=PostgreSQL35W;uid=saleor;server=localhost;port=5432, CreateOptions: ReadExternal, PacketSize: 0, EnableConnectionPooling: True, TransactionPropagationToken: null, ResourceGroup: null [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296] 12/3/2022 10:15:54 AM [Thread:3860] [NativeOdbcConnection:InformationEvent] (Info, Normal): NativeOdbcConnection.Open, ConnectionString: Dsn=PostgreSQL35W;uid=saleor;server=localhost;port=5432, CreateOptions: ReadExternal, PacketSize: 0, EnableConnectionPooling: True, TransactionPropagationToken: null, ResourceGroup: null [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296] 12/3/2022 10:15:54 AM [Thread:3860] [NativeOdbcConnection:InformationEvent] (Info, Normal): NativeOdbcConnection.Open, ConnectionString: Dsn=PostgreSQL35W;uid=saleor;server=localhost;port=5432;database=saleor, CreateOptions: ReadExternal, PacketSize: 0, EnableConnectionPooling: True, TransactionPropagationToken: null, ResourceGroup: null [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296] 12/3/2022 10:15:54 AM [Thread:3860] [NativeOdbcConnection:InformationEvent] (Info, Normal): NativeOdbcConnection.Open, ConnectionString: Dsn=PostgreSQL35W;uid=saleor;server=localhost;port=5432;database=saleor, CreateOptions: ReadExternal, PacketSize: 0, EnableConnectionPooling: True, TransactionPropagationToken: null, ResourceGroup: null [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296] 12/3/2022 10:15:54 AM [Thread:3860] [NativeOdbcConnection:InformationEvent] (Info, Normal): NativeOdbcConnection.Open, ConnectionString: Dsn=PostgreSQL35W;uid=saleor;server=localhost;port=5432;database=saleor, CreateOptions: ReadExternal, PacketSize: 0, EnableConnectionPooling: True, TransactionPropagationToken: null, ResourceGroup: null [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296] 12/3/2022 10:15:54 AM [Thread:3860] [NativeOdbcConnection:InformationEvent] (Info, Normal): NativeOdbcConnection.Open, ConnectionString: Dsn=PostgreSQL35W;uid=saleor;server=localhost;port=5432;database=saleor, CreateOptions: ReadExternal, PacketSize: 0, EnableConnectionPooling: True, TransactionPropagationToken: null, ResourceGroup: null [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296] 12/3/2022 10:15:54 AM [Thread:3860] [NativeOdbcConnection:InformationEvent] (Info, Normal): NativeOdbcConnection.Open, ConnectionString: Dsn=PostgreSQL35W;uid=saleor;server=localhost;port=5432;database=saleor, CreateOptions: ReadExternal, PacketSize: 0, EnableConnectionPooling: True, TransactionPropagationToken: null, ResourceGroup: null [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296] 12/3/2022 10:15:55 AM [Thread:3860] [NativeOdbcConnection:InformationEvent] (Info, Normal): NativeOdbcConnection.Open, ConnectionString: Dsn=PostgreSQL35W;uid=saleor;server=localhost;port=5432;database=saleor, CreateOptions: ReadExternal, PacketSize: 0, EnableConnectionPooling: True, TransactionPropagationToken: null, ResourceGroup: null [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296] 12/3/2022 10:15:55 AM [Thread:3860] [ExecuteMemoStatement:InformationEvent] (Info, Normal): Operation on External Table [SaleorApp] with LOB columns : Columns 17, LOBColumns 4, LOBStringColumns 4. [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296] 12/3/2022 10:15:55 AM [Thread:3860] [LimitNumberOfScaOpConst:InformationEvent] (Info, Normal): The number of literals in the query: 0. [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296] 12/3/2022 10:15:55 AM [Thread:3860] [OptimizedStatement:InformationEvent] (Info, Normal): Distributed QO time: 728.4171 ms [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296] 12/3/2022 10:15:55 AM [Thread:3860] [DMSPlanBuilder:InformationEvent] (Info, Normal): Loading into a table with Small resource class: Parallel writers will be disabled during this operation. [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296] 12/3/2022 10:15:55 AM [Thread:3860] [DMSPlanBuilder:InformationEvent] (Info, Normal): Loading into a table with Small resource class: Parallel writers will be disabled during this operation. [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296] 12/3/2022 10:15:55 AM [Thread:3860] [DMSPlanBuilder:InformationEvent] (Info, Normal): Loading into a table with Small resource class: Parallel writers will be disabled during this operation. [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296] 12/3/2022 10:15:55 AM [Thread:3860] [DMSPlanBuilder:InformationEvent] (Info, Normal): Loading into a table with Small resource class: Parallel writers will be disabled during this operation. [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296] 12/3/2022 10:15:55 AM [Thread:3860] [DMSPlanBuilder:InformationEvent] (Info, Normal): Loading into a table with Small resource class: Parallel writers will be disabled during this operation. [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296] 12/3/2022 10:15:55 AM [Thread:3860] [DMSPlanBuilder:InformationEvent] (Info, Normal): Loading into a table with Small resource class: Parallel writers will be disabled during this operation. [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296] 12/3/2022 10:15:55 AM [Thread:3860] [DMSPlanBuilder:InformationEvent] (Info, Normal): Loading into a table with Small resource class: Parallel writers will be disabled during this operation. [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296] 12/3/2022 10:15:55 AM [Thread:3860] [DMSPlanBuilder:InformationEvent] (Info, Normal): Loading into a table with Small resource class: Parallel writers will be disabled during this operation. [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296] 12/3/2022 10:15:55 AM [Thread:3860] [DMSPlanBuilder:InformationEvent] (Info, Normal): Loading into a table with Small resource class: Parallel writers will be disabled during this operation. [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296] 12/3/2022 10:15:55 AM [Thread:3860] [DMSPlanBuilder:InformationEvent] (Info, Normal): Loading into a table with Small resource class: Parallel writers will be disabled during this operation. [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296] 12/3/2022 10:15:55 AM [Thread:3860] [DMSPlanBuilder:InformationEvent] (Info, Normal): Loading into a table with Small resource class: Parallel writers will be disabled during this operation. [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296] 12/3/2022 10:15:55 AM [Thread:3860] [DMSPlanBuilder:InformationEvent] (Info, Normal): Loading into a table with Small resource class: Parallel writers will be disabled during this operation. [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296] 12/3/2022 10:15:55 AM [Thread:3860] [DMSPlanBuilder:InformationEvent] (Info, Normal): Loading into a table with Small resource class: Parallel writers will be disabled during this operation. [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296] 12/3/2022 10:15:55 AM [Thread:3860] [DMSPlanBuilder:InformationEvent] (Info, Normal): Loading into a table with Small resource class: Parallel writers will be disabled during this operation. [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296] 12/3/2022 10:15:55 AM [Thread:3860] [DMSPlanBuilder:InformationEvent] (Info, Normal): Loading into a table with Small resource class: Parallel writers will be disabled during this operation. [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296] 12/3/2022 10:15:55 AM [Thread:3860] [DMSPlanBuilder:InformationEvent] (Info, Normal): Loading into a table with Small resource class: Parallel writers will be disabled during this operation. [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296] 12/3/2022 10:15:55 AM [Thread:3860] [DMSManager:InformationEvent] (Info, Normal): ExecuteCommand Query:SID3171 Plan:3e7c8916-13e2-41c2-a8cf-8c32837d290f [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296] 12/3/2022 10:15:55 AM [Thread:3860] [DMSManager:InformationEvent] (Info, Normal): DMS Manager starting query: SID3171, plan: 3e7c8916-13e2-41c2-a8cf-8c32837d290f. [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296] 12/3/2022 10:15:55 AM [Thread:3860] [AbstractDataMovementExecutable`1:InformationEvent] (Info, Normal): DMS Manager finishing query: SID3171, plan: 3e7c8916-13e2-41c2-a8cf-8c32837d290f, queryId: QID3296 [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296] 12/3/2022 10:15:55 AM [Thread:3860] [ServerInterface:InformationEvent] (Info, Normal): Query SID3171:QID3296 completed. [Session.SessionId:SID3171][Session.IsTransactional:False][Query.QueryId:QID3296] 12/3/2022 10:15:55 AM [Thread:1940] [EngineInstrumentation:TdsAddConnectionEvent] (Info, Low): Starting TDS connection: 1929427042368 Client: , isFromDataSecurityProxy: False, isVnetAddress: False, clientAddressSafe: 12/3/2022 10:15:55 AM [Thread:2304] [EngineInstrumentation:EngineStopSessionBeginEvent] (Info, Low): Stopping session, in reset connection: False. [Session.SessionId:SID3171][Session.IsTransactional:False] 12/3/2022 10:15:55 AM [Thread:2304] [EngineInstrumentation:TdsRemoveConnectionEvent] (Info, Low): Stopping TDS connection: 1929427025984 12/3/2022 10:15:55 AM [Thread:5232] [EngineInstrumentation:TdsRemoveConnectionEvent] (Info, Low): Stopping TDS connection: 1929427042368 12/3/2022 10:15:55 AM [Thread:2512] [TdsRequestProcessor:InformationEvent] (Info, Normal): CancelBatch request started [Session.SessionId:SID3171] 12/3/2022 10:15:55 AM [Thread:2288] [EngineInstrumentation:EngineCancelQueryBeginEvent] (Info, Low): Cancel requested. [Session.SessionId:SID3171][Session.IsTransactional:False] 12/3/2022 10:15:55 AM [Thread:2512] [EngineInstrumentation:TdsRemoveConnectionEvent] (Info, Low): Stopping TDS connection: 1929345925184 12/3/2022 10:15:55 AM [Thread:2512] [EngineInstrumentation:EngineStopSessionBeginEvent] (Info, Low): Stopping session, in reset connection: False. [Session.SessionId:SID3171][Session.IsTransactional:False]
I don't know why that is, any help for me??? Thanks for watching
