Performace issues

More
6 years 11 months ago #9930 by agierbolini
agierbolini created the topic: Performace issues
Hello I have a issue of performace
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;
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

Please Log in or Create an account to join the conversation.

More
6 years 11 months ago #9932 by Moderator
Moderator replied the 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:
select * from openrowset(linkedsvr, 'select count(*) from tbl')

Please Log in or Create an account to join the conversation.

Time to create page: 0.070 seconds
Powered by Kunena Forum