-
-
Notifications
You must be signed in to change notification settings - Fork 3.6k
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Slow schema introspection query with Azure SQL #19486
Comments
Could you set |
I believe I can't use Profiler and set I see the load peeking to 25% on the SQL server tough during this login/fields getting period. |
What flavor of SQL did they use for Azrue SQL? Or better put which driver are you using to connect to it? as it is not a natively supported vendor for directus. |
Did some debugging with help of br41nslug and we found that the following query basically takes all the time. It's schema introspection from [directus].[sys].[columns]. I understood that Directus could not do anything about it as its caused by Azure SQL cloud database.
|
According to Microsoft Docs Azure SQL seems to be using the Microsoft SQL Server database engine. I'd love to see a comparison between different engines doing the same query to determine if its an issue specific to MS SQL or in general after enough collections/fields. @KonstantinSolv so you have roughly 1000 fields across how many collections? We'd need to make a comparable snapshot to investigate further. Thanks so much for your work looking into the specific query! |
We have the same issue. The schema query is taking as much as 15s to load. We are using Azure SQL Standard S1 20 DTU. |
We're also having an issue similar to this. We're self-hosting Directus with the docker image on Azure with a MSSQL database S1 10 DTU. After the login, the request "/fields" takes about 30 seconds. The same thing also happens everytime we create or edit a field. Restoring the database helps fixing this issue, but only for a brief moment. We have 58 collections with a total of 454 fields. Only our development environments seems to be affected for now. This issue is making it difficult for us to work with Directus. |
When this occurs, have you got a Redis cache setup on your Directus project? |
We do not use Redis. or any other caching. |
I did some digging and found a better query that handles NULLs inside the LEFT JOIN. The result is identical, but the query now runs in x milliseconds. The pull request needs some additional checks and tests. |
Describe the Bug
We are self hosting with Docker v10.5.2 on Azure. We use Web App and Azure SQL (Elastic Pools). Both services have sufficient resources and we are not stressing any limits.
I checked the database and we have 1010 entries in dbo.directus_fields.
After every login:
It takes around 12 seconds to GET fields:
After the initial wait everything is blazing fast. But every login takes 12 sec.
After debugging it became clear that this issue is caused due to everytime querying schema introspection:
To Reproduce
Directus Version
v10.5.2
Hosting Strategy
Self-Hosted (Docker Image)
The text was updated successfully, but these errors were encountered: