April 8, 2009 at 8:25 am
bob.willsie (4/8/2009)
Sounds like a case of way too many programmers with way too much time on their hands...
More like way too many programmers with way too little clue in their heads
April 8, 2009 at 8:40 am
Great article.
The worst I have seen is a Reporting application running in MSDB yes the application's database is MSDB so instead of the Agent using MSDB this application with hundreds of reports are run from MSDB in SQL Server 2000. And you know it was leaking memory the box must be rebooted every hour. And no I could not find out how a reporting application is run from MSDB, very strange. If you know the why I would like to know.
:Whistling:
Kind regards,
Gift Peddie
April 8, 2009 at 8:48 am
How about?
1. Everyone knows that databases don't scale.
2. Everyone knows that SQL Server is slow.
3. We are going to replace the hardware with faster stuff anyway.
4. The optimizer is supposed to figure out the fastest way to run it no matter how I write it.
5. You have to use a cursor or a loop to solve this problem.
6. Primary keys should always be GUIDs.
__________________________________________________
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 8, 2009 at 8:51 am
bob.willsie (4/8/2009)
Sounds like a case of way too many programmers with way too much time on their hands...
I wish i had that problem. I've never been completely satisified with a project at work, but with bosses, deadlines, etc. there's no choice.
Of course this leads to bugs that we later have to fix in a similar amount of time and with irritated (or worse) customers. :crazy:
April 8, 2009 at 8:55 am
I seem to be jumping in with more and more posts on this thread. Apologies; I'm not trying to monopolise, but it's rather an indicator of how this editorial has got me thinking.
One of the assumptions that is incredibly widespread is so common as to be almost invisible. It's the "them and us" assumption, and I think we're all guilty of it at some time or another. The assumption is that "we" are talking sense until proven otherwise, therefore anyone who's not part of "our" group (i.e. "them") should have their viewpoint treated with caution.
The classification of "us" is, of course, hugely variable. "We" might be one particular team, one department, one company, one regional subsidiary, supporters of one football team, one gender, one age group, one religious group. However, it takes quite a bit of self analysis to reduce the number of times we fall into the trap of assuming "we're right and the problem is with everyone else".
Semper in excretia, sumus solum profundum variat
April 8, 2009 at 9:10 am
I'm glad you've finally seen how wrong you are and finally realized how right I am...:-)
April 8, 2009 at 9:12 am
The temptation is to just do a little hard coding for that exception, assuming that this is a one-off situation.
The truly sad thing about this one is that it isn't hard to make exceptions table-driven. I once asked for an extra day to provide a procedure because it was going to be heavily used. The base function could have been done and tested in a couple of hours, but given an extra day it could be made very flexible. Since then, several requests for modifications have been answered with "Just change this parameter to..." or "Add this row to this table." The extra day more than paid for itself.
__________________________________________________
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 8, 2009 at 9:15 am
"all temp tables are bad" and "all sub-queries are bad"
The temp tables thing comes up a lot.
I was once doing some performance tuning for a client and encountered a query that took 32 hours to run. That's ONE query. (I was amazed that they had the perseverance to let it run that long.) The programmer was quite smug that he could get everything done with one query. It was doing a combination of inner and outer joins across a 30 TB database and the query touched literally billions of rows. Billion row tables joining against rows with multiple billions of rows. Needless to say they had some TempDB sizing issues as well as some memory issues. It took me about half a day but I got the execution down under a minute without touching an index simply by breaking the query down into five components using temp tables. The irony is that they weren't happy because "temp tables are bad".
I've run into the "temp tables are bad" thing all over the place. More than once I've demonstrated at various clients that breaking down queries that JOINed more than five tables into smaller units of work is demonstrably faster and uses fewer resources only to have my competence questioned because "temp tables are bad".
It's funny how people can cling to old ideas despite the evidence in front of them.
"Beliefs" get in the way of learning.
April 8, 2009 at 9:33 am
Perhaps people fell into that trap because some programmers don't clean up after themselves. I've come across code that continually created random named temp tables rather than creating and flushing or deleting the same tables.
Also, having taught courses, I know some students only catch half of what gets said. As in "Temp tables are bad..." when what was said as "Temp tables are bad if not used properly."
I'm not a SQL giant, but I know Access DBs have a tendency to grow and not purge themselves of deleted tables. Consequently they require occassional or frequent compacting. I would hope SQL Server recovers space in a better manner...
April 8, 2009 at 9:36 am
On the "temp tables are bad" thing, the coding requirements for the place I work state that table variables should be used instead of temp tables, and that temp tables need to be created first, then populated, if they absolutely must be used. The first is because table variables are in RAM and temp tables are on disk, the second is because of locks on system tables. Both are wrong (it's all SQL 2005, since that does make a difference).
Temp tables did have problems in SQL 7.5. Not really since 2000.
Which leads to one of the really bad assumptions: "If something was ever true, it must be true forever." And a corollary: "I already studied that subject, so I don't need to re-study it, ever."
- 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 8, 2009 at 9:56 am
You mean like SELECT... INTO.... ??
__________________________________________________
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 8, 2009 at 9:59 am
Bob Hovious (4/8/2009)
How about?1. Everyone knows that databases don't scale.
2. Everyone knows that SQL Server is slow.
3. We are going to replace the hardware with faster stuff anyway.
4. The optimizer is supposed to figure out the fastest way to run it no matter how I write it.
5. You have to use a cursor or a loop to solve this problem.
6. Primary keys should always be GUIDs.
6. ... what do you mean that the GUID PK shouldn't be clustered? That's rubbish - why? The PK IS the clustered index, that's what it is .....
April 8, 2009 at 10:03 am
How about the one I got told when I asked if they had any models of the database...
"You don't understand we don't know what the users want when we start to develop, it has to evolve, we don't have time to find the requirements and model anything"
Needless to say it was one of the worst database developments I've ever seen, and I've seen some really really bad ones. Actually when I heard that statement it explained so much!
April 8, 2009 at 10:03 am
Thanks, Andrew 🙂
7. The clustered index should always be on the Primary Key.
__________________________________________________
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 8, 2009 at 10:10 am
Just put everyone in db_datareader role. That's good enough security.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 15 posts - 31 through 45 (of 81 total)
You must be logged in to reply to this topic. Login to reply