Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Debugging a loooong query
Add to briefcase

Debugging a loooong query Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
shahgols
Posted Wednesday, November 14, 2012 10:03 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 11:12 AM
Points: 578, Visits: 3,258
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
Sean Lange
Posted Wednesday, November 14, 2012 12:08 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 1:17 PM
Points: 8,641, Visits: 8,273
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
Post #1384802
Jeff Moden
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: Yesterday @ 4:51 PM
Points: 32,923, Visits: 26,811
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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1384938
gmrose
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: Monday, April 29, 2013 2:49 PM
Points: 25, Visits: 194
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
robert.gerald.taylor
Posted Wednesday, November 14, 2012 8:42 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 12:57 PM
Points: 740, Visits: 790
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
vinu512
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: Today @ 3:06 AM
Points: 1,051, Visits: 1,442
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
aaron.reese
Posted Thursday, November 15, 2012 4:13 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, May 13, 2013 7:33 AM
Points: 234, Visits: 441
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
shahgols
Posted Thursday, November 15, 2012 12:08 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 11:12 AM
Points: 578, Visits: 3,258
Many thanks to everyone for their informative suggestions, I learned a few things that I can always put to use!



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


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, May 10, 2013 5:25 AM
Points: 694, Visits: 277
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

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.