Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Debugging a loooong query Expand / Collapse
Author
Message
Posted Wednesday, November 14, 2012 10:03 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 2:45 PM
Points: 619, Visits: 4,128
Hi all,

I have the pleasure of inheriting a select statement that has multiple subqueries and unions in subqueries, and all sorts of logic to the tune of 400 lines. It is throwing a conversion error and I'm having a hard time pinpointing where it is coming from. How would you guys debug a single, huge select statement like this? I can't put breakpoints in various parts since it's just one select statement. There are no variables involved, so the Locals window is useless as well.

Also, I am not a developer or programmer, so I have limited exposure to the Debug functions in SQL Server. Which is why I need help from those that are pros at this. Thanks in advance!



Post #1384750
Posted Wednesday, November 14, 2012 12:08 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:18 AM
Points: 11,981, Visits: 11,003
shahgols (11/14/2012)
Hi all,

I have the pleasure of inheriting a select statement that has multiple subqueries and unions in subqueries, and all sorts of logic to the tune of 400 lines. It is throwing a conversion error and I'm having a hard time pinpointing where it is coming from. How would you guys debug a single, huge select statement like this? I can't put breakpoints in various parts since it's just one select statement. There are no variables involved, so the Locals window is useless as well.

Also, I am not a developer or programmer, so I have limited exposure to the Debug functions in SQL Server. Which is why I need help from those that are pros at this. Thanks in advance!


Start by figuring out what type of conversion error you are getting. The datatype mentioned in the error will give you a starting point. Then remove anything in the where clause. If the query runs at that point you at least know the issue is in the where clause. Look at the datatypes of the columns being joined and identify anywhere the underlying columns do not have matching datatypes. With the complete lack of details that is about the best advice I can offer.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1384802
Posted Wednesday, November 14, 2012 5:16 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:19 AM
Points: 35,955, Visits: 30,245
shahgols (11/14/2012)
Hi all,

I have the pleasure of inheriting a select statement that has multiple subqueries and unions in subqueries, and all sorts of logic to the tune of 400 lines. It is throwing a conversion error and I'm having a hard time pinpointing where it is coming from. How would you guys debug a single, huge select statement like this? I can't put breakpoints in various parts since it's just one select statement. There are no variables involved, so the Locals window is useless as well.

Also, I am not a developer or programmer, so I have limited exposure to the Debug functions in SQL Server. Which is why I need help from those that are pros at this. Thanks in advance!


WHAT is the actual error message? Also, was the code ever in service before?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1384938
Posted Wednesday, November 14, 2012 5:38 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 07, 2014 10:29 AM
Points: 25, Visits: 211
I often need to do the same task as you, working with someone else's SQL statement, usually to create something similar. I like to copy the original SQL statement into a New Query window and make updates to the copy. Since in this case there are many unions and sub-queries, I would split each section of the statement at the union or sub-query clause and verify that each part worked correctly on its own. I sometimes include PRINT statements between the parts. If one of the statements fails, at least some of the PRINT statements and SELECT statements would show their output before the failure. Then I could narrow my checking to the first statements below the working parts.
Post #1384942
Posted Wednesday, November 14, 2012 8:42 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 03, 2014 6:23 AM
Points: 1,135, Visits: 1,149
I often find inherited SQL to be poorly formatted. Just getting things to line up helps me wrap my mind around what's going on.

My first step is to copy & paste the code into http://poorsql.com and take the formatted SQL into a new window. You can set the formatting options to match your standards/preferences.

Rob
Post #1384962
Posted Wednesday, November 14, 2012 11:54 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 2:40 AM
Points: 1,118, Visits: 1,573
Since, its a conversion error.....I would look at the error to know the datatypes in the error message. Then I would look at comparisons(equal to, greater than, lesser than etc.) in the Query - comparisons between two columns of tables, comparisons between a column and a temporary variable, comparison between two temporary variables etc.

Since it is a 400 line code....I would always keep in mind the datatypes shown in the error message so that I am only debugging that part of the query which uses the datatypes shown in the error message.


Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
Post #1384985
Posted Thursday, November 15, 2012 4:13 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, April 10, 2014 3:49 PM
Points: 366, Visits: 577
Whilst we are on the subject, I am not a big fan of sub-selects and co-related sub queries, even though the logic that is implemented is vital in many query situations

If you are using SQL2005 or above, you can (almost always) rewrite these as Common Table Expresssions which has the benefit that each can be tested independantly and keeps the main query clean as the CTEs are simply referenced like normal permanent or temp tables and unsually there is no change in the query plan.

Post #1385077
Posted Thursday, November 15, 2012 12:08 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 2:45 PM
Points: 619, Visits: 4,128
Many thanks to everyone for their informative suggestions, I learned a few things that I can always put to use!



Post #1385298
Posted Friday, November 16, 2012 2:55 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 1:42 AM
Points: 869, Visits: 349
Thanks for the http://poorsql.com tip, didn't know this existed and it works like a charm.
Vera
Post #1385567
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse