Route event layers - low performance

[ChaimSchwartzIroads]

Hi

I have been consistently experiencing low performance when publishing map services that are based on a route event layer. We use SQL Server and ArcGIS Enterprise 10.9.1, and we publish tabular data as route event layers using the following steps:

1. Creating an SQL Server View (from within ArcGIS Pro) with the tabular data we'd like to publish.

2. Addin the view to ArcGIS pro and visualizing it as route event layers, using road number + fromKM + toKM fiels that correspond to an underlying street layer. 

Is this a known fact that route event layers have lower performance (this is pretty clear to me, however I can't find official documentation for this) and is there any way to improve the performance?

Thank you

Chaim

[TanuHoque]

@ChaimSchwartzIroads 

a few questions:

1. how is the performance of the view? Just curious, what is the reason behind creating a view?
2. how does the route event layer perform in Pro? is map service performance on par with Pro?
3. is your road number field indexed?
4. how many routes are getting generated in your typical extent?

since these linear features are getting generated on the fly, we expect it to be slower than any polyline feature class/layer where line features are pre-generated.

[ChaimSchwartzIroads]

Hi 
Thank you for your response. 

1: In most cases we do create a view since these tables come from integrations with 3rd party software and we usually need to manipulate some of the fields (convert date format etc.). However, even when we create the route events directly from the source table, I am experiencing the same poor performance (we have such an example as well, with now View), so the issue is not with the view. 

2. The low performance is on par with Pro. 

3. The road number field on the road is layer is indexed. The road number field on the table is not indexed since the table participates in an intensive integration that requires many inserts/updates and an index would negatively affect that integration. 

4. I am experiencing the slow performance even with layers that show only a small number of records - a few dozen.

When you write "we expect it to be slower..." - that is exactly what I am looking for, an official statement stating so. I am just wondering how that can be, when there are whole solutions and extensions out there relying on LRS. Is there really no way to reach the same level of performance?

[TanuHoque]

Thanks @ChaimSchwartzIroads for providing me with details. I kind of assumed that would be the reason for creating a view...

As I kind of touched base in my previous comment, the reason performance might not be on par with regular/standard polyline feature layer is because in one case linear features are getting generated on the fly (which is computationally expensive); the other case it is all pre-generated. As we know, the rule of thumb is that pre-generated ones always performance better...

... that said, it shouldn't take that long to draw a few dozens of features! BTW, how long it takes for Pro to draw them currently?

Also, how complicated polylines in your roads layer?

That said, in case you haven't done that yet, can you pls do this as a quick test? Export your tabular aka event data in a table (no more views)... this is temporary thing only for the testing purpose... have the route id field indexed. Make sure no joins or anything.

Then create a route event layer off this temp table (not view) where all data are stored. Check if you see any performance improvements.

Next test would be to copy both your table and roads feature class in a FGDB and create a new route event layer off them.

If none of them help, then I'm afraid you need to contact Esri Support... there are a lot of factors that I won't be able to help over this thread 😞 but they can... sorry about that.

Look forward to your findings.

[Bud]

Related: Idea - Optimize Route Event Layers