Rapid Response: Database Debugging and Profiling to the Rescue

Eric Kavanagh: Okay, ladies and gentlemen, it is 4:00 Eastern time on a Wednesday, and of course that means [inaudible].

Robin Bloor: Can’t hear you, Eric.

Eric Kavanagh: I was there days ago, so you’re not alone. But so the topic today is really interesting stuff. It’s the kind of thing that you want to make sure is happening in the background at your company, unless you’re the person doing it, in which case you want to make sure you’re doing it properly. Because we’re talking about debugging. Nobody likes bugs, nobody likes when the software stops working – people get upset, users get unfriendly. That’s not good. So, we’re going to talk about “Rapid Response: Database Debugging and Profiling to the Rescue.”

There’s a spot about yours truly, hit me up on Twitter, @eric_kavanagh of course.

This year is hot. And debugging is going to be hot, no matter what. It’s really going to be one of these problems that’s never going to go away, no matter how good we get at this stuff, there’s always going to be issues, so the key is how do you get to where you can resolve those issues quickly? Ideally, you have great programmers, great environments, where not too much goes wrong, but as the old saying goes, “Accidents happen in the best of families.” And the same holds true for organizations. So, this stuff happens, it’s going to happen, the question is what is going to be your solution for dealing with it and solving those problems?

We’ll hear from Dr. Robin Bloor, then our own Dez Blanchfield from down under, and of course, our good friend, Bert Scalzo, from IDERA. And in fact, I’m going to hand off the keys to Robin Bloor, take it away. The floor is yours.

Robin Bloor: OK. This is an interesting topic. I thought because Dez is probably going to go on about the actual techniques and war stories about debugging, I thought I’d just do a background discussion so that we should get a fully rounded picture of what’s going on. I did this a long time, and I used to be a coder, so it’s like, and I was almost tempted with this presentation to start waxing lyrical about the idea of open source but I thought I’ll leave that to someone else.

Here’s a list of famous bugs, and most of these get onto anybody’s top list, basically, all except the last two cost at least $100 million. The first one was the Mars Climate Orbiter, got lost in space and it was because of a coding problem, where people confused metric units with (laughs) feet and inches. The Ariane Five Flight 501 there was a mismatch between an engine that was put on and the computers that were supposed to be running the rocket when it was launched. Multiple computer failures, exploding rocket, headline news. Soviet gas pipeline in 1982, said to be the largest explosion in the history of the planet; I’m not sure whether it is. The Russians stole some automated control software, and the CIA realized that they were going to do that and put bugs in it, and the Soviets implemented it without testing. So, blew a pipeline up, thought that was amusing.

The Morris worm was a coding experiment, which suddenly became a rapacious worm that went round everybody’s— it apparently caused $100 million worth of damage; that’s an estimate of course. Intel made a famous error with a maths chip – a math instruction on the Pentium chip in 1993 – that was supposed to have cost over $100 million. Apple’s Maps program is possibly the worst and most disastrous launch of anything that Apple has ever done. People who tried using it, were, I mean, someone was driving along 101, and discovered that the Apple Map said that they were in the middle of the San Francisco Bay. So, people started to refer to Apple Maps app as iLost. The— our longest outage in 1990 – it’s just interesting from a point of view of the cost of something like that – AT&T were out for about nine hours and it cost some $60 million in long-distance calls.

And I was at a U.K. insurance company, and the database, they implemented a new version of the database and it started wiping data. And I remember that extremely well, because I was called in afterwards to take part in some kind of database selection because of that. And it was very interesting that they had taken a new version of the database, and they had a battery of tests that they did for new versions of the database that it passed all tests. It found a really obscure way to wipe data.

So, anyway, that’s that. I thought I’d talk about the impedance mismatch and the SQL issued. It’s interesting that relational databases store data in tables and coders tend to manipulate data in object structures that really don’t map very well to tables. And because of that, you get what’s called the impedance mismatch, and somebody has to deal with it in some way or other. But what actually happens, because one model, the coder’s model and the database another model, are not particularly aligned. You get bugs that just wouldn’t happen if the industry had built things that work together, which I think is hilarious. So, basically, on the coders’ side, when you get hierarchies it can be types, it can result sets, it can be poor API capability, it can be a lot of things that just throw things out in terms interaction with the database. But the thing that’s most for me, really interesting; always amazed me that you had this SQL barrier that is also a kind of impedance in a way that the coders and the database work with each other. So, SQL has data recognition, which is fine and it has DML for select, project and join, which is fine. You can throw a lot of capability in terms of getting data out of the database with that. But it has very little mathematical language for doing things. It has a bit of this and that, and it has very little time-based stuff. And because of that, SQL is an imperfect, if you like, means of getting the data. So, the database guys built stored procedures to live in the database and the reason for the stored procedures living there was that you didn’t really want to be throwing data back and forth to a program.

For some of the functionality was extremely data specific, so it wasn’t just referential integrity and cascading deletes and things like that, the database was taking care of all of the sudden you’re putting functionality in a database, which meant of course that the functionality for an application could be split between the coder and the database itself. And that made the job of implementing some kinds of functions really quite difficult and therefore more error prone. So, that’s one side of the database game, because it means that you’ve got in a lot of implementations for instance, that I’ve been involved in relational databases there’s really an awful lot of code that sits in stored procedures that is handled separately from code that sits in the applications. And it seems like a very strange thing to have got to, it’s supposed to be fairly smart at doing various things.

I thought I’d also talk about database performance because performance errors are often regarded as bugs, but basically you can have a bottleneck at the CPU, at the memory, at the disk, on the network and you can have performance issues because of locking. The idea would be that the coder didn’t really need to be concerned about performance and the database would in actual fact perform reasonably well. It’s supposed to be designed so that the coder doesn’t need to know. However, you get bad database design, you get bad program design, you get concurrency in workload mixing, which can also lead to performance issues. You get load balancing, you get capacity planning, data growth – that can cause a database to just stop, or slow down. It’s an interesting thing, when databases get almost full, they slow down. And you can have data layers issue in terms of replication and the need to replicate and the need to do backup and recovery. Anyway, that’s a general overview.

