Diagramming Tool For SQL Select Statements 156
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... "
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.
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.
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).
Re: (Score:2)
You can use Database Resource Management to control maximum execution time for queries in Oracle; it's designed to protect such scenarios.
However, the "pessimizer" as you call it is not a magic wand - it's just decent heuristics for selecting the optimal path for data retrieval. It's pretty complex; and has taken decades to come to this level. It's damn good if you understand it and configure your instance correctly. Where it doesnt work, you always have had other options.
And no, I dont work for Oracle.
Re: (Score:2)
The question is: is there a semantically equivalent query that DOESN'T overload the system?
A cartesian product isn't necessarily a bad choice, if the product fits in memory. I personally haven't had this problem with Oracle, so I'd be interested in an example.
Re: (Score:2)
Yes, in the particular cases I ran into; I was able to reformulate the queries to avoid the problem. They were SELECT DISTINCT on a big hairy set of tables; by making that query into a subquery with SELECT ALL, and then doing a SELECT DISTINCT on the subquery, the problem was resolved.
Re: (Score:2)
The statement about being 'no need for a Cartesian product' reminds me of a guy who said inner joins should never be used because they can bring a system down. Because he wrote one once that was a poor query and it did just that.
Just because a person doesn't know when to use something properly, or uses it improperly from time to time, doesn't mean it has no use.
I've used Cartesian joins before. Not very often, but I do recall using them in the past for very specific requirements. If memory serves me, it was
Comment removed (Score:5, Interesting)
Re: (Score:2)
Oracle... we use it for some backend stuff at work, and frankly I think a monkey with a pen and paper could be faster.
Someone's done something wrong (probably stupidly wrong), as I've used Oracle on a system running stupidly complex queries against a table containing ~60 million rows, and while some queries can take a while most are surprisingly fast (sub-second or two). Yes, it's been designed and optimised to be fast - but that's what I meant about someone at your place having done something wrong...
Re: (Score:2)
Re: (Score:2)
Re: (Score:2)
Re: (Score:2)
Is it possible to bring Oracle down?
Yes. Run Oracle 10.2.0.1 on SLES10 and wait for 49 days until the value returned by the times() syscall wraps around. Oracle shits itself. It can take up to 247 days on other distros.
I'm sure they've fixed it since, but holy shit that's a stupid bug. What was that other software that crashed after 49 days? Windows 95. Unbreakable indeed.
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.
Re: (Score:2)
In that case, your web farm and/or email server dies, while the database server is still purring along :)
Re: (Score:2)
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:2)
Hmm... but what if X is a clustered index (or some other index type which physically orders the data on the physical storage...)? if you're updating the clustered index, you could wildly, inadvertently crush your server due to the disk I/O as data has to get reordered on the drive...
Not the biggest fan of clustered indexes in Sql Server/Sybase... Oh well, I suppose in one of the next few releases of SQL Server that MS will figure out how Oracle does some more of their low-level black magic, and they will q
Re: (Score:2)
Oracle's "low-level black magic" is multi-version concurrency control, where it's segregated redo and undo operations, eliminating locks as a scarce resource in the system.
And Oracle tracks clustering factor of every index. If you really want data to be clustered about an index in Oracle, one creates the table with the clause ORGANIZATION INDEX and the table is physically created as a B-tree index structure.
Re: (Score:2)
Hm-m-m-m. I do this on a daily basis with one of our instances. We use SQL Server as a data preparation engine for a predictive modeling operation. This typically involves mass updates of the type you're specifying. I'm working on a set of data right now, with table sizes up to 45M rows. Some of the longer updates will run for 3 to 5 minutes, but that's hardly locking up the box. If you poorly construct the query, it will run for a couple of hours, but I don't call that bringing the database down, it'
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:2)
This sounds like the server is doing potentially unbound amount of I/O or processing with a lock held. Otherwise the other queries should still run, just slightly slower due to increased load in the server. A query, no matter how poor, shouldn't b
Re: (Score:2)
One feature that I dearly wish RDBMS vendors would provide is a time-limit clause. Example:
SELECT * FROM foo TIMELIMIT 30
This would ensure that the statement does not run for more than 30 seconds.
Re:Bring a database down? (Score:5, Informative)
Re: (Score:2)
No. That is a feature of the server and can be configured in postgresql.conf or on a per user basis (ALTER USER SET statement_timeout = ...).
Just beware it applies to all statements. You may want to check those long running reports or maintenance queries.
Re: (Score:2)
Err, there are ways of configuring decent RDBMS to have this kind of behaviour, albeit typically on a per user basis - just like you can limit the number of parallel queries a particular user can execute.
Re: (Score:3, Insightful)
Re: (Score:2)
This type of error would go unnoticed? Err? If the query returns -the wrong data- (as a cross join by mistake would), and it goes unnoticed, something worse than your database crashing on you is waiting.
Re: (Score:2)
Re: (Score:2)
This is why there are "reference" systems - typically the same size and spec (or within the same ballpark) that contain the same data as the live system (or again, some representative sample of it) which are used for staging / integration / pre-release testing.
Re: (Score:2)
Dev, Testing, Staging, Prod ...
Technically Staging is identical to production environment with the same amount of data. Testing has less powerful hardware, but many times just as much data to deal with.
BTW with our more or less 100GIG database we run tests on WAY slower machines than what the actual DB server is. We do not have staging or testing, but one environment where we mess around. Development is pretty much the same, but with smaller DBs...
Re: (Score:2)
It's not that you bring it "down", you just slow it down to the point of a noticeable performance hit to everyone else. I've seen queries written by people just smart enough to be very dangerous, where the query consumes 100% of resources for an hour (when if it was written correctly, the query would take 2 seconds). That's as good as "down" in some cases.
Re: (Score:2)
Well, a modern RBDMS is practically an operating system. This means that they way you bring it down doesn't involve the kinds of things a tool like this tells you. You probably need to do something procedural, involving a mix of tasks the RDBMS can't handle efficiently.
Of course, it is always possible to tune things in a disastrous way. Oracle is an RDBMS that is highly tunable, which means that a lot of people make really bad choices, for example tuning things in a way that require greater memory witho
Re: (Score:2)
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?
Not as part of a system, no, but if I just need answers, I run untested queries on production servers all the time.
Re: (Score:2)
You clearly don't use SQL Server, it has a great propensity to run out of memory and crash (OK, not great, but every now and again).
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: (Score:2)
Data mining should be done on the datawarehouse's OLAP cubes, and the queries you do aren't pure SQL at that point (with all the MDX and whatsnot), so the tool become useless.
Re: (Score:2)
Indeed. I was quite careful to say "no -SINGLE- query..."
Well, unless you're running the database on a Pocket PC or something =P
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.
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
Or... (Score:1)
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)
There were lots in SQL Server 2000
With 2005 you really need to hit the high end stuff of Oracle/DB2.
One example is Oracle RAC, multiple servers acting as one database using one set of data files. SQL Server has only really simple failover, I think they want you to use Windows Clustering(?).
Native geospacial datatype is not in 2005 but I think is in 2008.
Not run on Unix and the like. Yes, for some shops its required. Depending on the OS version, you hit file size and RAM limits.
Pet Peeve - What's up with
Re: (Score:2)
Oh, and if you want to enforce query timeouts, that is supported in the user profile via CPU_PER_CALL (non-conforming queries are terminated and resources released)
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.
Re: (Score:2)
And remember, you don't look "to" credentials, you look "at" them.
PHLEBAS the Phoenician, a fortnight dead,
Forgot the cry of gulls, and the deep seas swell
And the profit and loss.
A current under sea
Picked his bones in whispers. As he rose and fell
He passed the stages of his age and youth
Entering the whirlpool.
Re: (Score:2)
Actually looking "to" is correct within the original context. But you go ahead and think what you think.
i smell a cheap trick... (Score:1)
your programmers shouldn't be writing SQL (Score:1)
this way the programmers are prevented from infecting the database from their crapness
Re: (Score:3, Insightful)
Quis custodiet ipsos custodes?
Re: (Score:1)
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)
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
I've done projects like that (Score:2)
And these type of edicts from up on high tend to really bite you in the behind over time. You wind up with hundreds upon hundreds of stored procedures, and nobody knows which ones are even in use any longer. One project will wind up requesting a change that affects another project, and it basically excludes any O/R mapping tools. It's just one huge mess.
Your best bet is to insist that your developers are just a little clued in. How hard is it to say, "As long as your queries always have an indexed field
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:Mod parents down!!! (Score:2)
> Any tool that only looks at the SELECT statement, without knowing about the indices or what the optimizer is doing, is nearly useless.
This is wrong. The indices or optimizer have very little to do with the SELECT; at the time of processing the SELECT clause, most database engines already are done with the FROM, JOIN, WHERE, GROUP and HAVING clauses. At this point there will be little gain to add/drop indices from the query plan, unless the platform does support included fields. As such, SELECT is like
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:2)
> I think you might've missed the point.
> The term SELECT statement generally refers to the whole statement, including FROM, WHERE, HAVING, etc clauses.
I did not miss the point:
>> For long queries with complex joins (like recursion), a diagram tool for SELECT can be very helpful
I simply disagree that the product is useless without knowing about the indices. Before one should start reviewing query plans and figuring out what index is important, one must make sure the query makes sense. Logical be
Query result analysis (Score:2)
Re: (Score:2)
> The problem is that sql lets you put statments inside of statments. Putting those in the wrong places can be devistating in performance. The rule is pretty easy. You can put select statments in the select clause or where clause areas but be prepared for it to take awhile to finish. Put them in the FROM clause where they belong.
The SQL optimizer will actually do that for you. It can replace a correlated subquery by a JOIN if this appears to me more optimal.
> When I have a perf issue I almost always s
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:2)
...and that is why you should switch to DB2.
What the heck are you doing with MS-SQL Server? Don't you know its for developers and kids?
Trying to use SQL Server in production is like trying to cut your toenails with a straight razor: You may end up cutting your toenails ultimately, but you are likely to bleed yourself to death before that.
Re: (Score:2)
> What the heck are you doing with MS-SQL Server? Don't you know its for developers and kids?
You should call Myspace.com and let them know that they made a stupid database choice.
Seriously, DB2 is so 1992.
Re: (Score:2)
myspace.com has been supplated by Facebook.
And DB2 is the granddaddy that is being trusted by ALL banks.
Give me a bank which does not store its data on DB2, and i will concede this.
And banks are the most thorough corporate IT customers.
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:2)
For the bigger banks
How about HSBC, Statestreet & Barclays? Both live on IBM.
I lived with many large banks for over a decade, especially with HSBC.
IBM is something they swear by. Not just because its well known, but because it is so good and thorough.
Mid level banks, your figures are correct: Oracle and then SQL Server.
I worked for a mid-level bank in CT which migrated from mainframe to Services-Bases Arch and uses Oracle as a back-end.
Plus, security of data and privacy lawsuits terrify large banks more. I had to go throug
Re: (Score:2)
MS SQL Server is good and relatively cheap. The type of problem the grandparent mentioned exists in ALL DBMSes from ALL vendors. But there's nothing that DB2 can do that MS SQL can't, and MS SQL has great data-flow tools that come along with it to make actual use of the data.
Now, Microsoft's graphical DB tools (SQL Server Management Studio) has gone backwards in many ways from the older Query Analyzer/Enterprise Manager toolset, but oh well.
You missed a keyword... (Score:2)
You missed the parenthesized word in "No single query will ever bring a (real) RDBMS down."
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.
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
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:2)
Re: (Score:3, Informative)
Heck, even ANSI finally got into recursive SQL using the WITH clause:
Re: (Score:2)
I tested this on SQL Server 2005 Express and either I am doing something wrong or your statement is false.
I created three tables Table1, Table2, Table3. Each one has tw
Re: (Score:2)
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...
Re: (Score:2)
SELECT * FROM Table1 t1 join table2 t2 on t1.column = t1.column.
WHOOPS. Its such a easy mistake to make, too, especially if you use silly non-significant aliases like T1, T2, T3 instead of shortening the name to something meaningful.