Performace issues

Using PGNP with MS SQL Server, Data Transformation Services (DTS), SQL Server Integration Services (SSIS), Linked Servers, Snapshot and Transactional Replication and more.

Performace issues

Postby agierbolini » Fri Jun 21, 2013 2:25 pm

Hello I have a issue of performace

Code: Select all
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: Select all
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: Select all
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
agierbolini
 
Posts: 2
Joined: Wed Dec 12, 2012 6:42 pm

Re: Performace issues

Postby Moderator » Mon Jun 24, 2013 7:06 pm

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 all
  select * from openrowset(linkedsvr, 'select count(*) from tbl')
User avatar
Moderator
Site Admin
 
Posts: 303
Joined: Wed Oct 29, 2008 11:27 pm


Return to SQL Server/DTS/SSIS/Linked servers/Replication

Who is online

Users browsing this forum: No registered users and 1 guest

cron