MSSQL: automatically introspect columns
Hi,
I'm trying DataGrip on a complex database on SQL Server 2022 with more than 2000 tables (and probably around 20000 columns).
So far it's fast, but the automatic introspection is very shallow: I get autocompletion for table and column names, but all columns have their types marked as “unknown” unless I refresh the table. When I refresh the table, I then have the types and foreign keys in the editor's hints, but I have to manually scroll to and refresh every table.
I guess this may be due to the large amount of tables, but it is very unconvenient.
I have set automatic introspection ON and tried to set all levels to 3 but it did not change anything.
- Is there a way to enable “lazy introspection” of those tables when typing in the console?
- Otherwise, is there a way to introspect everything at start? (I'm fine with a 10-15 seconds delay if this means good hints)
- Or is there a way to refresh a table directly from the console instead of scrolling to it in the database explorer?
Thanks
请先登录再写评论。
Well, just found the fix.
Found many posts about how to refresh the cache on the web but none of them worked and many of them were outdated.
Here is the solution:
- Right click on the DataSource and set the Introspection Level to 2 (it is enough to get types and foreign keys and still takes less than 10 seconds).
- Right click again, go to the last menu (Diagnostics) and click on Force Refresh (or press Ctrl+Shift+F5 if you cannot find the option, but you have to click on the DataSource first).
- It should now refresh and take more time than with Level 1, and you should have the types in hints.
https://www.jetbrains.com/help/datagrip/introspection-levels.html might need a little warning: a force refresh is required when changing the introspection level (simple refresh and application cache invalidation are not enough).
Do you recall what your initial introspection level was when you triggered your first introspection and noticed column types shown as unknown?
If you switch to a different introspection level, there's no need to force refresh the data source unless an issue occurs during the introspection that requires rebuilding the database tree from scratch. Unfortunately, we can't investigate the issue you described, as it removes all the footprints related to metadata when forcing a refresh. I'd, therefore, encourage you to continue observing this behavior and let us know if this issue happens again.
- Initial datasource configuration: no database selected, introspection level set to automatic with auto refresh.
- Only the “master” database, which is very small, was open in the tree when first connecting. The server has around 200 more databases with 2000 tables.
- I unticked master and ticked one of the 2000 tables database in the tree.
- No types, only tables and columns names.
- Changing the introspection to level 3 did automatically trigger a refresh according to the UI and the logs, but the refresh took around only 1 second and still no type information.
- Only Force Refresh (Ctrl+Shift+F5) made the change effective.
Someone else tried opening the same database with a new installation and new project, but this time setting the database from the start in the datasource instead of opening all 200 databases, and it worked fine.
I'll open a support ticket with the metadata if I or someone else can reproduce it with our server.
https://youtrack.jetbrains.com/newIssue?project=DBE