The best DBA of all time
-
So we're talking to the customer's DBA about how we've managed to improve certain queries on our system by a careful analysis of SQL Server usage and by adding indexes to a couple of database columns, removing indexes from others. The customer DBA looks very thoughtful at this and comes back a few days later with the air of a man with a plan. "I've been thinking about it" (worried silence from all) "I know how we can improve performance. Put an index of EVERY column of EVERY table." Politely nod. Sigh. Try to work out how best to let him down gently. Then wonder why he's getting paid three times what I am. Anyone else get this sort of thing?
My Blog: http://www.dwmkerr.com
-
I worked at a place that had a static database that was reloaded every night. (For details it was a tax law document library) They wanted to index every document to each other for faster retrieval times, hopefully to sell more access to those books. Problem was, the projected time to rebuild these expanded indexes was over 24 hours. See above on how the database was reloaded nightly, and I think you'll see the problem.
Psychosis at 10 Film at 11 Those who do not remember the past, are doomed to repeat it. Those who do not remember the past, cannot build upon it.
That's sort of like the daily backup that takes 28 hours.
-
So we're talking to the customer's DBA about how we've managed to improve certain queries on our system by a careful analysis of SQL Server usage and by adding indexes to a couple of database columns, removing indexes from others. The customer DBA looks very thoughtful at this and comes back a few days later with the air of a man with a plan. "I've been thinking about it" (worried silence from all) "I know how we can improve performance. Put an index of EVERY column of EVERY table." Politely nod. Sigh. Try to work out how best to let him down gently. Then wonder why he's getting paid three times what I am. Anyone else get this sort of thing?
My Blog: http://www.dwmkerr.com
Hes not a DBA then, or he is a dba in training... its a common mistake, and usually and honest one... however... he could be right if there are less than 5 columns and the RDBMS does not allow clustering of columns in the indexes... overall though you are correct, this is usually a terrible idea. The maintenance on this would be bad even for a small table and wasting CPU cycles (I know most everyone does not code for CPU inefficiency in distributed systems, unless the cycles are above NN% or NNN% for Mutliproc)... Not to mention that an insert update or delete would be very much drawn out longer than it needed to be. A rule of thumb for MOST major RDBMS (DB2, Oracle, MSSQL, Mysql) no greater than 5 indexes per table. IF you stick to this and follow your rules for using multicolumn indexes, High Cardinalyty and some very basic rules for SQL writing (group instead of sortby, Predicate order, etc...) you can very efficiently make your code SING!
-
Hes not a DBA then, or he is a dba in training... its a common mistake, and usually and honest one... however... he could be right if there are less than 5 columns and the RDBMS does not allow clustering of columns in the indexes... overall though you are correct, this is usually a terrible idea. The maintenance on this would be bad even for a small table and wasting CPU cycles (I know most everyone does not code for CPU inefficiency in distributed systems, unless the cycles are above NN% or NNN% for Mutliproc)... Not to mention that an insert update or delete would be very much drawn out longer than it needed to be. A rule of thumb for MOST major RDBMS (DB2, Oracle, MSSQL, Mysql) no greater than 5 indexes per table. IF you stick to this and follow your rules for using multicolumn indexes, High Cardinalyty and some very basic rules for SQL writing (group instead of sortby, Predicate order, etc...) you can very efficiently make your code SING!
Good user name! :thumbsup:
_____________________________ Give a man a mug, he drinks for a day. Teach a man to mug... The difference between an ostrich and the average voter is where they stick their heads.
-
I worked at a place that had a static database that was reloaded every night. (For details it was a tax law document library) They wanted to index every document to each other for faster retrieval times, hopefully to sell more access to those books. Problem was, the projected time to rebuild these expanded indexes was over 24 hours. See above on how the database was reloaded nightly, and I think you'll see the problem.
Psychosis at 10 Film at 11 Those who do not remember the past, are doomed to repeat it. Those who do not remember the past, cannot build upon it.
I've found the solution to this problem is to remove the indexing, do the reload and then reapply the indexing. I guess it depends on the amount of indexing involved but I remember one process that took a similar amount of time was cut to about 20 minutes. Indexing can really hammer data entry.
"You get that on the big jobs."
-
So we're talking to the customer's DBA about how we've managed to improve certain queries on our system by a careful analysis of SQL Server usage and by adding indexes to a couple of database columns, removing indexes from others. The customer DBA looks very thoughtful at this and comes back a few days later with the air of a man with a plan. "I've been thinking about it" (worried silence from all) "I know how we can improve performance. Put an index of EVERY column of EVERY table." Politely nod. Sigh. Try to work out how best to let him down gently. Then wonder why he's getting paid three times what I am. Anyone else get this sort of thing?
My Blog: http://www.dwmkerr.com
Perhaps you've missed my story on the head of IT that couldn't log in[^]? Yeah, I definitly get that kind of stuff :laugh:
It's an OO world.
public class Naerling : Lazy<Person>{
public void DoWork(){ throw new NotImplementedException(); }
} -
If you're concerned about this, you clearly have either a) too many tables, b) too many columns in your tables or c) too much data. After indexing, start deleting columns/tables until performance improves.
-
That's sort of like the daily backup that takes 28 hours.
I gave a suggestion to remove functions that produce a constant in the where clause. With an example that cut the query from 13 seconds to 30 milliseconds. The group proceeded to remove every function they could from where clauses. I asked about removing GETUTCDATE in the maintenance apps, since it didn't produce a constant value. "Since it was a daily cleanup job, the few remaining rows will be picked up the next day." That made emminent sense and since the idividual queries would run quicker, it went into production. Most of the jobs did run faster with no problem and all of them ran faster at first. Didn't pay attention to the jobs, as soon as one went over 24 hours, the next daily job didn't submit. The next job was 48 hours out of date and fairly quickly reached a running time of a week. Since the constant value query still made good sense, we analysed the queries, figured out the inefficiencies in the queries, fixed that as well. (In one case, the datetime field used to delete was indexed. Scratched my head, realized what was going on, forced the query to ignore that field for looking up records and increased loop speed 50 times.) Anyway, even good ideas can have bad consequences. The reason it worked OK with the slower query... If it ran for 16 hours, the next run only had 8 hours to fix. With daily and weekend slowdowns it remained stable.
-
Perhaps you've missed my story on the head of IT that couldn't log in[^]? Yeah, I definitly get that kind of stuff :laugh:
It's an OO world.
public class Naerling : Lazy<Person>{
public void DoWork(){ throw new NotImplementedException(); }
}It was that story that reminded me and made me post!!!
My Blog: www.dwmkerr.com My Charity: Children's Homes Nepal
-
It was that story that reminded me and made me post!!!
My Blog: www.dwmkerr.com My Charity: Children's Homes Nepal
That's great! :laugh:
It's an OO world.
public class Naerling : Lazy<Person>{
public void DoWork(){ throw new NotImplementedException(); }
}
