Hello. I'm a non-developer working with Retool 3.26.4 on premise. Today, with the help of Copilot, I was able to resolve (or at least work around) a query performance issue.
One of our resources is a SQL Server table with 4.3 million rows. Earlier this week, performance on a query using this table tanked. The app was unusable. There had been no updates to the query or database.
When troubleshooting, I replaced a dynamic variable in a where clause with a hardcoded date value.
WHERE PaymentDate = {{dateBatches.value}} AND Inactive = 0
became
WHERE PaymentDate = '2024-04-15T00:00:00.000Z' and Inactive = 0
The query runtime went from 80000ms to 500ms.
I sent both complete queries to Copilot and it responded with the below:
In the first query, you’re using a hard-coded date value, which allows the database engine to optimize the query execution plan. However, in the second query, the database engine might not be able to optimize the query in the same way because the value of
{{dateBatches.value}}is not known until runtime.
At the suggestion of Copilot, I made a parameter to contain the contain the dynamic variable.
DECLARE @datebatch datetime;
SET @datebatch = {{dateBatches.value}}
And I updated the where clause.
WHERE PaymentDate = @datebatch and Inactive = 0
The query now runs normally.
I am satisfied with this outcome and do not need Retool to troubleshoot. I'm posting here in case using a parameter in combination with a dynamic value is useful to anyone else.
