Performace issues

  • agierbolini
  • Topic Author
  • Visitor
  • Visitor
9 years 4 days ago #9930 by agierbolini
Performace issues was created by agierbolini
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
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:
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
Powered by Kunena Forum