MSSQL+Oracle Spatial: Refresh cache (partially) or deactivate caching

[woehling]

I have layers that are connected to a spatial database (MSSQL and/or Oracle).

Because AutoCAD Map 3D caches all the data in a client side cache, I have to refresh the cache when data changes on the server side.

I know that I can use AcMapLayer.CreateCache() to recreate the cache. But because I have too much data, I can't recreate the cache every time, because it takes too long.

Is there a way to partially refresh the cache (e.g. by region or by adding objects manually)?

Or is there a way to fully deactivate the cache?

Markus

[hence_the_name]

Hi Markus,

only two suggestions related to your question:

(1) - in Map you can refresh a layer (via context menu) - not sure if there is a API call for that. Instead of refreshing the cache - refeshing one or only few layers might be an option?

(2) - add layers/data to Map and apply a filter (spatial or attribute data) - this will decrease the amount of cached data.

Good Luck, Rob

[woehling]

Hi,

(1) AcMapLayer.CreateCache() is the API call that recreates the cache per layer (that's what the context menu function does internally, I think).
(2) Applying a filter is not an option...

Markus

[jakub.bican]

Hi.

We have a some experience that the cache is not behaving correctly in the way that it contains features that it should not contain. E.g. when user generates graphics on larger area then zooms in and generates graphics on smaller area, some features of the larger area remain in the map.

Have you got any similar experience? Do you think that these calls should help to solve soch sutiation (e.g. adding a button into Map3D which should somehow refresh or cleanup the cache).

The last think is that we suspect the cache that it reduces the performance significantly - even moving cursor over the map and features is very slow "sometimes". This does not happen for DWG entities, only in case of FDO.

Jakub

[woehling]

Hi,

I need to refresh the map when data changes are made on the server side.

I'm also waiting for a more stable version, but I could not reproduce your specific problems.

Markus

[Daniel.Du]

Have you ever tried AcMapLayer::ForceRefresh Method? Does it work for you? 

[woehling]

No, AcMapLayer::ForceRefresh() does not work.
I have to call AcMapLayer::CreateCache() to get the "new" data from database server.
If ForceRefresh() would load the complete dataset from server, It would be too slow anyway, I think.

Markus

[Daniel.Du]

Hi Markus,

Are you using Map 3D industry Models(IM) or just "data connect" to MSSQL/Oracle spatial in Map 3D? If you can not using Industry Model, Map 3D data connection should take care of the caching stuff, and you do not need to call " CreateCache() "  and "ForceRefresh()" will refresh the layer if you want to.

So to understand your situation, we need more information. 

[woehling]

Hello,

I just "data connect" to MSSQL/Oracle spatial in Map 3D.
Connection and layers are created programmatically based on database views. When there are data changes on the server, I want to see the changes in Map 3D.

My tests results are:
When I call ForceRefresh() there's no database access (SQL profiler says so).
When I call CreateCache() all rows of the layers are queried (SQL profiler says so). This takes a lot of time if there are many rows (e.g. > 100.000), especially if the "select" statement in the underlying database view is complex.

I know which rows are inserted/updated/deleted and which bounding box contains the changes, so I could do a partial refresh of the map - but there's no API that allows me to do that!

New test results:
I've just tested the behavior with a manually created connection to a database table (not a view): Now ForceRefresh() initiates the query (like you expected, right?), but the problem remains the same: too much data that is queried.

Because ForceRefresh() did not work for me, I did not try out the ForceRefresh(pFeatureIds) overload before.
But now I've tried it out and I'm getting an error that says that an identity column is needed!

I think that's the missing piece of the puzzle and so my new question is:
How can I define identity columns for database views?
I've just tried out the approach that we use for MapGuide (I added "config.xml" which contains the modifed schema as RESOURCEDATA), but that does not work in Map 3D 😞

I know that there are ways in Oracle to define identity columns for views directly in the database, but I haven't tried that out yet.
For MSSQL I would really need a configuration for the identity columns, because I know that there is no way to define identity columns in the database (at least for the type of view that I need).

I hope you can give me a hint in the right direction 😉

Markus

[Daniel.Du]

Oracle provides a way to indicate the primary key to expose for a view.

alter view <viewname> add constraint <constraintname> primary key (<columnlist>) disable novalidate;

For SQL Server, you can try to create a Unique Clustered index on the view in SQL Server.  It would be something like this:

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS

        WHERE TABLE_NAME = 'ANLY_BOMB_BLAST_MGE_V')

    DROP VIEW ANLY_BOMB_BLAST_MGE_V

GO

create view dbo.ANLY_BOMB_BLAST_MGE_V WITH SCHEMABINDING

as

select

      b.ID,

      b.BOMB_TYPE_ID,

      t.LONG_NAME as BOMB_TYPE_LONG_NAME,

      t.TNT_LBS as TNT_LBS,

      t.LETHAL_AIR_BLAST_DISTANCE_FT,

    t.BUILDING_EVAC_DISTANCE_FT,

    t.OUTDOOR_EVAC_FALLING_GLASS_HAZARD_DISTANCE_FT,

      b.NAME,

      b.DESCRIPTION,

      b.ORIGIN_GEOMETRY,

      b.LETHAL_AIR_BLAST_GEOMETRY,

      b.BUILDING_EVAC_GEOMETRY,

      b.FALLING_GLASS_HAZARD_GEOMETRY,

      'Bomb Blast\nType: ' + t.LONG_NAME + ' (' + CONVERT(nvarchar(max), t.TNT_LBS) + ' lbs.)' AS TOOLTIPTEXT

from

dbo.ANLY_BOMB_BLAST b

inner join dbo.ANLY_BOMB_TYPE_LUT t on t.ID = b.BOMB_TYPE_ID

GO

CREATE UNIQUE CLUSTERED INDEX [ANLY_BOMB_BLAST_MGE_V_ID_IDX] ON [dbo].[ANLY_BOMB_BLAST_MGE_V]

(

      [ID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO

However, there are significant limitations to using the Unique Clustered index – no outer joins are supported.  In this case, FDO Schema Overrides could be used to force the FDO Provider to use a specific view column as a primary key.  See http://themapguyde.blogspot.com/2010/08/using-fdo-schema-overrides.html.

[woehling]

Hello,

I can't use "WITH SCHEMABINDING" in SQL Server, so the way to go should be "FDO Schema Overrides".

As I mentioned, I've already tried it out using the exact same approach like in MapGuide:

- FeatureSourceType.ConfigurationDocument = "config.xml"

- I added "config.xml" as RESOURCEDATA using ResourceService.SetRecourceData(), but that did not work.

I found out, that maybe Map 3D does not support RESOURCEDATA&colon; "AutoCAD Map 3Ddoes use ResourceService.SetResourceData() for streams, but only for the configuration information for ODBC/WMS/Raster feature sources." (http://wikihelp.autodesk.com/AutoCAD_Map_3D/enu/2013/Help/0006-AutoCAD_0/0007-Resource7/0010-Differe...).

Then I've tried to save "config.xml" as local file, and that did work!

So I think I can achieve what I want!

It would be better if "config.xml" would be RESOURCEDATA, because otherwise I have to manage the files on my own.

Is there any way to to save "config.xml" as RESOURCEDATA, or is a local file the only way to go?

Thank you!

[woehling]

Hello,

I'm currently fighting another problem, when calling ForceRefresh(pFeatureIds).

I get an Exception "Can not update objects" (in German), AcMapVectorLayer.ForceRefresh line 1616 file AcMapVectorLayer.cpp.

This is working for most layers, but for some layers I get this exception. I don't see what's wrong with these layers. All problematic layers have in common, that I use them to display "text only", but that should be no problem?!

Any suggestions?

Markus

[Daniel.Du]

You don't have to save config.xml locally, please refer to the sample "BuildMap" in Map 3D SDK.

[Daniel.Du]

---

 Sorry but I am not clear about your meaning by "display text only", are you geting feature source and display some field as label? Since this topic is not related with the origianlly thread closely, so I would suggest you to create a seperate thread with detailed description.

@woehling wrote:

Hello,

I'm currently fighting another problem, when calling ForceRefresh(pFeatureIds).

I get an Exception "Can not update objects" (in German), AcMapVectorLayer.ForceRefresh line 1616 file AcMapVectorLayer.cpp.

This is working for most layers, but for some layers I get this exception. I don't see what's wrong with these layers. All problematic layers have in common, that I use them to display "text only", but that should be no problem?!

Any suggestions?

Markus

---

[woehling · Accepted solution]

Hello,

the "BuildMap" sample led me to the following:
if I use "config://config.xml" instead of "config.xml" as ConfigurationDocument, everything works fine!

Thank you.

Markus