The only thing that I would like to say is that database debugging can be only as onerous and non-trivial – and I say that because I’ve done a lot of it – and you will often discover it’s like all situations in debugging that I ever experienced is, is the first thing that you ever see is a mess. And you have to try and go from the mess to working out how the mess came about. And often when you’re looking at a database issue all you’re looking at is corrupt data and you’re thinking, “How the hell did that happen?”

Anyway, I shall pass on to Dez, who’s probably going to say more words of wisdom than I came out with. I don’t know how to pass you the ball, Dez.

Eric Kavanagh: I’ll pass it, stand by, hold on.

Automated voice: Participant lines muted.

Eric Kavanagh: All right, hang on one second, let me give Dez the ball.

Dez Blanchfield: Thank you, Eric. Yes, Dr. Robin Bloor, you are indeed most correct: this is a topic, a lifelong bugbear if you’ll pardon the pun, sorry I couldn’t help myself on that one. Hopefully you can see my first screen there, my apologies for the font size problem at the top. The topic of bugs is a daylong lecture, in many cases in my experience. It’s such a broad and wide topic, so I’m going to lay the focus on two key areas, specifically the concept of what we consider as much a bug, but a programming issue. I think these days introducing a bug per se generally gets picked up by the integrated development environments, although they may be long-running bugs. But often it’s more so a case of profiling code and it’s possible to write code that functions, that should be a bug. So, my title slide here, I actually had a copy of this in very high resolution A3, but unfortunately it got destroyed in a move of house. But this is a handwritten note on a programming sheet from circa 1945, where supposedly some folk at Harvard University in the USA, their second build of a machine called Mark II. They were debugging some issue, in common language, but they were trying to find a fault, and it turns out that something slightly different from what was a hardware and a supposedly software issue came along.

So, the urban myth is that round about September 9^th, 1945 a team at Harvard University were pulling apart a machine, they came across something they called “relay seventy” – in those days programming was done in a physical sense, you wound code around a board, and that was how you effectively programmed the machine – and they found this relay number seventy there was something wrong with it, and it turns out the actual term “bug” came about because it quite literally was a moth – supposedly there was a moth wedged in between some piece of copper wire going from one place to another. And the story goes that the legendary Grace Hopper as this caption, for my title slide, “first actual case of a bug being found” quote unquote.

But as Robin highlighted earlier in his first slide, the concept of a bug goes as far back as we can imagine humans doing compute, concepts like a patch. The term “patch” came from an actual piece of tape being taped over a hole on a punch card. But the whole point of this, is that the term “debugging” came out of this concept of finding a bug in a physical machine. And ever since, we’ve used that terminology around trying to deal with issues, either not so much as coding issues in a program that doesn’t compile, but as a program that doesn’t run well. And specifically hasn’t been profiled just find things like never-ending loops that just go nowhere.

But we also have a scenario, and I thought I’d put a couple of funny slides in before I got into a bit more detail. Here’s the classic cartoon, called XKCD on the web, and the cartoonist has some pretty funny views on the world. And this one’s about a kid called “Little Bobby Tables” and supposedly his parents named this young boy Robert’); DROP TABLE Students;– and it’s called, and sort of “Hi, this is your son’s school having some computer trouble,” and the parent replies, “Oh dear, did he break something?” And the teacher says, “Well, in a way,” and the teacher asks, “did you really name your son Robert’); DROP TABLE Students ;–?” And the parent says, “Oh yes, little Bobby Tables we call him.” Anyway, they go on to say they’ve now lost the year’s student records, I hope you’re happy. And the response is, “Well, you should clean and sanitize your database inputs.” And I use that many times to talk about some of the problems we have in finding things in code, that often the code doesn’t look at the data as well.

Another funny one, I don’t know if this is real or not – I suspect it’s a spoof – but again, it also touches my funny bone. Somebody changing the license plate on the front of their car, to a similar statement that causes databases to drop in speed cameras and so forth that capture cars’ license plates. And I always refer to it as that I doubt any programmer anticipated a hit and run of their code by an actual motor vehicle, but never underestimate that – power of an angry geek.

(Laughter)

But this leads to me to my key point, I guess, and that is that once upon a time, we could debug and profile code as mere mortals. But I’m very much of the view that that time has passed, and anecdotally in my experience, my first – and this’ll age me terribly, I’m sure; Robin you’re welcome to poke fun at me for this – but historically I’ve come from a background at the age of 14 wandering down the end of town, and knocking on the door of a data center called “Data Com” in New Zealand and asking if I could earn pocket money at the school by getting the late bus home, some 25 km of a commute every day, by putting paper in printers, and tapes in tape drives, and just being a general admin. And curiously enough they did give me a job. But over time, I managed to get myself into the staffing and find the programmers and realized I loved coding and went through the process of running scripts and batch jobs, which at the end of the day is still code. You have to write scripts and batch jobs that look like mini programs and then go through the whole process of sitting on a 3270 terminal writing code by hand.

In fact, my very first experience was on a teletype terminal, which was actually 132-column physical printer. Essentially, think of like a very old typewriter with paper that scrolled through it, ’cause they didn’t have a CRT tube. And debugging code on that was a very non-trivial issue, so you tended to write all your code by hand, and then act like a typist, doing your best not to get errors to sneak in, because it’s extremely frustrating to have to tell the one line editor to go to a certain line and then print the line and then type it back in. But once upon a time, that was how we wrote code and that’s how we debugged, and we got very, very good at it. And in fact, it forced us to have very good programming techniques, because it was a real hassle to fix it. But the journey then went through – and we’re all familiar with this – it went from the 3270 terminal experience in my world, to Digital Equipment VT220 where you could see things on the screen, but again, you were just doing the same thing you did on the paper tape sort of printed format just on a CRT, but you were able to delete more easily and you didn’t have that “dit dit dit dit” sound.

