October 13, 2017 at 5:15 pm
Comments posted to this topic are about the item Timing is Everything
Best wishes,
Phil Factor
October 13, 2017 at 5:15 pm
Nice one.
Just make sure when you're doing timings, use that million row table, not the ten row one from your dev system.
October 13, 2017 at 8:22 pm
Steve Jones - SSC Editor - Friday, October 13, 2017 5:15 PMNice one.Just make sure when you're doing timings, use that million row table, not the ten row one from your dev system.
😉
--Jeff Moden
Change is inevitable... Change for the better is not.
October 13, 2017 at 8:23 pm
Good article, Phil. I also wish that people would learn that "Set Based" <> "All In One Query".
--Jeff Moden
Change is inevitable... Change for the better is not.
October 14, 2017 at 7:48 am
Thanks for the article. It's good advice, as is the "make sure you use the million row table". I've made the eliminate joins mistake Phil refers to myself, and I've had to argue against it too.Nothing beats real, representative measurements. Chemistry (and Chemical Engineering, a past career) use the concept of a "rate limiting step". The concept moves across to computing quite well. In any process there is always a "rate limiting step". You need to know what the "rate limiting step" is and control that. Doing anything else is at best a waste of time and effort. In some cases (like the kind of thing Phil refers to) jumping to conclusions and changing other things can move the problem elsewhere and make things worse too.
Tom Gillies LinkedIn Profilewww.DuhallowGreyGeek.com[/url]
October 14, 2017 at 8:03 am
I've worked with consultants who would want to repeat columns in different tables "to reduce the joins that would be necessary." In the beginning I'd go along with some of these in the interest of compromise and teamwork. As I lived with the results and they didn't, I became much more adamant that any denormalization would be done only for significant performance purposes. That happened in only one or two times.
October 16, 2017 at 1:40 am
I think it may have been Phil Factor who said that a lot of the performance criticisms Devs make about SQL Server make no sense until you realise they are extrapolating from their experiences with MySQL.
A worrying number once wrote a bodged app to execute a dodgy query on a poor schema on a flaky install of MySQL on inadequate hardware and said "BEHOLD! Relational technology is not fit for purpose, I have PROOF"!
Remember, before you can denormalise something it has to be normalised. Un-normalised and denormalised are two radically different things.
October 16, 2017 at 7:48 am
That's the thing about attending .NET related developer conferences, when the topic of SQL coding comes up, we will often find ourselves shaking our head.
Abstracting the complexity of your SQL behind views and functions may save the developers a few key strokes and shorten the lines of code, but somebody (like the DBA) will be spending a LOT more time and keystrokes replying to emails from users complaining that the database is running slower. Personally, I'd rather just code my SQL to join and return only what's needed in the most efficient way. That ultimately saves everyone time and trouble.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 16, 2017 at 9:39 am
David.Poole - Monday, October 16, 2017 1:40 AMI think it may have been Phil Factor who said that a lot of the performance criticisms Devs make about SQL Server make no sense until you realise they are extrapolating from their experiences with MySQL.A worrying number once wrote a bodged app to execute a dodgy query on a poor schema on a flaky install of MySQL on inadequate hardware and said "BEHOLD! Relational technology is not fit for purpose, I have PROOF"!
Remember, before you can denormalise something it has to be normalised. Un-normalised and denormalised are two radically different things.
And then when you point out to them, " ... aye, but MySQL isn't an RDBMS ... "
THAT gets some interesting responses, as well as the usual "IT HAS TO BE!!! IT USES SQL!!!!"
So do Excel and IDMS-X
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
October 16, 2017 at 9:49 am
It's real simple, in most cases. I just ask them how to get the current date and time in SQL Server. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
October 16, 2017 at 10:55 am
Too many joins can make a query perform poorly and it can help to break down an extremely large query, especially if some of them are filtering logic joins and others are data presentation joins. For some reason, SQL Server will sometimes execute the joins in a poor order. If the statistics aren't the problem, then it's time to break it down since we know which joins are needed for which logic.
Of course, if all the presenter was doing was using views and functions, they really didn't eliminate any joins, just hid them from plain sight. 😉 Don't get me wrong, I think views and inline table vauled functions are a great code reuse mechanism, when used sparingly.
October 16, 2017 at 11:18 am
I'm convinced that inappropriate boundaries are the cause of life's woes. It doesn't matter if it's geography, technology, age, gender, religion or something else entirely. It is too easy to setup boundaries by accident.
I'm reaching the conclusion that managers should review their decisions and the behaviour of their subordinates and ask "have inappropriate boundaries been created".
October 19, 2017 at 6:58 pm
David.Poole - Monday, October 16, 2017 11:18 AMI'm convinced that inappropriate boundaries are the cause of life's woes. It doesn't matter if it's geography, technology, age, gender, religion or something else entirely. It is too easy to setup boundaries by accident.
I'm reaching the conclusion that managers should review their decisions and the behaviour of their subordinates and ask "have inappropriate boundaries been created".
Yes they should ask that question. And when they so they should also ask "have appropriate values been left uncreated. The first few times they'll get the answer yes to both questions the should eliminate the detected inappropriate boundaries and create the approriate boundaries discovered to be missing, and after a while (perhaps 4 weeks) repeat that question and fix process, and then repeat again and again. This will gradually increase both productivity and customer satisfaction while improving product stability. Once a reasonable level of team effectivenes is reached this process should still be repeated, but at a reduced frequency - perhaps twice a year.
But I don't think getting the boundaries wrong is the cause of all life's woes, just of some of them. So that's not the only question managers should be asking themselves (and their subordinates). They should also be asking whether the resources available are sufficient to achieve the imposed objectives (and are people pretending that the objectives have been agreed when in fact they have been imposed) and whether the imposed objectives actually make sense, and if the either or both answers are "no" they should try to find a way to fix that problem too; that too is somthing that should be reviewed regularly.
Finally I'll leap onto my hobby horse (as this is a great opportunity): one of the most difficult to fix causes of inappropriate boundaries is over-specialisation, and managers should do their best to ensure that their people understand that and don't overspecialise, in particular don't they specialise to the extent that important things are no-one's problem because they are outside each and every person's specialty and then sulk when told to extend their boundaries to cover what are essential parts of their jobs.
Tom
October 19, 2017 at 9:31 pm
Steve Jones - SSC Editor - Friday, October 13, 2017 5:15 PMNice one.Just make sure when you're doing timings, use that million row table, not the ten row one from your dev system.
And don't forget to make sure that a million rows are enough! I just play with data now, sometimes looking at interesting mathematical patterns for which I generate examples (because I'm still a mathematician, despite 42 years as a computer scientist/engineer/IT person), and one of the things I have going reached 24,075,007 rows last time I found time to do anything with it, and will grow as and when I find time to move it forwards
Tom
October 20, 2017 at 2:03 am
TomThomson - Thursday, October 19, 2017 6:58 PM...Finally I'll leap onto my hobby horse (as this is a great opportunity): one of the most difficult to fix causes of inappropriate boundaries is over-specialisation, and managers should do their best to ensure that their people understand that and don't overspecialise, in particular don't they specialise to the extent that important things are no-one's problem because they are outside each and every person's specialty and then sulk when told to extend their boundaries to cover what are essential parts of their jobs.
That's a good point. I suppose that these are some of the roles of "proper" management: making sure that everyone knows what they should be doing, and making sure that all the things that need to be done are being done by someone, even if that means extending the scope of someone's responsibilities.
On the subject of "overspecialisation", in an ideal world a Specialist should know enough about what the other guy is doing to appreciate why it is being done. The opposite of that is part of what you term "overspecialisation", and one of the results of that is that important things get left undone, because nobody recognises what is outside their own specialist area.
Writing it down, you're right - overspecialisation is a current problem. I thought we had outgrown "demarcation disputes" - clearly not. They have simply resurfaced in a different form.
Tom Gillies LinkedIn Profilewww.DuhallowGreyGeek.com[/url]
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply