get_SDA_property: server failure when number of mukey > ~1500

[dylanbeaudette]

SDA reports the following error when calling get_SDA_property() to aggregate horizon-level data by mukey, when there are more than approximately 1500 unique mukey values.

Invalid query: The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

This did not happen with the temporary table strategy that we used to use, possibly related to the way resource constraints are defined in the SQL Server config. I'm not sure, but it could be that the use of CTEs causes the server to think we are interacting with too many tables (based on some setting) within a single query.

Looking over the current SQL, I think that there is room for query simplification, but we could be up against a hard limit in the way SQL server plans queries in this context.

A simple work-around is to process fewer mukey at a time.

[brownag]

Please provide a reproducible example. Or at least provide some more info on the (number of) properties, aggregation method, and general goal of your query. There are quite a few more things to consider here beyond total amount of unique mukey.

[brownag]

Also, IMO the error is pretty self explanatory in this context. As you say, should use soilDB::makeChunks or similar. Chunk size will certainly have to depend on number of properties and selected aggregation method. For a large number of mukey, it may, depending on what you are trying to do, be better to use a custom WHERE clause or areasymbols argument. I think I have never encountered this error in a situation where one of those 3 options couldn't be used. I have seen it. Possibly the CTEs are an issue but actually I think that should be more performant, and compatible with other SQL dialects, so even if some very complex query used to work and now doesn't, I am not sure that in and of itself should be something we need to ensure works in perpetuity. The SQL server limits could be increased or decreased at any time, and we also should consider that these functions can be run against local backends or just to generate the query string.

If you have specific suggestions to optimize queries I would be open to testing them, of course. There is a lot of complexity, but I believe the CTEs are quite a bit simpler than the prior implementation

[dylanbeaudette]

The CTE approach is far simpler to understand and maintain. I prefer it over the temporary table approach. I was a little surprised when using a query from last year that finished without error and in a few seconds. Some of the following may be at play here.

• SDA (SQL Server) doesn't do well with large CTE queries involving many (> ~ 2000) records, no matter how many variables are included in the aggregation, causing an error due to timeout.
• SDA has resource constraints that kick-in with CTE queries involving many rows, causing an instant error
• The new CTE style queries in question can probably be simplified, I'll tinker with that next week.

Either way, I'll ask Kyle and any devs currently working on SDA to investigate. Based on what we find, I'll add some relevant documentation to this function.

A short demo:

library(soilDB)

# large number of mukey
x <- SDA_query("SELECT TOP 2500 mukey FROM mapunit ;")

# just a single variable
vars <- c('om_r')

# 2500 mukey, single variable: invalid query
p <- get_SDA_property(
  property = vars,
  method = "Weighted Average", 
  mukeys = as.integer(x$mukey),
  top_depth = 5,
  bottom_depth = 15,
  include_minors = TRUE, 
  miscellaneous_areas = FALSE
)

# 2000 mukey, single variable: timeout
p <- get_SDA_property(
  property = vars,
  method = "Weighted Average", 
  mukeys = as.integer(x$mukey[1:2000]),
  top_depth = 5,
  bottom_depth = 15,
  include_minors = TRUE, 
  miscellaneous_areas = FALSE
)

# many more variables
vars <- c('om_r', 'om_l', 'om_h', 'sandtotal_r', 'sandtotal_l', 'sandtotal_h', 'claytotal_r', 'claytotal_l', 'claytotal_h')

# 1000 mukey: finishes in a couple seconds
p <- get_SDA_property(
  property = vars,
  method = "Weighted Average", 
  mukeys = as.integer(x$mukey[1:1000]),
  top_depth = 5,
  bottom_depth = 15,
  include_minors = TRUE, 
  miscellaneous_areas = FALSE
)

[brownag]

I have been working on a new vignette for SDA queries, which covers most of this, and generally suggests using a chunk size in the realm of 500-1000 items (i.e. mukey) for "complex" queries like get_SDA_property(method="weighted average")

I want to re-emphasize that this issue is not really about the total number of mukey, it is a bit of a red herring, though it does manifest when using large array of mukey in the join constraints/WHERE clause. Which is why I asked for a better explanation of what you were trying to do.

For example, if I change your cases to target every mapunit in the great state of Texas, which is far more (n=13187) than what you are claiming is the limit, it works fine:

library(soilDB)

vars <- c('om_r')

p <- get_SDA_property(
  property = vars,
  method = "Weighted Average", 
  WHERE = "areasymbol LIKE 'TX%'",
  top_depth = 5,
  bottom_depth = 15,
  include_minors = TRUE, 
  miscellaneous_areas = FALSE
)
#> single result set, returning a data.frame
length(unique(p$mukey))
#> [1] 13187

p <- get_SDA_property(
  property = vars,
  method = "Weighted Average", 
  WHERE = "areasymbol LIKE 'TX%'",
  top_depth = 5,
  bottom_depth = 15,
  include_minors = TRUE, 
  miscellaneous_areas = FALSE
)
#> single result set, returning a data.frame
length(unique(p$mukey))
#> [1] 13187

# many more variables
vars <- c('om_r', 'om_l', 'om_h', 'sandtotal_r', 'sandtotal_l', 'sandtotal_h', 'claytotal_r', 'claytotal_l', 'claytotal_h')

p <- get_SDA_property(
  property = vars,
  method = "Weighted Average", 
  WHERE = "areasymbol LIKE 'TX%'",
  top_depth = 5,
  bottom_depth = 15,
  include_minors = TRUE, 
  miscellaneous_areas = FALSE
)
#> single result set, returning a data.frame
length(unique(p$mukey))
#> [1] 13187

I agree that this is perhaps something that should be fixed on the SQL server side--which is not really a soilDB issue.

[dylanbeaudette]

Interesting. Thanks for the clarification. Browsing *overflow articles seems to confirm that many RDBMS do not like very large IN statements. That explains why filtering via e.g. pattern matching against areasymbol is more efficient.

I encountered this problem iterating over rectangular blocks of data vs. SSA or states. Further testing suggests that SDA is "fine" with <= 1000 mukey per IN clause, at least for all of CONUS.

I look forward to the SDA vignette, closing the issue as most follow-up will be in the form of documentation or SDA configuration.

[brownag]

Thanks for raising it. While I am generally hesitant to hard-code any limits into the function itself because of the variety of methods and backends we try to support, there actually may be some things we can do in soilDB.

Some aggregation methods, like "weighted average" for get_SDA_property, use this large "WHERE" statement multiple times. As you mentioned efficiencies, this is an area where I have overlooked an inefficiency--the current handling of that is fairly naive. Perhaps (a bit ironically) using a CTE to store that info and then joining may be a more general solution within SQL rather than resorting to chunking.

There are ultimately limits with my suggested alternatives above with custom WHERE. As you pointed out it is possible to create a query that does not get smacked down immediately by the server, but then runs so long it times out. For instance, you can do all of Texas (~13k mapunits), or all of California (~19k mapunits), fairly quickly with my above counterexample, but doing all of Texas and California together (separated with OR) takes far longer than the sum of the parts and times out.

"weighted average" is a particularly demanding query pattern, from some quick testing it appears methods like "dominant component" can scale to considerably higher chunk sizes. method="none", the simplest, is more likely to hit the 100k record limit with a very large vector of mukey.