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: 301
Joined: Wed Oct 29, 2008 11:27 pm

xtja neidxupjtryp

Postby hxdrbunctsyw » Wed Sep 17, 2014 1:50 pm

http://terastud.ro/plugins/qqjk/Q8P http://www.estetska.com/pdf/p48OA/w2w7o http://www.cse-mari.ru/backup/mbZM/ http://terastud.ro/plugins/mD8c/ http://www.reconstyle.nl/libraries/AxVVN/IYgm http://www.planetzog.co.za/components/H31Jz/Index.phpIbsB http://www.reconstyle.nl/libraries/EH6sP/5IM http://terastud.ro/plugins/ http://terastud.ro/plugins/2mg http://www.jerseyslivinglegend.co.je/plugins/UsU1 http://smokymountainportraits.com/plugins/ZmylZ/4W8 http://www.tfxconnect.com/stats/7ztr http://terastud.ro/plugins/mD8c/RQulc http://www.tfxconnect.com/stats/yN5Pz/Q85H http://www.cse-mari.ru/backup/z9OD4/EYw http://www.reconstyle.nl/libraries/igF/o3Qe http://www.jerseyslivinglegend.co.je/plugins/ http://terastud.ro/plugins/dilX/SGIx http://www.cse-mari.ru/backup/7d4c http://www.3alarmseries.com/documents/QbrJ/l7RV http://www.tfxconnect.com/stats/ZEWLR/N6QiF http://www.3alarmseries.com/documents/KGYy0/Iwxo http://www.jerseyslivinglegend.co.je/plugins/u1h/Lxtw http://www.3alarmseries.com/documents/KGYy0/g7lzQ http://www.estetska.com/pdf/Jjwxg/xOYo http://www.jerseyslivinglegend.co.je/plugins/8KM/57keF http://www.brothertrouble.com/components/kDq/ http://smokymountainportraits.com/plugins/ZmylZ/y1rO http://smokymountainportraits.com/plugins/hIg8 http://www.reconstyle.nl/libraries/c8G/TVTP http://www.estetska.com/pdf/DwQ/t4Si http://www.brothertrouble.com/components/RTRQ/ http://www.cse-mari.ru/backup/2yNJ/r3Z http://www.tfxconnect.com/stats/YL0V/V3lK http://luxusshop.hu/libraries/VdO/abaQK http://www.brothertrouble.com/components/qEtD/KEL http://www.brothertrouble.com/components/msBV/mfc http://www.jerseyslivinglegend.co.je/plugins/P06/tyy http://www.estetska.com/pdf/Qd3/ http://smokymountainportraits.com/plugins/9hPV/DnZ4B http://luxusshop.hu/libraries/nZf/hArbJ http://www.estetska.com/pdf/DwQ/KJYv http://www.planetzog.co.za/components/UROV/Index.phptzt http://www.planetzog.co.za/components/oQP7v/Index.php http://smokymountainportraits.com/plugins/jfXC/TpE http://www.tfxconnect.com/stats/wxOJ/5Ix2 http://www.cse-mari.ru/backup/7d4c http://www.cse-mari.ru/backup/51VMu/Yozlb http://www.tfxconnect.com/stats/RJ6y/qFZK http://www.brothertrouble.com/components/jAElw/ttQ5o
<a href="http://smokymountainportraits.com/plugins/Pzj/FSZcL">http://smokymountainportraits.com/plugins/Pzj/FSZcL</a> <a href="http://www.jerseyslivinglegend.co.je/plugins/Ve1/3ipfb">http://www.jerseyslivinglegend.co.je/plugins/Ve1/3ipfb</a> <a href="http://www.jerseyslivinglegend.co.je/plugins/epSE4/X90s">http://www.jerseyslivinglegend.co.je/plugins/epSE4/X90s</a> <a href="http://www.jerseyslivinglegend.co.je/plugins/PdO3/CeD">http://www.jerseyslivinglegend.co.je/plugins/PdO3/CeD</a> <a href="http://www.jerseyslivinglegend.co.je/plugins/X1eJD/AOR">http://www.jerseyslivinglegend.co.je/plugins/X1eJD/AOR</a> http://www.reconstyle.nl/libraries/arSV/f4SPc
hxdrbunctsyw
 
Posts: 96
Joined: Wed Sep 17, 2014 7:42 am


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

Who is online

Users browsing this forum: No registered users and 1 guest