MapGuide Enterprise Wishes

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

Secondary source - SQLServer performance

2036 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,036 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: 273
Registered: ‎10-06-2008
Message 3 of 6 (2,036 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,036 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,713 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,354 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)

 

 

 

 

You are not logged in.

Log into access your profile, ask and answer questions, share ideas and more. Haven't signed up yet? Register

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 to get help installing your software.

Ask the Community