OMO: SQL query tools

[Heorot]

Keith_d, I apologise in advance if the following appears condescending, it's meant to be helpful.

if you want to write efficient SQL it is essential that you understand how relational databases work. A relational database contains many sets of related data that are linked by keys. In order to get the individual elements within the sets, you need to know which data elements are held in which sets. You should then look for the keys within those sets and use them to join them. As kaivalagi says, if you are going to use SQL more than occasionally, then you should go on a short course to learn the basics.

[kaivalagi]

Brettus, I'm more than happy to answer any questions you might have off the back of self learning, I'm sure a few others here would be too

Best not to use graphical tools for building queries unless you already understand the basics and can interpret what they are producing and tune as necessary

Like Heorot says if you can understand the basic premise of tables/views/sets of data and their keys/indexes/relationships you can create some half decent flat queries of the wanted columns from that data...

If you get into more complex stuff like aggregations within sets of data that then form new relationships to other data etc etc then you need to get to grips with more technical solutions that utilise CTE/temp tables/table variables etc or even step towards using SSIS for churning through big amounts of data and handling it's transformation as a pre-requisite for reporting data etc , all this needs a little experience really though...

Let us know if we can help! To start with have a look for "MS SQL Client Tools" and download and install the correct version on your desktop for your SQL server version

Cool! thanks for all the replies guys. Sorry for lack of reply, I've been reading every response (honest!)

I think my current problem is I'm not sure what my problem is. I like the visual design so I can quickly link the fields and it also helps me to lay it out (I resort to whiteboarding my queries first too) I'll see how I get on and when I've got an example of a problem (other than the clunky and CTRL-Z lacking query editor) I'll let you know.

[kaivalagi]

The visual design GUI for sql reporting will help you see the data and relationships but as you've seen it is limited. To me it sounds like you ought to use it to form a basic query and then play with it in the sql client tools until it runs right. In the query analyser there are options to see the actual execution plan etc for a query so you can see where a lot of time is being spend on operations, this can help you identity where indexes are lacking etc. There are a lot of other tools that can help too, worth getting familiar with the client tools and what they can help you with for sure!

Have a play and get back with any questions, it will be worth it to go down this road...getting a query wrong can cause table scans etc which have the knock on effect of blocking tasks which cause unwanted delays and or timeout exceptions within the database application etc

[Heorot]

Going back to keys. One thing to be aware of is that the key of a set (usually called a table) might comprise more than one element. To get a direct access, you will need to access the table using all the elements. That's why kaivalagi's suggestion that you look at the execution plan will save you hours of frustration. Some tools will identify where there is a non-indexed join.

[kaivalagi]

Originally Posted by Heorot

Some tools will identify where there is a non-indexed join.

Yep, in MSSQL that's normally going to result in a table scan in the execution plan taking up a high percentage of the overall execution, very easy to see.

There is also a tuning wizard that can be utilised to highlight indexing worth messing with...it's within the profiler tool and just requires logging of traced activity of the database first, e.g. http://www.datasprings.com/resources...ookieSupport=1

There's loads of good stuff to get your teeth into Brettus, a geeks dream come true lol!