SQL Server TSQL Debugging - Massive View - Any Tips?

Question

Hi,

I've been asked to debug a view that's producing split rows where they should be grouping. In trying to debug, I'm trying testing all of the joined sub tables in isolation. The thing is, there's so many dependancies (11 joins in all), that once you get all of the dependencies required to run the query, you basically have the original monsterous query. Figuring out which part of the query is causing the problem is difficult. I don't really want to have to rewrite it!

This is really painful. Any tips from experienced DBA's / SQL programmers!?

Answer 1

Review the joins by commenting them out, one by one, to see if you can isolate to hopefully one join. Then review the data in that situation that is causing the issue to figure out how to deal with it.

Answer 2

Use some visual tools with big queries; surprisingly ms access is not bad for such tasks, you can

• have a visual representation of all joins and conditions
• abstract and isolate parts by saving and reusing ms access query objects easily and without modifying the schema

Answer 3

Couple tricks...

Finding duplicate keys... Any query can be turned into a subquery and selected from.

select idcolumns,count(1) from
(put your entire query here as a subquery, 90+ lines is fine)
group by idcolumns
having count(1) > 1

This one in particular will show if you have any duplicate rows for your 'idcolumns'. If you're having problems with 'split rows', changing around the 'idcolumns' will allow you to see where these records are being split. You can also include a where clause so you're only looking at a small sample set from your original query...it's easier to identify a single record and work with it through this process (as Brad stated in his comment).

If this is an error in a GUI somewhere...isolate and replicate the error first. This seems more like a view in your case so it might not be valid here, but if you can't isolate and repeat an error, you cannot troubleshoot it.

Refer to OMGPonies post...remove all the tables in the joins and slowly introduce tables one by one. With each join you put back in, run the count query above and watch for changes.

I'm a bit of a drawer...I do recommend finding a white board and drawing out each relation...don't go overboard, but the visual reference helps me alot. Colour code it so it's easy to view (I do tables in black, table name in red, primary key in blue, FK's in Green, and connect the tables with this weird purply coloured marker I stole...er...permanently borrowed...from my last job).

And lastly...front to end mapping. Alot of the time these old queries were written when business requirements were different. Over time with employee churn, the start and end goal gets lost along the way and the process becomes more important than the goal. Identification of what your users are using this for can be as important of step as actually fixing this view.