And then you know, the Wyse terminals – like the Wyse 150, probably my favorite interface to a computer ever – and then the PC and then the Mac, and then these days modern GUIs and IDs that are web based. And a range of programs through that, programming in [PNL?] one and assembler and PILOT and Logo and Lisp and [Post Code?] and Fortran and Pascal and languages that might make people cringe. But these are languages that forced you to write good code; they didn’t let you get away with bad practices. C, C++, Java, Ruby, Python – and we get further up that programming stage, we get more script-like, we get closer and closer to Structured Query Language and languages like PHP that actually are used to invoke SQL. The point of telling you that is, coming from my background, I was self-taught in many ways and those that did help me learn, taught me very good programming practices and very good practices around design and processes to make sure I didn’t introduce buggy code.

Programming methods these days, things like, for example, Structured Query Language, SQL, it’s a very powerful, simple query language. But we’ve turned it into a programming language and I don’t really believe that SQL was ever designed to be a modern programming language, but we’ve skewed it to become that. And that introduces a whole bunch of issues, ’cause when we think about from two points of view: from the coding point of view and from the DBA point of view. It’s very easy to come along and introduce bugs for things like just poor programming techniques, lazy efforts at writing code, lack of experience, the classic pet peeve I have for example with SQL people jumping on Google and searching for something and finding a website that’s got an example and doing a copy and paste of existing code. And then replicating a bad coding, malpractice and putting it into production, because it just happens to give them the results they want. You’ve got other challenges, for example, these days we’re all rushing towards this, what we call the race to zero: trying to do everything so cheap and so fast, that we have a scenario where we’re not employing lower-paid staff. And I don’t mean that in a disingenuous way, but we’re not hiring experts for every possible job. Once upon a time anything to do with computers was rocket science; it was involved in things that went bang and were very loud, or went into space or engineers were heavily qualified men and women who had done degrees and had rigorous educations that kept them from doing crazy things.

These days, there’s a lot of folk getting into development and design and database who haven’t had years of experience, haven’t had necessarily the same training or support. And so you end with a scenario of just the traditional amateur versus expert. And there’s a famous line, I can’t actually remember who created the quote, the line goes, “If you think that it’s expensive hiring an expert to do a job, wait until you hire a couple of amateurs who create a problem and you have to clean it up.” And so SQL has that issue, and it’s very, very easy to learn, it’s very easy to use. But it’s not, in my view, a perfect programming language. It’s very easy to do things like do a select star from wherever and pull all that into a programming language that you’re more comfortable with like PHP and Ruby or Python, and use the programming language that you’re natively familiar with, to do the data manipulation, rather than doing a more complex query in SQL. And we see this a lot, and then people wonder why the database is running slow; it’s because a million people are trying to buy a ticket form an online ticketing system, where it does a select star from wherever.

Now, that’s a really extreme example, but you get the point out of all of that. So, to just really punch that point home, here’s an example that I carry around a lot. I’m a big fan of maths, I love chaos theory, I love the Mandelbrot sets. On the right-hand side there’s a rendition of the Mandelbrot set, which I’m sure we’re all familiar with. And on the left-hand there’s a piece of SQL that actually renders that. Now, every time I put this on a screen somewhere, I hear this “Oh my god, somebody rendered the Mandelbrot series with SQL, are you serious? That’s insane!” Well, the whole point of that is to illustrate what I was just outlining there, and that is yes, in fact you now can program almost anything in SQL; it’s a very heavily developed, powerful, modern programming language. When originally it was a query language, it was designed to just get data up. So, now we’ve got very complex constructs and we’ve got stored procedures, we’ve got programming methodology being applied to a [inaudible] language and so it’s very easy for poor programming practice, lack of experience, cut-and-paste code, low-paid staff trying to be high-paid staff, people pretending they know, but they have to learn on the job.

A whole range of things where code profiling and what we refer to as debugging, which is not so much finding bugs that stop programs from working, but bugs that are just hurting the system and poorly structured code. When you look at this screen now, and you think, that’s just, it’s kind of cute and you think, “Wow, what a great graphic, I’d love to run that.” But imagine that running on some piece of business logic. It looks pretty neat, but it speaks a mathematical graphically rendered chaos theory, but when you think about what it could potentially be used for in some business logic, you get the picture very quickly. And to really illustrate that – and I’m sorry the colors are reversed, it’s supposed to be a black background and green text to be a green screen, but you can still read that.

I went and had a quick look at an example of what you could potentially do if you were really crazy and had no experience whatsoever and came from a different background of programming and applied the likes of C++ to SQL, to really illustrate my point, before I hand over to our learned guest from IDERA. This is a structured query that’s written like C++, but it’s coded in SQL. And it actually executes, but it executes over about a three- to five-minute period. And it pulls back ostensibly one line of data out of multiple databases, multiple joins.

Again, the whole point of this is that if you don’t have the correct tools, if you don’t have the correct platforms and environments to be able to catch these things, and they get into production, and then you have 100,000 people hitting a system every day, or hour, or minute, very soon you end up with a Chernobyl experience where the big iron starts melting down and burying itself to the core of the planet, because that piece of code should never get into production. Your systems and your tools, excuse me, should pick that up before it goes anywhere near the— even through the test process, even through UAT and systems integration, that piece of code should be picked up and highlighted and someone should be brought aside and saying, “Look, that’s really pretty code, but let’s get a DBA to help you build that structured query properly, because frankly, that’s just nasty.” And the URL’s there, you can go and have a look – it’s referred to as the most complex SQL query you ever wrote. ‘Cause believe me, that actually does compile, it does run. And if you cut and paste that and just mock up the database, it is quite something to watch; if you’ve got the tools to watch the database just try and melt down over a three- to five-minute period, to call back what is one line of text.

