<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=utf-8"><meta name=Generator content="Microsoft Word 15 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Consolas;
panose-1:2 11 6 9 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
pre
{mso-style-priority:99;
mso-style-link:"HTML Preformatted Char";
margin:0in;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:"Courier New";}
span.HTMLPreformattedChar
{mso-style-name:"HTML Preformatted Char";
mso-style-priority:99;
mso-style-link:"HTML Preformatted";
font-family:"Consolas",serif;}
span.EmailStyle19
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=EN-US link=blue vlink=purple><div class=WordSection1><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Jack,<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>I'm guessing you have many tables in your SQL Server database is that right?<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>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.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><a href="https://wiki.osgeo.org/wiki/Paris_Code_Sprint_2016_:_GDAL_Agenda">https://wiki.osgeo.org/wiki/Paris_Code_Sprint_2016_:_GDAL_Agenda</a> (and if I can garner enough information, try to formulate a patch to submit).<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>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.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>The work around is to explicitly state the tables you need as part of the Foreign server definition<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>So change your CREATE SERVER to something like<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> FOREIGN DATA WRAPPER ogr_fdw<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> OPTIONS (<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> datasource 'ODBC:xxxxx/yyyyy(at)MedidataVPN,dbo.ObjectTypeR,dbo.AnotherTable',<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> format 'ODBC' );<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Then repeat.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>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.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>If you still have issues after that let me know.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Hope that helps,<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Regina<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:.5in'><b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>From:</span></b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'> postgis-users [mailto:postgis-users-bounces@lists.osgeo.org] <b>On Behalf Of </b>Jack Ort<br><b>Sent:</b> Friday, February 19, 2016 11:46 AM<br><b>To:</b> postgis-users@lists.osgeo.org<br><b>Subject:</b> [postgis-users] Where to Find Help with ogr_fdw ?<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'><o:p> </o:p></p><div><div><p class=MsoNormal style='margin-left:.5in'>Hello! I obtained ogr_fdw from the PostGIS winnie experimental builds ( <a href="http://winnie.postgis.net/download/windows/pg95/buildbot/extras/">http://winnie.postgis.net/download/windows/pg95/buildbot/extras/</a> ), which I learned about here - <a href="http://www.postgresonline.com/journal/index.php?url=archives/361-Foreign-Data-Wrappers-for-PostgreSQL-9.5-windows.html">http://www.postgresonline.com/journal/index.php?url=archives/361-Foreign-Data-Wrappers-for-PostgreSQL-9.5-windows.html</a><o:p></o:p></p></div><div><p class=MsoNormal style='margin-left:.5in'>------<o:p></o:p></p></div><div><p class=MsoNormal style='margin-left:.5in'>I originally posted this to pgsql-novice, but their moderator thought I might have better luck on a PostGIS forum.<o:p></o:p></p></div><div><p class=MsoNormal style='margin-left:.5in'><o:p> </o:p></p></div><div><div><p class=MsoNormal style='margin-left:.5in'>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.<o:p></o:p></p></div><div><p class=MsoNormal style='margin-left:.5in'><o:p> </o:p></p></div><div><p class=MsoNormal style='margin-left:.5in'>Best Regards,<o:p></o:p></p></div><div><p class=MsoNormal style='margin-left:.5in'>-Jack Ort<o:p></o:p></p></div></div><div><p class=MsoNormal style='margin-left:.5in'>------<o:p></o:p></p></div><div><div style='mso-element:para-border-div;border-top:1.5pt;border-left:1.0pt;border-bottom:1.5pt;border-right:1.0pt;border-color:windowtext;border-style:solid;padding:12.0pt 0in 12.0pt 24.0pt;margin-left:.5in;margin-right:0in'><pre style='border:none;padding:0in;word-wrap:break-word;white-space:pre-wrap;border-color:lightgray'><span style='font-size:14.5pt;color:black'>Installed postgres 9.5.1 using EnterpriseDB binary on a Windows 2012 R2<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>server.<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'><o:p> </o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>Downloaded org_fdw for x64 from <a href="http://postgis.net/"><span style='color:#00536E'>PostGIS.net</span></a> (<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'><a href="http://winnie.postgis.net/download/windows/pg95/buildbot/extras/ogrfdw-pg95-binaries-1.0.1w64gcc48.zip"><span style='color:#00536E'>http://winnie.postgis.net/download/windows/pg95/buildbot/extras/ogrfdw-pg95-binaries-1.0.1w64gcc48.zip</span></a>),<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>and copied files to 9.5 share\extension, \bin and \lib<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'><o:p> </o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>Using DependencyWalker, kept adding to my PATH variable for missing DLLs<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>until my CREATE EXTENSION command succeeded:<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>------<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>create extension ogr_fdw schema my_extensions;<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'><o:p> </o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>CREATE SERVER xyz<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'> FOREIGN DATA WRAPPER ogr_fdw<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'> OPTIONS (<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'> datasource 'ODBC:xxxxx/yyyyy(at)MedidataVPN',<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'> format 'ODBC' );<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'><o:p> </o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'><o:p> </o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>create schema if not exists xyz;<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'><o:p> </o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>import foreign schema ogr_all<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>from server xyz into xyz;<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'><o:p> </o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>All of this worked, although the CREATE SERVER command took about 5 minutes.<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>-------------------------------------------------------------------------<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>Using a VPN connection to a remote SQLServer database.<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'><o:p> </o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>Defined an 64-bit ODBC System DSN using the SQL Server driver (driver<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>version 6.03.9600.16384)<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'><o:p> </o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>Chose a simple 91-record table as my query target. SSMS shows the design<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>as:<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'><o:p> </o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>/****** Object: Table [dbo].[ObjectTypeR] Script Date: 2/17/2016<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>5:30:44 PM ******/<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>SET ANSI_NULLS ON<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>GO<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'><o:p> </o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>SET QUOTED_IDENTIFIER ON<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>GO<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'><o:p> </o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>SET ANSI_PADDING ON<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>GO<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'><o:p> </o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>CREATE TABLE [dbo].[ObjectTypeR](<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>[ObjectTypeID] [tinyint] NOT NULL,<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>[ObjectName] [varchar](100) NOT NULL,<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'> CONSTRAINT [PK_ObjectTypeR] PRIMARY KEY CLUSTERED<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>(<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>[ObjectTypeID] ASC<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, IGNORE_DUP_KEY = OFF,<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>) ON [PRIMARY]<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'><o:p> </o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>GO<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'><o:p> </o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>SET ANSI_PADDING OFF<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>GO<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>/***************************************/<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'><o:p> </o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>This query run on the Windows server that hosts postgres under Powershell<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>using ODBC gives an instant response:<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'><o:p> </o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>SELECT * FROM [dbo].[objecttyper]<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'><o:p> </o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>------------------<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>postgres' information_schema.columns shows this for the foreign table (I<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>wonder if that huge character_octet_length is a problem?) - OGR_FDW<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>apparently adds the fid and geom columns:<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'><o:p> </o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>table_name column_name ordinal_position column_default is_nullable data_type<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>character_maximum_length character_octet_length<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>dbo_objecttyper fid 1 YES integer<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>dbo_objecttyper geom 2 YES bytea<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>dbo_objecttyper objecttypeid 3 YES character varying 1073741824<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>dbo_objecttyper objectname 4 YES character varying 1073741824<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'><o:p> </o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>---------------------------<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>Running the equivalent query under pgAdmin III using the foreign server<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>defined above consistently takes > 10 minutes(!):<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'><o:p> </o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>EXPLAIN ANALYZE select * from xyz.dbo_objecttyper;<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'><o:p> </o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>"Foreign Scan on dbo_objecttyper (cost=25.00..1025.00 rows=1000 width=100)<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>(actual time=48.275..49.171 rows=91 loops=1)"<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>"Planning time: 316745.992 ms"<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>"Execution time: 316735.626 ms"<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'>----------------------------<o:p></o:p></span></pre><pre style='border:none;padding:0in'><span style='font-size:14.5pt;color:black'><o:p> </o:p></span></pre></div></div></div></div></body></html>