Geek Answers Handbook

Best tool to help write complex SQL queries?

I often find that I spend a lot of time figuring out why certain SUM () aggregates are mistakenly summed in SQL queries. This problem often occurs if I don't care about adding a JOIN, which results in duplicate values ​​being summarized, etc. If I work with a large query with a large number of JOINs, nested subqueries, GROUP BY, etc., Everything is very difficult to debug.

I would like to know if there is a tool that will simplify the collection and debugging of complex queries, graphically illustrating the summarized data, combining them, etc.

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

This, of course, is only an early layout, and I'm interested in any comments related to this.

+4
source share
6 answers

IMHO, the best "tool" for this is the textbook. This is one of those areas where software cannot do this for you. Of course, it can create simple fittings and associations, but not create aggregate functions or groupings, or subsamples, etc. To do this, you need to learn the old - manually / wetware. I recommend: “Head First SQL” by Lynn Begley, through O'Reilly Press.

  • Get a book.
  • Install the database of your choice.
  • Import some test data.
  • Try to complete the queries using as many features as possible.

Practice, practice, practice.

+2
source

Each database contains an internal command for analyzing complex sqls, explaining which connection is inefficient or takes longer to complete.

Example: if you use mysql then the command: explain

+1
source

TOAD is probably the best multi-database tool for this kind of thing, however I don't believe there is any substitute for carefully pushing SQL yourself. If you are having difficulty building a query, then it is almost impossible to debug it efficiently.

+1
source

When my queries get too ugly, I find it helpful to use one of the many SQL beautifiers that are there. They will not print graphics for you, but only the right indentation, syntax highlighting and brackets will help you to a great extent.

0
source

Check out the new Visual SQL Designer MyEclipse 7.5 .

0
source

I am using a visual tool called Factory Code for MySQL. This is easy for MySQL, but also a great learning tool.

0
source

More articles:


All Articles