Re: [mapguide-users] sqlserver view is no limit row search STSrid ?
Hi Jackie sorry about my previous Email select top 1 GeomColumnName from GeomTableName where GeomColumnName is not null if the all geom data is null will cause full table scan, make sqlserver cpu busy again. Please keep now version SampleColumnSrid is good sample . Just select top 1 GeomColumnName from GeomTableName From: 周宜和 [mailto:j...@mail.gaias.com.tw] Sent: Saturday, September 11, 2021 9:39 AM To: 'MapGuide Users Mail List' Subject: RE: [mapguide-users] sqlserver view is no limit row search STSrid ? Hi Jackie I try sqlserverProvider is work find. Thanks for great work. I still have some question,source code url https://trac.osgeo.org/fdo/browser/trunk/Providers/GenericRdbms/Src/SQLServerSpatial/SchemaMgr/Ph/ColumnGeom.cpp#L100 https://trac.osgeo.org/fdo/browser/trunk/Providers/GenericRdbms/Src/SQLServerSpatial/SchemaMgr/Ph/Owner.cpp#L429 If first row is null data mSRID = owner->SampleColumnSrid( GetParent()->GetName(), GetName() ); mSRID will get -1 then next time into FdoSmPhSqsColumnGeom::GetSRID(),the mSRID is -1 will select top 1 then return -1 to mSRID,again and again. How about select top 1 GeomColumnName from GeomTableName where GeomColumnName is not null From: mapguide-users [mailto:mapguide-users-boun...@lists.osgeo.org] On Behalf Of Gordon Luckett Sent: Friday, September 10, 2021 6:22 PM To: MapGuide Users Mail List mailto:mapguide-users@lists.osgeo.org> > Subject: Re: [mapguide-users] sqlserver view is no limit row search STSrid ? Hi Jackie, I just wanted to confirm that the build works great and the speed at getting the STSrid in SQL Server is very fast now. On Thu, Sep 9, 2021 at 11:12 AM Jackie Ng mailto:jumpinjac...@gmail.com> > wrote: Please try the following patched SQL Server provider https://download.osgeo.org/mapguide/patches/SQLServerProvider_Ticket996_FDO4.1/SQLServerProvider_Ticket996.zip Extract and overwrite your copy in your MGOS 3.1.2 install - Jackie You wrote: thanks for open active ticket <https://trac.osgeo.org/fdo/ticket/996> https://trac.osgeo.org/fdo/ticket/996 I found mysql is fix by Jan 7, 2020 <https://trac.osgeo.org/fdo/ticket/893#comment:5> https://trac.osgeo.org/fdo/ticket/893#comment:5 <https://trac.osgeo.org/fdo/attachment/ticket/893/LimitOneSrid.patch> https://trac.osgeo.org/fdo/attachment/ticket/893/LimitOneSrid.patch -- Please Note: I no longer create new posts or post replies to any OSGeo mailing list through nabble. As a result, you most likely won't see this message appear on nabble's view of any OSGeo mailing list and may only see this message through mailing list archives or depending on your mailing list subscription settings, through daily message digests or automated notifications from the mailing lists. ___ mapguide-users mailing list mapguide-users@lists.osgeo.org <mailto:mapguide-users@lists.osgeo.org> https://lists.osgeo.org/mailman/listinfo/mapguide-users ___ mapguide-users mailing list mapguide-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/mapguide-users
Re: [mapguide-users] sqlserver view is no limit row search STSrid ?
Hi Jackie I try sqlserverProvider is work find. Thanks for great work. I still have some question,source code url https://trac.osgeo.org/fdo/browser/trunk/Providers/GenericRdbms/Src/SQLServerSpatial/SchemaMgr/Ph/ColumnGeom.cpp#L100 https://trac.osgeo.org/fdo/browser/trunk/Providers/GenericRdbms/Src/SQLServerSpatial/SchemaMgr/Ph/Owner.cpp#L429 If first row is null data mSRID = owner->SampleColumnSrid( GetParent()->GetName(), GetName() ); mSRID will get -1 then next time into FdoSmPhSqsColumnGeom::GetSRID(),the mSRID is -1 will select top 1 then return -1 to mSRID,again and again. How about select top 1 GeomColumnName from GeomTableName where GeomColumnName is not null From: mapguide-users [mailto:mapguide-users-boun...@lists.osgeo.org] On Behalf Of Gordon Luckett Sent: Friday, September 10, 2021 6:22 PM To: MapGuide Users Mail List Subject: Re: [mapguide-users] sqlserver view is no limit row search STSrid ? Hi Jackie, I just wanted to confirm that the build works great and the speed at getting the STSrid in SQL Server is very fast now. On Thu, Sep 9, 2021 at 11:12 AM Jackie Ng mailto:jumpinjac...@gmail.com> > wrote: Please try the following patched SQL Server provider https://download.osgeo.org/mapguide/patches/SQLServerProvider_Ticket996_FDO4.1/SQLServerProvider_Ticket996.zip Extract and overwrite your copy in your MGOS 3.1.2 install - Jackie You wrote: thanks for open active ticket <https://trac.osgeo.org/fdo/ticket/996> https://trac.osgeo.org/fdo/ticket/996 I found mysql is fix by Jan 7, 2020 <https://trac.osgeo.org/fdo/ticket/893#comment:5> https://trac.osgeo.org/fdo/ticket/893#comment:5 <https://trac.osgeo.org/fdo/attachment/ticket/893/LimitOneSrid.patch> https://trac.osgeo.org/fdo/attachment/ticket/893/LimitOneSrid.patch -- Please Note: I no longer create new posts or post replies to any OSGeo mailing list through nabble. As a result, you most likely won't see this message appear on nabble's view of any OSGeo mailing list and may only see this message through mailing list archives or depending on your mailing list subscription settings, through daily message digests or automated notifications from the mailing lists. ___ mapguide-users mailing list mapguide-users@lists.osgeo.org <mailto:mapguide-users@lists.osgeo.org> https://lists.osgeo.org/mailman/listinfo/mapguide-users ___ mapguide-users mailing list mapguide-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/mapguide-users
Re: [mapguide-users] sqlserver view is no limit row search STSrid ?
Hi Jackie, I just wanted to confirm that the build works great and the speed at getting the STSrid in SQL Server is very fast now. On Thu, Sep 9, 2021 at 11:12 AM Jackie Ng wrote: > Please try the following patched SQL Server provider > > > https://download.osgeo.org/mapguide/patches/SQLServerProvider_Ticket996_FDO4.1/SQLServerProvider_Ticket996.zip > > Extract and overwrite your copy in your MGOS 3.1.2 install > > - Jackie > > You wrote: > > thanks for open active ticket > > https://trac.osgeo.org/fdo/ticket/996 > > I found mysql is fix by Jan 7, 2020 > > https://trac.osgeo.org/fdo/ticket/893#comment:5 > > https://trac.osgeo.org/fdo/attachment/ticket/893/LimitOneSrid.patch > > > -- > *Please Note: I no longer create new posts or post replies to any OSGeo > mailing list through nabble. As a result, you most likely won't see this > message appear on nabble's view of any OSGeo mailing list and may only see > this message through mailing list archives or depending on your mailing > list subscription settings, through daily message digests or automated > notifications from the mailing lists.* > ___ > mapguide-users mailing list > mapguide-users@lists.osgeo.org > https://lists.osgeo.org/mailman/listinfo/mapguide-users > ___ mapguide-users mailing list mapguide-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/mapguide-users
Re: [mapguide-users] sqlserver view is no limit row search STSrid ?
Please try the following patched SQL Server provider https://download.osgeo.org/mapguide/patches/SQLServerProvider_Ticket996_FDO4.1/SQLServerProvider_Ticket996.zip Extract and overwrite your copy in your MGOS 3.1.2 install - Jackie You wrote: thanks for open active ticket https://trac.osgeo.org/fdo/ticket/996 I found mysql is fix by Jan 7, 2020 https://trac.osgeo.org/fdo/ticket/893#comment:5 https://trac.osgeo.org/fdo/attachment/ticket/893/LimitOneSrid.patch -- *Please Note: I no longer create new posts or post replies to any OSGeo mailing list through nabble. As a result, you most likely won't see this message appear on nabble's view of any OSGeo mailing list and may only see this message through mailing list archives or depending on your mailing list subscription settings, through daily message digests or automated notifications from the mailing lists.* ___ mapguide-users mailing list mapguide-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/mapguide-users
Re: [mapguide-users] sqlserver view is no limit row search STSrid ?
thanks for open active ticket https://trac.osgeo.org/fdo/ticket/996 I found mysql is fix by Jan 7, 2020 https://trac.osgeo.org/fdo/ticket/893#comment:5 https://trac.osgeo.org/fdo/attachment/ticket/893/LimitOneSrid.patch From: mapguide-users [mailto:mapguide-users-boun...@lists.osgeo.org] On Behalf Of Gordon Luckett Sent: Wednesday, September 8, 2021 10:41 PM To: MapGuide Users Mail List Subject: Re: [mapguide-users] sqlserver view is no limit row search STSrid ? All Products that use FDO for SQL Server have this problem. It was never fixed in the FDO library. MapGuide, Infraworks, AutoCAD Map etc all use the FDO provider and this runs a full select on every table and view just to get the STSrid for each one. On Wed, Sep 8, 2021 at 8:49 AM 周宜和 mailto:j...@mail.gaias.com.tw> > wrote: Hi, I got cpu busy with sqlserver, My Web Server is mapguide 3.1.2, Target layer is 15 thousand address point of view, Then i find source code when table select top 1,but view is no limit row search STSrid. when layer preview from view , effect sqlserver cpu high loading. Source code below,or open url FdoStringP sqlStmt = FdoStringP::Format( isview ? L"select [%ls].STSrid as srid from %ls.%ls" : L"select top 1 [%ls].STSrid as srid from %ls.%ls", (FdoString*) columnName, (FdoString*) this->GetDbName(), (FdoString*) fmtObjectName ); https://trac.osgeo.org/fdo/browser/trunk/Providers/GenericRdbms/Src/SQLServerSpatial/SchemaMgr/Ph/Owner.cpp#L414 and mysql has limit 1 for search STSrid FdoStringP sqlStmt = FdoStringP::Format( L"select ST_SRID(%ls) as srid from %ls limit 1", (FdoString*)this->GetDbName(), (FdoString*)dbObject->GetDbName()); https://trac.osgeo.org/fdo/browser/trunk/Providers/GenericRdbms/Src/MySQL/SchemaMgr/Ph/ColumnGeom.cpp#L29 sorry for my bad engilsh ___ mapguide-users mailing list mapguide-users@lists.osgeo.org <mailto:mapguide-users@lists.osgeo.org> https://lists.osgeo.org/mailman/listinfo/mapguide-users ___ mapguide-users mailing list mapguide-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/mapguide-users
Re: [mapguide-users] sqlserver view is no limit row search STSrid ?
All Products that use FDO for SQL Server have this problem. It was never fixed in the FDO library. MapGuide, Infraworks, AutoCAD Map etc all use the FDO provider and this runs a full select on every table and view just to get the STSrid for each one. On Wed, Sep 8, 2021 at 8:49 AM 周宜和 wrote: > Hi, > > I got cpu busy with sqlserver, > > My Web Server is mapguide 3.1.2, > > Target layer is 15 thousand address point of view, > > Then i find source code when table select top 1,but view is no limit row > search STSrid. > > when layer preview from view , effect sqlserver cpu high loading. > > > > Source code below,or open url > > > > FdoStringP sqlStmt = *FdoStringP*::Format( > > isview ? L"select [%ls].STSrid as srid from %ls.%ls" : > L"select top 1 [%ls].STSrid as srid from %ls.%ls", > > (FdoString*) columnName, > > (FdoString*) this->GetDbName(), > > (FdoString*) fmtObjectName > > ); > > > > https://trac.osgeo.org/fdo/browser/trunk/Providers/GenericRdbms/Src/SQLServerSpatial/SchemaMgr/Ph/Owner.cpp#L414 > > > > and mysql has limit 1 for search STSrid > > > > FdoStringP sqlStmt = *FdoStringP*::Format( > > L"select ST_SRID(%ls) as srid from %ls limit 1", (FdoString*)this-> > GetDbName(), (FdoString*)dbObject->GetDbName()); > > > > > > > https://trac.osgeo.org/fdo/browser/trunk/Providers/GenericRdbms/Src/MySQL/SchemaMgr/Ph/ColumnGeom.cpp#L29 > > > > > > sorry for my bad engilsh > > > > ___ > mapguide-users mailing list > mapguide-users@lists.osgeo.org > https://lists.osgeo.org/mailman/listinfo/mapguide-users > ___ mapguide-users mailing list mapguide-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/mapguide-users
