February 3, 2009 at 10:14 am
Lynn Pettis (2/3/2009)
LeeBear35 (2/3/2009)
Not that I want to complain, but does anyone see a problem with a company that would retain a person that consistenly produces code like this:
DECLARE @Count INT
SELECT @Count=MIN(EID) FROM Es(NOLOCK) WHERE ProjectID=@ProjectID AND Scanned=0
WHILE @Count IS NOT NULL
BEGIN
EXEC up_CalculateEPoints_Insert @Count
SELECT @Count=MIN(EID) FROM Es(NOLOCK) WHERE ProjectID=@ProjectID AND Scanned=0 AND EID > @Count
END
Please note that is would be on a table that in most cases contains more than 4 million rows, when confronted the responce was that "well at least I did not use cursors!" My thought is that cursors should have far better performance, but I have not the time and desire to confirm that point.
Looks like an opportunity to train someone in a better way to do something, not a reason to "get rid of" someone. Now, if such training and mentoring has been provided and they just don't get "it", that would be different.
Counterpoint -- who wants to work for a company that looks for reasons to get rid of people they feel can't do the job because no one has taken the time to show someone a better way to do something?
I have been trying to train him, but he still continues to generate this kind of code. In the mean time I am the one that they are letting go of because I am a senior resource and he is entry level. I'm sure however that they will bring in consultants to address the issues, because that funding comes out of a different bucket.
I agree with you whole heartedly I would never seek to have a company cut someone especially if they just need better training, and a more watchful eye (code review). Oh well, we never realize just how much we don't know until we learn something new.
February 3, 2009 at 10:16 am
SELECT 'EXEC up_CalculateEPoints_Insert ' + EID AS Command
FROM Es
WHERE ProjectID=@ProjectID AND Scanned=0
ORDER BY EID
Save the result to a text file and run it back though SQLCMD maybe? :w00t:
No cursors and no loops. It's set based. Well, sort of. :Whistling:
ATBCharles Kincaid
February 3, 2009 at 10:25 am
LeeBear35 (2/3/2009)
Lynn Pettis (2/3/2009)
LeeBear35 (2/3/2009)
Not that I want to complain, but does anyone see a problem with a company that would retain a person that consistenly produces code like this:
DECLARE @Count INT
SELECT @Count=MIN(EID) FROM Es(NOLOCK) WHERE ProjectID=@ProjectID AND Scanned=0
WHILE @Count IS NOT NULL
BEGIN
EXEC up_CalculateEPoints_Insert @Count
SELECT @Count=MIN(EID) FROM Es(NOLOCK) WHERE ProjectID=@ProjectID AND Scanned=0 AND EID > @Count
END
Please note that is would be on a table that in most cases contains more than 4 million rows, when confronted the responce was that "well at least I did not use cursors!" My thought is that cursors should have far better performance, but I have not the time and desire to confirm that point.
Looks like an opportunity to train someone in a better way to do something, not a reason to "get rid of" someone. Now, if such training and mentoring has been provided and they just don't get "it", that would be different.
Counterpoint -- who wants to work for a company that looks for reasons to get rid of people they feel can't do the job because no one has taken the time to show someone a better way to do something?
I have been trying to train him, but he still continues to generate this kind of code. In the mean time I am the one that they are letting go of because I am a senior resource and he is entry level. I'm sure however that they will bring in consultants to address the issues, because that funding comes out of a different bucket.
I agree with you whole heartedly I would never seek to have a company cut someone especially if they just need better training, and a more watchful eye (code review). Oh well, we never realize just how much we don't know until we learn something new.
If they are letting you go because you are a senior resource (meaning you get paid more, and are probably more knowledgable regarding your companies systems) and keeping him since he is an entry level resource (lower pay, less knowledge), then they are being short-sighted. If they then have to bring in a consultant (maybe even you) to fix things at a higher short-term cost, what are they saving? It is obvious the entry-level person may consistently write substandard code requiring constant rewrites.
Hmm, who would want to keep working for that company? This may be a blessing in disguise. I know it was for me four years ago.
February 3, 2009 at 10:42 am
Lynn,
I think in many ways it is a blessing, but I do not like the timing. Seems like everyone out there sees this as a time to get senior experiance for entry level or at most mid-grade experiance. I would not mind but that is a big cut.
Anyhow, I have corrected a number of processes like this and dropped the overall processing from hours to minuted. Infact I have one process that has been refactored and using a million row set takes 6.5 minutes to run, but I have to confirm it against the original process that has been running for 4 hours and I think has 12 to 24 more hours to go.
Oh, and one of the solutions was to generate all of the EXEC statements then call SQLCMD. One of the other potential solutions is using a varchar(max) in SQL 2005, but there is a limit of about 8 million rows.
DECLARE @SQL AS VARCHAR(MAX)
DECLARE @CR AS CHAR(1)
SET @SQL = ''
SET @CR = CHAR(10) -- Just to make things readable
SELECT
@SQL = @SQL + @CR + 'EXEC up_CalculateEPoints_Insert ' + CONVERT(VARCHAR(19), EID)
FROM [dbo].[Es] (nolock)
WHERE [ProjectID] = @ProjectID
AND [Scanned] = 0
EXEC (@SQL)
But that is SQL 2005 and beyond...
February 3, 2009 at 10:50 am
Could you make a version of up_CalculateEPoints_Insert (say up_CalculateEPoints_Insert_All) that would incorporate the selection logic query so that the whole solution could be set based?
ATBCharles Kincaid
February 3, 2009 at 12:11 pm
Actually I did make a version that replaced the call to up_CalculateEmailPoints_Insert, it was more a real life example of RBAR and how someone did what they could to avoid a cursor, but ended up going further down the wrong road.
Cursors, Goto...there are those things that should be avoided, but there is nothing like coding a program on Advanced PICK (for anyone that knows it anymore) of 10 GOTO 10 - just hangs the whole system. 😛
February 3, 2009 at 1:54 pm
Joe Celko (2/3/2009)
Hey! I teach SQL, not ethics! 🙂
:w00t: And here I keep buying your books to wave around in our ethics meetings! :w00t:
Good, fast, cheep. Pick any two. Works for software and your dates.
I was tasked to document an existing system. We found a routine that opened the database connection, twenty lines of rem'd out code, start loop, more rem'd code, read record number 5, a few hundred lines of rem'd code from some other project, increment loop counter, more rem'd code, if count less than 5000 then loop, more rem'd code, close connection, more rem'd code, return success. If you took out all the remarks and comments the routine opened the database, read record five 5000 times, closed the connection, and said "I did it". No error trapping. If there was a failure it never got reported. The record number was hard coded. No data from the record was used. The client was charged for I/O and CPU time.
ATBCharles Kincaid
February 4, 2009 at 8:21 am
Charles Kincaid (2/3/2009)
Joe Celko (2/3/2009)
Hey! I teach SQL, not ethics! 🙂:w00t: And here I keep buying your books to wave around in our ethics meetings! :w00t:
Good, fast, cheep. Pick any two. Works for software and your dates.
I was tasked to document an existing system. We found a routine that opened the database connection, twenty lines of rem'd out code, start loop, more rem'd code, read record number 5, a few hundred lines of rem'd code from some other project, increment loop counter, more rem'd code, if count less than 5000 then loop, more rem'd code, close connection, more rem'd code, return success. If you took out all the remarks and comments the routine opened the database, read record five 5000 times, closed the connection, and said "I did it". No error trapping. If there was a failure it never got reported. The record number was hard coded. No data from the record was used. The client was charged for I/O and CPU time.
Sounds like a snippet that the owner of the system (the one who GOT paid for that CPU/IO usage) paid a developer to put into the system to generate more revenues. :hehe: I wouldn't mind getting into that racket myself - just like printing money!! hehehe
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 4, 2009 at 9:15 am
Well I could not prove that it was malicious. Could be just incompetence or the "many cooks" syndrome.
What I say is: If it walks like a duck and quacks like a duck then shoot the sun of a #$%^ and let me get back to sleep!
ATBCharles Kincaid
February 4, 2009 at 7:18 pm
LeeBear35 (2/3/2009)
I'm sure however that they will bring in consultants to address the issues, because that funding comes out of a different bucket.
There is nothing that says you couldn't become that consultant... I did that same thing last year. I was happy, the company was happy (got a raise out of it, too!), and the poor slobs that I left behind were happy.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 24, 2009 at 2:47 pm
corey lawson (12/5/2007)
So... the major problem with your article is that it posts no solutions for the problem at hand, or no pointers to where to look further to increase one's kungfu skills in this area.I can't find one of my JCelko books that talks about calc'ing running sums in SQL and how he or some of his readers do it, but that would be one good* place to look ("SQL for Smarties" etc).
(* I realize that some people out there have a definite love-hate..no, hate relationship with JCelko's scribblings...but I don't, so feh. But if you have other resources, do add them to the thread).
Just curious but are you saying that if one knows of a problem they shouldn't tell anyone else unless they also have the solution? I hope you don't apply that to everyting.
Kindest Regards,
Just say No to Facebook!February 17, 2011 at 8:09 pm
YSLGuru (2/24/2009)
corey lawson (12/5/2007)
So... the major problem with your article is that it posts no solutions for the problem at hand, or no pointers to where to look further to increase one's kungfu skills in this area.I can't find one of my JCelko books that talks about calc'ing running sums in SQL and how he or some of his readers do it, but that would be one good* place to look ("SQL for Smarties" etc).
(* I realize that some people out there have a definite love-hate..no, hate relationship with JCelko's scribblings...but I don't, so feh. But if you have other resources, do add them to the thread).
Just curious but are you saying that if one knows of a problem they shouldn't tell anyone else unless they also have the solution? I hope you don't apply that to everyting.
Ya know... I never did thank you for that thought. Thanks, YSL... 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
February 22, 2011 at 3:23 pm
Jeff Moden (2/17/2011)
YSLGuru (2/24/2009)
corey lawson (12/5/2007)
So... the major problem with your article is that it posts no solutions for the problem at hand, or no pointers to where to look further to increase one's kungfu skills in this area.I can't find one of my JCelko books that talks about calc'ing running sums in SQL and how he or some of his readers do it, but that would be one good* place to look ("SQL for Smarties" etc).
(* I realize that some people out there have a definite love-hate..no, hate relationship with JCelko's scribblings...but I don't, so feh. But if you have other resources, do add them to the thread).
Just curious but are you saying that if one knows of a problem they shouldn't tell anyone else unless they also have the solution? I hope you don't apply that to everyting.
Ya know... I never did thank you for that thought. Thanks, YSL... 🙂
My pleasure. I do not like people who can contorbute nothing but sarcasm to a conversation. Besides I think you've done more then your fair share of assistance on SSC.com.
Kindest Regards,
Just say No to Facebook!July 14, 2014 at 2:07 am
I'm 7 years late but that last bit of sample code is referencing a non-existent field SomeVal instead of RowNum created in the table just prior 🙂
November 19, 2019 at 7:45 pm
I'm 7 years late but that last bit of sample code is referencing a non-existent field SomeVal instead of RowNum created in the table just prior 🙂
Heh... and I'm 5 years late in replying to your 7 year late post. Still, it sounds like something might be amiss. I just don't know where. Are you talking about code in the article or someone's code in these discussions?
EDIT: Ah... never mind. I see that the error is in the article. Thank you and I'll see if I can get it fixed.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 241 through 255 (of 255 total)
You must be logged in to reply to this topic. Login to reply