Want to Challenge Yourself to Better Troubleshooting? (1 Viewer)

Isaac

Lifelong Learner
Local time
Today, 14:58
Joined
Mar 14, 2017
Messages
8,738
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!


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
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:58
Joined
Feb 19, 2002
Messages
42,976
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:)
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 08:58
Joined
Jan 20, 2009
Messages
12,849
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

Super Moderator
Staff member
Local time
Tomorrow, 08:58
Joined
Jan 20, 2009
Messages
12,849
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

Lifelong Learner
Local time
Today, 14:58
Joined
Mar 14, 2017
Messages
8,738
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

Super Moderator
Staff member
Local time
Tomorrow, 08:58
Joined
Jan 20, 2009
Messages
12,849
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:58
Joined
Feb 19, 2002
Messages
42,976
It never ceases to amaze me that so many people who use Access and VBA look at them as inferior tools and that other platforms are so much better that they'll make you feel smarter if you switch to them. Access is certainly not the best tool for every job and there are certainly cases where people try to use Access when they should be looking for something more appropriate. However, the best tool to use is the tool that best suits the job. There is a class of applications where Access is the absolute best tool. There is none better. Those are the applications I build and hopefully, you too. I work in that sweet spot. If you feel the need to constantly fight with Access, you either need to actually learn how to use it so that it works for you or switch to a different tool that suits your projects or temperament better.

Based on what I know about software development, VBA actually is an inferior language, not because of what it cannot do but because it is too flexible. There are too many ways to do something. There are too many ways to refer to certain types of objects. Tighter languages are easier to learn and code with because you have fewer decisions to make.

Spread your wings. Learn new things. They may make you appreciate Access more. Or you might find the need to move to working with a different tool.

@issac What can you do with Python that you can't do with a different tool? If you have a need for that application, you wouldn't have any trouble. But if your co-worker is pushing you because he thinks your tool is inferior, then he's wrong:) It may not do what Python does but Python can't do what Access does so does that make Python inferior?
 

Isaac

Lifelong Learner
Local time
Today, 14:58
Joined
Mar 14, 2017
Messages
8,738
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:58
Joined
Feb 19, 2002
Messages
42,976
Yes @Pat Hartman Access is great but (in my personal opinion) it is only "great" as a front end tool.
"Access" is ONLY a FE tool. I'm sure you know that. It is NOTHING else. Comparing Access to SQL Server is like comparing eggs to submarines. They have nothing in common. OK, they both have hard shells. They are not competitors as the SQL Server people think. They are complementary tools. Knowing SQL server is a very valuable skill. Using it as your BE frees you from the limitations of Jet and ACE which Access always takes the rap for.

The whole point of Access was as a tool to make a FE for ANY ODBC compliant BE. In fact, Jet did not even ship with Access until version 2.1. Jet was a Windows component and so was installed with Windows. It was used originally by Windows to hold various types of data.

Access isn't an exciting career path. It is not new and shiny. It is not slick. It is just plain solid. It does what it does and it does it well. It is the best of breed. Lots of companies depend on it to run all their applications. It's been around for 30 years and someone who just started using Access this week would still recognize the earliest version of Access and be able to manipulate its objects. MS doesn't make many updates and that isn't a terrible thing because they are prone to breaking things and that disrupts a lot of production applications.
 
Last edited:

Isaac

Lifelong Learner
Local time
Today, 14:58
Joined
Mar 14, 2017
Messages
8,738
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:58
Joined
Feb 19, 2002
Messages
42,976
You seem to be resistant to taking advantage of the back end you now acknowledge is an excellent option.
The difference in our opinions derives from your liking of bright shiny things and my desire to bring value to my clients. My clients aren't paying me to make Access apps "better". They are paying me to make them work reliably and at minimum cost. To make them easy for someone else to take over and then go away:) And as long as the "Access way" works, it would be fiscally irresponsible of me to push for "better". I will use a pass through query or stored procedure if Access can't give me that functionality or is too slow for my clients to be happy. But I would never use them for ordinary processing that Access handles perfectly well.

Using SQL Server as a BE isn't actually "better" than ACE. Many of the apps I build will never live long enough or grow large enough to justify the client paying for SQL Server support. Nor do they have the security requirements that dictate a more secure BE. Remember, I am not an employee. I can't be there to manage their SQL Server and besides, I am not a DBA. I develop applications. I don't provide database support except at a rudimentary level and I don't want to. I LIKE developing applications. To me, that is far more interesting than tinkering with queries to make them run faster. Sometimes I have to tinker with queries to make them run faster but that isn't what I go into work (or used to) every day for.

