Best tool for assisting writing complex SQL-queries?

views:

456

answers:

+3 Q:

Best tool for assisting writing complex SQL-queries?

Hello everyone,

I often find myself spending a lot of time figuring out why certain SUM()-aggregates sum up wrongly in SQL-queries. This problem often occurs if I do not take care when adding a JOIN, resulting in duplicate values being summed up etc. If I work with a big query with lots of JOINs, nested subqueries, GROUP BYs etc. things get very complicated to debug.

I would like to know if there exist a tool that will make it easier to construct and debug complicated queries, by graphically illustrating the data being summed up, joined together etc.

An example of what I'm looking for is illustrated in http://www.imada.sdu.dk/~sorenh07/misc/datacentric-querytool.png

It is of course just an early mockup, and I am interested in any comment related to this.

+2 A:

IMHO, the best "tool" for this is a textbook. This is one of those areas where the software can't do it for you. Sure, it can build simple selects & joins, but not aggregate functions or groupings, or sub-selects, etc. For that, you've got to learn to do it the old fashioned way -- by hand/wetware. I recommend: "Head First SQL" by Lynn Beighley, via O'Reilly Press

1) Get a book. 2) Install the database of your choice. 3) Import some test data. 4) Try to execute queries using as many functions as possible.

Practice, practice, practice.

lo_fye 2009-08-16 13:27:00

I certainly agree with what you are saying... I have a lot of experience with SQL and databases in general - both the syntax and the theory behind - all of it learned the old fashioned way :-).What I would like is a piece of software to unveil the preliminary datasets from which a result is built, to make it easier to see where values gets wrongly duplicated, filtered etc. That is, I would like a tool which can give a *data centric* view of the query (showing the actual data used) and not purely *schema centric* (just showing which tables are in use etc.).

Søren Haagerup 2009-08-17 16:29:39

I use Navicat to see the results of my queries. I have also used Eclipse's Database Explorer plugin.

lo_fye 2009-08-17 18:27:04

+1 A:

Every database comes with internal command to analyze complex sqls, explaining which join is not-efficient or taking more time to execute.

Ex: if you are using mysql then command is : explain

Niger 2009-08-16 13:41:51

For Oracle you can use the comman "Explain plan"

Niger 2009-08-16 13:42:56

+1 A:

TOAD is probably the best multi-database tool for this sort of thing, however I do not believe there is any substitute for thoroughly groking SQL yourself. If you have difficulty constructing a query then it's going to be well nigh impossible to debug it effectively.

Cruachan 2009-08-16 13:42:11

When my queries get too ugly, I find it helpful to use one of the many SQL beautifiers that are out there. These will not print graphics for you, but just proper indentation, syntax highlighting and brackets will help you out a good deal.

2009-08-16 13:52:30

Check out the new Visual SQL Builder of MyEclipse 7.5.

zvikico 2009-08-16 14:31:20

ansaurus

tags:

views:

answers:

Best tool for assisting writing complex SQL-queries?

related questions