fss_gsm

AGS Rest API - Query with multiple joins running in memory

Discussion created by fss_gsm on Aug 28, 2013
  • Comment • 0
I am running queries against a map service hosted in an ArcGIS Server 10.1 SP1 environment, which consists of a feature class that is joined to two attribute tables. All of the data is hosted in SDE (Oracle 11g R2). 

The problem I have is these queries are being run in memory rather than in Oracle, and for this reason are running much more slowly than I would like.

It seems to be dependent on which join I create first when constructing the MXD - queries against attributes in the first join will run in Oracle; queries against attributes in the second join will run in memory. Is this by design, or am I doing something wrong?

Further background/example:

I have a large feature class stored in an Oracle SDE geodatabase that has foreign key relationships to two attribute tables also stored in SDE. An example scenario is: 

COUNTRIES
========
ObjectID
Name
Currency_ID
Language_ID
Shape

CURRENCIES
=========
Currency_ID
Currency_Name

LANGUAGES
=========
Language_ID
Language_Name

In the above example, countries is the feature class, and currencies and languages are additional attribute tables. It is a many-to-one relationship between countries and currencies/languages. Eg both Portugal and Brazil speak Portugese, and both Belgium and France use the Euro.

In my MXD, I have created a join between the countries feature class and the currencies table, and the countries feature class and the countries table. I have experimented with using both "keep all records" and "keep only matching records".

If I create the languages join first, and then the currencies join, when I use the arcgis rest API:

http://server/arcgis/rest/services/countries/MapServer/0/query

and specify the where clause as "WHERE languages.Language_Name = 'Portugese'" I can trace the query being run against the database as:

SELECT 
ObjectID,
Name,
Currency_ID,
Language_ID,
Shape
FROM countries
LEFT OUTER JOIN languages ON countries.language_id = languages.language_id
WHERE (languages.Language_Name = 'Portugese')


This is very efficient, and exactly what I want.

BUT, when I try to query on currencies, for example 'WHERE currency.currency_name = 'Euro', tracing the query being run against the database returns:

SELECT 
ObjectID,
Name,
Currency_ID,
Language_ID,
Shape
FROM countries
LEFT OUTER JOIN languages ON countries.language_id = languages.language_id


Followed by:

SELECT 
Currency_ID
FROM currencies
WHERE (currency.currency_name = 'EURO')
AND (Currency_ID IN 1,2,3,4,5,7,8,24,44)


It appears that ArcGIS Server is querying all records for the first join against countries, and then doing an in memory join for matching records in the second join against currencies. This is terribly inefficient, and is going to make the query run very slowly, as compared to running the query in oracle which would run almost instantly thanks to indexing etc.

If I change around the order of the joins in the mxd so that I created a join against currencies first and then languages, the reverse is observed: querying currencies will run the query entirely against oracle, but querying languages appears to do the join in memory.

Is there any way I can configure my MXD or queries against the REST API so that it will do the queries in Oracle rather than in memory?

Thanks in advance for any help!

Outcomes