I get paid the big bucks because I have developed enough applications for different business units that I can hit the ground running. I have solid database design skills from more than 50 years of designing BE's using a wide range of different databases going all the way back to ISAM and VSAM through hierarchical, linked lists, and eventually to relational databases. Relational databases are still immensely popular after more than 40 years because in addition to being easy to program with, they can be used to provide users with direct access tools they can use to develop their own reports. That is the power of the DML part of SQL. And, like new Access users being able to recognize even the earliest versions of Access, new RDBMS users regardless of brand will recognize the basic relational concepts that have held solid since Codd wrote them down in the late 70's. I've also written my million lines of code as you've seen me write many time. Plus, I'm a lazy bum. If Access will do it for me, I would never do it myself unless I needed more speed.

We actually have very different opinions of how Access should be used. I believe that Access should be Access. I never write any code to replace an Access feature unless a client specifically wants me to and knows that he can get something similar for free or he can pay me for custom. I also make every effort to use Access the way the original designers intended. That means I'm not too proud to use the QBE or linked tables. I also understand the primary purposes for the events of the event model so I can write code in the most effective event unlike others who have to put code in multiple events to stop bad data from being saved.

We probably don't disagree on the soundness of SQL Server et al as a BE as opposed to Jet and ACE. SQL server frees Access from the limits of Jet and ACE. If you think about all the bad press "Access" gets, it is actually complaints about Jet and ACE rather than Access by people who can't tell the difference between Access and the database engines it uses to hold its objects. Instead of a hard limit of 255 users and a soft limit between 50 and 100, my FE's are now limited to the number of seat licenses my client owns. So, I can have thousands of concurrent users. I don't because my Fortune 100 clients use "real" development environments for their company wide apps and only use Access for ancillary projects. I can't tell you how many ancillary apps I've built for SAP because it just doesn't live up to the sales pitch. Instead of a hard limit of 2Gig, my databases can be as large as the RDBMS can support. Turns out that little ol' Access is actually infinitely expandable. Who knew?

Many old clients call me back for new projects or to make enhancements to old projects because they could see that I never tried to make myself indispensable. I LIKE being a consultant. I LIKE always developing new applications. I LIKE learning new businesses. To me, becoming the resident expert in the payroll application would be like the death of a thousand cuts.

And most of all, I LIKE teaching people the "Access way" because it is the easiest way to get to where they want to go although perhaps not the slickest way.

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

Isaac

Lifelong Learner
Local time
Today, 14:58
Joined
Mar 14, 2017
Messages
8,738
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

Super Moderator
Staff member
Local time
Tomorrow, 08:58
Joined
Jan 20, 2009
Messages
12,849
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".
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:58
Joined
Feb 19, 2002
Messages
42,976
Your dismissive arrogance is reaching new levels
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?
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 08:58
Joined
Jan 20, 2009
Messages
12,849
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:58
Joined
Feb 19, 2002
Messages
42,976
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.
Apparently, you don't actually read what I write so what is there to discuss? You are just looking for a fight.
 

Mike Krailo

Well-known member
Local time
Today, 17:58
Joined
Mar 28, 2020
Messages
1,030
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

Lifelong Learner
Local time
Today, 14:58
Joined
Mar 14, 2017
Messages
8,738
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.
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:58
Joined
Feb 19, 2002
Messages
42,976
@Issac, glad your new job is working out. Sounds like it is quite challenging. I happen to like the black box that Access offers when using queries as if they were tables. It allows you to partition your work and get it checked out piece by piece. You can then encapsulate the pieces so you can ignore them unless you need to look at them. Having to put everything all together into one procedure might to some people seem easier but not to me. That is how you get the 1500 line queries. I'm not sure I ever created anything this large (I never counted the lines) but I created some that were large enough that I documented them using Excel so I could keep the hierarchy straight and also so I could reuse queries in different reports when I was creating monstrous reports for a bank in Alabama. The reuse ensured consistency should I need to change something. I only had one place to change it instead of in 60 reports. The downloads I got from the mainframe were running about 12 million rows for two years worth of transaction data. I thought I would need to move to SQL Server but I never did. Probably because the tables were tall but narrow so they never exceeded the 2Gig limit. A third year to analyze would have sent me scurrying to SQL Server due to exceeding the 2Gig limit.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 08:58
Joined
Jan 20, 2009
Messages
12,849
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.
 

Users who are viewing this thread

Top Bottom