Debugging a loooong query

  • 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!

  • 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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • 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.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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.

  • 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

  • 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[/url] 😉

  • 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.

  • Many thanks to everyone for their informative suggestions, I learned a few things that I can always put to use!

  • Thanks for the http://poorsql.com tip, didn't know this existed and it works like a charm.

    Vera

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply