Improve performance of the $select guidance

[waldekmastykarz]

We should implement the $select guidance on top of a local SQLite db to improve the performance of the $select guidance plugin.

In #302 we improved the accuracy of the $select guidance by using the Open API docs for Microsoft Graph v1.0 and beta. While using these files increases the accuracy of our guidance, it significantly affects the performance of the proxy. The Open API docs for Graph v1.0 and beta are large (~30MB) and loading them into memory takes ~15s and ~500MB of memory.

We can mitigate this performance hit, by using a local SQLite db with just the necessary information about Graph endpoints. Using the information from the Open API docs, we'd build a local SQLite db, with just the necessary information needed for the proxy. Early tests showed that the SQLite file would be roughly 1.5MB and take 20MB of memory, which is a huge improvement.

We'd implement this improvement as follows:

• add a new subcommand to the proxy named msgraphdb (so to run it, you'd call m365proxy msgraphdb). This command, would download the latest Open API files for Graph v1.0 and beta and create the local SQLite db. Using this command would allow users to regenerate the local db
• include a default version of the SQLite db with the proxy release which we refresh before each release so that the proxy has a proper out of the box experience
• update the $select guidance plugin to use the local SQLite db

[waldekmastykarz]

@gavinbarron @sebastienlevert @garrytrinder any feedback?

[garrytrinder]

Great suggestion @waldekmastykarz

I think we should also add a check in the proxy to see if there is a new version of the OpenAI docs, comparing to what has been downloaded into the db. We can then present a message to the developer to download the latest update, like we do with checking for the Proxy version.

[waldekmastykarz]

Given how infrequently Graph Open API docs are updated, I wonder if we need to include this functionality directly from the start or if we start without it and consider it for the future.

[garrytrinder]

Happy to consider this for the future. Improving performance is a more pressing matter.

[sebastienlevert]

Couple of thoughts on this. I think this is a big improvement we could get but it has a cost from a DevX standpoint.

• I think this should be part of our build where we grab the OpenAPI, get the latest OpenAPI and bring it to SQLite
• The OpenAPI changes on a weekly basis so we need to identify a way to get the changes in (The Graph changes since the last time your ran the Proxy. When we will be closing the proxy, we will update the OpenAPI.)
• The command should be the last resort for developers

Definitely, performance is the most pressing part of this issue so I recommend we split the issue in 2?

[waldekmastykarz]

To automatically update the db, we could run it in the background while the proxy runs. When you start it, we'd grab the latest OpenAPI docs and transform them into the db. At any point in time, you can choose to update the db manually yourself using the command that we ship. The command and autoupdate would use the same logic.

Since we're not doing automated tests at this moment, I suggest that we don't implement refreshing the db as part of our build, because it it has errors, we wouldn't be able to catch them. Instead, I suggest that we do it manually, verify changes and only then commit. We can change this process when we introduce automated tests.