Beta
×

Welcome to the Slashdot Beta site -- learn more here. Use the link in the footer or click here to return to the Classic version of Slashdot.

Thank you!

Before you choose to head back to the Classic look of the site, we'd appreciate it if you share your thoughts on the Beta; your feedback is what drives our ongoing development.

Beta is different and we value you taking the time to try it out. Please take a look at the changes we've made in Beta and  learn more about it. Thanks for reading, and for making the site better!

Diagramming Tool For SQL Select Statements

kdawson posted more than 6 years ago | from the unique-like-a-snowflake dept.

Databases 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... "

Sorry! There are no comments related to the filter you selected.

Bring a database down? (5, Informative)

Shados (741919) | more than 6 years ago | (#24461071)

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? (5, Informative)

Anonymous Coward | more than 6 years ago | (#24461201)

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? (5, Informative)

MBCook (132727) | more than 6 years ago | (#24461369)

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? (5, Informative)

russotto (537200) | more than 6 years ago | (#24461649)

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).

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:Bring a database down? (1)

popeyethesailor (325796) | more than 6 years ago | (#24462019)

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:Bring a database down? (5, Interesting)

liquidpele (663430) | more than 6 years ago | (#24461925)

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).

Re:Bring a database down? (0)

Anonymous Coward | more than 6 years ago | (#24463487)

I'm far from a SQL guru, but isn't that sort of situation the place to store user search results (vBulletin-esque) and query for the stored search before hitting the live data?

Re:Bring a database down? (1)

Slashcrap (869349) | more than 6 years ago | (#24464621)

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:Bring a database down? (0)

Anonymous Coward | more than 6 years ago | (#24462193)

If you are worried about adversly effecting system performance look at the query plan estimate before running the thing for hints that you may have missed something (Not only join problems but missing indexes, statistics...etc)

If it still takes too long to run you can always cancel it or use your platforms query governer if you need to be that paranoid.

Tools are great but in this case it seems like your platform tools should provide better coverage for detecting any problems.

Re:Bring a database down? (3, Funny)

Anonymous Coward | more than 6 years ago | (#24461379)

Terrabyte? A planet byte?

Re:Bring a database down? (1)

haltenfrauden27 (1338125) | more than 6 years ago | (#24461551)

I mean, what people mean when they say "bring it down" is basically what you said - "slow it down a lot." In practice, there's no difference.

This reminds me of something Bill Gates used to say about Windows. Something to the effect that if users were more patient many "crashes" would actually take care of themselves.

Re:Bring a database down? (2, Informative)

GoofyBoy (44399) | more than 6 years ago | (#24461847)

>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 verify the data is ok, then let everyone back in. There may be an backup involved if you are unlucky. You definitely want to figure out what happened.

Re:Bring a database down? (3, Funny)

NerveGas (168686) | more than 6 years ago | (#24461597)

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:Bring a database down? (1)

Shados (741919) | more than 6 years ago | (#24461725)

In that case, your web farm and/or email server dies, while the database server is still purring along :)

Re:Bring a database down? (-1, Troll)

Anonymous Coward | more than 6 years ago | (#24461647)

I like eating pee pee

Re:Bring a database down? (0)

Anonymous Coward | more than 6 years ago | (#24461687)

Trollly trolly trollllly
likes to play with
dolly dolly dolly
hee hee hee
ha ha ha
I can do a dance
I'm not wearing pants
Bye Bye Bye!

Re:Bring a database down? (1, Funny)

Anonymous Coward | more than 6 years ago | (#24461721)

so you're a linux user. what else would you like to tell us?

Re:Bring a database down? (0)

Anonymous Coward | more than 6 years ago | (#24461885)

so you're a linux user. what else would you like to tell us?

I also like wearing women's clothes.

Re:Bring a database down? (2, Informative)

killjoe (766577) | more than 6 years ago | (#24461765)

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:Bring a database down? (1)

Shados (741919) | more than 6 years ago | (#24461819)

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 available in Oracle...)

Re:Bring a database down? (1)

Forbman (794277) | more than 6 years ago | (#24463059)

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 quietly deprecate clustered indexes... (that is, keep them in spirit, but change their implementation as a New Innovation!!!)...

Re:Bring a database down? (1)

Threni (635302) | more than 6 years ago | (#24463147)

I'm a fan of clustered indexes in SQL Server 2000, as they are generally fast than non-clustered indexes. Never managed to get them to crash, either. What am I doing wrong?

Re:Bring a database down? (1)

reybrujo (177253) | more than 6 years ago | (#24462353)

Incidentally, your query crashes the referenced tool. Talk about a really nasty query ;-)

Re:Bring a database down? (2, Interesting)

mbourgon (186257) | more than 6 years ago | (#24461955)

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:Bring a database down? (2, Informative)

Shados (741919) | more than 6 years ago | (#24462103)

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 won't be locked, and everything will be fine. Laggish for sure, but the server definately won't tank.

Somewhere I used to work for, I would even run millions of inserts, continually, on our staging server (which was shared among a 50 or so devs) because I was testing an ETL routine... the server was slower for sure when I messed up and did a multi cross join on my insert source on a friday evening, but it never brought it down.

Re:Bring a database down? (1)

ultranova (717540) | more than 6 years ago | (#24463929)

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.

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 be able to monopolize resources; it should share them equally with all other running queries, and merely take its sweet time to return.

In other words, it sounds like a bug.

Re:Bring a database down? (1)

Tablizer (95088) | more than 6 years ago | (#24463011)

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? (5, Informative)

Craig Ringer (302899) | more than 6 years ago | (#24463221)

craig:~$ psql
Welcome to psql 8.3.3, the PostgreSQL interactive terminal.
craig=> set statement_timeout=1000;
SET
craig=> SELECT generate_series(0,100000000000000000);
ERROR: canceling statement due to statement timeout

Re:Bring a database down? (2, Insightful)

Venik (915777) | more than 6 years ago | (#24463183)

SQL code is usually developed on some small server or the DBA's own workstation. The dev database is representative of the prod version only in structure and not in size. So this type of errors sometimes go unnoticed until the code is migrated to the prod environment. The effect of such errors vary depending on server architecture. The most sensitive are HA cluster environments, where the clustering engine overreacts and starts failing things over, exacerbating the problem.

Re:Bring a database down? (1)

Shados (741919) | more than 6 years ago | (#24464611)

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.

Might come in handy (0)

Anonymous Coward | more than 6 years ago | (#24461099)

I know, it would have caught this


SELECT *
    FROM Event E
    LEFT JOIN Document D ON D.DocumentTitle = 'Daily Note'
  WHERE D.DocumentTitle IS NULL

Hint: LEFT JOIN runs in O(N) time where NOT IN runs in O(N2) time on this system.

Re:Might come in handy (-1, Troll)

Anonymous Coward | more than 6 years ago | (#24461165)

why would you write such a stupid query to begin with?

Re:Might come in handy (1)

VGPowerlord (621254) | more than 6 years ago | (#24461871)

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:Might come in handy (0)

Anonymous Coward | more than 6 years ago | (#24462201)

"Don't prevent your users from doing stupid things as that will also prevent your users from doing cleaver things."

I have never needed a left cartesian product, but I have needed a left join and a cartesion product in the same query. The easiest way to get it is ON 1 = 1.

No, it doesn't complain, and I'm glad it doesn't.

Thanks for noticing.

Still no cure for cancer? (5, Informative)

hkz (1266066) | more than 6 years ago | (#24461105)

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:Still no cure for cancer? (0)

Anonymous Coward | more than 6 years ago | (#24461215)

That was submitted to Slashdot by the project's creator, no less...

Re:Still no cure for cancer? (1)

magarity (164372) | more than 6 years ago | (#24461255)

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? (5, Informative)

LSD-OBS (183415) | more than 6 years ago | (#24461267)

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 (2, Interesting)

Craig Ringer (302899) | more than 6 years ago | (#24461337)

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.

WTF (-1, Troll)

GleeBot (1301227) | more than 6 years ago | (#24461121)

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.

SQL isn't Turing complete. It can't loop. That's sorta the whole point.

Troll, but I'll bite anyway (0)

Anonymous Coward | more than 6 years ago | (#24461155)

WITH A AS (
SELECT 1
UNION ALL
SELECT 1
) SELECT *

runs forever.

Re:Troll, but I'll bite anyway (0)

Anonymous Coward | more than 6 years ago | (#24461479)

Is this an SQL statement (ANSI/ISO)? I don't think so.

Re:Troll, but I'll bite anyway (0)

Anonymous Coward | more than 6 years ago | (#24462031)

Except for the fact that it's been so long since I used that construct that my syntax is probably off, it is in SQL'93.

Re:Troll, but I'll bite anyway (1)

Craig Ringer (302899) | more than 6 years ago | (#24463237)

The WITH clause was apparently introduced in SQL:1999. It's still not particularly widely supported, though.

Looking for a problem? (5, Insightful)

Craig Ringer (302899) | more than 6 years ago | (#24461217)

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? (3, Insightful)

SQLGuru (980662) | more than 6 years ago | (#24461281)

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 (1)

Craig Ringer (302899) | more than 6 years ago | (#24461361)

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:Halting problem (-1)

Anonymous Coward | more than 6 years ago | (#24462025)

If it was, such a query analysis tool would be provably incapable of handling all queries because of the halting problem.

Thanks for a link explaining what the halting problem is on Slashdot.

Since you've obviously passed your freshman year of Comp Sci, stick around and you'll learn that SQL queries are not turing complete, ergo, the halting problem doesn't apply.

Re:Halting problem (1)

Craig Ringer (302899) | more than 6 years ago | (#24462177)

Yes - exactly my point. Please read my post and its parent.

Re:WTF (3, Informative)

ahmusch (777177) | more than 6 years ago | (#24461443)

Really? Most of us would call recursive SQL "looping" SQL, and something like this in Oracle is recursive:

SELECT LPAD(' ', 2*LEVEL, ' ' ) || ename empName, dname, job,
sys_connect_by_path( ename, '/' ) cbp
FROM emp e, dept d
WHERE e.deptno = d.deptno
CONNECT BY PRIOR empno = mgr
ORDER SIBLINGS BY job;

Heck, even ANSI finally got into recursive SQL using the WITH clause:

with TransClosedEdges (tail, head) as
( select tail, head from Edges
union all
select e.tail, ee.head from Edges e, TransClosedEdges ee
where e.head = ee.tail
)
select distinct * from TransClosedEdges;

Now let's imagine queries with multiple levels of nesting using such clauses - after all, any SELECT statement can generally be used in any FROM clause.

Now, perhaps you're Chris Date or Fabian Pascal and are truly concerned with the completeness of SQL as implementing the relational model. For the rest of us, however, recursive SQL can answer interesting questions without getting into the nastiness of procedural code.

Oh, and considering the default join in virtually any SQL database is a nested-loop join, I'd say all databases loop by default. And a statement as innocuous as :

select * from a, b, c;

Can absolutely crater cpu and I/O performance. If each has 1,000 rows and there's not enough memory, there's 1,000,001 table scans. Hope your disk is fast.

Or... (1)

yttrstein (891553) | more than 6 years ago | (#24461125)

You can do what I've done and seen done a number of times, and write a hunk of middleware that parses SQL statements for runaways and send back a warning to the user. That, and not using medium and low duty databases lile MSSQL and MySQL can go a very long way to keeping users happy.

Re:Or... (2, Informative)

mino (180832) | more than 6 years ago | (#24461329)

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 this) just show that they don't know what they're talking about.

Re:Or... (1)

yttrstein (891553) | more than 6 years ago | (#24461399)

Let me know when MySQL is able to do a real hot dumb without locking tables, and I'll stick it right up there with Sybase on the spot. :)

Re:Or... (1)

yttrstein (891553) | more than 6 years ago | (#24461411)

Whoops! Hot dump that is. Wow. That was a great slip. But just to be very specific here, I was of course not bashing MSSQL or MySQL, but rather putting them in their proper place. Neither one of them has anywhere near the tools that Oracle or DB2 (IBM's implementation) do, but each does definitely have their place in the continuum of databases.

Re:Or... (1)

mino (180832) | more than 6 years ago | (#24461467)

Just out of curiosity, what features/tools do Oracle and DB2 have that MS SQL doesn't have, which keep it out of that 'level' in your opinion?

I'm not disagreeing, just geniunely curious.

Re:Or... (1)

GoofyBoy (44399) | more than 6 years ago | (#24461959)

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 having a generic dbo, but then each user has its own schema?

Re:Or... (1)

mino (180832) | more than 6 years ago | (#24462037)

Thanks GoofyBoy, interesting.

I must admit I don't know that much about RAC -- but it doesn't really seem to me THAT different from clustering a SQL Server. I don't see a heap of difference between and Oracle Clusterware infrastructure and an active-active Windows Cluster Service (or whatever the hell they're calling it now, the damn thing changes names every 38 seconds) setup running SQL Server Enterprise (ditto).

Fair enough on the other points -- they're not really things I'd consider as stopping it being an 'enterprise database' but that may be just because I've never hit those problems myself -- never done geospatial work, and running it on Windows-only has never REALLY been a problem wherever I've worked, but obviously YMMV. I don't know about the filesize and RAM thing though -- pick your tools for the job, I guess. Don't run SQL Server on 32-bit Windows Server on a FAT filesystem, any more than you would try and run a huge Oracle install on 32-bit Linux with (some crappy Linux filesystem).

And as for the user/schema thing, yeah that's weird. But it's gone away in 2005. :)

How do these stories get picked? (0, Troll)

Hackerlish (1308763) | more than 6 years ago | (#24461129)

Poorly Written Summary. Something not many people many if any will care about. Yesterday we the 'Language Translation Error' One-liner and now this? How did this story get picked?

Re:How do these stories get picked? (3, Insightful)

larry bagina (561269) | more than 6 years ago | (#24461177)

Posted by kdawson

When did infinite loop become an "invention" (0)

bsharma (577257) | more than 6 years ago | (#24461141)

They have been there from the days of "Halting problem" (of Turing machines - before electronic computers were invented)

So, an alpha project for what exactly? (5, Interesting)

Mycroft_514 (701676) | more than 6 years ago | (#24461231)

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:So, an alpha project for what exactly? (2, Informative)

Hackerlish (1308763) | more than 6 years ago | (#24461237)

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:So, an alpha project for what exactly? (1)

Mycroft_514 (701676) | more than 6 years ago | (#24461273)

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:So, an alpha project for what exactly? (1)

blantonl (784786) | more than 6 years ago | (#24461333)

Look to my credentials, only been a DBA for 20+ years!

Yes, but a DB2 developer.

And remember, you don't look "to" credentials, you look "at" them.

Re:So, an alpha project for what exactly? (1)

Matchstick (94940) | more than 6 years ago | (#24462643)

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.
                                                    Gentile or Jew
O you who turn the wheel and look to windward,
Consider Phlebas, who was once handsome and tall as you.

i smell a cheap trick... (1)

JonnyChaos (1308859) | more than 6 years ago | (#24461233)

posted by the admin of the project? the spam tag is accurate... "yes this is an open source clearing house, no we will not all rapidly sign up to your cute little project." though, i would be willing to be this is a Masters Thesis project and alxtoth is hoping to get some fast-tracking going on...

ATTENTION SHOPPERS! (-1, Troll)

Anonymous Coward | more than 6 years ago | (#24461243)

ATTENTION SHOPPERS: PAY NO ATTENTION TO THE NECROTIC DOG PENIS. I REPEAT, PAY NO ATTENTION TO THE NECROTIC DOG PENIS CURRENTLY LOOMING OUTSIDE LOT 4. CONTINUE SHOPPING BUT PLEASE ENSURE YOU LEAVE VIA AN ALTERNATIVE EXIT AS WE ARE NO LONGER ABLE TO GUARANTEE YOUR SAFETY IN LOT 4, DUE TO THE NECROTIC DOG PENIS. FOR YOUR INFORMATION, LOTS 1, 2, 3, 5 AND 6 ARE CURRENTLY FREE OF BAYING NECROTIC DOG PENIS. PAY NO ATTENTION TO THE NECROTIC DOG PENIS. THANK YOU.

Lameness filter encountered. Post aborted! Lameness filter encountered. Post aborted! Lameness filter encountered. Post aborted! Lameness filter encountered. Post aborted! Lameness filter encountered. Post aborted! Lameness filter encountered. Post aborted! Lameness filter = censorship, lameness filter = censorship, lameness filter = censorship, lameness filter = censorship, lameness filter = censorship, lameness filter = censorship.

Your comment violated the "postercomment" compression filter. Try less whitespace and/or less repetition.

your programmers shouldn't be writing SQL (1)

timmarhy (659436) | more than 6 years ago | (#24461247)

all your apps should only be able to access the DB as unprivileged users with resource limits to prevent crashing, and they should only be able to run stored functions which someone qualified at sql creates for the application guys.

this way the programmers are prevented from infecting the database from their crapness

Re:your programmers shouldn't be writing SQL (2, Insightful)

lastchance_000 (847415) | more than 6 years ago | (#24461295)

Quis custodiet ipsos custodes?

Re:your programmers shouldn't be writing SQL (1)

KeithJM (1024071) | more than 6 years ago | (#24461299)

For things like reports, your developers have to write complex SQL. You can argue that it shouldn't be a developer, instead it should be done by a "development DBA" or whatever, but essentially whoever writes the SQL IS the developer for reports. Even experienced DBAs can leave out a join in a complex (10 or more table) query, and it often isn't found if it's only run against a development and/or QA database with limited data and no real load. Cartesian products should be found if anyone actually reads the output in QA, but that doesn't always seem to be the case.

Re:your programmers shouldn't be writing SQL (1)

timmarhy (659436) | more than 6 years ago | (#24461571)

no. have your DBA/database guy write a function to return the datasets you need and use THAT to make the report. I understand some shops can't afford to have both and in that case you must work with what you got, but SQL in the code is bad bad bad.

this is one reason i've always been critical of OSS and it's lack of a real report developing environment like reporting services.

Re:your programmers shouldn't be writing SQL (1)

cduffy (652) | more than 6 years ago | (#24461841)

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 The Real World.

Isolate your queries into a specific set of packages and require DBA review before changes to those packages can be promoted to the production branch; there are plenty of ways to keep developers from messing up production by writing bad queries less heavy-handed than the strict access-control approach given, and you should have a revision control infrastructure enforcing code review anyhow.

Re "lack of a real report developing environment" -- DataVision [sf.net] ? Agata Report [agata.org.br] ?

Re:your programmers shouldn't be writing SQL (1)

timmarhy (659436) | more than 6 years ago | (#24461969)

Oh i'm well aware it isn't popular in the real world, because it usually cuts developers grass.

it's better in the following ways:

1. more readable code, there is less of it

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

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.

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

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

I've had people fight me tooth and nail on this before but they always end up coming around. a lot of the time it's also about job security - people worry taking a task off their hands threatens their jobs.

Re:your programmers shouldn't be writing SQL (2, Interesting)

cduffy (652) | more than 6 years ago | (#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.

Re:your programmers shouldn't be writing SQL (1)

timmarhy (659436) | more than 6 years ago | (#24463007)

Ok your argument makes sense that the DB could change something and it'd be invisible to the programmers, and impossible for them to work around but that comes down to common communication, and if things were that bad this would be the LEAST of your worries ;)

i'm certainly not advocating patch culture either, but it's invenitable that you'll need to. in the case of most places i've worked you have a centralized database which is far easier to update than all the clients

Re:your programmers shouldn't be writing SQL (1)

TheRealMindChild (743925) | more than 6 years ago | (#24461431)

Sure, things that actually use the database for production shouldn't be trying to do dirty things with it, but developers, whether dedicated "DBA's", or the poor shop with only one tech guy of any kind, need to be able to "play" with the database to be able to tweak it and ... well... do anything of meaning other than retrieve data. Sometimes this can be dangerous, but this is why they are testing on a development server... right?

Re:your programmers shouldn't be writing SQL (2, Interesting)

Samah (729132) | more than 6 years ago | (#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.

EXPLAIN (5, Insightful)

Craig Ringer (302899) | more than 6 years ago | (#24461257)

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. (1)

Animats (122034) | more than 6 years ago | (#24461417)

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!!! (1)

lucm (889690) | more than 6 years ago | (#24462253)

> 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 ORDER BY, a final operation that has little weight in the overall plan (unless there is a huge amount of fields, but even then...)

For long queries with complex joins (like recursion), a diagram tool for SELECT can be very helpful, because it can help you validate that you actually get what you ask for. I would see this product as a logical optimization tool, not a physical one.

Re:Mod parents down!!! (2, Informative)

Craig Ringer (302899) | more than 6 years ago | (#24463279)

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 all, but rather any non-DDL query.

Re:EXPLAIN (0)

Anonymous Coward | more than 6 years ago | (#24461535)

Yeah this story seems to be ignorant of a simple explain plan which is available in every major rdbms that I'm aware of. It will show you cartesian joins, nested loops, use of indexes and partitions, etc. There are some differences with regards to which execution path will be taken (like cost or rule based execution decisions, hints, etc), but the analyst should be aware of this and use the explain plan to tweak their queries for optimal results. Tools like SQL Analyzer or TOAD display the explain plans in a reasonably user friendly and convenient manner, or a script can be built to dump it into a nested or execution ordered text file for analysis.

If the rdbms doesn't have some sort of explain tool you're in deep trouble anyway. Particularly it isn't always clear which index will be used if you specify in your join or where clauses multiple indexed fields without looking at the explain.

This tool just looks like it produces pretty pictures.

Re:EXPLAIN (2, Informative)

Craig Ringer (302899) | more than 6 years ago | (#24461595)

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 query, something like this begins to look faintly useful. Personally, though, I can't imagine voluntarily using such a database.

Re:EXPLAIN (1)

daveime (1253762) | more than 6 years ago | (#24464437)

AFAIK, THe MySQL doesn't actually select the rows, but does determine which, if any, of the available indices it WOULD use if the query was run, using the cardinality stats stored in the indices themselves.

So you can see from the EXPLAIN that he had indices "a","b", and "c" available, and chose "a" as it would probably return the fewest rows if the query was run.

Sometimes it can cause problems, as his optimizer tends to always choose the index that returns the fewest rows, but experience has shown me that sometimes this doesn't always equate to the fastest response time.

Lest we forget that if an index is not stored in memory, he's still going to have the I/O overhead of loading the MYI (index file) from disk anyway.

Is this (1)

jchawk (127686) | more than 6 years ago | (#24461367)

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:Is this (1)

cduffy (652) | more than 6 years ago | (#24461745)

I'm not flaming, I just thought it was interesting as I loved programming in python when in college!

"When in college"? Why not now?

Python jobs aren't hard to find.

In tablespace, no one can hear you scream... (3, Funny)

fahrbot-bot (874524) | more than 6 years ago | (#24461401)

If you sit close to the DBAs, you can hear them screaming...

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. :-)

Re:In tablespace, no one can hear you scream... (0)

Anonymous Coward | more than 6 years ago | (#24464001)

Windoze? LOL, good one!

Are you serious? (2, Informative)

SpasticWeasel (897004) | more than 6 years ago | (#24461437)

So SQL Server has had a graphical execution plan view for ever, and it's better than this lameness. But of course its not free, and we all know that free software is better, even when it sucks. Seriously, compare this to the real tools included with a serious RDBMS, and I have to question why this was even posted. It's almost farcical.

Re:Are you serious? (1)

Shados (741919) | more than 6 years ago | (#24461489)

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:Are you serious? (0)

Anonymous Coward | more than 6 years ago | (#24461823)

Not exactly free as in beer when you have to buy an expensive operating system to run it and pay for most uses of it.

Re:Are you serious? (2, Insightful)

weicco (645927) | more than 6 years ago | (#24463027)

Oh damn. I had to buy a whole PC set to run Debian. I guess Linux isn't free after all.

SQL injection detection (1)

rgovostes (814720) | more than 6 years ago | (#24461529)

I've thought about such a tool for detecting SQL injection. Essentially, you have a whitelist of SELECT statement "diagrams" stored somewhere. Before running a query, you generate a diagram of the current statement and check it against the diagram.

Of course a better investment would be to write your code the right way first...

Re:SQL injection detection (1)

Shados (741919) | more than 6 years ago | (#24461803)

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...

I'm screaming from the summary. (2, Funny)

sootman (158191) | more than 6 years ago | (#24461555)

Can we have that in English please? Possibly with a diagram?

Re: Bring a database down? (2, Insightful)

scdeimos (632778) | more than 6 years ago | (#24462819)

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: Bring a database down? (1)

freedom_india (780002) | more than 6 years ago | (#24462987)

...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.

What crap (0)

Anonymous Coward | more than 6 years ago | (#24463029)

Nobody with any sense is going to use this because it's under that fucking evil BSD license, right Linuxistas?

Bite me.

ANSI Joins (1)

8bit (127134) | more than 6 years ago | (#24464227)

Just use ANSI style joins. You're guaranteed never to accidentally do a cartesian join.

For example:
Normal join - "SELECT * FROM table1 t1 JOIN table2 t2 ON (t1.column = t2.column)"
Cartesian join - "SELECT * FROM table1 t1 CROSS JOIN table2 t2"

Big difference, no mistakes no matter how many tables/columns you're pulling in to your query.

Wanna slashvertise your v0.01 software? (0)

Anonymous Coward | more than 6 years ago | (#24464463)

Build a website for it. (Don't have "Homepage" link to an empty directory.) Take screenshots in a decent resolution. Offer an open forum.

Load More Comments
Slashdot Login

Need an Account?

Forgot your password?