SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Debugging a loooong query


Debugging a loooong query

Author
Message
shahgols
shahgols
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2239 Visits: 5738
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!



Sean Lange
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62199 Visits: 17954
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.

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)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)

Group: General Forum Members
Points: 213739 Visits: 41977
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
gmrose
gmrose
SSC-Enthusiastic
SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)

Group: General Forum Members
Points: 122 Visits: 278
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.
Rob Taylor
Rob Taylor
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3812 Visits: 1616
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
vinu512
vinu512
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3639 Visits: 1626
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 ;-)
aaron.reese
aaron.reese
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2405 Visits: 907
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.
shahgols
shahgols
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2239 Visits: 5738
Many thanks to everyone for their informative suggestions, I learned a few things that I can always put to use!



Vera-428803
Vera-428803
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1251 Visits: 451
Thanks for the http://poorsql.com tip, didn't know this existed and it works like a charm.
Vera
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search