Azure sql view that uses a function works ok in design but get error 8632 when select top 1000 rows

Question

hi all

I made Azure sql view that uses a UDF (scalar) function works in design but get error 8632 when select top 1000 rows.

The UDF converts UTM cooridnates to LatLong, so is a bit of code.... (I can run this on an on-premise SQLServer Standard setup, so issue seems specific to Azure SQL)

If I change from a UDF scalar function to an Inline table-valued functions (TVF) then it works. but the TVF I cannot declare some variables like a variable for a table name input parameter (have to hard code the table name in the TVF) , so is not as flexible for use in other tables.

I tried to change the Azure SQL compute tier from Basic (5 DTU) to Standard (100 DTU) and then to Premium (250 DTU) but still the same error. I tried vCore (4 CPU) and same issue (beyond my budget given anyway); to me this should have worked,

I can workaround by hardcoding the LatLong as columns in the table and just doing a UPDATE on those columns. So not a big deal, more forward thinkinng on other UDF that I may use ...

TIA

Answer 1

Hi Luke Burlet,

Thanks for reaching out to Microsoft Q&A.

The error 8632 in azure sql typically points to a query processing memory error, esp, with complex calculations or large datasets. Suggest you to consider the folowing stategies.

• Since scalar udfs are often not as performant due to their row-by-row execution nature, ensuring the function is as efficient as possible could help. You mentioned a workaround using a table-valued function (tvf); it might be worth exploring further if the function can be optimized or simplified.
• Check if there are optimizations that can be applied to the tables involved in the udf, such as indexes that can reduce the computation load.
• If the function is being executed over a large dataset, consider breaking the data into smaller batches to reduce the instantaneous memory demand.
• For complex computations, sometimes moving the logic outside of sql and using a more dedicated processing system (like an azure function or databricks) can be more efficient and then updating the database with the results.
• Sometimes adding query hints can alter the execution path and help avoid memory issues. However, this requires careful testing as it can lead to other performance issues.
• As a more flexible workaround, you could consider using a stored proc where more complex logic and variable declarations are easier to manage compared to tvfs.

Each of these solutions has trade-offs in terms of performance, cost, and complexity, so it might be worth evaluating them based on your specific use case and other udfs you plan to implement.

Please 'Upvote'(Thumbs-up) and 'Accept' as an answer if the reply was helpful. This will benefit other community members who face the same issue.