SQL Complete -> DataGrip

Answered

I'm evaluating moving from SQL Complete to DataGrip. Thanks JB for the 30 day eval, because 30 days is what's needed.

My eval time is almost up. Here my observations and questions using w/SQL Server (in no particular order):

1. Does introspection support cross server queries? We have servers lined and write queries which pull data between them. DG offers no writing assistance when querying through a linked server.

2. A nice feature of SQL Complete is aggregation in results view. DG have something like this?

3. Is there a way to move the results of an execution to the footer of the editor, close to the source?

This:

Should be here:

4. Is there a way code completion for table names put an "as" between table name and alias? DG puts the alias. Looking to put the "as," e.g. "select * from mytable as MT"

5. In formatting options, can there be "As in database" or "As in metadata" option? The "No Change" option seems to not touch text as I typed, which is not preferable. (The other casing options won't work.) Ex: "select * from salesorder" should format to "select * from SaleOrder" if SalesOrder is the table's name. Ditto for procs, etc. Bonus if you can add a "Initial caps each word" option.

6. Is there a way to invoke by keyboard the hint which appears for function and procedure calls? Sometimes the hint appears for the first parameter after typing opening parenthesis and then will disappear. Would be nice to call it back up.

7. Why are joins not suggested? I know DG is supposed to suggest joins, but it's never worked for me. Is Control-Space supposed to show possible joins? And it should work between tables without FK defined by columns names in common. Right?

8. What is the equivalent function for "Change Connection" in SQL Server Management Studio? 

9. Are there colors for the editor gutter to indicate code which has changed since load or changed and saved since load? See SSMS or many other IDEs. I'm using dark scheme. Not sure if that scheme is hiding it. The scrollbar area has lots of colors, but that's bird's eye view. Not sure it has the info I'm looking for.

10. Can console names default to server or database name? When attaching to a console, it's hard to know what is what. And I don't have a desire to name every console I open.

11. Is there a way to execute results into a new tab? Scenario is I run query A, results appear. Now I want to run query B into a new results tab.

12. What is the best practice for setting up projects, especially database connections? I work w/multiple databases on a server, often querying across databases. Do I set up a single datasource for all databases? Or is it better to set up a datasource for each database? If I do the latter, will I lose cross-database introspection when writing a queries in the editor that's connected to a datasource?

This is important not just for getting insight writing assistance, but it determines when introspection happens. Introspection is killing performance of DG. And the way I work makes it worse. I have many object scripts which recreates procs, func, views, etc. by dropping the existing object and creating the object again. When this happens, DG kicks off a "background" introspection.

Background is not really background as it causes DG to lock up. Likely the source of this slowdown is the fact my datasource includes a database with lots of objects, MS Dynamics GP. When I see the status message appear saying beginning an introspection, even incremental, DG slows down.

Postscript: There are number of code formatting issues I've yet to figure out how to resolve with given settings. I'll keep trying and query here later.

6 comments
Comment actions Permalink

Thank you for the detailed feedback and your interest in DataGrip!

>1. Does introspection support cross server queries? We have servers lined and write queries which pull data between them. DG offers no writing assistance when querying through a linked server.
>2. A nice feature of SQL Complete is aggregation in results view. DG have something like this?

It is not possible currently, please vote for these requests:
https://youtrack.jetbrains.com/issue/DBE-3934
https://youtrack.jetbrains.com/issue/DBE-5278

>3. Is there a way to move the results of an execution to the footer of the editor, close to the source?

It is not possible. The results are displayed in the status bar which is bind to the window lower bound.

>4. Is there a way code completion for table names put an "as" between table name and alias? DG puts the alias. Looking to put the "as," e.g. "select * from mytable as MT"

Please vote for https://youtrack.jetbrains.com/issue/DBE-9505

>5. In formatting options, can there be "As in database" or "As in metadata" option? The "No Change" option seems to not touch text as I typed, which is not preferable. (The other casing options won't work.) Ex: "select * from salesorder" should format to "select * from SaleOrder" if SalesOrder is the table's name. Ditto for procs, etc. Bonus if you can add a "Initial caps each word" option.

I'm not sure I follow. Do you want identifiers to be camel casing? Note that casing options are database-specific and db identifier of the one name but different casing may be actually the identifier of a different object. Also it depends on the quoting options - quoting may be necessary with camel-casing naming for some databases.

>6. Is there a way to invoke by keyboard the hint which appears for function and procedure calls? Sometimes the hint appears for the first parameter after typing opening parenthesis and then will disappear. Would be nice to call it back up.

Use shortcut for the Main menu | View | "Parameter Info" action (Ctrl+P by default) - you can change in in Keymap settings. See also Code reference information.

>7. Why are joins not suggested? I know DG is supposed to suggest joins, but it's never worked for me. Is Control-Space supposed to show possible joins? And it should work between tables without FK defined by columns names in common. Right?

Joins are suggested based on table references information. Can you describe the use-case, including the DDL of tables (so to be able to reproduce) and the exact steps to reproduce when it does not work? Is your database MS SQL Server?

>8. What is the equivalent function for "Change Connection" in SQL Server Management Studio?

What exactly does this action do? In DataGrip you can e.g. attach/detach console (db connection) to a particular sql file, see Managing connection sessions.

>9. Are there colors for the editor gutter to indicate code which has changed since load or changed and saved since load? See SSMS or many other IDEs. I'm using dark scheme. Not sure if that scheme is hiding it. The scrollbar area has lots of colors, but that's bird's eye view. Not sure it has the info I'm looking for.

For object structural changes IDE displays them in gutter on the left, see example with Resolving conflicts when submitting changes. See also complete article about changing the code/data from DataGrip: Submitting changes to a database.

>10. Can console names default to server or database name? When attaching to a console, it's hard to know what is what. And I don't have a desire to name every console I open.

Console name defaults to the Data Source name. If you want later to save this console as a file inside your project use Refactor | Copy File... action (F5 default shortcut), see more at Database consoles.

>11. Is there a way to execute results into a new tab? Scenario is I run query A, results appear. Now I want to run query B into a new results tab.

See Settings (Preferences on macOS) | Database | General | Open results in new tab option.

>12. What is the best practice for setting up projects, especially database connections? I work w/multiple databases on a server, often querying across databases. Do I set up a single datasource for all databases? Or is it better to set up a datasource for each database? If I do the latter, will I lose cross-database introspection when writing a queries in the editor that's connected to a datasource?

There are no cross database references. You can use single data source with all the databases and open new consoles where you set default schema/database (see Select the default schema) and work from here or you can set up several data sources with only needed database set an open consoles only for this database.


>Background is not really background as it causes DG to lock up. Likely the source of this slowdown is the fact my datasource includes a database with lots of objects, MS Dynamics GP. When I see the status message appear saying beginning an introspection, even incremental, DG slows down.

Would be great if you report performance problem at https://youtrack.jetbrains.com/issues/DBE with CPU snapshot take ant the very moment of the issue and attach all log folder zipped (Help | Compress Logs and Show in ... action).

0
Comment actions Permalink

Thanks for the feedback, Kyle! 

I just want to add about JOIN completion by column names, now it works with no references when the column names are matching with 'id' inside. If you want to create your own custom matching patterns, you can do that. More info is here: https://blog.jetbrains.com/datagrip/2020/01/22/datagrip-2019-3-2-virtual-foreign-keys-and-more/

And feel free to drop a line to datagrip@jetbrains.com if you wish to prolong your eval period.


1
Comment actions Permalink

Thank you for taking the time for addressing my questions. For all the notes below, I'm working in SQL Server.

1,2: Thanks. Voted!

3: A little disappointing as that feedback is important to what's going on in the code editor. SSMS gets this right in that the editor footer is used for code execution feedback and the window footer is for app activities feedback.

4. Thanks. Voted!

5. I am requesting 2 options: "As in the database" casing and "CamelCaps" casing. The former is more important to me than the latter. I know the latter is DB sensitives, and that's okay. The former should not be DB sensitive as the tool is using the case of the object name as is in the metadata.

Looking for these 2 options:

6. Works. Thanks!

7. This animation illustrates the difference between the two tools and what I mean about join suggestion. The example query is querying 2 of the most utilized tables in Dynamics GP. I am typing in lowercase at all times except for when I typed "SL".

SQL Complete

DataGrip

Differences

  • SC formats as I type. Note the casing corrections as I type.
  • SC statement formatting rules apply when typing statement terminator, the semicolon. Shift-Control-L in DG.
  • Even after initiating DG formatting, the text case is not what I want, i.e. text case as in database.
  • SC offers suggested joins, which can be used to quickly build out the entire clause. Caveat: I am using DG on other databases 1/25th the size of Dynamics and join suggestions seem to work fine.

8. It's used to jump to a different server. The DG equivalent is to have all the servers set up in advance as a datasource and start a session on that source.

9. I do comparison like that when checking in files. The color coding I'm speaking of is in the editor in real time.

Here is same concept in another IDE.

Really handy feature.

10. Correct. The name of the datasource, which can be pointing to who knows what database.

If I attach a query to a console, what database is TempoGP will it run against? (There are 7.)

Which of the 7 databases in TempoGP in "console" pointing to? It's not clear.

This could be a project organization issue. Based on your answer to Q12, my current set up is correct: one datasource with all databases I'll possibly work with checked.

11. Awesome. There are more options than I expected.

On performance, I'll look into reporting. I work with other projects and don't really have an issue. It's the very large Dynamics GP database that brings it to its knees. My current tool also has to do metadata refreshes, and it takes a while. But, it doesn't slow the IDE down.

I have upped the memory footprint for DG. I'll try upping again, to 2GB.

Maxim,

Thanks for the link. Virtual Foreign Keys -- That is pretty sweet.

I'll take you up on your offer & drop datagrip@ a note.

0
Comment actions Permalink

5. About the casing: our formatter does not know about the objects, so it can only do something like "to upper" or "to lower". The question is: why do you type the name of the object COMPLETLEY? If you choose it from the completion list, everything will work as you wish.
Please also share your thoughts here: https://youtrack.jetbrains.com/issue/DBE-2230

7. Are there foreign keys established for these tables? So, DataGrip just does not offer you it in this case?

9. Please create a feature request in our tracker https://youtrack.jetbrains.com/issues/DBE

10. In our model, the database is the subject for CLIENT to choose, not for session. But if you attach the file, the current database oа the session (from the DB perspective, not from DG's one) will be used. I agree that is unclear, and it is also a good candidate for a ticket in our tracker.

 

 

0
Comment actions Permalink

5. So the formatter just knows it's an identifier, keyword, etc. and formats accordingly. It doesn't look up the object name when formatting. I see,

There are at least three scenarios where object-aware formatting is useful:

  1. Someone handed you a script or query that's a mess. They did not maintain object name case. Formatting gets everything structurally more readable. Would be nice to have case follow.
  2. Code insight isn't working because the parser cannot figure out what's going on because code is not syntactically correct. The code is in development/flux, and there's dangling pieces above yet to be completed. Ex: Working on bottom part of procedure while returning to top to flesh things out. Another stumbling block is working in a scratchpad with lots of SQL snippets that makes sense to me, but not to the parser. I finish tweaking in Scratchpad and copy/paste back into procedure or script.
  3. I type faster than code insight. It would take more time to type partial text, find in list, select and move on.

Shared on issue.

7. No FK's between these tables or any Dynamics GP table I think. However, the field names joining the tables match.

9. Done! https://youtrack.jetbrains.com/issue/DBE-10122

10. Done! https://youtrack.jetbrains.com/issue/DBE-10123

Thanks for all the help and feedback!

0
Comment actions Permalink

7. Ok, so virtual foreign keys is the feature you need :)

0

Please sign in to leave a comment.