MSSQL API slow with large tables due to incorrect varchar/nvarchar in sp_executesql

[tdutch1]

Discussed in #1964

I converted the discussion to an issue since I've been able to identify the problem. Please see my comment here: #1964 (comment)

^{Originally posted by tdutch1 January 11, 2024}
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.

[JerryNixon]

Thanks Tim. Great detail for us.

[tdutch1]

I added repro steps in the attached document. I recommend loading a table with 15 or 20 million records to see where DAB is taking 5+ seconds to respond with varchar columns. DAB queries for varchar data (indexed) is about 3-times slower than other SQL Server data types.
Subaru - DAB - varchar performance issue 1973- Azure SQL - Timd.docx

[Aniruddh25]

Thanks for providing the repro - we plan to pick this up in the next milestone.

[tdutch1]

@Aniruddh25 It's welcome news that this issue on the list. It's critical to my client's projects, and I'm sure for most other large companies with millions of records in Azure SQL.

Do you have an approximate timeframe for 0.12? We have a demo with a client coming up and ideally we'd like to use our existing (large) SQL tables. Thanks!

[tdutch1]

Please confirm that the fix made for this for SQL Server (Azure SQL) also applies to views, not just tables. We're seeing that DAB is using nvarchar for a varchar column in a view. The underlying table has several million records so the query that DAB generates is timing out.