So, to summarize, with that in mind, my whole background in coding has taught me that you can give people a gun and if they’re not careful they will shoot themselves in the foot; the trick is to show them where the safety mechanism is. With the right tools and the right software at your fingertips, after you’ve done the coding, you can review your code, you can find issues by profiling the code, you can find effectively unintended bugs that are performance issues, and as I said earlier on, once upon a time, you could do it looking at a green screen. You can’t anymore; there are hundreds of thousands of lines of code, there are tens of thousands of apps deployed, there are millions of databases in some instances, and even super humans can’t actually do this by hand anymore. You quite literally need the right software and the right tools at your fingertips and you need the team to be using those tools, so that you can find these issues and address them very, very quickly, before you get to the point, whereas Dr. Robin Bloor highlighted, things either become disastrous, and things blow up, or more commonly, they just start costing you a lot of dollars and a lot of time and effort and destroying morale and stuff, when they can’t work out why things take a long time to run.

And with that in mind, I’m going to hand over to our guest and I look forward to hearing how they’ve solved this issue. And particularly the demo I think that we’re about to receive. Eric, I’ll pass back over.

Eric Kavanagh: OK, Bert, take it away.

Bert Scalzo: OK, thank you. Bert Scalzo here from IDERA, I’m the product manager for our database tools. And I’m going to talk about debugging. I think one of the most important things that Robin said earlier – and it’s very true is that debugging is onerous and non-trivial, and when you go to database debugging it’s an order of magnitude even more onerous and non-trivial – so, that was an important quote.

OK. I wanted to start with programming history, because a lot of times I see people who are not debugging, they do not use a debugger, they just program with whatever language they’re using, and a lot of times they’ll say to me, “Well, those debugger things are new, and we haven’t started using those yet.” And so what I do is I show them this timeline chart, sort of pre-history, the old age, the middle ages, it’s kind of say where were we in terms of programming languages. And we had very old languages starting back in 1951 with assembly code, and Lisp and FACT and COBOL. Then we get into the next group, Pascals and Cs and then the next group, the C++s, and look where that question mark is – that question mark is approximately right around the 1978 to maybe 1980. Somewhere in that range we had debuggers available to us, and so to say, “Hey, I’m not using a debugger, ’cause that’s one of those new things,” then you must have started programming, you know, back in the 1950s, ’cause that’s the only way you’d get away with that claim.

Now the other thing that’s funny about this chart is Dez just made a comment about Grace Hopper, I actually knew Grace, so it’s kind of funny. And then the other thing I laughed at is he talked about teletypes and I’m sitting there going, “Man, that was the greatest leap we ever had in productivity, when we went from cards to teletypes, that was the biggest jump ever.” So, and I’ve programmed in all the languages on here, including SNOBOL, which no one’s ever heard of before, it was a CDC, Control Data Corporation, so I guess I’m getting a little bit too old for this industry.

Dez Blanchfield: I was going to say, you’ve aged us terribly there.

Bert Scalzo: Yeah, I’m telling you, I feel like Grandpa Simpson. So I look at debugging and there’s different ways of doing debugging. You could be talking about what we all think of as traditional getting into a debugger and stepping through code. But also, people will instrument their code; that’s where you stick statements into your code and maybe you produce an output file, a trace file or something, and so you instrument your code. I would count that as debugging, it’s a little bit harder, a way of doing it, but it counts. But also, we’ve got the famous print statement: you watch and people actually put print statements in and I’ve actually seen a tool where – and it’s a database tool – where if you don’t know how to use a debugger, you push a button and it will stick print statements throughout your code for you and then when you’re done you push another button and it strips them out. Because that’s how a lot of people debug.

And the reason we debug are twofold: first of all, we got to find things that make our code ineffective. In other words, typically that means there’s a logic mistake or we missed a business requirement, but what it is, is the code is not effective; it doesn’t do what we expected it to do. The other time we go and we do debugging, it’s for efficiency and that could be a logic mistake, but what it is, is I did the right thing, it just doesn’t come back quickly enough. Now, I make that point because a profiler’s probably better for that second scenario and we’re going to talk about both debuggers and profilers. In addition, there’s this concept of remote debugging; this is important because a lot of times if you’re sitting on your personal computer, and you’re using a debugger, that hits a database where the code is actually executed on the database, you’re actually doing what’s called remote debugging. You may not realize it, but that’s what’s happening. And then, it’s very common with these debuggers to have break points, watch points, step in and step over and some other common things, that I’m going to show those on a screen snapshot in a moment.

Now, profiling: you can do profiling in a couple of different ways. Some people will say that workload capture and replay where it captures everything, that that counts as profiling. My experience has been more it’s better if it’s done sampling. There’s no reason to catch every single statement, because some statements may just run so quickly that you don’t care, what you’re really trying to see is, well, which are the ones that keep showing up over and over again, because they run too long. So, sometimes profiling can mean sampling rather than running the whole thing. And typically, you will get some kind of output that you can use, now that could be visual inside of an IDE development environment, where it may give you like a histogram of the performance of the various lines of code, but it could also still be that it produces a trace file.

Profilers first appeared in 1979. So, those have been around for a long time, too. Great for finding resource consumption, or performance issues, in other words that efficiency thing. Generally speaking, it’s separate and distinct from the debugger, although I have worked with debuggers that do both at the same time. And while profilers I think are the more interesting of the two tools, if I feel that not enough people debug, then definitely not enough people profile, because one out of ten debuggers will profile, it seems. And that’s a shame, because profiling can really make a huge difference. Now, database languages, as we’ve talked about earlier, you’ve got SQL – and we’ve sort of forced the round peg into the square hole here and forced it to become a programming language – and Oracle. That’s PL/SQL – that’s procedural language SQL – and SQL Server, it’s Transact-SQL, it’s SQL-99, it’s SQL/PSM – for, I think, it’s Procedure Stored Module. Postgres gives it another name, DB2 yet another name, Informix, but the point is everybody has forced 3GL-type constructs; in other words, FOR loops, at variable declarations and all the other stuff that’s foreign to SQL are now part of SQL in those languages. And so, you need to be able to debug a PL/SQL or a Transact-SQL just like you would a Visual Basic program.

