A whole day wasted on one stored procedure
-
Half a day to find the sp causing the problem... another half to fix it and send emails out about it. The first problem is that the original programmer (not here) said that the problem didn't happen. This was "a while back" and eventually the users found a way prove that the problem does exist. So I find the offending sp and it is: almost 200 lines long, creats a temp table as it's first action, contains two cursors, and the cursor for each fetch had an "IF NOT EXISTS(...". Correct me if I'm wrong: using the EXISTS is a big no-no, right? Shouldn't you just do an extra left join (if necessary) and use that field instead of the EXISTS? In fairness to the original coder, I am not sure how this could have been done without a cursor. (I know it can... I'm just saying that generally if I stare at something with a cursor I quickly see how to do it without one - but that didn't happen this time) Seven hours I spent on this today, plus having our contractor in my cube for about half a day, plus the meeting with my boss + her boss, plus what I spent on this last week. Ironically, the original programmer was "literally correct" the problem (as stated by the user) was not a bug, though the application *was* certainly wrong. [sigh] Since I'm mostly getting this off my chest I put it in coding horrors instead of the db forum.
-
Half a day to find the sp causing the problem... another half to fix it and send emails out about it. The first problem is that the original programmer (not here) said that the problem didn't happen. This was "a while back" and eventually the users found a way prove that the problem does exist. So I find the offending sp and it is: almost 200 lines long, creats a temp table as it's first action, contains two cursors, and the cursor for each fetch had an "IF NOT EXISTS(...". Correct me if I'm wrong: using the EXISTS is a big no-no, right? Shouldn't you just do an extra left join (if necessary) and use that field instead of the EXISTS? In fairness to the original coder, I am not sure how this could have been done without a cursor. (I know it can... I'm just saying that generally if I stare at something with a cursor I quickly see how to do it without one - but that didn't happen this time) Seven hours I spent on this today, plus having our contractor in my cube for about half a day, plus the meeting with my boss + her boss, plus what I spent on this last week. Ironically, the original programmer was "literally correct" the problem (as stated by the user) was not a bug, though the application *was* certainly wrong. [sigh] Since I'm mostly getting this off my chest I put it in coding horrors instead of the db forum.
-
Half a day to find the sp causing the problem... another half to fix it and send emails out about it. The first problem is that the original programmer (not here) said that the problem didn't happen. This was "a while back" and eventually the users found a way prove that the problem does exist. So I find the offending sp and it is: almost 200 lines long, creats a temp table as it's first action, contains two cursors, and the cursor for each fetch had an "IF NOT EXISTS(...". Correct me if I'm wrong: using the EXISTS is a big no-no, right? Shouldn't you just do an extra left join (if necessary) and use that field instead of the EXISTS? In fairness to the original coder, I am not sure how this could have been done without a cursor. (I know it can... I'm just saying that generally if I stare at something with a cursor I quickly see how to do it without one - but that didn't happen this time) Seven hours I spent on this today, plus having our contractor in my cube for about half a day, plus the meeting with my boss + her boss, plus what I spent on this last week. Ironically, the original programmer was "literally correct" the problem (as stated by the user) was not a bug, though the application *was* certainly wrong. [sigh] Since I'm mostly getting this off my chest I put it in coding horrors instead of the db forum.
ChrisC(@ncmail) wrote:
Correct me if I'm wrong: using the EXISTS is a big no-no, right? Shouldn't you just do an extra left join (if necessary) and use that field instead of the EXISTS?
There are a couple of good discussions about this here: http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx[^] http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!766.entry[^]
-
ChrisC(@ncmail) wrote:
Correct me if I'm wrong: using the EXISTS is a big no-no, right? Shouldn't you just do an extra left join (if necessary) and use that field instead of the EXISTS?
There are a couple of good discussions about this here: http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx[^] http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!766.entry[^]
Thanks! :thumbsup: http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!766.entry[^] My tendency is to use a join, so I found this comment especially useful: [quote] So in conclusion, the point of this post is to avoid using joins to try to compare two sets, since this is not its function. In the end the join method doesn't seem clear as to what you are asking. I only touched on performance, but the join will often peform worse than the using in or exists, simply because the work involved to do the join is greater. [/quote] [sigh] live and learn
-
Thanks! :thumbsup: http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!766.entry[^] My tendency is to use a join, so I found this comment especially useful: [quote] So in conclusion, the point of this post is to avoid using joins to try to compare two sets, since this is not its function. In the end the join method doesn't seem clear as to what you are asking. I only touched on performance, but the join will often peform worse than the using in or exists, simply because the work involved to do the join is greater. [/quote] [sigh] live and learn
ChrisC(@ncmail) wrote:
will often peform worse
Test it.
ChrisC(@ncmail) wrote:
the work involved to do the join is greater
Though that work may be beneficial in some cases.