Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««2324252627»»

Hidden RBAR: Triangular Joins Expand / Collapse
Author
Message
Posted Tuesday, February 3, 2009 10:07 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:21 AM
Points: 23,010, Visits: 31,512
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?




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #649051
Posted Tuesday, February 3, 2009 10:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 2:18 PM
Points: 12, Visits: 64
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.



Post #649061
Posted Tuesday, February 3, 2009 10:16 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, February 6, 2014 12:59 PM
Points: 801, Visits: 1,962
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?
No cursors and no loops. It's set based. Well, sort of.


ATB

Charles Kincaid

Post #649067
Posted Tuesday, February 3, 2009 10:25 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:21 AM
Points: 23,010, Visits: 31,512
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.




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #649082
Posted Tuesday, February 3, 2009 10:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 2:18 PM
Points: 12, Visits: 64
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...



Post #649105
Posted Tuesday, February 3, 2009 10:50 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, February 6, 2014 12:59 PM
Points: 801, Visits: 1,962
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?

ATB

Charles Kincaid

Post #649110
Posted Tuesday, February 3, 2009 12:11 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 2:18 PM
Points: 12, Visits: 64
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. :P



Post #649187
Posted Tuesday, February 3, 2009 1:06 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, July 20, 2014 11:55 AM
Points: 1,945, Visits: 2,860
LOL! In the early days of SQL, we wrote MAX(DISTINCT x) to force a sort that would slow down performance. Likewise, we did SELECT DISTINCT when we knew a SELECT would work, etc. The program would lrun like glue; we would change a few keywords and be heroes!

Hey! I teach SQL, not ethics! :)


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #649257
Posted Tuesday, February 3, 2009 1:54 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, February 6, 2014 12:59 PM
Points: 801, Visits: 1,962
Joe Celko (2/3/2009)
Hey! I teach SQL, not ethics! :)


And here I keep buying your books to wave around in our ethics meetings!
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.


ATB

Charles Kincaid

Post #649324
Posted Wednesday, February 4, 2009 8:21 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 3:19 AM
Points: 4,320, Visits: 6,113
Charles Kincaid (2/3/2009)
Joe Celko (2/3/2009)
Hey! I teach SQL, not ethics! :)


And here I keep buying your books to wave around in our ethics meetings!
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. 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 at GMail
Post #649887
« Prev Topic | Next Topic »

Add to briefcase «««2324252627»»

Permissions Expand / Collapse