Data API Builder (DAB) API response time (performance)

[tdutch1]

I'm working on a POC using DAB in a Static Web App. My data source is an Azure SQL table. It's taking anywhere from three to ten seconds for the DAB API to return a response from database queries. When capturing and running the same SQL in SSMS, the queries return results in less than one second.

Is it generally expected that DAB APIs, when running in a SWA, will take at least three seconds to return results? Also, what are some factors that would cause the range to go from three seconds to ten? The Azure SQL database is not heavily used so that does not appear to be the issue.

[thomasgauvin]

@tdutch1 we went through an improvement last week as there was a window of unusually long responses, can you check it out again? Response times should be generally quite quick

[tdutch1]

@thomasgauvin It's also slow running locally (v0.9.7). I'm 99.9% sure that the problem is that DAB is specifying nvarchar instead of varchar when it ultimately executes sp_executesql. It's a known problem that if you specify nvarchar for a varchar column, SQL Server ends up needing to do a full scan of the index.

As I mentioned above, I took the sp_executesql statement that DAB runs for a 20 million record table and ran it directly in SSMS. The statement took 10 seconds, so the DAB API will take 10+ seconds. I then changed the nvarchar data type in the sp_executesql paramter to varchar (to match the Azure SQL table column with an index on it) and sp_executesql took only 100 ms to complete. So, I'm convinced that this is the problem -- that the DAB code somehow needs to detect the correct varchar vs. nvarchar column data type so that it can provide that data type to sp_executesql. Otherwise, all queries on indexed varchar columns for "large" tables will be very slow.

This is easy to reproduce. All that's needed is an SQL table with a varchar column. Populate it with millions of records and create an index on it. Use DAB to query it. I believe you'll see that it's very slow.