May 8, 2009 at 9:55 am
As Cade alluded to earlier, and perhaps others I didn't have time to read all the replies, non-clustering indexes can perform better.
On a large table with many columns, a small covering index on the identity/primary key can satisfy numerous queries that have high selectivity faster than the PK.
May 8, 2009 at 10:06 am
varchar PK's!?! If we could only carry guns in the office.
May 8, 2009 at 10:10 am
I know this sounds simplistic, but a few things I found in a third party ERP.
Multi value, multi type fields:
Ex: "30 1LAP0 " Read it and weep! Each character represents either a field value or a flag setting! Spaces are either a missing values or space for more then one character values. :w00t:
Inappropriate field location in a table. I have a field value that has a one to one relationship with a record, yet it's stored in a time stamp table. The result is that the same field value is multiplied over and over again (tens of thousands of instances). :crying:
May 8, 2009 at 10:21 am
This editorial truly frustrated me. I am primarily a developer, but I also have to work with SQL Server with some frequency. I do not claim to be a DBA. I know that I make some mistakes when writing queries and stored procedures, developing Reporting Services reports, and who knows what else. I appreciate the advice that database professionals post on sites like this one. I also realize that there are developers that are reckless when it comes to databases. Calling all developers reckless when it comes to databases is a broad generalization, and, in my opinion, this editorial when completely overboard though.
Either they don't realize that the PK is an index, or they aren't smart enough to know the difference between a CI and an NCI.
Was it necessary to, simply put, call developers stupid if they don't have specific knowledge about database indexes? Is it okay for a developer to call a database professional stupid because he or she doesn't know the intricacies for C# or Java? I don't feel that it is. Thus, I don't feel that it was okay for Steve Jones to call developers to task for not knowing about indexes.
If you're selling a product, I think you ought to know how to tune it for a database. If you don't, I'd like to see some recourse for clients. Maybe some common settlement in a lawsuit. A few of those and I bet you'd have more DBAs hired by software firms.
Really? It's a good idea to introduce litigation that could lose a developer a job and possibly a career? Unless the goal is to make development industry akin to the medical industry where developers and database professionals have to take out insurance to help protect them against litigation, this idea is foolish. If a customer has a problem with the way a product is built, they should take their complaints and ideas to the software company. Let's keep the computing industry as proactive as possible, not a place where we solve minor problems in court.
Once again, I don't mind criticism, but keep it proactive. I don't appreciate anybody tearing me and my profession down. Even if your intentions were to give us developers advice, which I appreciate when helpful, the approach taken in this editorial was poor, and, in my opinion, equated to a very public flaming of developers. Take into consideration the situations that developers are put in. Not every developer has a DBA they can turn to. Some developers get thrown into situations where they are forced to do database work even if they are not qualified for it, and, no matter how much they try, can only gleam so much information from sites like this one. So, cut them some slack when appropriate. You wouldn't expect Lebron James to be able step into a Cleveland Browns uniform and play quarterback. So don't expect developers to be DBAs either.
May 8, 2009 at 10:40 am
As a developer (currently working mainly in T-SQL for backend processes), it's often better to not have a DBA at all than to have one who doesn't understand development or doesn't participate constructively in the database design.
A LOT of DBAs don't know how to write much T-SQL, don't understand the relational model and don't know much about systems architecture - designing for maintenance, product lifecycle, and flexibility. They just handle backups, storage and service packs and escalate performance issues to the vendor or developer or senior DBA. I call these functional DBAs and I see this a lot - especially if there is not a lot of inhouse development. It makes it expecially hard to hire a DBA for a development intensive shop, because you are looking for a lot more experience and exposure in design and architecture - i.e. 5-10 years instead of the always advertised minimum 2-3 years.
In all cases, I assume I will not be able to simply ask a DBA to tune my tables - I build the indexes up front (in addition to the PK and choice of CI) as part of the DB design. I tune the procs to perform with data and adjust indexes as merited.
May 8, 2009 at 11:01 am
IMHO one of the biggest mistakes a development shop can make is failing to tap the power of the database engine by placing all the T-SQL in the application code rather than using stored procedures.
Any my pet peeve is the pervasive myth of stored proc performance due to pre-compiled caching. I don't have easy access to a recent version right now, so I checked it out in BOL for Sql2000 and sure enough:
Search for
cache execution plan
Then scroll down to title "SQL Stored Procedures", topic "SQL Server Architecture". Quoting directly:
SQL Server 2000 and SQL Server version 7.0 incorporate a number of changes to statement processing that extend many of the performance benefits of stored procedures to all SQL statements. SQL Server 2000 and SQL Server 7.0 do not save a partially compiled plan for stored procedures when they are created. A stored procedure is compiled at execution time, like any other Transact-SQL statement. SQL Server 2000 and SQL Server 7.0 retain execution plans for all SQL statements in the procedure cache, not just stored procedure execution plans.
To see the content of your cache execute:
SELECT text, plan_handle, * FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp
(Connor Cuningham's older blogs are down right now so I can't make any more precise attributions or refinements to the query here)
If you use parameterized sql in your application code, you will see it in the cache and know that the "power of the database engine" is indeed at work. You will also note a distinct lack of stored proc code from less frequently used procedures, validating the BOL quote above.
May 8, 2009 at 11:02 am
...and another one. No defaults for mandatory fields.
You're going along, adding values to a form. Click on the next page. Add a value to a field that you own. Hit "F2-Save" and bang, you're stuck with a "Field xxx requires a value..." error on a field you don't own. You can simply select something, go off and find the person who owns the field and ask what to add or hit "Esc" which wipes out your session.
May 8, 2009 at 11:03 am
gjohnson (5/8/2009)
This editorial truly frustrated me.
Apologies, but I don't believe I called all developers reckless. Some of this was in fun, and you might step back and realize I said I appreciated developers' efforts, and this was an attempt to find out what things they need to learn, or that someone can help them learn better.
Was it necessary to, simply put, call developers stupid if they don't have specific knowledge about database indexes?
I didn't call them stupid, though saying they aren't smart isn't much better. I should have said "ignorant" of the difference. and you
As far as litigation, I'm don't know what to do here. Sales misleads customers often, and when things don't perform as they are advertised what is the alternative? Complain? Too many companies don't do anything about that and once you've installed a product you can
a) re-tune some things, and possibly violate a support agreement
b) live with it
c) litigate
I don't want to see a lot of litigation, but many companies do take advantage of customers and it's not as cut and dried as "just buy something else" A little liability (little with limits) might help. Lots of developers throw things together, sometimes because they're not good at things, sometimes because they're pressured to do so.
I didn't tear down all developers. I thought I was being proactive, and dozens of people before you seem to have taken it that way. Perhaps you ought to re-examine how this struck you.
May 8, 2009 at 11:17 am
Shane Petroff (5/8/2009)
IMHO one of the biggest mistakes a development shop can make is failing to tap the power of the database engine by placing all the T-SQL in the application code rather than using stored procedures.
Any my pet peeve is the pervasive myth of stored proc performance due to pre-compiled caching.
I totally agree, and it detracts from the primary good reasons to use SPs, which is control, design and maintenance.
Control, because you can use execute permissions to control access.
Design, because SPs can be used to structure an interface layer and can be used from within each other to avoid redundancy
Maintenance (by design), because SPs can be maintained independently of the application and that abstraction layer is a great place to provide consistent changes to behavior which can be applied in a single place.
May 8, 2009 at 11:50 am
I will agree with some of the statements about litigation but not litigation purely because of performance of the product. The issue may begin with the performance of the product due to poor database interaction but when it gets to an appropriate time for litigation, the issue should have grown to more than just a technical one.
If the (3rd party) product does not meet the stated performance abilities and the company does not address the issues when brought up thru the appropriate channels (support -> managers -> executives), then litigation may be a last resort but (to me) the issue then is more about the company being unresponsive and misrepresenting the product than simply bad database indexing.
A company should stand behind the product and the people building it. Additionally, if litigation is involved, I would hope it is against the company and not the developers. Hopefully, if an issue gets to the point of litigation, they will not just turn around and berate the developers for their choices but look at why those database choices had to be made (no DBA to consult with, did not know that they would need a DBA to consult with, too cheap to hire a DBA or developers with good database skills, etc.)
May 8, 2009 at 12:02 pm
I agree with you completely about litigation.
As far as litigation, I'm don't know what to do here. Sales misleads customers often, and when things don't perform as they are advertised what is the alternative? Complain? Too many companies don't do anything about that and once you've installed a product you can
a) re-tune some things, and possibly violate a support agreement
b) live with it
c) litigate
In these cases, where sales misleads customers, complaints about a product are ignored, and general attempts at mitigating issues with the product are ignored, then litigation is probably the only choice left. From the original editorial, I took what you were saying completely differently in regard to litigation.
Apologies, but I don't believe I called all developers reckless.
No, you did not. I did. Some developers are reckless. Some developers will completely mess up tasks related to SQL because they don't care that they are doing them incorrectly.
I do appreciate the advice given on this topic. I want to know when I'm doing something incorrectly. I don't want to mess up a database because I'm ignorant of how to do things correctly. Topics like this are useful to me. A few key comments in your editorial seemed a bit strong or not fleshed out enough. That was what incited my previous post, not the topic itself.
May 8, 2009 at 12:04 pm
Both sides are full of crap the developer thinks the database is storage to the application so data is dumped into the database. The DBA says give the developer read only permissions. These are the reason for about 80 to 90 percent of software development and maintenance problem because both are not in touch with what is needed to develop scalable application. The DBA needs read only permissions to databases and the developer needs to see users timeout while accessing the storage database.
I remember telling a bank IT security team to call the bank VP they have decided to close his online banking for security reasons. The reason these security people decided the Agent account proxy account should be disabled, one pesky problem the Agent populates online banking SQL Server with DB2 AS400 deposits.
Third party application problems either corruption or control problems, IT control is impeding other department needs for tools and IT departments buying applications to keep someone's brother in-law employed.
😉
Kind regards,
Gift Peddie
May 8, 2009 at 12:16 pm
"You wouldn't expect Lebron James to be able step into a Cleveland Browns uniform and play quarterback."
Actually He does play for the Cleveland Browns...... in a commercial in his dreams 😀
Seriously, I was a bit surprised when I saw Steve's quote about developers not knowing the difference between a PK and NCI. I also figured it was worded differently; that it wasn't meant to be derogatory.
ken
May 8, 2009 at 12:30 pm
How about these little gems:
INSERT INTO some_table
SELECT * FROM some_other_table
Or this method of calling procs that I have found in embedded far too many applications:
EXEC dbo.some_proc 'parameter1', 'parameter2', 0, NULL, 'parameter5'
Add an extra parameter in the middle of the parameter list of the proc and you get some interesting results...
Lastly, it seems that quite a few developers just don't want to listen to us DBAs!!
May 8, 2009 at 1:37 pm
To add to the list, inappropriate domains and inappropriate physical representation of domains is a common design flaw. A case in point is the msdb jobhistory table.
Inappropriate domain is a problem with column run_status, which has possible values of 0 = Failed, 1 = Succeeded, 2 = Retry, 3 = Canceled, and 4 = In progress. That is at least three different pieces of information in one column - Executing (a Boolean), Executing Status (many values), Successful Completion (a Boolean) and Successful Completion Reason (Failed or Cancelled).
Inappropriate physical representation of domains is most famously known as the Y2K problem and can be seen in these three columns:
run_date int NOT NULL ,
run_time int NOT NULL ,
run_duration int NOT NULL ,
Column run_date has an internal format of CCYYMMDD
Column run_time has an internal format of HHMMSS
column run_duration has an internal format of HHMMSS
Here is Microsoft's description on how to calculate the end time:
CONVERT(DATETIME, RTRIM(run_date))
+ ((run_time/10000 * 3600)
+ ((run_time%10000)/100*60) + (run_time%10000)%100) / (86399.9964 )
+ ((run_duration/10000 * 3600) + ((run_duration%10000)/100*60) + (run_duration%10000)%100 ) / (86399.9964 )
AS End_DateTime
This would be a lot simpler with 2 columns:
run_timestamp datetime
run_duration_seconds integer (for 10 hours, a value of 3600 would be stored).
SQL = Scarcely Qualifies as a Language
Viewing 15 posts - 61 through 75 (of 113 total)
You must be logged in to reply to this topic. Login to reply