0

I have one external table that has only 2 columns and 3 rows in it and when i run query to fetch data using external table that take almost 15 mins to get the data. I am not sure why that much slow as there is only 3 row and 2 columns in it.

This test i am running on local database using SQL Polybase service.

Approach i have followed is created Module table in main database and created external table on another database and trying to fetch from other database. I used below script to create source and external table.

CREATE LOGIN testlocal
WITH PASSWORD = 'test#!451235'

CREATE USER [testlocal] 
FOR LOGIN [testlocal] 
WITH DEFAULT_SCHEMA = dbo; 

ALTER ROLE db_datareader ADD MEMBER [testlocal]; 

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'test#!451235';

create DATABASE SCOPED CREDENTIAL CrossDbCredLocal  
WITH IDENTITY = 'testlocal',                    
SECRET = 'test#!451235';                    
GO

CREATE EXTERNAL DATA SOURCE testlocal
WITH (
  LOCATION = 'sqlserver://localhost' ,
  CREDENTIAL = CrossDbCredLocal
);

CREATE EXTERNAL TABLE dbo.Module
(
    ID int NOT NULL,
    Name varchar(50) NOT NULL
)
WITH
(
LOCATION='ModuleIdentity.dbo.Module',
DATA_SOURCE = testlocal
);

I am missing something or did not configured properly?

4
  • Is this an external table to a database in the same SQL Server instance? If so, why not just use a "raw" cross-database query? (i.e. SELECT foo FROM dbName.schema.TableName)? You don't need Polybase for that. Commented Jan 5, 2024 at 15:13
  • @Dai Yes its on same SQL server but problem with this is i have to deploy this same setup on azure using Elastic Query so if i use way you suggesting then while deployment i have to change my query and code and i really dont prefer this. Commented Jan 5, 2024 at 15:21
  • @Dai On Azure SQL managed instance direct cross db access like dbName.schema.TableName is not allowed. Commented Jan 5, 2024 at 15:30
  • 1
    Have you performed an XEvents trace to investigate? If not, why not? Commented Jan 5, 2024 at 15:30

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.