MapGuide Enterprise Wishes

MapGuide Enterprise Wishes

Reply
Active Member
JohanJansma3145
Posts: 6
Registered: ‎01-22-2010
Message 1 of 6 (2,058 Views)

Secondary source - SQLServer performance

2058 Views, 5 Replies
03-24-2010 08:09 AM
Hello,

We are joining our geometric data with data in SQLServer, using the 'secondary source' feature. This works, but is a little bit slow. After running some traces with Profiler we discovered two things which aren't very effecient when Mapguide is getting data from SQLServer.

1) cursors are slow, it's better to use a set-based approach;
2) the IN() operator is also slow, it's better to use a table variable;

Could Autodesk change this two things? We are experiencing slow performance because of these two things and customers won't be happy when they have the same issues. Edited by: JohanJansma3145 on Mar 24, 2010 3:09 PM
Active Member
JohanJansma3145
Posts: 6
Registered: ‎01-22-2010
Message 2 of 6 (2,058 Views)

Re: Secondary source - SQLServer performance

04-09-2010 06:02 AM in reply to: JohanJansma3145
Anyone has an answer ?
Valued Mentor
jackie.ng
Posts: 279
Registered: ‎10-06-2008
Message 3 of 6 (2,058 Views)

Re: Secondary source - SQLServer performance

04-12-2010 06:29 PM in reply to: JohanJansma3145
Is the SQL Server connection through an ODBC provider? If so, then optimization opportunities are limited.

The ODBC provider is a "lowest common denominator" provider and while your suggestions have merit, those suggestions probably won't be acted upon if these optimizations are SQL Server specific.

- Jackie
Active Member
JohanJansma3145
Posts: 6
Registered: ‎01-22-2010
Message 4 of 6 (2,058 Views)

Re: Secondary source - SQLServer performance

04-13-2010 03:39 AM in reply to: JohanJansma3145
We're not using the ODBC provider, but connecting through the 'Autodesk FDO Provider for SQL Server'. I assume this is the quickest way to connect to a SQLServer database.
New Member
AnetteNorgaard6000
Posts: 1
Registered: ‎01-27-2009
Message 5 of 6 (1,735 Views)

Re: Secondary source - SQLServer performance

12-15-2010 04:33 AM in reply to: JohanJansma3145

Hello, I'm experiencing the same problems, especially when wanting to display tooltips and links. These were much faster using OSgeo FDO provider for OGR than the OSgeo FDO provider for SQL server spatial.

 

Were there ever a useful answer to this question?

 

 

 

Active Member
MikePT
Posts: 9
Registered: ‎09-23-2009
Message 6 of 6 (1,376 Views)

Re: Secondary source - SQLServer performance

11-28-2011 02:01 PM in reply to: jackie.ng

I just posted a new topic on sql performance issue....not sure if same? or related? 

 

We are connecting to SQL 2008 using a DSN using windows authentication.  If we switch to a connection string and a 2008 SQL Server and database that has SQL based userid/password authentication our performance is fast. With windows authentication though map load is 30+ seconds for initial load.

 

Is this a known issue? Are there performance issues with using windows authenticatiing SQL Server security and accessing databases with DSN?  I wasn't able to get AIMS 2012 Windows Authenticating SQL 2008 Spatial map layer configured in studio to load up without using the ODBC driver and a DSN.

(couldn't get dsn-less to work in AIMS studio for windows authenticating sql server)

 

 

 

 

Announcements
Are you familiar with the Autodesk Expert Elites? The Expert Elite program is made up of customers that help other customers by sharing knowledge and exemplifying an engaging style of collaboration. To learn more, please visit our Expert Elite website.
Need installation help?

Start with some of our most frequented solutions or visit the Installation and Licensing Forum to get help installing your software.