Want to Challenge Yourself to Better Troubleshooting?

[Isaac]

Although we all embrace the necessity of the Q&A method to resolving problems on our forum, it's an excellent practice to allow ourselves to be challenged in how to troubleshoot problems on our own. This article (and a few others like it) should be a sticky on AWF! It separates the mediocre from the excellent, although of course most of us dip our toes into both categories depending on the day!

This challenged me quite a bit - and also made me drool over the tools that other platforms have, like Debug.Assert

Anyway, enjoy!

How to debug small programs

(Este artigo está disponível em português.) One of the most frequent categories of bad questions I see on StackOverflow is: I wrote this program for my assignment and it doesn’t work. [20 lin…

ericlippert.com

This is my favorite quote if I had to pick one:

If your program still has a bug, obtain a rubber duck. Or if a rubber duck is unavailable, get another computer science undergraduate, it’s much the same. Explain to the duck using simple words why each line of each method in your program is obviously correct. At some point you will be unable to do so, either because you don’t understand the method you wrote, or because it’s wrong, or both. Concentrate your efforts on that method; that’s probably where the bug is. Seriously, rubber duck debugging works. And as legendary programmer Raymond Chen points out in a comment below, if you can’t explain to the duck why you’re executing a particular statement, maybe that’s because you started programming before you had a plan of attack.

Once your program compiles cleanly and the duck doesn’t raise any major objections, if there’s still a bug then see if you can break your code up into smaller methods, each of which does exactly one logical operation. A common error amongst all programmers, not just beginners, is to make methods that try to do multiple things and do them poorly. Smaller methods are easier to understand and therefore easier for both you and the duck to see the bugs

 

[Galaxiom]

I agree with the idea of explaining the problem to something inanimate. Just formulating a question to ask here has often resolved the errors for me in the past.

 

[Galaxiom]

One of the most important traits I built as a developer is the ability to exactly read every character of what is actually on the screen rather than the words I assumed I had typed.

As a side benefit, I have become a very skilled proof reader of the policies and procedure documents in our company too. I have a special attention for punctuation, grammatical syntax and consistent formatting, which of course are vital when writing code.

 

[Isaac]

The "experts" on Stack Overflow are much less tolerant of beginners than those at AWS but they're pretty funny. I have a squeezy thing that is shaped like a dinosaur that I sometimes talk to

That so-called intolerance has taught me many a lesson that I needed (but did not want) to learn.

But in Python, I will confess I am crying baby tears over my frustrations with only half-hearted attempts to solve them like a big boy.
I think the reason, deep down, is because I'm not really passionate about learning Python. It strikes me as a quick-make-a-script-and-run platform (maybe not as to the platform itself, but as to most of its subscribers), which requires endless installs of optional libraries (libraries that the really smart programmers have made, which the amateurs are using), that is not well suited for my strict corporate environment to allow. I have been "nudged" by a co-worker (many years my Senior, so I defer to them a lot) to learn it, but am struggling to maintain interest.

My actual dream is to learn c#.net. The difficulty is that I am just experienced enough in programming to have a sense, and a respect, for just how big and deep of a journey that will be, and, (like my occasional musing about the possibility of going back to law school), I am just not sure I have the stamina or determination for it. Thus I have settled for my co-worker's dream of using Python. Honestly, I would rather even learn Powershell. Powershell also seems to me like a quick-make-a-script-and-run environment, but at least I feel it would be more useful to my life and in doing so I could finally put VBScript to rest (respectfully, as I have thoroughly enjoyed using it but at some point dinosaurs need to be limited to stuffed animals!)

 

[Galaxiom]

I have been "nudged" by a co-worker (many years my Senior, so I defer to them a lot) to learn it, but am struggling to maintain interest.

It is very hard to maintain interest in something if you don't have an immediate need for it. Every newcomer is learning Python now. Better to build on something you are already skilled in where there are less players.

Have you looked into CLR functions for SQL server? They take dlls and make their methods accessible to T-SQL much like VBA API calls can make them available to Access SQL.

I wrote one for Damerau-Levenshtein Distance and it vastly outperformed even the TSQL equivalent. (Running in 60% of the time required for the SQL version and on another planet compared to the VBA version it was adapted from.)

It isn't a big step from VBA to creating a dll using VB.NET in Visual Studio. You can paste VBA code straight in. VS will adjust some of it automatically (dropping the SET keyword since all variables are objects) and ninety percent of it would work straight up. VS will tell you what else you need to fix, often pretty much what to write instead. Understanding the structure of the project is a little jump but not too high. It is just about needing a wrapper to call the guts of the code. (BTW You can use the dlls you write in Access too if you want.)

That will introduce you to working in the environment. Then you can then move to writing the dlls in C# if you want to go in that direction. Once you have grasped what it means to write in a true object oriented language, using another OO language is really just about the syntax.

After that you could take on UI design if you want. My preference was to dabble in ASP and make a couple of simple web applications to work with some my databases because that is the environment where I work.

I have not bothered with C#. However my curiosity was piqued when I recently discovered that Universe (a PICK based database operating system we also use) can integrate C# assemblies for much the same purpose as CLR in SQL Server.

 

[Isaac]

It is very hard to maintain interest in something if you don't have an immediate need for it. Every newcomer is learning Python now. Better to build on something you are already skilled in where there are less players.

Have you looked into CLR functions for SQL server? They take dlls and make their methods accessible to T-SQL much like VBA API calls can make them available to Access SQL.

I wrote one for Damerau-Levenshtein Distance and it vastly outperformed even the TSQL equivalent. (Running in 60% of the time required for the SQL version and on another planet compared to the VBA version it was adapted from.)

It isn't a big step from VBA to creating a dll using VB.NET in Visual Studio. You can paste VBA code straight in. VS will adjust some of it automatically (dropping the SET keyword since all variables are objects) and ninety percent of it would work straight up. VS will tell you what else you need to fix, often pretty much what to write instead. Understanding the structure of the project is a little jump but not too high. It is just about needing a wrapper to call the guts of the code. (BTW You can use the dlls you write in Access too if you want.)

That will introduce you to working in the environment. Then you can then move to writing the dlls in C# if you want to go in that direction. Once you have grasped what it means to write in a true object oriented language, using another OO language is really just about the syntax.

After that you could take on UI design if you want. My preference was to dabble in ASP and make a couple of simple web applications to work with some my databases because that is the environment where I work.

I have not bothered with C#. However my curiosity was piqued when I recently discovered that Universe (a PICK based database operating system we also use) can integrate C# assemblies for much the same purpose as CLR in SQL Server.

Yes I've been fascinated with CLR's conceptually and may indeed try - not sure if the permissions setup of my current job will allow it but it does seem like a nice way to bring together a little .net coding with SQL server.

Yes @Pat Hartman Access is great but (in my personal opinion) it is only "great" as a front end tool. For me personally it makes no sense to have a career in database development without an industry-grade RDBM like SQL Server, but maybe that's just me. It becomes clearer in hindsight. Then of course there is the career aspect, the overall career potential of SQL Server vs. Access is 99 to 1 in both volume and level, but they can work together very nice too.

The VBA discussion is different, I actually do like VBA.
Actually the least fighting involves Access with SQL Server, IMO.

The Python discussion (which I started a separate thread for in the other software) has nothing to do with Access; I actually don't use Access in my current job any more. It's more about personal enjoyment, I'm just not enjoying learning Python. I would like to know more .Net (vb.net I have some experience in, but c# is preferred by a majority of US employers so I would rather go there), but really making serious .net applications is a major step up and would require a lot of practice so I'm cautiously considering starting out. Galaxiom's CLR suggestion might be a perfect way to dip my toes in.

 

[Isaac]

Then in that case we ought to be seeing more eye to eye, if you already know that you need a decent back end to work with Access.
You seem to be resistant to taking advantage of the back end you now acknowledge is an excellent option.

This debate never had anything to do with "SQL Server vs. VBA" or "SQL Server vs. Access as a front end", why would it?
Sql Server is not a front end!

You seem to be taking this as if we are pitting SQL Server against VBA or SQL Server against another front end, which doesn't make sense to me, as SQL Server isn't a FE to begin with.
No, it's more like SQL Server against nothing else at all that fits the bill.
And of course, I agree with your perhaps a bit over-stated "Access is only a FE tool", although as you well know, many people do still use it to hold data, but I appreciate and agree with whatever hyperbole it takes to convince people to strive for a more robust BE.

Well, almost. The only debate that's left is when to use Access queries vs. T-SQL of some kind or another. And perhaps we diverge a bit there. And there are some more things too, like taking advantage of more server side constraints, etc., but I've no interest in getting into that debate, as I myself enjoy doing a fair amount of FE Form-level validation as I know you do too.

I think we've debated ourselves into finding out we don't disagree on much after all. Only I am a bit more enthusiastic about taking advantage of SQL Server for querying, perhaps.

 

[Isaac]

Know your tools. Use the best tool for the job.

Well, we agree on that, anyway. Of course the latter is easier when combined with the former.

I don't like SQL Server because it is shiny nor new (it's not, really), I like it for the same reason the rest of planet earth does, but if you don't use it much, you simply don't know what you're missing and no amount of rationalizations will change that.

