April 23, 2009 at 3:47 pm
Since we are sharing some bad code stories, I have to tell you the true and sad story from few years ago. At that time I was working as a production DBA for a big company. One day one of the big managers in the company ordered us to install a new application that the company purchased and paid lots of money for it. Since the whole system was already paid for, not installing it was not an option regardless of how bad it was. After the application was installed and a new database was created, I started up profiler to see what errors will be shown on the server because of the new application and database. To my surprise there were many errors that it generated. Each second we had about 4 or 5 errors about commit transaction or rollback transaction without begin transaction. I spoke with the developers from the company that sold that application and told them about it. I wanted to explain them about the use of @@trancount, but they claimed that they know all about it, but they caused this error deliberately. I was stunt and asked them why. I got the answer that when they started working with SQL Server, they were not sure that it really commits or rollbacks the transaction, so they decided to write it twice. They noticed that they get run time error so they handled it in the application’s code. At first they didn’t want to write commit or rollback once because they still weren’t sure that once is always enough. When they understood that writing it once is enough, they decided that checking the value of @@trancount creates performance problems, so they continue to cause a run time error and deal with it in the application.
By the way if anyone thinks that I made it up, let me tell you, that I wish that I had this kind of imagination and creativity.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 23, 2009 at 4:46 pm
Greg Edwards (4/23/2009)
Bob Hovious (4/23/2009)
My topic would more likely be... Laughable SQLWhy isn't there an SQL equivalent to Iron Chef? That would make for an interesting competition. Instead of a mystery basket of ingredients, the competitors get an inherited database with really bizarre schema, and equally bizarre requirements... then given an hour to code. Judges would rate the solutions on speed, flexibility, innovation, etc.
That sounds like a good 'hands on' interviewing technique.:-D
Book smart or real life expreience would show pretty quickly.
Greg E
I just see if someone can take a direct hit from a high velocity pork chop. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
April 23, 2009 at 4:51 pm
Alvin Ramard (4/23/2009)
GSquared (4/23/2009)
Laughable SQL would be a great topic. Probably quite popular, and potentially quite valuable.Could have examples of some of the really bad cursor solutions as one part, nested recursive CTEs for another, CLR solutions that T-SQL already does a better job of, gross violations of normal form, at least a few minutes on nolock-addiction, and so on.
Kind of a condensed version of http://www.sqldumbass.com and thedailywtf.com, but with real solutions to stupid problems, not just "look at how dumb this guy was!"
hmmmm...Where would one look for examples of bad SQL? (I'm ducking to avoid flying pork chops) 😀
Bob, how about: SQL 2008, the sequel?
Heh... I was going to suggest just looking for grease marks. Seems like I'm in good company... lots of folks have learned to hurl some mighty pork chops backed up with code without getting out of line. 😛
You could also take a look at some intended bad code on the thread for Barry's cursor thread. Lots of folks think cursors and while loops are, ummmm.... "more intuitive".
--Jeff Moden
Change is inevitable... Change for the better is not.
April 23, 2009 at 4:57 pm
Adi Cohn (4/23/2009)
Since we are sharing some bad code stories, I have to tell you the true and sad story from few years ago. At that time I was working as a production DBA for a big company. One day one of the big managers in the company ordered us to install a new application that the company purchased and paid lots of money for it. Since the whole system was already paid for, not installing it was not an option regardless of how bad it was. After the application was installed and a new database was created, I started up profiler to see what errors will be shown on the server because of the new application and database. To my surprise there were many errors that it generated. Each second we had about 4 or 5 errors about commit transaction or rollback transaction without begin transaction. I spoke with the developers from the company that sold that application and told them about it. I wanted to explain them about the use of @@trancount, but they claimed that they know all about it, but they caused this error deliberately. I was stunt and asked them why. I got the answer that when they started working with SQL Server, they were not sure that it really commits or rollbacks the transaction, so they decided to write it twice. They noticed that they get run time error so they handled it in the application’s code. At first they didn’t want to write commit or rollback once because they still weren’t sure that once is always enough. When they understood that writing it once is enough, they decided that checking the value of @@trancount creates performance problems, so they continue to cause a run time error and deal with it in the application.By the way if anyone thinks that I made it up, let me tell you, that I wish that I had this kind of imagination and creativity.
Adi
Heh... I think I know those guys! 😛 Half of them said that cursors were more intuitive on Barry's last article. :hehe:
Wow! That's one of the nastiest cases of true spaghetti code I've ever heard of. Throw it against the wall, then throw the bowl at it to try to get it to stick. I love it.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 23, 2009 at 5:04 pm
GSquared (4/23/2009)
"Bait and Switch".
BWAA-HAA!!! Energy drink my foot! It would be a heck of a name for a marriage counseling service. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
April 23, 2009 at 6:16 pm
Can someone help out here? I started to answer yet another question about catching output from a procedure which builds dynamic SQL. Jeffrey Williams and I both explained to him there's a problem with doing that. It can be done, but is always more trouble than it's worth. So a redesign is in order.
I want to suggest a different approach but, for some reason, I'm really struggling with what he is actually trying to do. English isn't his first language, but I've seen worse. I hate to just leave him hanging until my head clears. Thanks.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 23, 2009 at 7:51 pm
Bob Hovious (4/23/2009)
Can someone help out here? I started to answer yet another question about catching output from a procedure which builds dynamic SQL. Jeffrey Williams and I both explained to him there's a problem with doing that. It can be done, but is always more trouble than it's worth. So a redesign is in order.I want to suggest a different approach but, for some reason, I'm really struggling with what he is actually trying to do. English isn't his first language, but I've seen worse. I hate to just leave him hanging until my head clears. Thanks.
Bob, I think he figured out that building it dynamically was causing problems in not only what he was trying but other processes.
From what I gather - he has changed the process to no longer create the table with dynamic column names and has chosen to 'hide' non-relevant columns in the report itself. I think that is the best approach.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 24, 2009 at 2:14 am
Can someone help out here? I started
My suggestions (for what their worth)
get p2 to retrive column info from syscolumns and bulid dynamic sql to produce temp table of single varchar line for proc p3
or
Get p1 to produce the temp table of single varchar line for proc p3
Far away is close at hand in the images of elsewhere.
Anon.
April 24, 2009 at 11:13 am
Jeff Moden (4/23/2009)
GSquared (4/23/2009)
"Bait and Switch".BWAA-HAA!!! Energy drink my foot! It would be a heck of a name for a marriage counseling service. 😛
If you're going that route, I think "Cold Fusion" might be a bit too appropriate...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 25, 2009 at 5:55 am
@Bob
Did you receive my message?
Greets
Flo
April 25, 2009 at 1:00 pm
Hey Flo:
I haven't seen anything. When, where, and how did you send it? 🙂
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 25, 2009 at 1:28 pm
Florian Reischl (4/25/2009)
The more I learn the more I konw what I do not know
Psst, Flo, there's typo in your sig.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 25, 2009 at 2:39 pm
Bad code stories? Okay, how's this.
Company hired a VB developer to rewrite an application from Delphi to VB, but gave him rein over SQL. The rewrite was supposed to be done in 14 months. He claimed that he was one of the original group of sql consultants that MS had for dealing with customers with sql issues (has anybody ever heard of this before???). He convinced the bosses that he knew what he was doing, and he had no need for any help from me.
One of the features of the application was that for a specific canned report, users would have a set of optional fields that they could use to filter by. Under the original application, most reports would have the entire result set back in under 15 seconds.
About 10 months into the rewrite, testers were going to the boss complaining about the slowness of these reports. The boss finally got tired of the complaints, and asked me to look into it. I talked to the testers to find out what reports were having slowness issues, and just how slow were they. One of the testers told me he would fire up any report, then go to the lunch room and get a cup of coffee. Drink that cup. Get another cup. Drink that. Go to the restroom. Then go back to his desk and wait for the report to finish up. Literally, a half hour or more for a report that used to run in under 15 seconds.
When I investigated what was going on, I found:
1. He was using a common global temp table for all users to store the criteria for the report. But there was nothing in there to separate the different users.
2. For the slowness of the reports, he was copying the tables needed for a specific report to a temp table (he did not add any indexes to them). He would then proceed to delete out the records that did not match the filter criterias. One criteria at a time. (Most of the reports ran off of the three biggest tables in our system.) When he finally finished all of that, he would take the remaining records and put them together to send to the application.
He was gone within a week.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 25, 2009 at 3:00 pm
WayneS (4/25/2009)
Bad code stories? Okay, how's this.Company hired a VB developer to rewrite an application from Delphi to VB, but gave him rein over SQL. The rewrite was supposed to be done in 14 months. He claimed that he was one of the original group of sql consultants that MS had for dealing with customers with sql issues (has anybody ever heard of this before???).
Yeah, it's the CAT, Customer Assistance Team. I've talked with a couple of these guys at PASS. He sure doesn't sound like any of the ones I spoke with.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 25, 2009 at 3:16 pm
WayneS (4/25/2009)
2. For the slowness of the reports, he was copying the tables needed for a specific report to a temp table (he did not add any indexes to them). He would then proceed to delete out the records that did not match the filter criterias. One criteria at a time. (Most of the reports ran off of the three biggest tables in our system.) When he finally finished all of that, he would take the remaining records and put them together to send to the application.
Well, that's a novel way of doing things. Slow, stupid, but novel.
I've seen similar things done, just not as extreme. Load up a temp table from somewhere (often XML). Delete invalid rows and then process the rest
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 3,661 through 3,675 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply