MapGuide Enterprise Wishes (Read Only)
cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Secondary source - SQLServer performance

5 REPLIES 5
Reply
Message 1 of 6
JohanJansma3145
2737 Views, 5 Replies

Secondary source - SQLServer performance

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
5 REPLIES 5
Message 2 of 6

Anyone has an answer ?
Message 3 of 6
jackie.ng
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
Message 4 of 6

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.
Message 5 of 6

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?

 

 

 

Message 6 of 6
MikePT
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)

 

 

 

 

Can't find what you're looking for? Ask the community or share your knowledge.

Post to forums  

Autodesk Design & Make Report