I understand that you are focused on the front end and there is nothing wrong with that. In my life it has made more sense to take a holistic approach, but maybe it is not for everyone.

Cheers!

 

[Galaxiom]

The difference in our opinions derives from your liking of bright shiny things and my desire to bring value to my clients.

Your dismissive arrogance is reaching new levels. You seem fixated on justifying your obviously limited knowledge of SQL Server. Are you really that insecure about it that you need to belittle others? What you are doing is systematically destroying the respect than many of us have always held for you. Please don't continue down the path you appear to be on and turn into a cranky old woman.

I understand that Access does a great job in the market sector where you have targeted your business and you are very good at it. However there are many other sites where SQL Server (or other DBMS) is vital. There are also many places where Access combined with an SQL Server back end is part of the mix. Some of us work in those environments and deserve better than being told our opinions are based on "liking bright shiny things".

 

[Galaxiom]

You are of course entitled to your opinion.

I use SQL Server, Oracle or DB2 for the majority of the apps I create and have for 40 years. But in all cases, Access is the overriding software. Think about our audience here. How many do you think are capable of actually creating unbound forms or a stored procedure? Many can't write VBA or create a query without the QBE and you're telling them that they must, in order to create "good" applications get into the details with SQL Server. Maybe it is you who is just showing off. An Access application linked to SQL Server does NOT necessarily need to go any deeper. If you find that you can't use Access with ODBC linked tables and bound forms, you are using the wrong platform and should "upgrade".

