Slashdot Log In
Diagramming Tool For SQL Select Statements
Posted by
kdawson
on Sun Aug 03, 2008 07:49 PM
from the unique-like-a-snowflake dept.
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... "
Related Stories
This discussion has been archived.
No new comments can be posted.
The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
Full
Abbreviated
Hidden
Loading... please wait.
Bring a database down? (Score:5, Informative)
No single query will ever bring a (real) RDBMS down. Even on a terrabyte of data or more, doing a crazy multi-hundred-table cross join, you're not going to bring it down.
Now, it could seriously slow down a production server, but... you're not pushing untested SQL on a production server now, are you? Right? Riiiiiiiiiiight?
So at worse, you're slowing down your own localhost development database engine for everyone else trying to access it (read: no one).
Not much for the DBA to scream about...
Re:Bring a database down? (Score:5, Informative)
No single query will ever bring a (real) RDBMS down. Even on a terrabyte of data or more, doing a crazy multi-hundred-table cross join, you're not going to bring it down.
A real ACID-compliant database, no. MySQL, maybe.
Now, it could seriously slow down a production server, but... you're not pushing untested SQL on a production server now, are you? Right? Riiiiiiiiiiight?
Unfortunately sometimes you do need to run new queries against production servers. Of course, with a real database like MSSQL or Oracle, you can see how a query will execute, what path the optimizer will follow, and what the cost of the query will be.
Parent
Re:Bring a database down? (Score:5, Informative)
Explain/describe exists in MySQL, it's just very hard to do.
Is it possible to bring Oracle down? I would think so, it would just take a lot (note: assuming normal hardware, not a large high-power cluster). Is it possible to take MySQL down? Easily. It can be surprisingly easy to lock the server completely. Even when you select off one set of tables (A) and want to insert into another set (B, possibly in a different schema/DB) it is possible to have things locked. It's very easy. We haven't seen a crashing bug in MySQL in a while (fun: a query that formated dates with the date format function could reliably crash MySQL 4.0 or 4.1 (don't remember which).
Does explain help? No. On Oracle it may help. In Postgres it seems to help. I have no experience with MSSQL. In MySQL you have to watch out. While it can be useful, it is very limited.
It's row counts can be horribly useless. It can list 1.2 million rows when in fact it can take a fraction of a second to get the data because it's all in an index in memory.
Worse: it will run the query for you. Under some circumstances (using a subquery can do it, using more than one level of subquery is almost guaranteed to do it) it will just run the inner query and then use that to produce results. This means that describe/explain can lock the database and take hours to return (if you had a query that was bad enough and didn't kill the describe/explain). It's all the fun of running the real query, without the results actually presented to you.
Note: We're using 5.0 (since 5.1 isn't production ready yet). Some of this may be fixed.
Parent
Re:Bring a database down? (Score:5, Informative)
Oh yes, Oracle can be brought to a grinding halt (even on substantial hardware) by a big nasty query. It may not be crashed, but it's nonresponsive. Especially annoying when there is no need for the cartesian product; Oracle's pessimizer just chose to do one when something else was MUCH more appropriate. Alas this tool would not catch that situation (but EXPLAIN PLAN does).
Parent
Re:Bring a database down? (Score:5, Interesting)
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).
Parent
Re: (Score:3, Funny)
Terrabyte? A planet byte?
Re:Bring a database down? (Score:4, Funny)
So, you don't put an untested query on a production server. Great. What happens when someone changes data in such a way that your query now explodes? :D
In the last case I had to deal with that, one boneheaded programmer had his code set to send him an email if it couldnt' find a good match in the DB. Someone changed the data, and with the amount of traffic, his code, spread across our web serving farm, had injected almost a million messages into the email queues. Programmers are awesome.
Parent
Re: (Score:2)
In that case, your web farm and/or email server dies, while the database server is still purring along :)
Re: (Score:3, Informative)
Depends on your database. I know I have been able to bring SQL server down with a query.
Try this...
begin transaction
update rows set a=b where x=y
commit transaction.
On your workstation this could run really fast because you only have ten records. On the production database server this could crush the server if you had a few million records effected.
Re: (Score:2)
Not from where I'm standing. The query itself will be slow if it affects a lot of rows (if I use exactly the query you're giving, and that X is indexed, it will be near instant, no matter how many rows I have =P), but the server will purr along just fine with other queries (assuming its configured to use snapshot mode, else it could lock away other queries... but who doesn't use snapshot mode since its been available in SQL Server anyway? Especially after people been bitching about it from the day it was av
Re: (Score:3, Interesting)
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.
Re: (Score:3, Informative)
Maybe my poor queries writing skills are bad :) Because I've seriously -tried- before... cross joins on 100+ tables, all of which containing several douzen gigs of data, totally multiple terabytes...the scheduling was good enough to give the query very low priority, leaving the server ok.
If you use (in SQL Server at least) the default settings, that will basically render your database useless... but if you use the newer locking strategies from 2005 (which had been available in Oracle for ages), the tables
Re: (Score:3, Insightful)
Re: (Score:3, Informative)
>In practice, there's no difference.
To a DBA its a big difference.
1. Just a massive slow down - login (SQL Server there is a Dedicated Administrator Connection, don't think I've had problems connecting with a problem Oracle db as long as I can get on the OS (partly because sessions are processes)), and just kill the process. The DB should clean everything up. (as long as its not a toy db; I'm looking at you MySQL.)
2. A crash - then you have to go through a whole number of steps to bring it up and then
Re:Bring a database down? (Score:5, Informative)
Parent
Still no cure for cancer? (Score:5, Informative)
A link to an alpha project on Sourceforge that was created three days ago and doesn't even have its own website? That apparently outputs LaTeX tables instead of something readable without having to compile it first, like HTML, SVG, or even indented text? I know it's silly to expect every story to be about a cure for cancer, but come on...
Re: (Score:2)
an alpha project on Sourceforge that was created three days ago
... and won't go much farther because even a casual reading of the explain plan can plainly show what the project is trying to track down. It sounds like a complete duplication of effort.
Re:Still no cure for cancer? (Score:5, Informative)
Yup, not cool.
Word to the wise: if you're going to actually start advertising a project, please make sure you have some binaries built for some common relevant platforms, and make sure you have some decent information online even if it's just an ugly page with screenshots or examples of what it does.
In this case, we're talking about some scripts written in Python. At least let people know this on the front page, and list the project dependancies! ie, GraphViz, or whatever.
This way, your potential users won't immediately discard it due to a lack of compelling information, and your potential (future) developers can see how far you've got and maybe get inspiration to chip in and help!
That said, this sounds like it should be a great tool for beginner or intermediate SQL users, and I look forward to throwing a few of our mammoth 12-table-join queries at for much fun.
Parent
Existing tools (Score:3, Interesting)
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.
Re: (Score:3, Informative)
Ditto. I downloaded it to take a look and see how good it was at parsing T-SQL, since we have a few saved T-SQL queries with WHILE loops in them. I gave up after seeing it's... nothing. Just a Python script. It requires Graphviz, Python, and Pyparsing (even though it comes with pyparsing!? WTF!), and even more damning is that you can't use it for ad-hoc queries, the query has to be saved into a file first.
Someone slap a GUI on this that lets you paste in a query, and bundle all the requirements along with t
So, an alpha project for what exactly? (Score:5, Interesting)
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!
Re: (Score:2, Informative)
Watch out! Anyone pointing out how a kdawson story isn't news gets moderated down as a troll. I can't even work out how this got out of the firehose.
Re: (Score:2)
I have karma to burn, and I don't take kindly to anyone (even the powerrs that be) modding down when what I say is based upon fact!
Look to my credentials, only been a DBA for 20+ years!
Re: (Score:2)
Yes, but a DB2 developer.
And remember, you don't look "to" credentials, you look "at" them.
EXPLAIN (Score:5, Insightful)
I don't see what this has over EXPLAIN [postgresql.org] and an appropriate graphical display tool like PgAdmin-III [pgadmin.org]. There are large numbers of tools that display graphical query plans [postgresonline.com] - and unlike this simple SQL parser, they know how the database will actually execute the query once the query optimiser [wikipedia.org] is done with it.
Furthermore, a simple SQL parser has no idea about what indexes [wikipedia.org] are present, available working memory for sorts and joins, etc. It can't know how the DB will really execute the query, without which it's hard to tell what performance issues may or may not arise.
See comment 24461217 [slashdot.org] for a more detailed explanation of why this whole idea makes very little sense.
Mod parent up. (Score:2)
That's right. Mod parent up. Any tool that only looks at the SELECT statement, without knowing about the indices or what the optimizer is doing, is nearly useless.
Re: (Score:3, Informative)
I think you might've missed the point.
The term SELECT statement generally refers to the whole statement, including FROM, WHERE, HAVING, etc clauses.
This is pretty clear in context, as it'd be nonsensical to produce a graphical explain tool for the result field list in the SELECT clause its self.
That's why the parent said SELECT statement not SELECT clause .
As it happens the same issues regarding the need for planner knowledge etc are true for DML like INSERT, UPDATE and DELETE. It's not about SELECT at al
Re: (Score:3, Informative)
Another comment here revealed part of why someone might think a tool like this was useful:
In MySQL, EXPLAIN apparently works more like PostgreSQL's EXPLAIN ANALYZE (and related features in other RDBMSs). MySQL's EXPLAIN actually executes the query rather than just running it through the query planner. The documentation [mysql.com] even warns that data modification is possible with EXPLAIN in some circumstances.
If your database gives you no way to ask the query planner what it will do without actually executing the quer
Is this (Score:2)
They day of the python? There were 2 different tools written in python.
I'm not flaming, I just thought it was interesting as I loved programming in python when in college!
Re: (Score:2)
"When in college"? Why not now?
Python jobs aren't hard to find.
In tablespace, no one can hear you scream... (Score:4, Funny)
I've noticed that when things go horribly wrong, you don't actaully have to sit that close. To be fair, as a Unix SA who has to deal with Windoze systems, I've done my fair share of screaming. :-)
Are you serious? (Score:2, Informative)
Re: (Score:2)
Not free as in Freedom, but definately free as in free beer. The SQL Management Studio Express version has the execution plan stuff, and it works quite peachy, for free.
Re: (Score:3, Insightful)
Oh damn. I had to buy a whole PC set to run Debian. I guess Linux isn't free after all.
I'm screaming from the summary. (Score:3, Funny)
Can we have that in English please? Possibly with a diagram?
Re: Bring a database down? (Score:3, Insightful)
No single query will ever bring a (real) RDBMS down. Even on a terrabyte of data or more, doing a crazy multi-hundred-table cross join, you're not going to bring it down.
You've obviously not tried anything simple on MS-SQL, like expanding a varchar(4) column to nvarchar(10) on a table with a few million rows. MS-SQL spins its wheels filling-up the transaction log until it overflows, then rolls it all back again. A 4GB log file, filled with a 250meg table (and no indexes because they were already dropped)?
In the end we had to drop all FK refs, select * into another table, drop the original table then select * (with conversions) into newTableWithOriginal's name and reset all the FK's. *shakes head*
Re: (Score:3, Insightful)
> myspace.com has been supplated by Facebook.
Facebook being more popular than mySpace has nothing to do with the database back-end. If you need more big customers for SQL Server 2005, they are easy to find: Barnes & Nobles, HMV online music store, NASDAQ (over 5000 transactions/sec).
So basically your statement that SQL Server is a toy database might have attracted a few claps 6 or 7 years ago on Slashdot, but the reality is that SQL Server is a robust product finding its way in many markets. As one c
Re: (Score:3, Insightful)
Looking for a problem? (Score:5, Insightful)
Execution of SQL statements can require the RDBMS to perform nested loops over parts of the query execution.
This can be an issue if the DBMS is forced to do something like perform a sequential scan of one table for each record matched in another table. That gets expensive *fast*.
There are many other possible performance issues, of course.
However, I don't see how SQL parsing can tell you much about the performance characteristics of the query. The database's query optimiser makes choices about how to execute the query, and is free to change its mind depending on configuration parameters, available resources, system load, disk bandwidth, present indexes, statistics gathered about data in the table, etc. PostgreSQL's planner for example does make heavy use of table statistics, so query plans may change depending on the quantity and distribution of data in a table.
Any decent database can already tell you how it will execute a query (and usually give you a performance readout from an actual execution of the query). There are plenty of GUI tools for displaying the resulting query plan output graphically. PgAdmin-II can do it, for example.
A simple SQL parser can have no idea about what indexes are configured, the distribution of the data, how much working memory the database has available for sorts and joins, etc. The database knows these things - and can already tell you how it will, or did, execute a query - so why not let it do its job?
The whole project doesn't make much sense.
Parent
Re:Looking for a problem? (Score:4, Insightful)
I use diagrams as a tuning tool, but only to look for paths that don't make sense or alternate paths through tables or for "dead-ends"......but these are things that a computer can't really tell you because they require an understanding of the data.
But you're right, the explain plan is the single most useful tool for tuning a query. If you understand how the engine is going to execute the query you know what areas you can affect. And tuning is manipulaing those effects in a way that makes the query faster.
Layne
Parent
Halting problem (Score:2)
If it was, such a query analysis tool would be provably incapable of handling all queries because of the halting problem [wikipedia.org].
Thankfully most SQL dialects are limited to expressing queries that can be executed in finite time with a defined end point.
Re: (Score:3, Informative)
Heck, even ANSI finally got into recursive SQL using the WITH clause:
Re: (Score:3, Insightful)
Quis custodiet ipsos custodes?
Re: (Score:2)
Re: (Score:3, Interesting)
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
Re: (Score:2)
You can tout that as "the right way", but there's still no reason this has to be a technical-design issue rather than a process-design issue -- and while my background is as an OSS groupie, I've been the OSS groupie at enough proprietary shops (ie. the party responsible for dealing with upstream on projects used as underlying infrastructure for actually running the proprietary software we built) that I can say with a fair bit of confidence that the approach you're espousing just isn't all that popular in Th
Re: (Score:3, Interesting)
Counterargument: Less readable code, as it's split into two places.
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 downgr
Re: (Score:2, Informative)
That, and not using medium and low duty databases lile MSSQL and MySQL can go a very long way to keeping users happy.
Honestly, to describe MSSQL as "medium and low duty" is pretty rich. You'd best believe I'm happy to bash MS as much as the next guy but SQL Server is a high-performing, highly maintainable, high-availability database and doesn't deserve to be mentioned in the same sentence as MySQL.
Hell, MSSQL might actually be the only truly good product MS make -- in fact, it probably is. It's not a toy and people who assume it is, just because it comes from MS (I'm not saying this is what you're doing, but people DO do
Re: (Score:2)
Besides, for legacy applications that were coded like shit, there's already a bunch of tools that will scan them (either the application, black box style, though mostly for the web, or the code, white box style) to find sql injection vulnerabilities.
Still sad that even to this day, if you go to your favorite programming language XYZ forum, half of the newbies use concatenated strings, because a large amount of tutorials on the net do it that way...
Re: (Score:2)
I'm surprised your RDBMS doesn't complain that only one table is referenced in the ON statement.
Then again, I've never tried an ON statement that only references one table...