1

i have this problem:

Making queries over linked server to postgresql is very slow, very, very slow, for example:

if i run in pgAdmin this query:

select max(oldmedicionid) from tl.tlinputtable

it returns the max result in just: 246 msec

But if i run that over linked server (using sqlserver 2008), i create the dblink using an odbc to postgresql, so if i run this:

select MAX(oldmedicionid) from LINKPDATL.PDATL.tl.tlinputtable

the query give me result in 1 minute or more sometimes...

What could be my problema?, i think is not with my postgresql database, is something like the dblink is very slow,

How can i improve the performance?

Max Pinto
  • 1,463
  • 3
  • 16
  • 29
  • What happens when you run `SELECT max_oldmedicionid FROM OPENQUERY(LINKPDATL,'select max(oldmedicionid) max_oldmedicionid from PDATL.tl.tlinputtable')`? – Bacon Bits Feb 29 '16 at 18:07
  • with this way works perfect, why only with openquery works fast? – Max Pinto Mar 01 '16 at 20:46
  • As @RobinsTharakan's answer suggests, the aggregation is probably being performed by SQL Server. That means the query being run on the PostgreSQL server is essentially `SELECT oldmedicionid FROM tl.tlinputtable`, then that entire result set is sent to the MS SQL Server, and the MS SQL Server is doing the aggregation. It's just poor optimization on the part of either SQL Server or the PostgreSQL provider. `OPENQUERY()` allows you to control exactly what the query being used is. – Bacon Bits Mar 01 '16 at 21:59

1 Answers1

4

Identification: There are good chances that the aggregation MAX(x) in the ODBC method is being done at the client side (not at the server side). This can easily be cross-checked by seeing that doubling the row-count would approximately double the Query time as well.

Resolution: If this is among a few corner cases, you could create a VIEW that computes this on the Postgres server-side, and the ODBC pick the aggregated value.

Robins Tharakan
  • 2,209
  • 19
  • 17