Forgot your password?
typodupeerror
Databases

Diagramming Tool For SQL Select Statements 156

Posted by kdawson
from the unique-like-a-snowflake dept.
alxtoth writes "Snowflake is a new BSD-licensed tool that parses SQL Select statements and generates a diagram. It shows parts of the underlying SQL directly in the diagram. For example: x=30, GROUP BY (year), SUM (sales), HAVING MIN (age) > 18. The primary reason for the tool was to avoid Cartesian joins and loops in SQL written by hand, with many joined tables. The database will execute such a statement, if syntactically correct, resulting in runaway queries that can bring the database down. If you sit close to the DBAs, you can hear them screaming... "
This discussion has been archived. No new comments can be posted.

Diagramming Tool For SQL Select Statements

Comments Filter:
  • by Mycroft_514 (701676) on Sunday August 03, 2008 @09:16PM (#24461231) Journal

    Doesn't name WHICH RDBMS, and then you throw SQL at it? So what? For DB2 we have a thing called "Visual Explain" which NOT ONLY does this, but is free, provided by IBM, but also shows you other things like whch index is being used for each step, etc.

    This is news? This isn't even worth a second look!

  • Existing tools (Score:3, Interesting)

    by Craig Ringer (302899) on Sunday August 03, 2008 @09:34PM (#24461337) Homepage Journal

    Most PostgreSQL users don't seem to use the existing, and superior, tools like EXPLAIN, EXPLAIN ANALYZE, PgAdmin-III's graphical explain, etc. I'm sure the same is true for users of many other databases.

    It's not like these tools are particularly difficult to use or understand. No training is required, though being willing to think and read a little documentation helps if you want to get the most out of them. Understanding at least vaguely how databases execute queries is handy for any database user anyway. The same understanding is required to get anything useful out of this just-posted tool.

    Anyway, as I've noted elsewhere the exiting tools for this do a much better job due to integration with the RDBMS and superior knowledge of how the DB will execute the query.

  • by Samah (729132) on Sunday August 03, 2008 @10:43PM (#24461699)
    Generally what happens on my project is that the team (headed by an analyst) decides on the best design for the task, then subtasks are delegated to developers based on their level of skill with PL/SQL and/or Java.
    Business logic (for the most part) is done on the server-side with PL/SQL packages, while the application itself is a Java fat client running on a Citrix cluster.
    Before you make statements about keeping business logic separate from the database, this situation works well for this application, as it allows for less client-server communication, easier handling of commits and rollbacks, and much faster data access. A bonus is that when a severity 1 case is raised that is related to business logic, it doesn't require a long system outage.
    The production server has read-only access for standard developers, and a logged full access account for support (and senior developers).
    Every code change is reviewed by one or more senior developers to ensure it won't break existing functionality or contains (as you put it) "crapness".
    From your comments I take it you are a DBA and have had bad experiences with poor programmers. In your case, maybe what you've suggested is a decent option for you, but I really don't think you should be stating it as the "right way".
    As always, YMMV.
  • by liquidpele (663430) on Sunday August 03, 2008 @11:15PM (#24461925) Journal
    Ha! I remember I had MySQL doing a full-text search over about a million documents in a web-app. If someone searched for something stupid like "problem" it would take like 10 minutes to return. Then if someone else did a search in the middle of that, the database would cease functioning for that table and all new queries that hit that table would simply sit for very large values of x.

    I had to write a system so that before I ran the full-text search query, I looked at the current running queries and if any of them had been running longer than 15 seconds I killed them before running the new query. It actually worked well enough and gave me time to move the system to lucene ;)

    SQL Server is pretty robust... you can spike the processor to 100% with stupid queries, but they usually finish unless there is a problem with the index. SQL Server is notorious for corrupting its indexes (maybe just our customers?) and then just having the weirdest problems on those tables.

    Postgresql I've never tested with huge queries so not sure about. Oracle... we use it for some backend stuff at work, and frankly I think a monkey with a pen and paper could be faster. But I don't control that system so maybe it's just set up badly or the web-app I do have access to just gives it *really* bad queries that take 10 minutes to come back (if at all - stupid 25 connection limit).
  • by mbourgon (186257) on Sunday August 03, 2008 @11:19PM (#24461955) Homepage

    AHAHAHAHAHAHAHAHAHA. Since I do run terabyte-sized databases, I'll contradict you - poor queries _can_ tank a server, even with small tables, if the query is poor enough. While it technically may be running, if nobody else can access it, then for practical purposes the server is down. And never underestimate the ability of one user with enough knowledge to be dangerous, to spread that selfsame query across as many people as possible.

  • by cduffy (652) <charles+slashdot@dyfis.net> on Monday August 04, 2008 @12:09AM (#24462255)

    1. more readable code, there is less of it

    Counterargument: Less readable code, as it's split into two places.

    2. easier to maintain - change in the database and the change happens realtime, no need to a new release (if your doing binaries)

    Counterargument: Harder to maintain - more upgrades will require the database to be revved as opposed to only the application, and synchronization between the two becomes more of an issue.

    (Granted, IRL there needs to be robust infrastructure for database upgrades and downgrades no matter what -- but making previously code-only minor patches impact both components doesn't necessarily make things easier).

    3. better access control in many situations. sometimes you want to get at data but don't want the users to have that kind of access. you can run a function as a higher level user but allow lower level users select access to the function.

    Yup; that is indeed a good reason to use stored procedures or views.

    4. faster/more accurate. in general, your DBA will write a better/faster query than your programmers.

    Of course the DBA will write better queries; that's why I advocate making DBA review mandatory for code changes impacting the data access layer. In shops with a good DBA, the programmers will come to the DBA first when they have a complex query to write anyhow; that's what happens where I'm at presently. (Our DBA is a rockstar, incidentally poached from my last employer, and very well-respected; at that last job, however, we had a CEO's-college-buddy incompetent before we had the rockstar, and I'd have hated to see him hold the power your workflow would grant).

    5. One less thing for your programmers to worry about. it means they can focus on writing the application (which is their job remember).

    From the perspective of the programmers writing the data access layer (you're doing a proper tiered application with business logic and data access broken off from each other, right?), they need to worry about interfacing with the DB no matter what; your proposal reduces their scope considerably (by making the code they maintain effectively into a collection of nearly-opaque stubs referencing logic stored elsewhere), but certainly doesn't eliminate the relevant work from development's domain.

    I'm largely playing Devil's Advocate here: What you're advocating is a good workflow, but I think that calling it the only good workflow is a serious misrepresentation -- the problems it addresses can be resolved through other means, and at least some of the benefits are two-sided.

An optimist believes we live in the best world possible; a pessimist fears this is true.

Working...