April 22, 2018 at 9:54 am
Sergiy - Saturday, April 21, 2018 9:21 PMpatrickmcginnis59 10839 - Thursday, April 19, 2018 9:16 AMTheres a reason for instance that Excel or Word doesn't store their data in a relational database, because spreadsheets would then take forever to recalculate, and word documents would take forever to repaginate. Obviously these are extreme examples but they illustrate the mismatch very well. Designing arbitrary data structures and serializing them are often not thought of in terms of rows, tables and constraints.Are you sure?
The source code of this web page (before I added my comment) contains 358k characters (including spaces), which makes up nearly 22k words, 136 pages.
With the size of the whole database < 0.5 MB I don't know how bad must be SQL code to do repagination slower than it happens in MS Word.As for Excel.
Let's create a simple spreadsheet:
Put "1" into A1 and "=A1+A$1" into A2.
Copy A2 to all the cells below, up to the last one: 1048576.
Then, if I change the value in A1 from 1 to 4, it takes about 2 seconds to recalculate the spreadsheet on my laptop.Now, I create a similar table in SQL Server:
create table #Temp (
Value decimal(19,4)
)
insert into #Temp
select N
from dbo.TallyGenerator(1,null, 1048576, 1)And now let's do recalculation:
set statistics time on
select TOP 200 T1.Value + T2.Value Result
from #Temp T1
cross apply (select top 1 Value from #Temp order by Value) T2
where T1.Value < 100000
order by Result descWHERE clause together with TOP emulates viewing a specific range of rows in Excel.
SQL Server Execution Times:
CPU time = 548 ms, elapsed time = 159 ms.When it comes to saving the data, It took about 4 seconds for Excel to save the spreadsheet.
And it took <1 second to run this code:select T1.Value + T2.Value Result
INTO #Temp2
from #Temp T1
cross apply (select top 1 Value from #Temp order by Value) T2
order by Result
checkpoint
same laptop, same hard drive.You can compare performance of reading the data from disk for yourself.
As you can see, Excel data format does not give any advantage in terms of performance of data manipulations.
SQL Server is times faster in every aspect.
what column is the formula in?
I don't have any doubt that SQL can perform calculations in a set based fashion faster than excel, my point is more that excel has not chosen to put the back end in rows and columns on SQL Server for a reason and I suspect that given the completely arbitrary nature of the things you're allowed to do with Excel, implementation with SQL Server would be interesting to say the least.
I'm sort of surprised that you aren't considering this but it might be the case that more people haven't used spreadsheets than I initially assumed. (Or seen some examples from folks who routinely make heavy use of them.)
We don't have to really beat this subject up though, just google this phrase:
"why don't spreadsheets store their data in relational databases"
April 22, 2018 at 2:57 pm
Jason A. Long - Thursday, April 19, 2018 11:12 PMKeven Boles is far more eloquent than I am, so I'll just leave this here... Know What Your Code is Doing to SQL Server!
That's funny, he could have been in my place right now. But he left the company some months before I joined it.
April 23, 2018 at 1:49 am
why don't spreadsheets store their data in relational databases
Better question:
Why Windows registry does not store it's data in relational database, considering Windows has SQL Server 2005 inbuilt in the system since XP, if I'm not mistaken.
Especially considering persistent performance and consistency issues caused by that horrible data storage format.
_____________
Code for TallyGenerator
May 5, 2018 at 6:44 am
This gave me a pretty good laugh because in real Scrum there are no project managers.
That's why you have scrum meetings. And a project manager.
It all depends on what the optimizing goals are for the architecture. Saddling developers with external dependencies like having to wait for a proc to be developed by someone else or even send it to someone else for revisions before it can be deployed is an impediment (in Scrum terms) and as we look for ways to help the team go faster we look to remove impediments.
Logic follows:
1 Procs require specialized skills or are somehow slowing the team down
2 Some critical mass of decision makers decide Procs are an impediment
3 Decision makers decide on an architecture that does not include Procs
The proof is not in the Procs worth it’s in their ability to be part of an agile development process. Have you looked into Visual Studio Database Projects or redgate ReadyRoll? Change or be phased out. Those are your options.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 7, 2018 at 6:56 am
Orlando Colamatteo - Saturday, May 5, 2018 6:44 AMThis gave me a pretty good laugh because in real Scrum there are no project managers. That's why you have scrum meetings. And a project manager.It all depends on what the optimizing goals are for the architecture. Saddling developers with external dependencies like having to wait for a proc to be developed by someone else or even send it to someone else for revisions before it can be deployed is an impediment (in Scrum terms) and as we look for ways to help the team go faster we look to remove impediments.Logic follows:1 Procs require specialized skills or are somehow slowing the team down2 Some critical mass of decision makers decide Procs are an impediment3 Decision makers decide on an architecture that does not include Procs The proof is not in the Procs worth it’s in their ability to be part of an agile development process. Have you looked into Visual Studio Database Projects or redgate ReadyRoll? Change or be phased out. Those are your options.
I can only conclude that someone forgot about the rather strong and CRITICAL need to manage the database, or that somehow, databases are things to be played with instead of properly cared for. You don't abandon stored procedures because you have developers that don't know how to write them. You either train the ones you have to be able to properly code stored procedures, hire better developers to begin with, or suffer consequences. Your choice... And while agile/scrum advocates are prone to whining about the costs of such things, I simply explain that if you're worried about costs, why did you hire such poorly skilled developers in the first place? Or how about the cost of the agile infrastructure because they continue to perceive the database as an impediment? Things tend to get real quiet whenever I bring that topic up. It's as if they just don't consider a database an engine that drives the business. The level of incompetence that kind of thinking represents is extraordinary. I basically go back to "If you think professionals are expensive, wait until you hire an amateur" (Red Adair).
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 7, 2018 at 7:34 am
sgmunson - Monday, May 7, 2018 6:56 AMOrlando Colamatteo - Saturday, May 5, 2018 6:44 AMThis gave me a pretty good laugh because in real Scrum there are no project managers. That's why you have scrum meetings. And a project manager.It all depends on what the optimizing goals are for the architecture. Saddling developers with external dependencies like having to wait for a proc to be developed by someone else or even send it to someone else for revisions before it can be deployed is an impediment (in Scrum terms) and as we look for ways to help the team go faster we look to remove impediments.Logic follows:1 Procs require specialized skills or are somehow slowing the team down2 Some critical mass of decision makers decide Procs are an impediment3 Decision makers decide on an architecture that does not include Procs The proof is not in the Procs worth it’s in their ability to be part of an agile development process. Have you looked into Visual Studio Database Projects or redgate ReadyRoll? Change or be phased out. Those are your options.I can only conclude that someone forgot about the rather strong and CRITICAL need to manage the database, or that somehow, databases are things to be played with instead of properly cared for. You don't abandon stored procedures because you have developers that don't know how to write them. You either train the ones you have to be able to properly code stored procedures, hire better developers to begin with, or suffer consequences. Your choice... And while agile/scrum advocates are prone to whining about the costs of such things, I simply explain that if you're worried about costs, why did you hire such poorly skilled developers in the first place? Or how about the cost of the agile infrastructure because they continue to perceive the database as an impediment? Things tend to get real quiet whenever I bring that topic up. It's as if they just don't consider a database an engine that drives the business. The level of incompetence that kind of thinking represents is extraordinary. I basically go back to "If you think professionals are expensive, wait until you hire an amateur" (Red Adair).
Heh.... you beat me to it with the Red Adair quote. One of my favorite quotes is on of my own making... "If you want it real bad, you usually get it that way". 😀
Also, one of the reasons why I don't like pure Agile environments is because Project Managers are supposedly "not needed". One of the things that people don't realize is that a good Project Manager will not only help keep the current sprints on track but they also serve the huge and greatly needed functionality of being an enabler. If you need something to get the job done, a good Project Manager will go into the mode of pounding on people that can provide the correct resources, physical or otherwise, which prevents Developers from having to suffer such distractions.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 7, 2018 at 7:41 am
Orlando Colamatteo - Saturday, May 5, 2018 6:44 AMThis gave me a pretty good laugh because in real Scrum there are no project managers. That's why you have scrum meetings. And a project manager.It all depends on what the optimizing goals are for the architecture. Saddling developers with external dependencies like having to wait for a proc to be developed by someone else or even send it to someone else for revisions before it can be deployed is an impediment (in Scrum terms) and as we look for ways to help the team go faster we look to remove impediments.Logic follows:1 Procs require specialized skills or are somehow slowing the team down2 Some critical mass of decision makers decide Procs are an impediment3 Decision makers decide on an architecture that does not include Procs The proof is not in the Procs worth it’s in their ability to be part of an agile development process. Have you looked into Visual Studio Database Projects or redgate ReadyRoll? Change or be phased out. Those are your options.
First of all, most stored procedures should be written by the developers and shouldn't wait for someone else to complete them.
Second, every single piece of code should be reviewed by someone else before even getting to QA testing.
If people really believe that one hour to review a piece of code is slowing down development, I hope they realize the amount of work that will need to be done to fix all the technical debt. Right now, we're having issues with a log table clustered on a GUID that is filling the log when rebuilding it. The excuse is that it was designed by developers and they didn't know better. That could have been prevented if the table design was reviewed by someone that actually knows how indexing works, but as you say, it would have been an impediment to ask for someone else to review it (that was sarcasm).
May 7, 2018 at 7:49 am
Orlando Colamatteo - Saturday, May 5, 2018 6:44 AMThis gave me a pretty good laugh because in real Scrum there are no project managers. That's why you have scrum meetings. And a project manager.It all depends on what the optimizing goals are for the architecture. Saddling developers with external dependencies like having to wait for a proc to be developed by someone else or even send it to someone else for revisions before it can be deployed is an impediment (in Scrum terms) and as we look for ways to help the team go faster we look to remove impediments.Logic follows:1 Procs require specialized skills or are somehow slowing the team down2 Some critical mass of decision makers decide Procs are an impediment3 Decision makers decide on an architecture that does not include Procs The proof is not in the Procs worth it’s in their ability to be part of an agile development process. Have you looked into Visual Studio Database Projects or redgate ReadyRoll? Change or be phased out. Those are your options.
Ouch!!! This is almost the exact path of decision making that the development manager made at my wife's company. They had all these stored procedures written a LONG time ago. They still have *= joins all over the place to give you an indication (and they can't upgrade past sql 2005 since that join syntax is no longer valid starting in sql 2008). They have moved all of their sql code into the application layer on purpose because they are convinced that stored procedures are slower to execute and harder to maintain. She is a scrum product owner and has been moved to another team that is in even worse shape.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 7, 2018 at 9:25 am
Jeff Moden - Monday, May 7, 2018 7:34 AMsgmunson - Monday, May 7, 2018 6:56 AMI can only conclude that someone forgot about the rather strong and CRITICAL need to manage the database, or that somehow, databases are things to be played with instead of properly cared for. You don't abandon stored procedures because you have developers that don't know how to write them. You either train the ones you have to be able to properly code stored procedures, hire better developers to begin with, or suffer consequences. Your choice... And while agile/scrum advocates are prone to whining about the costs of such things, I simply explain that if you're worried about costs, why did you hire such poorly skilled developers in the first place? Or how about the cost of the agile infrastructure because they continue to perceive the database as an impediment? Things tend to get real quiet whenever I bring that topic up. It's as if they just don't consider a database an engine that drives the business. The level of incompetence that kind of thinking represents is extraordinary. I basically go back to "If you think professionals are expensive, wait until you hire an amateur" (Red Adair).Heh.... you beat me to it with the Red Adair quote. One of my favorite quotes is on of my own making... "If you want it real bad, you usually get it that way". 😀
Also, one of the reasons why I don't like pure Agile environments is because Project Managers are supposedly "not needed". One of the things that people don't realize is that a good Project Manager will not only help keep the current sprints on track but they also serve the huge and greatly needed functionality of being an enabler. If you need something to get the job done, a good Project Manager will go into the mode of pounding on people that can provide the correct resources, physical or otherwise, which prevents Developers from having to suffer such distractions.
I'll argue the other side here slightly. I don't think Orlando is saying that you shouldn't have competence, but that you don't necessarily need to slow down with process.
Smunson says that you need to train developers. Where in Orlando's post did he mention that developers shouldn't be trained? He said no project managers and everyone needs to work together. The proof is in the procs being used in the agile process, not in the procedures, the developers, or the DBAs.
Jeff dislikes the lack of PMs, who can save developers' time by getting things done. I'll say that I've worked in no shortage of environments where the PMs don't get things done, and really just push on everyone to do more work instead of smoothing the process. It's not Agile that's bad or PMs that are good, but you are highly dependent on the people doing a good job. It's really the people and most of you that bang on Agile or DevOps have worked with lots of unprofessional and competent people and you're blaming the system as much as the staff.
Whether you work in Agile or DevOps or Scrum or whatever, the system isn't usually the problem. Some systems hide problems more than others, but all will fail if you don't have your staff, and your management, supporting the system. DevOps, Agile, etc. are asking people to regularly and constantly improve. That doesn't just mean that work flows faster and faster, which is what developers start to want, and management does. Skills improve, we change based on feedback, and we self-manage in many ways.
Is that a good use of time? I'm torn. Certainly having a highly paid, talented Mr. Moden chase down the allocation of new resources when he could tune code (or teach/mentor others) might not be a good use of time. The thing I think about is that having a break and some changes in routine, some breaks, is a good way to ensure that talented people don't burn out. Finding a balance there is tough, and certainly in the DevOps space, this doesn't mean less staff. It means that all staff gets trained to handle a bit of everything. The HADR guy doesn't get the same coding responsibilities as the C# developer, but they ought to be able to help out in places and know a little. Just like the developer learns a bit about HADR, which might change how they build something.
It's almost always the people, not the system or platform or architecture.
May 7, 2018 at 11:18 am
Steve Jones - SSC Editor - Monday, May 7, 2018 9:25 AMJeff Moden - Monday, May 7, 2018 7:34 AMsgmunson - Monday, May 7, 2018 6:56 AMI can only conclude that someone forgot about the rather strong and CRITICAL need to manage the database, or that somehow, databases are things to be played with instead of properly cared for. You don't abandon stored procedures because you have developers that don't know how to write them. You either train the ones you have to be able to properly code stored procedures, hire better developers to begin with, or suffer consequences. Your choice... And while agile/scrum advocates are prone to whining about the costs of such things, I simply explain that if you're worried about costs, why did you hire such poorly skilled developers in the first place? Or how about the cost of the agile infrastructure because they continue to perceive the database as an impediment? Things tend to get real quiet whenever I bring that topic up. It's as if they just don't consider a database an engine that drives the business. The level of incompetence that kind of thinking represents is extraordinary. I basically go back to "If you think professionals are expensive, wait until you hire an amateur" (Red Adair).Heh.... you beat me to it with the Red Adair quote. One of my favorite quotes is on of my own making... "If you want it real bad, you usually get it that way". 😀
Also, one of the reasons why I don't like pure Agile environments is because Project Managers are supposedly "not needed". One of the things that people don't realize is that a good Project Manager will not only help keep the current sprints on track but they also serve the huge and greatly needed functionality of being an enabler. If you need something to get the job done, a good Project Manager will go into the mode of pounding on people that can provide the correct resources, physical or otherwise, which prevents Developers from having to suffer such distractions.
I'll argue the other side here slightly. I don't think Orlando is saying that you shouldn't have competence, but that you don't necessarily need to slow down with process.
Smunson says that you need to train developers. Where in Orlando's post did he mention that developers shouldn't be trained? He said no project managers and everyone needs to work together. The proof is in the procs being used in the agile process, not in the procedures, the developers, or the DBAs.
Jeff dislikes the lack of PMs, who can save developers' time by getting things done. I'll say that I've worked in no shortage of environments where the PMs don't get things done, and really just push on everyone to do more work instead of smoothing the process. It's not Agile that's bad or PMs that are good, but you are highly dependent on the people doing a good job. It's really the people and most of you that bang on Agile or DevOps have worked with lots of unprofessional and competent people and you're blaming the system as much as the staff.
Whether you work in Agile or DevOps or Scrum or whatever, the system isn't usually the problem. Some systems hide problems more than others, but all will fail if you don't have your staff, and your management, supporting the system. DevOps, Agile, etc. are asking people to regularly and constantly improve. That doesn't just mean that work flows faster and faster, which is what developers start to want, and management does. Skills improve, we change based on feedback, and we self-manage in many ways.
Is that a good use of time? I'm torn. Certainly having a highly paid, talented Mr. Moden chase down the allocation of new resources when he could tune code (or teach/mentor others) might not be a good use of time. The thing I think about is that having a break and some changes in routine, some breaks, is a good way to ensure that talented people don't burn out. Finding a balance there is tough, and certainly in the DevOps space, this doesn't mean less staff. It means that all staff gets trained to handle a bit of everything. The HADR guy doesn't get the same coding responsibilities as the C# developer, but they ought to be able to help out in places and know a little. Just like the developer learns a bit about HADR, which might change how they build something.
It's almost always the people, not the system or platform or architecture.
Heh... Orlando said that they didn't want to wait on someone to write stored procedures. That strongly implies that the developers don't know how to do such things on their own which implies that they're not trained to do it... or simply don't want to do it.
And, yeah... I agree what you said about some program managers... that's why I stipulated "good" program managers. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
May 7, 2018 at 2:18 pm
Steve Jones - SSC Editor - Monday, May 7, 2018 9:25 AMJeff Moden - Monday, May 7, 2018 7:34 AMsgmunson - Monday, May 7, 2018 6:56 AMI can only conclude that someone forgot about the rather strong and CRITICAL need to manage the database, or that somehow, databases are things to be played with instead of properly cared for. You don't abandon stored procedures because you have developers that don't know how to write them. You either train the ones you have to be able to properly code stored procedures, hire better developers to begin with, or suffer consequences. Your choice... And while agile/scrum advocates are prone to whining about the costs of such things, I simply explain that if you're worried about costs, why did you hire such poorly skilled developers in the first place? Or how about the cost of the agile infrastructure because they continue to perceive the database as an impediment? Things tend to get real quiet whenever I bring that topic up. It's as if they just don't consider a database an engine that drives the business. The level of incompetence that kind of thinking represents is extraordinary. I basically go back to "If you think professionals are expensive, wait until you hire an amateur" (Red Adair).Heh.... you beat me to it with the Red Adair quote. One of my favorite quotes is on of my own making... "If you want it real bad, you usually get it that way". 😀
Also, one of the reasons why I don't like pure Agile environments is because Project Managers are supposedly "not needed". One of the things that people don't realize is that a good Project Manager will not only help keep the current sprints on track but they also serve the huge and greatly needed functionality of being an enabler. If you need something to get the job done, a good Project Manager will go into the mode of pounding on people that can provide the correct resources, physical or otherwise, which prevents Developers from having to suffer such distractions.
I'll argue the other side here slightly. I don't think Orlando is saying that you shouldn't have competence, but that you don't necessarily need to slow down with process.
Smunson says that you need to train developers. Where in Orlando's post did he mention that developers shouldn't be trained? He said no project managers and everyone needs to work together. The proof is in the procs being used in the agile process, not in the procedures, the developers, or the DBAs.
Jeff dislikes the lack of PMs, who can save developers' time by getting things done. I'll say that I've worked in no shortage of environments where the PMs don't get things done, and really just push on everyone to do more work instead of smoothing the process. It's not Agile that's bad or PMs that are good, but you are highly dependent on the people doing a good job. It's really the people and most of you that bang on Agile or DevOps have worked with lots of unprofessional and competent people and you're blaming the system as much as the staff.
Whether you work in Agile or DevOps or Scrum or whatever, the system isn't usually the problem. Some systems hide problems more than others, but all will fail if you don't have your staff, and your management, supporting the system. DevOps, Agile, etc. are asking people to regularly and constantly improve. That doesn't just mean that work flows faster and faster, which is what developers start to want, and management does. Skills improve, we change based on feedback, and we self-manage in many ways.
Is that a good use of time? I'm torn. Certainly having a highly paid, talented Mr. Moden chase down the allocation of new resources when he could tune code (or teach/mentor others) might not be a good use of time. The thing I think about is that having a break and some changes in routine, some breaks, is a good way to ensure that talented people don't burn out. Finding a balance there is tough, and certainly in the DevOps space, this doesn't mean less staff. It means that all staff gets trained to handle a bit of everything. The HADR guy doesn't get the same coding responsibilities as the C# developer, but they ought to be able to help out in places and know a little. Just like the developer learns a bit about HADR, which might change how they build something.
It's almost always the people, not the system or platform or architecture.
I'm going to have to agree with Jeff. If devs see sprocs as an impediment in any way then there's a considerable lack of expertise in not just the devs, but also in the scrum masters and or project managers that go along with that nonsense. In my book, that rises above mere incompetence, to the level of negligence. Treating a database like it's basic structures are impediments is ludicrous, at best, and that's why my book says negligence. If the PM's are getting away with it, then the level of incompetence probably rises well up into management.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 7, 2018 at 2:33 pm
If devs see stored procs as impediments (and I don't think that they do) it would not be because devs can't write stored procs. It would be because any DB changes mean that they then have to factor in a DBA into their deployment pipeline - who would be needed to either apply the changes or set up DB permissions so that the dev team can apply them i.e. bringing a human into a process that is automated in every other way.
May 7, 2018 at 3:07 pm
sgmunson - Monday, May 7, 2018 2:18 PMSteve Jones - SSC Editor - Monday, May 7, 2018 9:25 AMJeff Moden - Monday, May 7, 2018 7:34 AMsgmunson - Monday, May 7, 2018 6:56 AMI can only conclude that someone forgot about the rather strong and CRITICAL need to manage the database, or that somehow, databases are things to be played with instead of properly cared for. You don't abandon stored procedures because you have developers that don't know how to write them. You either train the ones you have to be able to properly code stored procedures, hire better developers to begin with, or suffer consequences. Your choice... And while agile/scrum advocates are prone to whining about the costs of such things, I simply explain that if you're worried about costs, why did you hire such poorly skilled developers in the first place? Or how about the cost of the agile infrastructure because they continue to perceive the database as an impediment? Things tend to get real quiet whenever I bring that topic up. It's as if they just don't consider a database an engine that drives the business. The level of incompetence that kind of thinking represents is extraordinary. I basically go back to "If you think professionals are expensive, wait until you hire an amateur" (Red Adair).Heh.... you beat me to it with the Red Adair quote. One of my favorite quotes is on of my own making... "If you want it real bad, you usually get it that way". 😀
Also, one of the reasons why I don't like pure Agile environments is because Project Managers are supposedly "not needed". One of the things that people don't realize is that a good Project Manager will not only help keep the current sprints on track but they also serve the huge and greatly needed functionality of being an enabler. If you need something to get the job done, a good Project Manager will go into the mode of pounding on people that can provide the correct resources, physical or otherwise, which prevents Developers from having to suffer such distractions.
I'll argue the other side here slightly. I don't think Orlando is saying that you shouldn't have competence, but that you don't necessarily need to slow down with process.
Smunson says that you need to train developers. Where in Orlando's post did he mention that developers shouldn't be trained? He said no project managers and everyone needs to work together. The proof is in the procs being used in the agile process, not in the procedures, the developers, or the DBAs.
Jeff dislikes the lack of PMs, who can save developers' time by getting things done. I'll say that I've worked in no shortage of environments where the PMs don't get things done, and really just push on everyone to do more work instead of smoothing the process. It's not Agile that's bad or PMs that are good, but you are highly dependent on the people doing a good job. It's really the people and most of you that bang on Agile or DevOps have worked with lots of unprofessional and competent people and you're blaming the system as much as the staff.
Whether you work in Agile or DevOps or Scrum or whatever, the system isn't usually the problem. Some systems hide problems more than others, but all will fail if you don't have your staff, and your management, supporting the system. DevOps, Agile, etc. are asking people to regularly and constantly improve. That doesn't just mean that work flows faster and faster, which is what developers start to want, and management does. Skills improve, we change based on feedback, and we self-manage in many ways.
Is that a good use of time? I'm torn. Certainly having a highly paid, talented Mr. Moden chase down the allocation of new resources when he could tune code (or teach/mentor others) might not be a good use of time. The thing I think about is that having a break and some changes in routine, some breaks, is a good way to ensure that talented people don't burn out. Finding a balance there is tough, and certainly in the DevOps space, this doesn't mean less staff. It means that all staff gets trained to handle a bit of everything. The HADR guy doesn't get the same coding responsibilities as the C# developer, but they ought to be able to help out in places and know a little. Just like the developer learns a bit about HADR, which might change how they build something.
It's almost always the people, not the system or platform or architecture.
I'm going to have to agree with Jeff. If devs see sprocs as an impediment in any way then there's a considerable lack of expertise in not just the devs, but also in the scrum masters and or project managers that go along with that nonsense. In my book, that rises above mere incompetence, to the level of negligence. Treating a database like it's basic structures are impediments is ludicrous, at best, and that's why my book says negligence. If the PM's are getting away with it, then the level of incompetence probably rises well up into management.
I would say that it almost always rises well up into management. Remember that there are very few (if any) management types that are one or two levels over a DBA or dev that have any depth of technical knowledge. In my wife's job she knew it was wrong to take all the sql out of procedures and move it to the application but she doesn't have the depth of knowledge to be able to explain to the development manager as to why. She asked me about it a week or so after they made that decision and I nearly fell out of my chair. They didn't treat or see the database as an impediment to progress, they foolishly saw procedures as the performance bottleneck. From a performance perspective their weeks long effort was an exercise in futility. But from a system and maintenance perspective it was huge leaps backwards. And they still have massive performance issues and she can't get the dev guy to listen to me or all the others out there that tell them they are going about is all wrong. They have queries that generate thousand and thousand of rows for grids that are paged. They aren't doing any server side paging or caching, just querying the same multiple thousand row query for every page of data.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 7, 2018 at 3:25 pm
They have queries that generate thousand and thousand of rows for grids that are paged. They aren't doing any server side paging or caching, just querying the same multiple thousand row query for every page of data.
yes, but are we saying fixing this requires a stored procedure?
edit:
let me put it another way, can we DO paging and caching without stored procedures? how about procedure cache bloat, can we avoid that without stored procedures?
I'm the type that likes to separate out concerns into discrete tidy buckets for consideration.
(another edit, I had a typo in there)
May 7, 2018 at 4:12 pm
Jeff Moden - Monday, May 7, 2018 7:34 AMsgmunson - Monday, May 7, 2018 6:56 AMOrlando Colamatteo - Saturday, May 5, 2018 6:44 AMThis gave me a pretty good laugh because in real Scrum there are no project managers. That's why you have scrum meetings. And a project manager.It all depends on what the optimizing goals are for the architecture. Saddling developers with external dependencies like having to wait for a proc to be developed by someone else or even send it to someone else for revisions before it can be deployed is an impediment (in Scrum terms) and as we look for ways to help the team go faster we look to remove impediments.Logic follows:1 Procs require specialized skills or are somehow slowing the team down2 Some critical mass of decision makers decide Procs are an impediment3 Decision makers decide on an architecture that does not include Procs The proof is not in the Procs worth it’s in their ability to be part of an agile development process. Have you looked into Visual Studio Database Projects or redgate ReadyRoll? Change or be phased out. Those are your options.I can only conclude that someone forgot about the rather strong and CRITICAL need to manage the database, or that somehow, databases are things to be played with instead of properly cared for. You don't abandon stored procedures because you have developers that don't know how to write them. You either train the ones you have to be able to properly code stored procedures, hire better developers to begin with, or suffer consequences. Your choice... And while agile/scrum advocates are prone to whining about the costs of such things, I simply explain that if you're worried about costs, why did you hire such poorly skilled developers in the first place? Or how about the cost of the agile infrastructure because they continue to perceive the database as an impediment? Things tend to get real quiet whenever I bring that topic up. It's as if they just don't consider a database an engine that drives the business. The level of incompetence that kind of thinking represents is extraordinary. I basically go back to "If you think professionals are expensive, wait until you hire an amateur" (Red Adair).
Heh.... you beat me to it with the Red Adair quote. One of my favorite quotes is on of my own making... "If you want it real bad, you usually get it that way". 😀
Also, one of the reasons why I don't like pure Agile environments is because Project Managers are supposedly "not needed". One of the things that people don't realize is that a good Project Manager will not only help keep the current sprints on track but they also serve the huge and greatly needed functionality of being an enabler. If you need something to get the job done, a good Project Manager will go into the mode of pounding on people that can provide the correct resources, physical or otherwise, which prevents Developers from having to suffer such distractions.
I've seen great agile teams who don't get caught up in the whole "database or stored procedures are the bottleneck" mentality. I think all of them did have a project manager despite them "not being needed" in the agile world. Often it's the lack of resources being one of the more significant pieces of the problem. And/or getting the right resources. That is exactly what a good PM does. They would help manage all of the pieces from development to deployment. Agile is a development methodology, not a deployment methodology and those PMs recognized the difference.
But I keep thinking that it seems that those dumping stored procedures because it's faster to change the code must be really hoping that whatever they work on is never going to get very big or they never worked on a somewhat large project. Otherwise, if they had one database server and 20 or more app servers, they would know it is faster to change one stored procedure than it is to update code on 20 app servers.
Sue
Viewing 15 posts - 91 through 105 (of 191 total)
You must be logged in to reply to this topic. Login to reply