Now, database objects, this is important because people will say, “Well, what things do I have to debug in a database?” And the answer is, well, whatever you can store in the database as code – if I’m doing T-SQL, or PL/SQL – and I’m storing objects in the database, it’s probably a stored procedure or stored function. But there’s also triggers: a trigger is sort of like a stored procedure, but it fires on some kind of event. Now, some people in their triggers will put one line of code and call a stored procedure so that they keep all of their stored code and procedures, but it’s the same concept: it’s still the trigger could be what initiates the whole thing. And then as Oracle, they have something called a package, which is sort of like a library if you will. You put 50 or 100 stored procedures into one grouping, called a package, so it’s kind of like a library. So, here’s the debugger the old way; this is actually a tool that will actually go in and stick all these debug statements in your code for you. So, everywhere you see debug block, do not remove, the auto debugger start and trace, those were all stuck in by some tool. And the lines outside of that, which is the minority of the code, well, that’s the non-manual debugging method.

And the reason I bring this up is, if you’re trying to do this by hand, you’re actually going to type more debugging code to put in all these print statements than you are with the code. So, while this may work, and while it’s better than nothing, this is a very difficult way to debug, especially since, what if it’s taken 10 hours for this thing to run, and where it has a problem is in line three? If I were doing an interactive debugging session, I would have known at line three – five minutes into it – hey, there’s a problem here, I can quit. But with this, I’ve got to wait for it to run, all the way to completion and then I’ve got to look at some trace file that probably has all these print statements in it, and try and find the needle in the haystack. Again, this is better than nothing, but it wouldn’t be the best way to work. Now, this is what that file would look like that came from the prior slide; in other words, I ran the program, and it’s just got a bunch of print statements in this trace file and I may or may not be able to siphon through this and find what it is I need to find. So, again, I’m not so sure that this is the way you would want to work.

Now, interactive debuggers – and if you’ve used something like Visual Studio to write programs, or Eclipse, you’ve had debuggers and you used them with your other languages – just didn’t think to use them over here with your database. And there are tools out there, like our DB Artisan and our Rapid SQL, this is Rapid SQL here, which have a debugger, and you can see on the left-hand side, I have a stored procedure called “check for duplicates.” Basically, it’s just going to go and look and see if I have multiple rows in the table with the same movie title. So, the database is for movies. And you could see on the right-hand side, on the top third, I’ve got my source code in the middle, I’ve got what are called my watch variables and my call stack trays, and then at the bottom I’ve got some output messages. And what’s important here is, if you look over that first red arrow, if I mouse over a variable, I actually can see what value is in that variable at that moment in time, as I’m stepping through the code. And that’s really useful, and then I can step one line at a time through the code, I don’t have to say execute, I could say step a line, let me look what happened, step another line, let me see what happened, and I’m doing this in the database. And even though I am sitting on Rapid SQL on my PC and my database is up in the cloud, I still can do that remote debugging and see it and control it from here, and do debugging just like I would with any other language.

Now, the next arrow there – you can see the little like arrow pointing to the right, towards that DBMS output, that’s where my cursor is at the moment – so in other words, I’ve stepped and that’s where I’m at at the moment. So, if I say, “Step again,” I’m going to go to that next line. Now just below that you’ll see the red dot. Well, that’s a breakpoint, that says “Hey, I don’t want to step over these lines.” If I just want to jump over everything and get to where that red dot, I can hit the run button and it’ll run from here either to the end, or to a breakpoint, if there are any breakpoints set, and then it will stop and let me do the stepping again. And the reason this is all important and powerful is, is because when I’m doing all of this, what’s happening in the middle and even the bottom – but most importantly the middle – will change and I can see the values from my variables, I can see my call stack trace, you know, and so all of that information is displayed there as I’m stepping through the code, so I actually can see and feel and get an understanding for what’s going on and how the code actually is working at execution time. And typically I can find a problem, if there is one, or if I’m good enough to catch it.

OK, now I’m going to talk about a profiler, and in this case, this is a profiler that I can see through a debugger. Remember I said sometimes they’re separate and sometimes they can be together? In this case, and again, I’m in Rapid SQL, and I can see there’s a margin, on the left-hand side, next to the line numbers. And what that is, is that’s the number of seconds or microseconds that it took to execute each line of code, and I can see that clearly, all of my time is spent in this one FOR loop where I’m selecting everything from a table. And so, whatever’s happening inside of that FOR loop probably is something I need to look at, and if I can make it better, it will pay dividends. I’m not going to get any improvements by working on those lines that have like 0.90 or 0.86; there’s not much time spent there. Now, in this case, and again, I’m in Rapid SQL, you’re seeing how I can do profiling intermixed with my debugging. Now, what’s nice is Rapid SQL also allows you to do it the other way. Rapid SQL allows you to say, “You know what? I don’t want to be in the debugger, I just want to run this and then I want to look at graphically or visually the same type of information.”

And you can see that I’m no longer in the debugger and it runs the program and after the execution is done, it gives me charts to tell me things so I can see that I’ve got one statement that looks like it’s taking up most of the pie chart and if I look, I see on that grid towards the bottom, line 23, there’s the FOR loop again: he’s taking up the most time, he is in fact that dark red chewing up all the pie chart. And so, this is another way for doing profiling. We happen to call that “Code Analyst” in our tool. But it’s basically just a profiler separated from a debugger. Some people like to do it the first way, some people like to do it the second way.