Maybe it's the environments I work in. I recently had one client convert from PostgreSQL to Oracle and you know what? I relinked the tables. Period. THAT is the power of Access but only if you don't weld it to the BE. That flexibility is the heart of Access but you are telling people that is bad and inferior. That is what I object to, not to writing sp's or pass through queries to do the occasional complex report or search form. Let Access be Access and you do the things that Access cannot do. When you insist on doing the things that Access does because you think you can do it better, that is hubris. Was that too arrogant?

My opinion is objectively substantiated. Let me remind you of what you said.

The difference in our opinions derives from your liking of bright shiny things and my desire to bring value to my clients.

On another thread you claimed that "T-SQL was a very limited language" which clearly demonstrates you have VERY limited abilities in its use. You obviously don't realise that your knowledge of the platform is extremely superficial despite admitting you are "not a dba".

I am well aware of the audience here. Some are very skilled, experienced and interested in expanding their database skills beyond Access. It is actually an intelligent career move for a young database developer.

At no time did I say that anyone needed to get into SQL Server to create good applications. I do use Access/VBA to its full capabilities but I don't bother writing a ton of code to replicate what SQL Server can provide in a few lines.

Let me know when you get Access to run a 1500 line query that pulls data from multiple databases including a relatively obscure one like Universe or read directly from Active Directory. Let me know when Access can save a complex query not supported in the VBE designer without completely scrambling the readability. Or join tables on arbitrarily complex expressions. Or efficiently concatenate related records. Or use ROW_NUMBER and PARTITION BY to return the top results from multiple groups at once in a single query. Or manage fine grained column level security or work with Roles and Schemas. Or back itself up while in use. Or roll back updates to arbitrary points. Or send emails. The list goes on and on.

