SWA hosted 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.

[tdutch1]

Postman is showing that 99% of the time is going to waiting for the response:

[yorek]

Waiting for seconds should definitely not be the default experience. It might happen that the container running DAB is put to sleep if it hasn't received any request (given that for now DAB is in preview and 100% free of charge) in the last minutes in which case the startup it might take a few seconds, but once the container is running, response time should be sub-second (provided that, like you said, they query itself run in less than a second)

[JerryNixon]

The team has reported that one region is currently out of rotation and being redirected. They expect this issue to be resolved by next week. Could you please check again next week and let us know if you still face this latency problem?

[tdutch1]

@yorek @JerryNixon The response time appears to be related to the number of records in the data source. For example, running locally (v 0.9.7), Postman responds in 124 ms for an Azure SQL table with about 10,000 records but for a table with 20 million records it takes 3.5 seconds. In SSMS, both select * queries (the ones generated by DAB) return in less than one second. Is there pre-query code in DAB that might cause it to run slower for large data sets?

[tdutch1]

I created a new Azure SQL table with the same structure and indexes as the one with 20 million rows. I populated the new table with 10,000 records. DAB responds in about 150 ms for the 10K row table but around 4 seconds for the large table.

I ran DAB with LogLevel 0 but I'm not seeing enough debug output to help determine what's causing the slower response.

[tdutch1]

More findings... with a GET request on the 20 million row table where the primary key API syntax is used, DAB responds in about 100 ms. An API request on the same table using an indexed column takes about 4 seconds.

GET request examples for the 20 million record Azure SQL table:

• This one is fast (100 ms): http://localhost:5000/api/Customer/ROW_GUID/C6DBD289-547D-EE11-8925-000D3A8C392E
• This one is slow (4 seconds): http://localhost:5000/api/Customer?$filter=CUST_ID eq '983943'

Note that there's an index on the CUST_ID column and queries on it in SSMS return in less than a second.

We have several tables that require a uniqueidentifier as the primary key (for other requirements on the tables), so we will mostly need to query the large tables using indexed columns. Hopefully the DAB team will determine the method(s) that are causing the slowness. I'm tempted to run the DAB source myself and profile it but I don't have the time right now.

[tdutch1]

@yorek @JerryNixon I believe I found the cause for the slow response on large Azure SQL tables. I turned on auditing in Azure SQL and found that DAB is executing the query like this:

exec sp_executesql N'SELECT [dbo_CUSTOMER].[CUST_ID] AS [CUST_ID], [dbo_CUSTOMER].[FULL_NAME] AS [FULL_NAME], [dbo_CUSTOMER].[ROW_GUID] AS [ROW_GUID]
FROM [dbo].[CUSTOMER] AS [dbo_CUSTOMER]
WHERE ([CUST_ID] = @param0) FOR JSON PATH, INCLUDE_NULL_VALUES',N'@param0 nvarchar(6)',@param0=N'983943'

The CUST_ID column in the table is defined as VARCHAR(25), but the query is defining the parameter for it as NVARCHAR(6).

When I run that statement as provided by DAB, it takes 4 seconds to get the response.

When I change NVARCHAR to VARCHAR, the statement returns in less than 1 second.

The following is an old article but it looks like it relates to the automatic usage of nvarchar instead of varchar due to a misinterpretation of the underlying column data type: https://forum.hibernate.org/viewtopic.php?p=2328587

[tdutch1]

This appears to relate as well: https://blog.sqlgrease.com/are-varchar-or-char-hurting-your-performance-due-to-implicit-conversions/

[yorek]

Fantastic, this is what I was looking for. Can you please open an issue as of course this is a bug? Thanks!

[tdutch1]

Ok, last comment for today.. just to confirm that this is a VARCHAR/NVARCHAR issue with the parameters, I sent a request for records that equal a guid (20 million row table; index on CompanyGuid), and the results came back in 91ms. So, the problem is definitely with the varchar to nvarchar conversion and the need for Azure SQL to perform a full scan on the index.

Should I convert this discussion record to an issue to get it on the roadmap? This will probably be a blocking issue for a lot of users with larger tables.

[tdutch1]

Another related article: https://stackoverflow.com/questions/5328393/subsonic-generate-sql-uses-nvarchar-instead-of-varchar-which-causes-index-scan

[soagenesis]

What timing! My team is running into this as well with SQL tables and views with 1+ million records, and it’s only a problem with varchar columns. Queries on other data types are fast. Any updates on this will be greatly appreciated

[yorek]

I notified the development team. As mentioned in my comments above, opening an issue (referencing this discussion) will help to have the issue fixed ASAP. Thanks!

[tdutch1]

Great, thanks for bringing this up. It's an easy problem to reproduce. Let me know if you need more details.

My suggestion is for the DAB QA team to work with large tables going forward. That might reveal similar issues with the other databases.

I opened this issue: #1973

Thanks again!