Why do we do debugging and profiling? It’s not because we want to write the world’s greatest code and get a pay raise – that might be our reason, but that’s not really the reason you do it – you promised the business you would do something correctly, that your program will be effective. That’s what you’ll use the debugger for. In addition, business end users; they’re not very patient: they want results even before they press the key. We’re supposed to read their mind and do everything instantaneously. In other words, it’s got to be efficient. And so, that’s what we would use the profiler for. Now, without these tools, I really do believe you’re this guy in the business suit with the bow and arrow and you are shooting at the target and you are blindfolded. Because how are you going to find how a program executes by just looking at static code and how are you going to figure out which line is where it would really spend the most time in execution, again, just by looking at static code? A code review may or may not turn up some of these things, but there’s no guarantee a code review would find them all. Using a debugger and profiler you should be able to find all of those bugs.

OK, I’m just going to do a real quick demo here. It’s not my intention to push product, I just want to show you what a debugger looks like ’cause a lot of times people will say, “I’ve never seen one of these before.” And it looks pretty in the screen snap slides, but what does it look like when it’s in motion? So, here on my screen I’m running our DB Artisan product; we have a debugger in there as well. The DB Artisan is meant more for DBAs, the Rapid SQL is more for the developers, but I have seen developers who use DB Artisan, and I’ve seen DBAs who use Rapid. So, don’t get caught up on the product. And here, I have the choice of doing a debug, but before I launch the debug, I’m going to extract this code so you can see what the code looks like before I start running it. So, here is the exact same code that was in the screen snapshot, this is my check for duplicates. And I want to debug this, so I press debug. And now, it takes a moment and you say, “Well, why is it taking a moment?” Remember remote debugging: the debugging is actually happening over on my database server, not on my PC. So, it had to go over and create a session over there, create a remote debugging thing, hook my session to that remote debugging session and set up a communication channel.

So, now, here’s my arrow, it’s up there at the top, by line one, that’s where I’m at in the code. And if I press the third icon there, which is a step into, you’ll see that arrow just moved, and if I keep pressing it, you’ll see it keep moving. Now, if I wanted to go all the way down to this FOR loop, because I know that’s where the problem is, I can set a breakpoint. I thought I set that. Oh shoot, I had one of my screen capture keys mapped to the same key as the debugger, that’s what’s causing the confusion. OK, so I just manually set a breakpoint there, so now instead of doing a step, step, step, step until I get there, actually I can just say, “Go ahead and run this thing,” and it will stop. Notice it moved me all the way down to where the break point is, so I am now into the context of running this loop, I can see what all my variables are set to, which is not a surprise, ’cause I initialized them all to zero. And now, I can step into this loop and start looking at what’s going on inside of this loop.

So, now it’s going to do a select count from my rentals and I can mouse over that guy and look, he’s two, two is greater than one, so it is probably going to do the next piece of this code. In other words, it found something. I’m just going to go ahead and let that run. I don’t want to go through everything here; what I want to show you is when a debugger is done, it finishes up just like a normal program. I’ve got the breakpoint set, so when I said run, it just went back to the next breakpoint. I’m letting it run to the end, ’cause what I want you to see is that a debugger doesn’t change the behavior of the program: when it’s done running, I should get the exact same results if I had run it not inside a debugger.

And with that, I’m going to suspend the demo and go back ’cause we want to make sure we have time for questions and answers. And so, I will open it up for questions and answers.

Eric Kavanagh: All right, Robin, maybe a question from you and then a couple from Dez?

Robin Bloor: Yeah, sure, I find this fascinating, of course. I’ve worked with stuff like this, but I’ve never worked with anything like this in the database. Can you give me some idea of what people use the profiler for? Because it’s like, are they looking at – ’cause I presume they are – they’re looking at performance issues, is it going to help you distinguish between when a database takes time and when a code takes time?

Bert Scalzo: You know, that’s a fantastic question. Let’s say I’m working in Visual Basic, and I, inside of my Visual Basic I’m going to call a Transact-SQL or a PL/SQL. Let me do the PL/SQL, since Oracle doesn’t play well always with the Microsoft tools. I might be profiling my Visual Basic code, and the profile there may say, “Hey, I called this stored procedure and it took too long.” But then I can go into the stored procedure and I can do a database profile on the stored procedure and say, “OK, out of the 100 statements that are in here, here’s the five that were causing the problem.” And so, you may have to do a tag team, where you have to use multiple profilers.

The idea is if ever you get told the performance problem is in your database, a database profile may help you find the needle in the haystack on which statements are actually the ones where you have a problem. I tell you another thing that turned up with profiling: if you have a piece of code that gets called a million times, but it only takes a microsecond each of the million times, but it gets called a million times, what the profiler would show, that thing ran for this many units of time. And so while the code may be highly efficient, you might look and say, “Ooh, we’re making this call to this piece of code way too often. Maybe we should only call it every so often, rather than every time we process a record,” or something. And so you can actually find where there is efficient code that’s just called too often, and that’s actually a performance problem.

Robin Bloor: Yeah, that’s wonderful. I’ve never done this. You see, of course, when I had database problems it was like I would in one way or another either be dealing with database or be dealing with code; I could never deal with both of them at the same time. But there, again, I didn’t do a— I’ve never actually been involved in building applications where we had stored procedures, so I guess I’ve never actually run into problems that used to drive me wild, the idea that you’d split the code up between a database and a program. But so, do all— I’m presuming the answer’s going to be yes, but this is part of a development team activity, when you’re in one way or another trying to fix something that’s broken, or maybe trying to bring a new application together. But does this all tailor in with all of the other components I would expect in the environment? Can I expect that I could clip this together with all of my test packs and all of that other stuff that I would be doing and with my project management stuff, is that how all this clips together?

