Performace issues

  • agierbolini
  • Topic Author
  • Visitor
  • Visitor
9 years 11 months ago #9930 by agierbolini
Performace issues was created by agierbolini
Hello I have a issue of performace
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;
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 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:
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
Powered by Kunena Forum