- Thank you received: 0
Performace issues
- agierbolini
- Topic Author
- Visitor
-
9 years 11 months ago #9930
by agierbolini
Performace issues was created by agierbolini
Hello I have a issue of performace
it take more than 3 minutes
if i use
it take less than a second
but i can't safe the returning value into a variable
What can i do? I try
and i receive the following error
OLE DB provider "PGNP" for linked server "LINKSERVER" returned message "MSDTC XARMCreate error".
Msg 7391, Level 16, State 2, Line 1
The operation could not be performed because OLE DB provider "PGNP" for linked server "LINKSERVER" was unable to begin a distributed transaction.
FYI: the table have about 1,000,000 records
Code:
declare @basecode
Select @basegcode = 'ABCDEFGH'
declare @parm as int
Select @parm = count(column) from LINKSERVER.DATABASE.schemma.table
where left(column, 8) = @basegcode
it take more than 3 minutes
if i use
Code:
EXEC ( 'SELECT count(*) as column
FROM DATABASE.schemma
where left(column, 8) = ''ABCDEFGH''') AT LINKSERVER;
it take less than a second
but i can't safe the returning value into a variable
What can i do? I try
Code:
CREATE TABLE #msver
(internal_value INT)
Insert #msver
EXEC ( 'SELECT count(*) as column
FROM DATABASE.schemma
where left(column, 8) = ''ABCDEFGH''') AT LINKSERVER;
OLE DB provider "PGNP" for linked server "LINKSERVER" returned message "MSDTC XARMCreate error".
Msg 7391, Level 16, State 2, Line 1
The operation could not be performed because OLE DB provider "PGNP" for linked server "LINKSERVER" was unable to begin a distributed transaction.
FYI: the table have about 1,000,000 records
Please Log in or Create an account to join the conversation.
- Moderator
-
- Offline
- New Member
Less
More
9 years 11 months ago #9932
by Moderator
Replied by Moderator on topic Re: Performace issues
This performance issue occurs because of the way the linked server is implemented in SQL Server. First sample code results in retrieving all rows from Postgres, sending them to SQL Server, and then counting them. This is why second sample is significantly faster.
We recommend using "openrowset" syntax, for example:
We recommend using "openrowset" syntax, for example:
Code:
select * from openrowset(linkedsvr, 'select count(*) from tbl')
Please Log in or Create an account to join the conversation.
Time to create page: 0.157 seconds