Bert Scalzo: Yeah, it can become part of any structured process to do your programming or development efforts. And it’s funny, last week I had a customer who was building a web application, and their database had been small, historically, and so the fact that they weren’t very good programmers never hurt them. Well, their database has grown over the years, and now it takes 20 seconds in a web page, between when you say, “Log me in and give me some data to see” and when the screen actually comes up, and so now it’s a performance problem. And they knew the problem wasn’t in any of their Java or any of those other places. But they had thousands of stored procedures and so they had to start profiling the stored procedures to find out why is this web page taking 20 seconds to come up? And we actually found they had a Cartesian join in one of their select statements and didn’t know it.

Robin Bloor: Wow.

Bert Scalzo: But someone said to me one time, “Well how could they have a Cartesian join and not know it?” And this’ll sound really horrible; sometimes a programmer who’s not very comfortable with SQL will do something like give me a Cartesian join, but then only give me back the first record, so I know I got something, and I only need the first one. And so, they don’t realize they just brought back a billion records or they look through a billion records, ’cause they got the one they were interested in.

Robin Bloor: Wow, I know, that’s what’s called— well, that’s what Dez was going on about, in terms of people not exactly as skilled as perhaps they should be, you know. If you’re a programmer, you should know what the implications of issuing any command is. I mean, really, there’s no excuse that level of stupidity. I’m also presuming that you’re, in one way or another, just language agnostic as regards this, because this is all focusing on the database side. Am I right in that? Is it just the same, whatever you’re using on the coding side?

Bert Scalzo: Absolutely, you can do this in Fortran or C or C++. In fact, on some Unixes you can even do it for their scripting languages; they actually provide the same tools. And then I want to go back a second for what you said on no excuse. I am going to give the programmers one break, ’cause I don’t like throwing programmers under the bus. But the problem is really the academic environment because when you go to learn how to be a programmer, you’re taught record-at-a-time thinking. You are not taught set thinking, and that is what Structured Query Language, or SQL works with sets; that’s why we have the union, the intersect and the minus operator. And it’s very hard sometimes for a person who’s never thought in terms of sets, to quit, let go of record-at-a-time processing and work with sets.

Robin Bloor: Yeah, I’m with you on that. I mean, I get now, that’s an education issue; I think that’s completely an education issue, I think that it’s natural for programmers to think procedurally. And SQL is not procedural, it’s declarative. You are actually just saying, “This is what I want and I don’t care how you do it,” you know? Whereas with programming languages you’ve often got your sleeves rolled up and you’re down into the minutiae of even managing the counts, while you do a loop. I’ll hand on to—

Bert Scalzo: No. OK, continue.

Yeah, I was going to say you brought up one other example that a profiler would be good catching the, kind of goes on with this record-at-a-time processing. Sometimes, a programmer who is good at a record-at-a-time logic, cannot figure out how to do SQL program. Well, let’s say he makes two FOR loops and basically does a join, but he does it on the client side. So, he’s doing the same effect as a join, but he’s doing it himself, and a profile would catch that, because you would probably end up spending more time doing the join manually than letting the database server do it for you.

Robin Bloor: Yeah, that would be a disaster. I mean, you would just be thrashing around. Thrashing’s always bad.

Anyway, I’ll pass on to Dez; I’m sure he’s got some interesting questions.

Dez Blanchfield: Thank you, yeah, I do. I’m going to join you in the not throwing programmers under the bus. I mean, I’ve spent too many years in my life being a coder myself, at every level, you know, whether it’s as you said, sitting on the command line of the Unix machine, and in some cases, I was even involved in a couple of different ports of Unix from one hardware platform to another. And you can imagine the challenges we had there. But the reality is here’s that get-out-of-jail card for every coder and scripter in the world. It is a rocket science, quite literally, to write really tight every time, all the time, is a rocket science. And famous stories of people like Dennis Ritchie and Brian Kernahan working on some piece of code independently and then turning up to a code review chat over a coffee and finding out they’d written exactly the same piece of code, in exactly the same program, in exactly the same way. And they did it in C. But that purist level of programming exists very rarely.

The fact is that on a daily basis, there’s only 24 hours in a day, seven days in a week, and we just have to get stuff done. And so, when it comes to not just traditional programmers, the DBAs, and coders, and scripters, and sysadmin, and network admins, and security staff, and everything all the way through to the citizen data side these days; we hear, everyone’s just trying to do their job. And so I think the great takeaway from this whole thing is I loved your demo and I loved the takeaway that you left us with there, just a moment ago, talking to Robin about the fact that this does have a particular – maybe not so much a niche – but a broad space that it applies to, as far as fixing code and SQL and databases. But I was really excited to hear you say that you could poke it at a shell script and find some issues, because you know, in today’s day and age we’re always working to the lowest cost on everything.

The reason you can buy a $6 shirt somewhere, is because somebody built a system cheaply enough to actually manufacture and ship and logistically deliver and sell and retail and take online payments to get that $6 shirt. And that doesn’t happen if you’ve got people being paid $400,000 a year to write code in the perfect way; it’s just entire development. So, that point, I guess one of the questions I’d really love you to just give us some more insight, is what’s the breadth and reach of the type of people you’re seeing currently that are deploying these kinds of tools to profile a code and look for performance issues? Initially, historically, where do they come from? Have they been the big engineering houses? And then, going forward, is it the case, am I correct in thinking that more and more companies are implementing this tool, or these tools, to try and help coders, who they know who are just getting things done to get the job finished and get it out the door? And sometimes do we need a get-out-of-jail card? Am I right in thinking that historically we had a more engineering focus and development? That now, we’re getting a less, as Robin said, academic approach, and now it’s self-taught, or cut-and-paste code, or just get things built? And does that match to the sort of people who are taking the product on now?

