SQL Design opinion

[Former Member]

I often code a multi-step "procedural" Stored Proc instead of a complex SQL statement. My reasoning is: when the SQL statement produces erroeous results (especially after it is in production!), the only way I know to debug it is to break the SQL into components and test one part at a time (and risk introducing another bug when reassembling the parts). On the other hand, it is fairly easy to add debug statements to a Stored Proc and/or see intermediate results in the debugger.

I understand the tradeoffs to be: - the complex SQL statement would no doubt be more efficient. - but when under pressure to solve a production problem, the Stored Proc and debugger are quick and easy.

I am just curious if others have an opinion or other tradeoffs to note?

Thanks

[VolkerBarth · Accepted solution]

Hm, it's difficult to comment on a rather vague design... - as you state you use a SP instead of (one) complex SQL statement, am I rigt to assume that you do use a temporary table to store intermediate results?

I do certainly agree that "divide and conquer" is a very helpful strategy to handle complexity (and I'm sure Breck will second, too:)).

IMHO it's often possible to use that within one complex SQL statement itself, too, say by using the WITH clause to build local views or by using derived tables to build intermediate results and join them finally...

I know that's a vague description, as well, but personally, I have learnt to build complex statements that way, and when doing so, I usually keep a SQL script with the "building blocks" in case I have to re-check the full statement. So that's another method to "debug the single steps"...

To get an idea, here's a sample from the forum to build an UPDATE statement (whether you call that complex or not is up to you, apparently):

http://sqlanywhere-forum.sap.com/questions/25886#25893

[VolkerBarth · Accepted solution]

Just to add: Funnily, I just read one of my own comments on that answer, and I guess that's the basic decision:)

I guess my answer is "because it think I understand that construction, [...]"

[Former Member · Accepted solution]

yes, it is a "philosophical" question. I have no details in mind, just every now and then I question myself on "why" I use certain techniques, and is it time to change.

I developed my first set of "techniques" from printing and reading Breck's 38 Performance Tips in 1998. Since then I keep adding to my toolbox. The good thing is: I can usually get something done well in a minimal amount of time. The bad thing is: easy to get in a rut and miss newer/better methods.

The example in the link you gave is "medium" complexity in my mind, I would probably use that. It also makes me realize: I use more complex "selects" because they are easy to debug in parts with the source data remaining the same. I turn to SPs more for "updates" because I can include checks for unexpected conditions at intermediate steps and abort out right there.

So: my conclusion is: I haven't missed any earthshaking ideas or methods that I should change to.

Thanks for th reply Volker.

[VolkerBarth · Accepted solution]

So: my conclusion is: I haven't missed any earthshaking ideas or methods that I should change to.

Hm, I'm not sure you should make such a bold decision based on just my opinion:)

But obviously it's worthwhile to re-check one's toolset now and then... I remember I had learnt (IIRC, from Breck's according list, too) that the following query was usually slow

SELECT ... FROM myTable where fk_Columnn1 = x or fk_Column2 = x

and would better be replaced by an UNION statement such as

SELECT ... FROM myTable where fk_Columnn1 = x
UNION ALL
SELECT ... FROM myTable where fk_Columnn2 = x

but with v11 the optimizer was enhanced to use multiple indexes in parallel, making such split queries unnecessary. Or think of v12's "IS NOT DISTINCT FROM" predicate that has helped me to get rid of not sargable "ISNULL(col1, '') = ISNULL(col2, '')" comparisons.

These are samples from SQL improvements that may help to simplify queries and thereby help to reduce complexity, and personally, also samples how it usually takes time until I notice (or even use) those instead of the older "learnt-by-heart" methods...

[VolkerBarth · Accepted solution]

In case anyone else likes to have a feeling of nostalgia:

This seems to be the cited "Tip 77: Sybase SQL Anywhere Performance Tips & Techniques".

See, in contrast to Ex-Sybase, Breck does not withdraw his older scrolls...:)

[justin_willey · Accepted solution]

"IS NOT DISTINCT FROM" is an interesting addition which I wasn't aware of. It is slightly different from the ISNULL comparison though:

if null is not distinct from '' then 'Same' else 'Different' endif	if isnull(null,'') = isnull('','') then 'Same' else 'Different' endif
Different	Same

[VolkerBarth · Accepted solution]

Justin, thanks for the pointer - yes, the drawback of trying to treat null as equal to null with ISNULL() is that you have to use a replacement value that is not used as ordinary value, so I usually have used "extraordinary values" then, such as the fictive

ISNULL(col1, 'YouWouldNotUseThatAsAnOrdinaryValueDoYou?') = ISNULL(col2, 'YouWouldNotUseThatAsAnOrdinaryValueDoYou?')

IS NOT DINSTINCT FROM helps to avoid such pitfalls, and as stated, it's sargable where ISNULL() is not. Such a pity that it is SQL standard and MS SQL Server 2014 does not yet have it...

[justin_willey · Accepted solution]

The whole thing is a pain. I know Breck would like to abolish NULL, my preference would be to abolish empty string - after all there is no such thing as an empty number or an empty date.

We try to eliminate it with better column definitions, constraints, defaults etc but with database going back 20 years plus it is always a bit of a challenge.

[Breck_Carter]

> multi-step "procedural" Stored Proc instead of a complex SQL statement

I am sure that does not mean "record-oriented fetch loop instead of set-oriented SQL statement", but in case it does, please reconsider.

If necessary (to preserve your sanity) try separate set-oriented SQL operations to divide-and-conquer a single complex operation. Sometimes that means using intermediate temporary tables, but not always... views are often a great help; e.g., FROM derived tables, local WITH SELECT view clauses, CREATE VIEW statements.

Sometimes people use fetch loops so they can COMMIT a long UPDATE or DELETE at intermediate points along the way; before doing that, consider an ordinary loop containing a repeated set-oriented UPDATE TOP START AT plus COMMIT. Fetch loops are fraught with inter-connection conflict issues that don't affect atomic UPDATE statements; if you need proof just read the rules for DECLARE CURSOR NO SCROLL - DYNAMIC SCROLL - SCROLL - INSENSITIVE - SENSITIVE (tip: anything other than INSENSITIVE is asking for trouble).

[Former Member]

OUCH. Your comments have revealed some problems with my cursor use: 1) I always declare NO SCROLL since the documentation says "it is the most efficient", INSENSITIVE is all I am looking for so I guess that is the keyword I should use, and 2) I realize I have missed FOR READ ONLY in quite a few.

I do use views. I do use temp tables; in more complex situations I declare them as regular tables and use CURRENT USER as part of the PK so I can later browse the data the user created.

It isn't that we face a huge number of bugs, but since we are outside consultants, it is often sometime before an issue reaches us and when it does I like to be able to nail it down quickly when the user has long forgotten what they were doing at the time.

I’ll get busy and fix all my DO-FOR cursors. Thanks!