You know even less of the environment I work in than you do about SQL Server, so your suggestion that I need to work in another platform is ridiculous. In fact most of my simpler reporting apps use an Excel front end. These highly sophisticated templates accept user settings, read the requested data and then save themselves with automatically generated file names. I did it that way because Excel has excellent presentation capabilities and that is what my customers wanted.

I also do have highly functional Access applications that simply use ODBC linked tables and views. Oh wait, I used a view, so I must be showing off I guess.

 

[Mike Krailo]

I would like to see that 1500 line query just out of curiosity and why that is necessary. Is it mainly due to a great number of fields or what is reason for such a large query?

 

[Isaac]

At my new job which is all SQL and no Access, I have done about 85 reports so far. Most of those queries are between 200 and 500 lines, but a few have been in the 1000-2000 range. Most of it is because I chunk it out into readable portions that execute business logic - and are decent for performance because of the use of temp tables to reduce data. I work with health care data and all report requests have inclusion criteria, exclusion criteria, date ranges and final list of data elements/columns. The first few chunks might be 'reference' tables - like a list of Drugs, Microbiology procedures, Units, Drug Classes, etc. (I can join to them later on their Keys, whose values are stored as a foreign key in the fact tables).
After that there might be a patient population. After that the sky's the limit - the report requestors ask some really weird things, like: "what was the highest lab result value for lab test 12345 that was taken in between the time the patient was in their first ICU unit and the time they got their last dose of insulin". Sometimes these can be handled using outer apply's and rownumbers() in the final select, but often it's just easier to chunk them out into temp tables of different types, like #AllICUVisits, etc. Then in the final select I can left join to all those little 'chunks' / tables.

Also many times I have a select statement that's already busy enough with joins here and there, and it is easier to enforce an Exclusion rule by waiting until the select statement is done, then running a Delete on it ... rather than working that into the joins.

Thus the "query" is a lot of steps rather than one giant select. Whether or not I encapsulate it in a procedure, which I'm currently only doing when the web developers want to incorporate it into the front end. Which is very rarely, as I'm considered quite the Junior developer here and am learning from SQL developers who are my betters.

Note: most of my previous comments about SQL server usage, which heavily recommended encapsulating most queries in stored procedures, were in the context of holistic development involving front end apps + sql queries, whereas this post is just referring to my current full time job which is mostly ad-hoc report requests.

Galaxiom said it much more succinct than I am able to: SQL Server is a very intelligent move for a database developer of any stripe....it just is.
The market for small biz Access projects is....Legit, but limited. The market for SQL Server developers is enormous, and well paid. It's just something I like to encourage newcomers to start thinking about - it's usually not too early.

 

[Galaxiom]

I would like to see that 1500 line query just out of curiosity and why that is necessary. Is it mainly due to a great number of fields or what is reason for such a large query?

That particular query starts by writing a lot of temp tables using OPENQUERY on a Linked database connected to a Universe database via ODBC. By design, the Universe ODBC interface is sequential so multiple simultaneous connections are not possible. Performance with more than a couple of connections at the same time is very poor without the temp tables.

Then it runs a number of Common Table Expressions (CTEs). These are basically the same as a Temp table but held in memory rather than being written to disk.

The main query itself selects 56 columns, many with CASE statements which i always spread across multiple lines, and there are dozens of joins and several levels of subqueries.