Bert Scalzo: Yeah, exactly. And I’ll give you a very specific example, we do just want to get the job done, ’cause the business people don’t want perfection. It’s sort of like a computerized chess game: the chess game doesn’t look for the perfect answer; it looks for an answer that’s good enough in a reasonable amount of time, so that’s how we program. But what I’m finding now is, most people instead of saying they want a profiler as part of their unit testing – which is how I would do it, ’cause I don’t see it as a waste of time – what’s happening is now that that’s being done later, sometimes, during integration testing or stress testing, if we’re lucky. But most of the times it’s part of an escalation, where something’s gone into production, it ran for a while, maybe even ran for years, and now it doesn’t run well, and now we’ll profile it. And that seems to be the more common scenario now.

Dez Blanchfield: Yeah, and I think the term “technical debt” is probably one you’re more than familiar with; I know Robin and I certainly are. I think these days, particularly in agile approaches to developing and system building, to me, the concept of technical debt now is a very real thing, and we actually account for it in projects. I know, I mean, we’ve got our own projects like the Media Lens and others, where we’ve got coding happening on a daily basis, and various things across the Bloor Group. And whenever we’re building something, we kind of look at, I look at it, and always look at [inaudible] from the point of view of what is it going to cost me to fix this right now, versus can I just get it in the can and get it out there, and then watch and see if this thing’s going to break. And inherit this technical debt that I know I’ll have to circle back later and fix.

And I mean, I’ve done that in the last seven days: I’ve written a couple of tools and scripts, I’ve written a couple of pieces of Python language, and I’ve deployed it to Mongo back end, making sure it’s nice and clean and secure, but it just gets the query I need done, knowing that I need that function to work, to get to the bigger puzzle; that’s where my real pain is. And so you incur this technical debt, and I think this is now not just an occasional thing, I think this is part of the DNA of developing now. People just – not disingenuously – they just accept the technical debt is a normal modus operandi type of issue, and they just have to incur it. It’s where you incur the technical debt. And I think the great thing about what you showed us in the demo was you can literally profile and watch how long something takes to run. And that’s probably one of my favorite things. I mean, I’ve actually built profiling tools – we used to build tools in Sed and Lex and Orc to run our code and see where the loops were, before tools like this was available – and when you’ve built code to go and review your own code, you get very good at not having to review your own code. But that isn’t the case now. With that in mind, is there a particular market segment that takes this up more than any other? Seeing like a mass—

Bert Scalzo: Oh yeah, I’ve got— I’m going to draw an analogy for you, and show you that non-programmers do it all the time. ‘Cause if I’m ever teaching a debugger and profiling class or session, I’ll ask people, “OK, how many people in here go into Microsoft Word and purposefully never use the spell checker?” And nobody puts their hand up, because for writing documents, we all know we can make English mistakes, and so everyone uses the spell checker. And I said, “Well, how come when you’re writing text in your IDE like Visual Basic, you’re not using the debugger? It’s the same thing, it’s like a spell checker.”

Dez Blanchfield: Yeah, actually, that’s a great analogy. I hadn’t really thought about, I have to admit that I actually do something similar with a couple of tools I use. In fact, one, ODF, my favorite with Eclipse is just cut and paste code in there and go looking for things that just highlight immediately and realizing I made a typo in some class call. And, but it’s interesting now with the tool like this you can do it in real time as opposed to coming back and looking at it later, which is kind of nice to catch it upfront. But yeah, that’s a great analogy of just putting text into a word processor, ’cause it’s an interesting wake-up call that, just realize that you’ve made some typos or even a grammar error, right?

Bert Scalzo: Exactly.

Dez Blanchfield: So, are you seeing more of an uptick now from I guess, I mean, the final question from me, before I throw to our Q&A maybe, for our attendees. If you were going to give some sort of recommendation around the approach to do this – I’m assuming this is rhetorical – is it the case that you get in early and get this implemented as you’re developing, before you’re developing? Or it is the case that you’re predominantly get building, get moving, build something then come in and profile it later? I suspect it’s the case of get in early and make sure your code’s clean upfront. Or is it a case that they should be considering this part of their post-deploy?

Bert Scalzo: Ideally, they would do it upfront, but because everybody is in the hustle, bustle world where they just got to get stuff done, they tend not to do it until they run into a performance problem that they can’t solve by adding more CPUs and memory to a virtual machine.

Dez Blanchfield: Yeah. So, actually you mentioned something interesting, if I can quickly? You mentioned before that this can be run from anywhere, and can talk to database at the back end. So this is comfortable with the sort of bimodal concept we talk about now, of on-premise/off-premise cloud, by the looks of things as well, at the end of the day, if it can talk to the back end and see the code, it doesn’t really care, does it?

Bert Scalzo: Exactly, yeah, you can run this in the cloud.

Dez Blanchfield: Excellent, ’cause I think that’s kind of where our new brave world is going. So, Eric. I’m going to throw back to you now and see that we’ve got a few questions here and I want our attendees to still stay with us, even though we’ve gone past the hour.

Eric Kavanagh: Yeah, there’s a few folks out there, I’ll just make a quick comment: Bert, I think that metaphor, the analogy you give to using spell check is frankly brilliant. That is worthy of a blog or two, quite frankly, because it’s a good way to frame the context of what it is that you’re doing, and how valuable it is, and how it really should be a best practice to use a debugger on a regular basis, right? I bet you get some heads nodding when you throw that one out, right?

Bert Scalzo: Absolutely, ’cause what I tell them is, “Why do I run a spell check on my documents? I don’t want to be embarrassed by stupid spelling mistakes.” Well, they don’t want to be embarrassed by stupid coding mistakes!

Eric Kavanagh: Right. Yes, indeed. Well, folks, we’ve burned through an hour and five minutes here, so big thanks to all of you out there for your time and attention. We do archive all these web chats, do feel free to come back any time and check them out. Best place to find those links is probably techopedia.com, so we’ll add this to this list right here.

And with that, we’re going to bid you farewell, folks. Once again, great job, Bert, thanks to our friends from IDERA. We’ll talk to you next time, we’ll talk to you next week, in fact. Take care! Bye bye.