SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
FR: Query/Statement Visualizer

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
FR: Query/Statement Visualizer
Author Message
Mindflux



Joined: 25 May 2013
Posts: 810
Country: United States

Post FR: Query/Statement Visualizer Reply with quote
Would it be possible or feasible to have a tool inside of SQL Assistant that you could click "Visualize" on the selected query and it could give some sort of visual representation of the joins?




(I stole this from a Stack Overflow post asking for such if a tool exists) but there's not really anything out there that I've found.
Thu Nov 10, 2016 3:09 pm View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 810
Country: United States

Post Reply with quote
Looks like Visual Studio Community will do it, but at 6GB installed just for something to visualize queries is a bit heavy.
Thu Nov 10, 2016 3:31 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
I used DbSchema in the past (and still use it for design), a very good designer, knows a quite a few different database types. It's a java application, and as such, it sucks at a number of things (most importantly not being able to cooperate with SA). It had a decent visual query builder. But I think you'd need it the other way around, right? I guess most of the apps even remotely touching this topic aren't doing it reverse. I recall, Visual Studio having something, and I have it on board due to SSIS anyway, but I can imagine installing 6gigs (and a bunch of unwanted features) to be an overkill.

Didn't SSMS had something similar? Though it might require the query built visually there too.

By the way, why is this post closed?
Thu Nov 10, 2016 4:08 pm View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 810
Country: United States

Post Reply with quote
gemisigo wrote:
I used DbSchema in the past (and still use it for design), a very good designer, knows a quite a few different database types. It's a java application, and as such, it sucks at a number of things (most importantly not being able to cooperate with SA). It had a decent visual query builder. But I think you'd need it the other way around, right? I guess most of the apps even remotely touching this topic aren't doing it reverse. I recall, Visual Studio having something, and I have it on board due to SSIS anyway, but I can imagine installing 6gigs (and a bunch of unwanted features) to be an overkill.

Didn't SSMS had something similar? Though it might require the query built visually there too.

By the way, why is this post closed?



SSMS does have a query designer, but when you're dealing with #tempTable or @tempTable it doesn't do well there.

I did find http://www.snowflakejoins.com that seems OK for statements under 800 char. It's based on RevJ http://revj.sourceforge.net/ ... I might try and get RevJ going on my machine to avoid the 800 char limit.


As far as any tools that do it, I'd like to be able to paste in my statement and have it visualize the joins. I could go reverse on it, but it's a bonus if it'll parse what I've got to confirm I'm on target. This is only a problem for me when I get too many joins going or start doing self joins I get crosseyed.
Thu Nov 10, 2016 4:15 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7841

Post Reply with quote
Believe it or not we are planning Code Visualizer for the next maintenance release 9.1.
I cannot give you more details at this time, it's still pretty much in a research stage.
Thu Nov 10, 2016 5:01 pm View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 810
Country: United States

Post Reply with quote
SysOp wrote:
Believe it or not we are planning Code Visualizer for the next maintenance release 9.1.
I cannot give you more details at this time, it's still pretty much in a research stage.


Sweet! Looking forward to it.
Thu Nov 10, 2016 5:03 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
Okay, I knew there was something I missed and it stalked all the time in my subconscious :) Though it was not why I asked for it here, and it does not show the columns (only primary keys) on the diagram, Sentry Plan Explorer might be what you're looking for. It does show you join for queries (even complex ones). Never checked their validity though. How about you give it a test ride until there's a working Code Visualizer (eagerly awaiting it, btw)?


Thu Nov 10, 2016 5:16 pm View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 810
Country: United States

Post Reply with quote
How do you get that to show up?

*edit: got it.
Thu Nov 10, 2016 5:52 pm View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 810
Country: United States

Post Reply with quote
I was looking at your plan explorer screenshot again. Why do you have so many CASE statements that seemingly evaluate the same thing over and over?
Fri Nov 11, 2016 10:49 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
Mindflux wrote:
I was looking at your plan explorer screenshot again. Why do you have so many CASE statements that seemingly evaluate the same thing over and over?

The rest of the statement cannot be seen :) but from what's visible you can tell that it returns three columns. Those are all different but whether they yield a value or a NULL depends on the state of that DI column and you cannot return three columns from a single case statement. It's a result of bad planning. Someone hates NULLs (I don't think that person grasps the concept :) ) and instead of returning NULL in certain columns, 0s were put there and now I have to check values in other columns to see if that's a valid value or bs.
Fri Nov 11, 2016 10:56 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7841

Post Reply with quote
A new tool called Code Visualizer has been added to version 9.2. It's not exactly like what's pictured in previous posts. Nevertheless, it aims at helping to visualize the code logic and document it as well.
Sat Jul 15, 2017 8:10 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
Great! May I request a hotkey for it in case there isn't one yet? I can see myself wearing it all the way down to the hull.
Sat Jul 15, 2017 9:52 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7841

Post Reply with quote
Thank you. I will submit an enhancement request to add a configurable hot key.

Just in case, if you have two monitors or a large one, you can set it to show up in a floating window on a second monitor to save real estate for code on the main one, using the common pane dock feature available for all dockable panes.
Sat Jul 15, 2017 11:05 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
Good hint, thanks.
Sat Jul 15, 2017 11:09 am View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 810
Country: United States

Post Reply with quote
Very nice. Looks great so far.
:)
Mon Jul 17, 2017 10:48 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.