Sample Header Ad - 728x90

Polybase In SQL server 2019 and 2022 take so long time

1 vote
0 answers
479 views
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: 1. When i run in pgadmin enter image description here 2. run with Linked Server enter image description here **As you can see it took only 329 ms** 2. 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
Asked by Duc Trinh (11 rep)
Dec 3, 2022, 03:10 AM
Last activity: Dec 9, 2022, 04:59 AM