With such huge nested queries, it is vital to format them well or they quickly become impossible to work with. I use one line per column in selects, multiple lines to declare joins and white space between the keyword phrases to enhance readability. So that contributes to the number of lines,

The ability of T-SQL to include both inline and block comments in the code is really important in a query that is so complex so some lines are also taken up with comments.

 

[Galaxiom]

BTW T-SQL queries and Stored Procedures incorporate virtually all of the functionality that Access developers use in VBA. They have variables, code loops, can work with the equivalent of Recordsets (known as 'cursors' in T-SQL) and build dynamic SQL commands to be executed. Consequently a T-SQL query can sometimes look rather more like a VBA procedure than what an Access developer usually thinks of as SQL.

This is why I take exception to comments such as in a recent post where Pat Hartman claimed that T-SQL was "a very limited language". Anyone who has spent years working with T-SQL should know that is complete and utter nonsense.

Being able to write both the code and the actual query in the same incredibly powerful editor is a major advantage. SQL Server Management Studio is based on Visual Studio so its Intellisense and syntax checking facilities are extraordinary. It also has inline highlighting of syntax errors, and words that are searched in the Find box. Lines with recently saved and as yet unsaved changes are also highlighted in the scroll bar making it very easy to navigate through the query sections being edited. Sections of the query can be closed down to a single line to get it out of the way when working on other sections.

MSSQLSMS does have a query designer too (itself quite superior to that provided in Access) but the T-SQL editor is so good that anyone reasonably fluent in SQL would prefer the editor over the designer. And it doesn't screw up the formatting of the query like Access does.

Access is great for simple databases but the vast majority of developers looking forward to a long career in databases should be exploring their way into SQL Server or another DBMS. The scope for available work is much greater with SQL skills. Pat has made herself a very good niche by focusing on Access but there are considerably more opportunities for developers who have added more advanced SQL skills to their repertoire.

 

[Mike Krailo]

@Galaxiom Thanks for the detailed explanation. I've always thought creating large complex queries was not as good as multiple smaller queries run in sequence but maybe that's not so in SQL server land.

 

[Galaxiom]

@Galaxiom Thanks for the detailed explanation. I've always thought creating large complex queries was not as good as multiple smaller queries run in sequence but maybe that's not so in SQL server land.

Yes not really the case in T-SQL. I have written queries that appeared to choke the server but they always turned out to have errors in the joins that ended up returned many tens of millions of rows. For reasons that don't make sense to me, it is possible to join two tables with a condition that involves columns in a completely different table. Access won't allow this. (It is easy to do when copying an pasting joins without paying enough attention to the subsequent editing.) It is an incredibly robust platform that appears to chug away at any task unperturbed, no matter how stupid the instruction.

T-SQL isn't just a querying language like Access SQL. It is a full blown procedural language. The big queries I'm talking about have lots of separate stages in them that would be considered completely separate queries in Access.

When I comes to optimising the basic database queries themselves, both Access and SQL Server use some pretty serious techniques. Many years ago it was said that the optimiser could not work across multiple levels of queries as separate objects in Access but I have since read (right or wrong) that is no longer the case. I don't think it makes much difference any more whether the queries are nested into one query or as separate objects. Isaac and Pat Hartman have covered the pros and cons of each approach earlier in this thread.

Building Views upon views was also once frowned on in SQL Server but that has apparently been sorted out too. Of course it is still entirely possible to build hideous structures that the query oprimiser can't handle.

There are many more factors involved in making a query efficient than whether objects were nested or not. Most of us know about indexes and Sargability but there are others.

In SQL Server they are exposed to the dba. One of the big ones is Statistics. The optimiser uses statistics about the data, mainly relating to the diversity of values, to make decisions for the query plan. If those statistics get out of date, the performance can be very adversely affected. It is a very well documented subject for SQL server and other DBMS.

I don't know what Access does to manage this. I have sometimes decompiled Access databases in order to regenerate the query plans but I don't have any hard facts about this. Decompile is an undocumented feature of Access so I somehow doubt that it could be vitally important. I have never found it to a bad thing though.