Is it possible to maintain a 43 page query?

Question

I always thought an SQL compiler would break but apparently nesing can nearly be infinite. Is this code to be trashed immediately or is there some glimmer of hope that something like this can function?

This query doesn't really belong to me so I cannot post it... However let's just pretend it is this one:

[SELECT /*+ NOPARALLEL bypass_recursive_check */ 
SP_ALIAS_190, 
((CASE SP_ALIAS_191
WHEN 1
THEN 'PROVIDER::ALL_PROV::'
WHEN 0]

Answer 1

Clearly, you've never seen the SQL that comes out of the Sharepoint DAL.

Answer 2

I ran into a problem similar to this recently and I came to a decision by considering a couple of things:

• How long is this going to take to maintain vs. rewrite?
• How critical is this? There may be a lot of logic that may be difficult to unravel and the value in the fact that "it works" exceeds the value from an immediate rewrite.

And of course, there was the political decision management had to make concerning risking explaining why something that was recently created would have to be rewritten.

In the end (for me), find + replace was my friend.

Answer 3

Is it possible to maintain a 43 page function, say, in C#? The answer is obvious ;). I just cannot imagine this. If I were you I would break it into smaller parts.

Answer 4

This sounds like a candidate for the dailyWTF.

Answer 5

If the query is generated by a tool (or by code), then it may be relatively simple to maintain (in the sense that the query generation code may in fact be well written and maintainable)

Answer 6

Refactor it using the WITH statement. Add lots and lots and lots of comments.

If you break it into pieces that can be managed, you stand a much better chance.

Answer 7

If it contains allot of nesting I would say no.

Like any code no matter what language, you should only look at re-writting it because you can make it more efficient or easier to understand.

Based on my experiance I have been able to reduce badly written SQL 4 to 5 times its size and many times its performance because the origonal auther really had no idea.

Answer 8

If you think that's bad, you should see Industrial Logic's sample video on code smells: Technical Debt. Definitely not autogenerated.

Answer 9

Two things:

• Will only machines ever need to read this SQL?
• Are you stuck with the underlying schema?

If you have a 43 page query and you answered yes to the first two questions, welcome to SharePoint development