[postgis-users] Where to Find Help with ogr_fdw ?

[Regina Obe]

Jack,

 

I'm guessing you have many tables in your SQL Server database is that right?

 

This is one of the things on my todo for Paris Code Sprint to discuss and try  to tackle if we can improve GDAL performance with ODBC sources that have a large number of tables.

 

https://wiki.osgeo.org/wiki/Paris_Code_Sprint_2016_:_GDAL_Agenda  (and if I can garner enough information, try to formulate a patch to submit).

 

The issue seems to be that GDAL tries to query all the meta data of all the tables in an ODBC source so as the number of tables gets large, the performance gets to a point to become unusable.

 

The work around is to explicitly state the tables you need as part of the Foreign server definition

 

So change your CREATE SERVER to something like

 

  FOREIGN DATA WRAPPER ogr_fdw

  OPTIONS (

    datasource 'ODBC:xxxxx/yyyyy(at)MedidataVPN,dbo.ObjectTypeR,dbo.AnotherTable',

    format 'ODBC' );

 

Then repeat.

 

Other minor gripe I have about ODBC is that it always adds a geom column even when there is no geometry column in table.  This again I traced back to GDAL (not ogrfdw).  Another on my todo to fix, but less annoying than the meta data table scan.

 

If you still have issues after that let me know.

 

Hope that helps,

Regina

 

 

 

 

From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Jack Ort
Sent: Friday, February 19, 2016 11:46 AM
To: postgis-users at lists.osgeo.org
Subject: [postgis-users] Where to Find Help with ogr_fdw ?

 

Hello! I obtained ogr_fdw from the PostGIS winnie experimental builds ( http://winnie.postgis.net/download/windows/pg95/buildbot/extras/ ), which I learned about here - http://www.postgresonline.com/journal/index.php?url=archives/361-Foreign-Data-Wrappers-for-PostgreSQL-9.5-windows.html

------

I originally posted this to pgsql-novice, but their moderator thought I might have better luck on a PostGIS forum.

 

I welcome any suggestions on where to turn for help!  I really need this to work!!  The full text of my pgsql-novice post is below.

 

Best Regards,

-Jack Ort

------

Installed postgres 9.5.1 using EnterpriseDB binary on a Windows 2012 R2
server.
 
Downloaded org_fdw for x64 from  <http://postgis.net/> PostGIS.net (
 <http://winnie.postgis.net/download/windows/pg95/buildbot/extras/ogrfdw-pg95-binaries-1.0.1w64gcc48.zip> http://winnie.postgis.net/download/windows/pg95/buildbot/extras/ogrfdw-pg95-binaries-1.0.1w64gcc48.zip),
and copied files to 9.5 share\extension, \bin and \lib
 
Using DependencyWalker, kept adding to my PATH variable for missing DLLs
until my CREATE EXTENSION command succeeded:
------
create extension ogr_fdw schema my_extensions;
 
CREATE SERVER xyz
  FOREIGN DATA WRAPPER ogr_fdw
  OPTIONS (
    datasource 'ODBC:xxxxx/yyyyy(at)MedidataVPN',
    format 'ODBC' );
 
 
create schema if not exists xyz;
 
import foreign schema ogr_all
from server xyz into xyz;
 
All of this worked, although the CREATE SERVER command took about 5 minutes.
-------------------------------------------------------------------------
Using a VPN connection to a remote SQLServer database.
 
Defined an 64-bit ODBC System DSN using the SQL Server driver (driver
version 6.03.9600.16384)
 
Chose a simple 91-record table as my query target.  SSMS shows the design
as:
 
/****** Object:  Table [dbo].[ObjectTypeR]    Script Date: 2/17/2016
5:30:44 PM ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
SET ANSI_PADDING ON
GO
 
CREATE TABLE [dbo].[ObjectTypeR](
[ObjectTypeID] [tinyint] NOT NULL,
[ObjectName] [varchar](100) NOT NULL,
 CONSTRAINT [PK_ObjectTypeR] PRIMARY KEY CLUSTERED
(
[ObjectTypeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
SET ANSI_PADDING OFF
GO
/***************************************/
 
This query run on the Windows server that hosts postgres under Powershell
using ODBC gives an instant response:
 
SELECT * FROM [dbo].[objecttyper]
 
------------------
postgres' information_schema.columns shows this for the foreign table (I
wonder if that huge character_octet_length is a problem?) - OGR_FDW
apparently adds the fid and geom columns:
 
table_name column_name ordinal_position column_default is_nullable data_type
character_maximum_length character_octet_length
dbo_objecttyper fid 1 YES integer
dbo_objecttyper geom 2 YES bytea
dbo_objecttyper objecttypeid 3 YES character varying 1073741824
dbo_objecttyper objectname 4 YES character varying 1073741824
 
---------------------------
Running the equivalent query under pgAdmin III using the foreign server
defined above consistently takes > 10 minutes(!):
 
EXPLAIN ANALYZE select * from xyz.dbo_objecttyper;
 
"Foreign Scan on dbo_objecttyper  (cost=25.00..1025.00 rows=1000 width=100)
(actual time=48.275..49.171 rows=91 loops=1)"
"Planning time: 316745.992 ms"
"Execution time: 316735.626 ms"
----------------------------
 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20160219/56155f2d/attachment.html>