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
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.
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?
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)