May 8, 2009 at 1:43 pm
user defined functions in WHERE clauses.
Gethyn Elliswww.gethynellis.com
May 8, 2009 at 2:13 pm
It seems like some of you folks had a chance to look at the 3rd-party code I'm struggling with at the moment...
You'll find almost everything that's mentioned already, but within a single proc!
And there's one thing that needs to be added to the list: using table variables where ever possible.
Scenario:
The original proc itself has just a little less than 1.000 lines (when you exclude the lines for documentation purposes, the number of lines will remain exactly the same ...), grabbing data of roughly 10 tables with a few hundred lines each.
The output is a table structure with about 500 lines (note: it's a SP, not a function...).
Inside the proc all subresults are stored in table variables, even if it's the result of a cross join (!) of three or more tables (some internal table vars go as far as a some ten thousand lines...) and, of course, the result is heavily used by other joins later on.
There is no CTE or anything like it. Instead of this there is a cursor at the end of the proc, grabbing each value of an intermediate table var, doing some IF .. ELSE ..IF.. stuff on it together with a "need" while loop in between and inserting the result into yet another table variable having just one column more than the previous one - containing the value calculated in the cursor.
It's mandatory to say that not a single base table has more than the primary key index.
Result: Complains from the end users regarding performance of the new software. (approx. 10 seconds for a spreadsheet with the 500 values as from above...)
That's definitely the worst thing I've ever seen being proposed as a solution from a Software Dev. company, but there's always one more thing that can make it worse... In this case it's the answer from the vendor regarding the performance complaints: "It's state of the art and since you requested SS2K5 together with SSRS and .NET framework there's nothing more you can ask for in terms of performance!"
So, I've been asked to have a look at it without changing anything.
By just modifying the procedure code via SSMS (T-SQL with no cursors, indexed temp tables where required, CTEs instead of repetitive huge joins a.s.o.) it went down from approx. 7 seconds to just a friction of a second to get the stuff done (including the output of the 500 rows in SSMS). The source code after two days is about one third of the original, keeping the code formatting structure. Note: There is no index on a base table yet. I'll keep that for later on...
Did you see anything worse yet?
I also have to say that I've been involved in software projects with programmers that really, really did know what they were doing. I had one project where I needed to schedule an immediate meeting with some of the programmers and I've been told that they cannot participate because they were going to a local SQL user group conference at the same time and they'd like to discuss some of the issues we had within this group. I decided to reschedule my meeting... The results proved me right...
Bringing everything down to the point:
It would be false to claim all programmers guilty by default (which, afaik, didn't happen in this thread). But I do see a reverse correlation between the price for a software project and the resulting code quality.
PS: Thanx for letting me vent. I'm feeling better now!!!
May 8, 2009 at 2:23 pm
As a developer and occasional "accidental DBA" I am intrigued and very interested in these types of topics. In my current role I am a senior developer and work new projects and optimizing existing procedures. I currently have one that we reduced from 14 hours to 4 hours in a "test" or "cert" database but unfortunately the first production run only reduced to 11 hours. This was with DBA input as well.
I mention this because even with the best efforts by developers and DBA's, it sometimes takes a few iterations to really tune a procedure ... especially in a production environment 😉
May 8, 2009 at 3:28 pm
one of the common mistakes of the application side is:
Create / Alter / Drop SQL objects on the fly.
With the implementation of DDL of SQL2005, I had so many confrontations because of it.
Another one is to drop / disable the FK constraints in order to update the parent tables key values, ...... re-create it back or just forget to put it back......
May 8, 2009 at 3:32 pm
I have worked at a software company before and it boils down to 1 or more of the following which I believe all fall on the decission makers at these companies and not so much the developer who is trying to make a living. Thats not to say that some times developers aren't at least partially responsable such as when they convence the decission makers that they can do it all.
1) Ignorance - NOT understanding that a well designed application consists or more then good coding in (insert favorite procedural programming language here)
2) Arrogance - Not caring that a well designed application consists or more then good coding; seeing database design, user interface design & user accessability & understanding of the application as being superfolous.
3) Greed - Being to cheap to do it right; putting of till a later Service Pack/ Relase/Update (especially when its for paid support clients only) what should have been in the initial release
Of these it seems to me like #2 & #3 take the lead (in frequency) with #1 being a distant second in the pack. The market that the vendor caters too seems to also weigh heavily on whcih of these is most prominent. For example vendors who make apps for the more technical markets are far less likely to be Ignorant as Arrogant or Cheap while those vendors catering to the less tech savvy are sometimes as ignorant to what should be done and how as the clients they cater to.
Q: How then can vendors like this sell tehir products and make profit?
A: Unless a vendor has a solid lock on a market (as is the case if there is no competitor) they all know the importance of doing marketing & sales the right/proper way. I bet you won't find a single vendor who sells porrly designed products have their programmers making sales calls or passing out material at conferences.
Kindest Regards,
Just say No to Facebook!May 8, 2009 at 5:26 pm
Dynamic SQL! Why would you do it? Why?? It's because you hate the DBA, isn't it?
On the bright side, the Cloud will magically fix all of these problems for us 🙂
James Stover, McDBA
May 9, 2009 at 9:46 am
I'm surprised this one hasn't been mentioned: inadequate or in appropriate definition of business unique identifiers. This one can go to either extreme.
The one extreme is to use a GUID or incremental integer for a system-generated PKID on every table, but then completely ignore the definition of actual data that makes for a correlating unique identifier. This allows for duplicates in the database even though the PK itself is not duplicated. It usually stems from the developer/dba (often the same person actually) not understanding the business model, or not fully understanding how to implement it in data.
The other extreme (of which I was guilty for many years) is to never use a system-generated key and always used business values for keys. The potential problem with this approach is that concatenated keys will be required, sometimes to many levels, which causes data duplication in many tables, all of which need to be kept up to date. The issues related to changing primary key values and the dependencies on foreign keys are obvious.
I've found the best approach to be:
a) always use a system-generated key (I prefer a sequencer with an integer for human-readable benefit of being able to tell which records were created first, but this is not really of primary concern; using a GUID helps with other features such as replication and, later on, merging with other databases when the inevitable mergers and acquisitions happen),
- and -
b) define an alternate primary key (aka unique index) on the data fields that make up a unique identifier from a business standpoint.
Using this combination allows for the FKs of other tables to refer to the static, non-changing, primary key, while the second index ensures uniqueness at the business level.
And, in the common event that a business-duplicate does get defined and an exception is generated, it forces the data model and business process to undergo more scrutiny because, obviously, something was overlooked the first time around. It causes noise and aggravation, but it's better to get the dust out of the house than to sweep it under the rug where we forget about it. 🙂
May 9, 2009 at 9:53 am
I've edited out the "smart" comment.
Apologies for those that took it the wrong way.
May 10, 2009 at 12:45 pm
Let me start by saying I am a developer and proud of it and have been for the last 10 years. 3 years ago I was thrown in the deep end when a client bought a new server and sql server 2005 and expected me to maintain and administer the database and the server. Since I haven't done DBA work before I had to learn very quickly and had to put some plans in place to do backups and other tasks on the database. It was at this point that I came upon SSC. Now, I have learnt a lot here and got a lot from this web site but there is one thing that stabs me straight to the heart and that is the generalization of developers. I think in any profession you get the people that tries to do their best to do their job very efficient and then you get the buffoons that are just doing the job as quick as they can and do not consider what trouble they cause their others. I have said this before and I will most probably say it again and that is that if you see someone (a developer?) do something that is not good for the database then TRAIN them! I'll bet there is some DBA's out there as well that are doing stuff that other people later on have to battle with.
Now here is a generalization for you. I think DBA's are arrogant!
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
May 10, 2009 at 1:19 pm
Manie Verster (5/10/2009)
...but there is one thing that stabs me straight to the heart and that is the generalization of developers. ...Now here is a generalization for you. I think DBA's are arrogant!
Hi Manie,
I think there's no generalization so far in this thread. It's just a summary of what has been seen from the "bad side of programming". Unfortunately, this looks like it refers to all developers.
But that's definitely not the intention as far as I can see. If you look at my post earlier in this thread I described both programming extremes I've been faced with: the worst AND the best. And, if you look at the worst I described I'm pretty sure you wouldn't consider those developers as being qualified to be called so.
The generalization regarding DBA's you put in your post most probably isn't your true opinion of DBA's rather than it's driven by your current mood - which I can understand to some extent.
Since you've been around this forum for a while I'm sure you have noticed that there are all kind of folks around: DBA's, developers, consultants a.s.o.
Maybe you'd feel better if there'd be a separate thread "Checking up on DBA's" followed by "Checking up on Consultants". Most probably you'd get the same amount of replies with the same scary stories and for the same reason: Nobody is perfect.
So, what I take out of this thread (and would take out of the others if started) is simple: What do I have to look for during my daily business to make the "final product" better for the end user. It's more "mistakes to avoid (focussed on the subject)" than "developers are stupid (focussed on a group of people)".
May 10, 2009 at 2:32 pm
Guys and gals,
As first, I'm a developer. This thread really sounds like a "all developers have no idea about databases"! I read almost all posts and I saw only two people (thanks to Gift Peddie and lmu92!!) who mentioned that there are different people on both sides.
I've seen many of the noticed failures done by developers. Anyway I've seen great solutions in design and implementation done by developers.
I've seen DBAs who had no idea about index maintenance (no joke!) and I've seen great DBAs keeping the system alive and working good. (Glad to have my current DBA 😉 )
Greets
Flo
May 11, 2009 at 12:48 am
lmu92 (5/10/2009)
Manie Verster (5/10/2009)
...but there is one thing that stabs me straight to the heart and that is the generalization of developers. ...Now here is a generalization for you. I think DBA's are arrogant!
Hi Manie,
I think there's no generalization so far in this thread. It's just a summary of what has been seen from the "bad side of programming". Unfortunately, this looks like it refers to all developers.
But that's definitely not the intention as far as I can see. If you look at my post earlier in this thread I described both programming extremes I've been faced with: the worst AND the best. And, if you look at the worst I described I'm pretty sure you wouldn't consider those developers as being qualified to be called so.
The generalization regarding DBA's you put in your post most probably isn't your true opinion of DBA's rather than it's driven by your current mood - which I can understand to some extent.
Since you've been around this forum for a while I'm sure you have noticed that there are all kind of folks around: DBA's, developers, consultants a.s.o.
Maybe you'd feel better if there'd be a separate thread "Checking up on DBA's" followed by "Checking up on Consultants". Most probably you'd get the same amount of replies with the same scary stories and for the same reason: Nobody is perfect.
So, what I take out of this thread (and would take out of the others if started) is simple: What do I have to look for during my daily business to make the "final product" better for the end user. It's more "mistakes to avoid (focussed on the subject)" than "developers are stupid (focussed on a group of people)".
No, you're right, I just get the feeling that developers are getting slaughtered over here. I see blood all over the place and people seem to forget that developers also visits this site and also learn from this site.
Steve, you said that you did not mean to generalize or to degrade developers here (can't remember you exact words) but there are people (DBA's) that log on to this site and generalize and absolutely slaughter the developers. Next time please write a warning on the top of your article stating: "Developers might be slaughtered here. Not for developers to join." Why can't we have articles from which a person can learn or something where no human being will be degraded or slaughtered.
I feel like I might have to salute the next time I walk past a DBA and say: "Sir, Yes Sir!"
Thank you, I have ranted enough here now.
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
May 11, 2009 at 1:29 am
Manie Verster (5/11/2009)
No, you're right, I just get the feeling that developers are getting slaughtered over here. I see blood all over the place and people seem to forget that developers also visits this site and also learn from this site.
Where's the blood? I saw a lot of posts talking about things that developers often forget/ignore about databases (probably because they're not DB specialists). The only insulting generalisation I saw was yours. Maybe I'm just reading the posts with a different point of view.
I'm a developer. Jeff's a developer. Barry, iirc, classifies himself as a developer. There are a lot of developers around here, you're by far not the only one.
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
May 11, 2009 at 2:35 am
GilaMonster (5/11/2009)
Manie Verster (5/11/2009)
No, you're right, I just get the feeling that developers are getting slaughtered over here. I see blood all over the place and people seem to forget that developers also visits this site and also learn from this site.Where's the blood? I saw a lot of posts talking about things that developers often forget/ignore about databases (probably because they're not DB specialists). The only insulting generalisation I saw was yours. Maybe I'm just reading the posts with a different point of view.
I'm a developer. Jeff's a developer. Barry, iirc, classifies himself as a developer. There are a lot of developers around here, you're by far not the only one.
Gila, I think you must take those rose tinted glasses of yours off. Yes, not all the posts was about developers and yes, I agree there is bad developers or developers that forget as you say. I also saw a lot of slaughtering take place. Anyway, I also remember a couple of other developers in this thread that complained. I never see any articles about bad DBA's, no sirree. They are the good guys. The people that so arduosly defend their databases against developers that will come in like viruses and destroy their databases.
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
May 11, 2009 at 9:26 am
I think I mentioned in the editorial that I appreciate what developers do. And I'll welcome a "problem with DBAs" guest editorial if you want to write one.
And I know I make mistakes.
This thread was designed to highlight things that developers commonly do wrong. I'm sure we could have a much longer, and more heated, discussion about what DBAs do wrong.
Viewing 15 posts - 76 through 90 (of 113 total)
You must be logged in to reply to this topic. Login to reply