Performace issues
- agierbolini
- Topic Author
- Visitor
-
9 years 4 days 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
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
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
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.
9 years 1 day 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:
select * from openrowset(linkedsvr, 'select count(*) from tbl')
Please Log in or Create an account to join the conversation.
Time to create page: 0.057 seconds
- You are here:
-
Home
-
Forum
-
Main PGNP Forum
-
SQL Server/DTS/SSIS/Linked servers/Replication
- Performace issues