Is all code really code? One question from an interview
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.c
-
Good point made in your article...i agree with what you say !!!
------------------------------
Life is far too important to be taken seriously -
Why don't people suggest using debuggers on stored procs?
My experience is - until recently (recently being the debut of Sql2k) they just didn't work
And then nobody knew where the damned things were - I stumbled across the one in QA strictly by accident myself.BTW, my answer to the question would probably be:
1: I don't particularly like pushing business logic into stored procs, so at some point I'd probably think about rewriting it, but not now, as it's written
. And sometimes there's good reasons for stuffing business logic in a stored proc, so we'll just go with it .2: Use the debugger or stick print statements in, whichever you're more comfy with.
3: If you don't get it in 30 minutes or an hr or whatever feels like enough time, come get me and we'll go over it together, it's almost always something obvious that someone that hasn't been working on this will spot relatively quickly.
--woody
C. Woody Butler, MCP
cwbutler@cwoodybutler.com -
Hmmm. All very well, but as a developer, I don't have access to luxuries such as debugging and tracing / profiling. Our DBAs just don't allow it.
At a push, they will run a trace on a development database, but are not willing to do so in Production. Unfortunately, they are six hours behind us, so that limits the window when we can get traces run as well.
This leaves us the Print Statement, checking the data and copying the contents of the SP out into Query Analyser, and running bits of it individually to try to find out what the problem it.
While I do have Business Logic running inside Stored Procedures to enable efficient batch processing, I make sure that they are not required to perform a lot of steps. I would rather have a lot of Stored Procedures where I can run one, see the results and move on.
I am also a believer in the many eyes will spot something wrong. We were required to build something very quickly a while ago and instead of dividing the tasks between the two of us, we did all the steps together, which actually gave better results and was a quicker process.
-
I would love to use an integrated T-SQL debugged from within the Visual Studio IDE. Unfortunately, at the last THREE sites where we have tried to get it to work, we have been defeated by a minefield of Windows permissions and policy settings that prevented this legendary integrated service from ever functioning.
When it 'just works', I'll use it. Till then, it's very much a case of using Query Analyzer to 'step through' the code by running a line at a time and examining intermediate results.
Using binary search in conjunction with PRINT to find the line that is not behaving as expected is quick and easy, too.
-
Hmmm I was never brought up to write code and just assume it would work, so I would presume that the junior developer had made some attempt at debugging before coming to see me. So my answer would be go through the code together or check the data.
Maybe I'm just a pointy headed boss!
-
Interesting point - having thought about it briefly, I don't look as stored procedures in the same way as a piece of VB code. The reasons are probably many and various, but one important point does occur to me: while many languages have been designed and come with decent debugging tools, TSQL ain't one of those beasts. IMHO it's rather like Topsy - it just growed and growed. In comparison with a real language, TSQL's debugging tools are poor, in fact the whole language structure is badly put together (I'm not talking about the SQL part, but all the rest of the stuff). Formatting code is a nightmare, the begin..end syntax is archaic, etc. The real problem is that we're all trying to use TSQL for things it was never designed to do. Write a simple program in TSQL and compare the number of lines used with, say, Visual Basic, and you'll see my point. Just think about editing a stored procedure using EM - while you're doing that, you can't look at any other objects because the edit box is modal - how stupid is that?
-
There are several issues to consider here:
1) Does the developer have rights to use the debugger? None of the developers in my current environment would have rights to use the debugger.
2) Will the debugger work for this procedure? I have used the debugger and have found that there are some procedures it is unable to work on.
3) As far as I was able to determine the debugger will not let you see the contence of temp tables or for that matter table variables.
4) Does the developer have access to sql profiler? This will at least tell you the path that proc is using.
Depending on the type of issue and how the proc is written:
1) Is it operating on one record at a time?
2) Is it operating on sets?
3) Does it use temp tables or table variables?
The best way to go sometimes is to execute the procedure as inline SQL in Query Analyzer so that you can examine all information at each step of the way. The included debugger will sometimes help to debug issues but it is VERY primitive and limited.
Cast has a SQL debugger that I used several years ago (SQL 6.5). It was solid and helped me debug many complicated procedures and triggers. It had some concurrency issues but was a superb product.
-
Interesting article and interesting responses. I've been coding in a multitude of languages professionally now for about 20 years, and while I tend to agree with most of the commentary I would contend that T-SQL -- while syntactically sloppy -- is actually a fairly robust little language. It isn't that hard to debug either, even without anything more than query analyser and the languages own printing capabilities, assuming the procedure is actually well-written to start with.
I can see though how such a question might be a pain to answer. It's too undefined to tackle explicitly, and most rock-solid senior developers I have known would feel their skin crawl to have such a question tossed at them. It would be similar to phoning someone and saying, "My cat is black and I wonder how come my air conditioner doesn't work when set on high?" The question as it is posed raises the size of the code (the cat is black) and observes that it is full of business logic (an air conditioner) and indicates an error condition (doesn't work on high), but really lacks any concrete description of what is happening. For example, what is the nature of the logic that requires 200 lines? Is it substitution logic (ID becomes string data), selection logic (case or if...then), a mix of many forms? I think most developers would have to vary their priority of attack on the problem based upon that type of information. I have seen far too many circumstances where junior developers spent hours weating 200 lines of code only to have the problem turn out to be their expectations were wrong.
But as a rule, I think it is always wise to stay simple in stored procedures. If during the debug effort you can break the stored procedure down into 20 line components it should in fact be rewritten that way.
On an only vaguely related point I might even have opened by asking a question in return: "Where's the design documentation describing the procedure's role and function within the larger system?" In other words, is it failing because of a lack of planning and poor design, or a because of a failure to follow the design?
-
As a SQL/Database developer my response would be to request addtional information. The "results not as expected" condition may or may not indicate a problem with the code. The first step should be to examine exactly how the results differ from expectations. For example, a proc whose purpose is to sum the sales for a group of products by a product group code. The recevied results differ from the expected results in that some, but not all of the group totals are off. This would more likely be a data problem than a code problem. It is also possible that the coded business logic differs from the specifications. If all of the results are off, that indicates a code problem and calls for use of the debugger (print statements are SO 1990s).
-
Personally, I think questions like this are designed more to "trick" someone rather than really finding out what they know about SQL. There could be several different responses to this question and none of them are totally wrong. Rather than concentrating on "nebulous" questions like this in an interview,try to stick to more finite questions that will accurately guage a person's expertise. Remember you are there as a representative of the company to accurately find out his/her qualifications for the position they are applying for. Not to "stump" them with vague and nebulous questions or to prove how much you know and they don't.
-
I’d use Query Analyzer. In fact I design my sprocs (and DTS scripts) in Query Analyzer with variables to test the sprocs, and then comment out the variables when I move it to production. This way if problems occur six months down the road I can start troubleshooting right away.
Question, though. Isn't it more efficient to have a sproc build an initial dataset than to return a load of data to C# and perform the business logic there?
I should note that I use asp.net C# and MS Access for building end user solutions and I have no qualms about putting business logic on the server (not related to data integrity). In fact, I've found this is sometimes the only viable way when using MS Access and often times it's a lot easier to modify sprocs than middle tier C# code.
Everett Wilson
ewilson10@yahoo.com -
"Check the data" is absolutely the correct FIRST answer.
Can anyone here NOT remember wasted hours that turn out to be incorrect or unexpected INPUT?
Peace
G. Waleed Kavalec
-
I think debugger is best tool to troubleshoot the complex stored procedure, I always encourage my developers to use and I used myself while developing some tricky sp and wouldn't able to finish if debugger is not there.
Regards
--
Farhan
FS -
Keep in mind that if the sproc has test variables then the data (variables in question) can be plugged straight in to the sproc and the behavior monitored.
I find that check the data often means a rare data event that was not considered. If the sproc is performing business logic then inevitably there's a business case that no one thought about, in this case the behavior needs to be understood in order to design a solution.
Also, incorrect data is often data that must be handled. Even though I perform data integrity in C# I deal with multiple data warehouses who's data can be suspect due to a distributed environment (weak central accountability). My sprocs must handle this data, it's not enough to point a finger at someone else.
Everett Wilson
ewilson10@yahoo.com
Viewing 15 posts - 1 through 15 (of 41 total)
You must be logged in to reply to this topic. Login to reply