Start Fixing Your DB with Better Code

Question

Start Fixing Your DB with Better Code

Viewing 15 posts - 16 through 30 (of 41 total)

You must be logged in to reply to this topic. Login to reply

David.Poole SSC Guru Points: 76000 More actions · Answer 1

I think I've been lucky in being a developer before choosing the DBA route. The debate about whether something belonged in the DB or app never occurred because I was equally happy with both. It might as well be an argument about whether cars should contain an engine or passenger cell.
Some of the most awful apps I've seen have been those where a religious war has taken place between devs and DBAs. You could see the incontrovertible truth of this in early versions of BizTalk where derogatory code comments were embedded in stored procedures.
In the Agile world the team owns the whole app and refactors code mercilessly. It's all about continuous improvement. If you know something will be made better it makes you less defensive and more approachable. Looking back much of my bad behaviours have been driven by the knowledge that I would be the unwilling supporter of a long lived syphilitic turd of an application. The powerless frustration of the situation didn't help though gallows humour did

LinkedIn Profile

Aaron N. Cutshall SSCrazy Eights Points: 9011 More actions · Answer 2

RonKyle - Thursday, November 16, 2017 1:08 PM
Agreed. Thanks for the clarification. It seemed like you might not have been in favor of stored procedures (something else I've seen).

Sorry for the confusion. i totally believe in making use of stored procedures and feel that all data operations should be conducted there as it's much closer to the data and far more efficient. Even business rules involving data selection/manipulation should be there, IMHO. Let the database handle the data and the application handle the user.

LinkedIn: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com

RonKyle SSC-Dedicated Points: 31556 More actions · Answer 3

i totally believe in making use of stored procedures and feel that all data operations should be conducted there as it's much closer to the data and far more efficient. Even business rules involving data selection/manipulation should be there, IMHO. Let the database handle the data and the application handle the user.

I don't think there's a bright shining line that can't be crossed. But I remember when working under the direction of some consultants on my first enterprise level database writing a long stored procedure that calculated taxes under a wide variety of scenarios. All the MTS (?) packages did was call the stored procedure and the SP did the rest of the work. When delivered the application was very slow for lots of reasons. As we dug in, one of the things we discovered is that the business objects needed to do more of the work. I wrote an MTS package that encapsulated the business logic that I'd previously put in the stored procedure, and the stored procedure broken out into three different ones, only one of which would be called. It worked much faster. I still have the before and after code somewhere in my files. That said, I have included rudimentary logic in my stored procedures, especially in an ETL environment. But generally you'd get an argument from me if there's anything other than the most basic logic in the SPs.

Aaron N. Cutshall SSCrazy Eights Points: 9011 More actions · Answer 4

RonKyle - Thursday, November 16, 2017 2:23 PM
I don't think there's a bright shining line that can't be crossed. But I remember when working under the direction of some consultants on my first enterprise level database writing a long stored procedure that calculated taxes under a wide variety of scenarios. All the MTS (?) packages did was call the stored procedure and the SP did the rest of the work. When delivered the application was very slow for lots of reasons. As we dug in, one of the things we discovered is that the business objects needed to do more of the work. I wrote an MTS package that encapsulated the business logic that I'd previously put in the stored procedure, and the stored procedure broken out into three different ones, only one of which would be called. It worked much faster. I still have the before and after code somewhere in my files. That said, I have included rudimentary logic in my stored procedures, especially in an ETL environment. But generally you'd get an argument from me if there's anything other than the most basic logic in the SPs.

I agree that the line is very flexible and should fit the appropriate situation. Don't get me wrong. It's OK to break out things like you did into multiple sprocs if needed. After all, performance is critical. What I'm referring to is like the situation that I'm in now with over 10 layers of indirection. It's like unraveling a spaghetti bowl with a pair of tweezers. The current system has so many dependencies it's crazy. Just when I think I've reached bottom I find additional functions/procedures that are called. The complexity is giving me quite the headache! 🙁

LinkedIn: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com

call.copse SSCoach Points: 18050 More actions · Answer 5

Aaron N. Cutshall - Thursday, November 16, 2017 2:30 PM
RonKyle - Thursday, November 16, 2017 2:23 PM
I don't think there's a bright shining line that can't be crossed. But I remember when working under the direction of some consultants on my first enterprise level database writing a long stored procedure that calculated taxes under a wide variety of scenarios. All the MTS (?) packages did was call the stored procedure and the SP did the rest of the work. When delivered the application was very slow for lots of reasons. As we dug in, one of the things we discovered is that the business objects needed to do more of the work. I wrote an MTS package that encapsulated the business logic that I'd previously put in the stored procedure, and the stored procedure broken out into three different ones, only one of which would be called. It worked much faster. I still have the before and after code somewhere in my files. That said, I have included rudimentary logic in my stored procedures, especially in an ETL environment. But generally you'd get an argument from me if there's anything other than the most basic logic in the SPs.
I agree that the line is very flexible and should fit the appropriate situation. Don't get me wrong. It's OK to break out things like you did into multiple sprocs if needed. After all, performance is critical. What I'm referring to is like the situation that I'm in now with over 10 layers of indirection. It's like unraveling a spaghetti bowl with a pair of tweezers. The current system has so many dependencies it's crazy. Just when I think I've reached bottom I find additional functions/procedures that are called. The complexity is giving me quite the headache! 🙁

Quite - the only line I'd draw for certain, at least these days, is keeping UI code out of the database. It often did make some occasional sense in some older styles of development but never these days.

Two levels of indirection are adequate for nearly all circumstances I feel.

Eric M Russell SSC Guru Points: 125561 More actions · Answer 6

The industry of SQL coding suffers from the same predicament as HTML; a lot of folks code it, but few truly know it at a professional level. Consider how many developers self identify as "software engineers" versus "database engineers". A lot of effort is put into GUI, OODP, and SOA, but at the end of the project they have it all stacked on top of a very poor database design, because it falls outside the realm of what they consider to be the engineered product.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Aaron N. Cutshall SSCrazy Eights Points: 9011 More actions · Answer 7

Eric M Russell - Friday, November 17, 2017 6:58 AM
The industry of SQL coding suffers from the same predicament as HTML; a lot of folks code it, but few truly know it at a professional level. Consider how many developers self identify as "software engineers" versus "database engineers". A lot of effort is put into GUI, OODP, and SOA, but at the end of the project they have it all stacked on top of a very poor database design, because it falls outside the realm of what they consider to be the engineered product.

I totally agree Eric. Way too often the database is handled as an afterthought and used only as a mechanism to hold data needed for an application. For them, they might as well store data in flat files for all the performance benefit they would have. I have seen situations where lots of focus was placed on the application and any and all performance issues were blamed on the database. In those situations, they were nearly correct as the database was not properly designed but merely reflected the thinking of the application without regard to how a database properly operates.

In one case the application executed queries against the database that were horrendously slow and the database was dutifully blamed. What happened was that the queries generated were extremely poor with huge WHERE clauses with lots of OR statements causing full table scans by ignoring indexes. Once I showed how a properly written query would perform they still refused to acknowledge that the issue was in the application that generated the query because to them it was still a database issue. :crazy:

LinkedIn: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 8

Aaron N. Cutshall - Friday, November 17, 2017 7:10 AM
In one case the application executed queries against the database that were horrendously slow and the database was dutifully blamed. What happened was that the queries generated were extremely poor with huge WHERE clauses with lots of OR statements causing full table scans by ignoring indexes. Once I showed how a properly written query would perform they still refused to acknowledge that the issue was in the application that generated the query because to them it was still a database issue. :crazy:

I'm VERY fortunate. Somehow, we managed to hire Developers (with a capital "D", in this case) that actually "get it" (the two leads and myself normally conduct the interviews). We're still fixing some rather horrible code that our predecessors wrote that and we're doing it together. I find the problems and if I can't make a simple fix because the code is generated by the GUI, I open a ticket and the Developers jump all over like white on rice to make a fix. The really cool part is that their fixes are normally to rewrite the code to call a stored procedure and THEY'RE the ones that write the stored procedure. They not afraid to ask for help when they need it and have a keen sense of when they need it. I'll take some of the credit there because of the mentoring I do during peer reviews but only a little. These folks are seriously motivated to write good, fast, accurate, and well documented code and they rightfully take great pride in doing so. All their new code follows suit.

They're freakin' awesome and I'm the luckiest DBA in the world to be working with such a great group of folks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eric M Russell SSC Guru Points: 125561 More actions · Answer 9

Jeff Moden - Friday, November 17, 2017 7:46 AM
...
I'm VERY fortunate. Somehow, we managed to hire Developers (with a capital "D", in this case) that actually "get it" (the two leads and myself normally conduct the interviews). We're still fixing some rather horrible code that our predecessors wrote that and we're doing it together. I find the problems and if I can't make a simple fix because the code is generated by the GUI, I open a ticket and the Developers jump all over like white on rice to make a fix. The really cool part is that their fixes are normally to rewrite the code to call a stored procedure and THEY'RE the ones that write the stored procedure. They not afraid to ask for help when they need it and have a keen sense of when they need it. I'll take some of the credit there because of the mentoring I do during peer reviews but only a little. These folks are seriously motivated to write good, fast, accurate, and well documented code and they rightfully take great pride in doing so. All their new code follows suit.
They're freakin' awesome and I'm the luckiest DBA in the world to be working with such a great group of folks.

Jeff, we're all familiar with your tactic of hiring the best developers by asking potential candidates how to get the current date and time in T-SQL, and apparently it works! 🙂

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 10

Eric M Russell - Friday, November 17, 2017 8:13 AM
Jeff Moden - Friday, November 17, 2017 7:46 AM
...
I'm VERY fortunate. Somehow, we managed to hire Developers (with a capital "D", in this case) that actually "get it" (the two leads and myself normally conduct the interviews). We're still fixing some rather horrible code that our predecessors wrote that and we're doing it together. I find the problems and if I can't make a simple fix because the code is generated by the GUI, I open a ticket and the Developers jump all over like white on rice to make a fix. The really cool part is that their fixes are normally to rewrite the code to call a stored procedure and THEY'RE the ones that write the stored procedure. They not afraid to ask for help when they need it and have a keen sense of when they need it. I'll take some of the credit there because of the mentoring I do during peer reviews but only a little. These folks are seriously motivated to write good, fast, accurate, and well documented code and they rightfully take great pride in doing so. All their new code follows suit.
They're freakin' awesome and I'm the luckiest DBA in the world to be working with such a great group of folks.
Jeff, we're all familiar with your tactic of hiring the best developers by asking potential candidates how to get the current date and time in T-SQL, and apparently it works! 🙂

ROFLMAO! 🙂 I was just thinking the same thing! Too funny. Thanks for making me laugh out loud!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

seo.reputeinfosystems Grasshopper Points: 11 More actions · Answer 11

I am currently working on a monitoring tool where the database "architecture" was driven by the application development and objected-oriented methodologies were applied to the database resulting in multiple layers of indirection with multiple nested views, numerous scalar functions, procedurally coded triggers and a rat's nest of stored procedures. And they wonder why they have performance issues! BigGrin

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 12

seo.reputeinfosystems - Thursday, November 23, 2017 12:01 AM
I am currently working on a monitoring tool where the database "architecture" was driven by the application development and objected-oriented methodologies were applied to the database resulting in multiple layers of indirection with multiple nested views, numerous scalar functions, procedurally coded triggers and a rat's nest of stored procedures. And they wonder why they have performance issues!

That's the way it was at the company I currently work for and one previous. We also had cursors everywhere for that one previous. They had also hired a contractor that advised them to replace the cursors with <major face palm><head-desk><head-desk><head-desk> temp tables and While loops and they spent a hell of a lot of money doing just that. It took substantial time (measured in years) and effort to clean most of that up and the company moved to another state before we could complete it. I did "follow" their efforts through some of the people that made the move with the company and it only took them a couple of months to revert back to those same poor practices because most of the developers they hired locally after the move were of the same gene pool as the originals. My only joy in that is it serves them right for moving to another state.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

x SSC-Insane Points: 23660 More actions · Answer 13

Jeff Moden - Thursday, November 23, 2017 11:44 AM
seo.reputeinfosystems - Thursday, November 23, 2017 12:01 AM
I am currently working on a monitoring tool where the database "architecture" was driven by the application development and objected-oriented methodologies were applied to the database resulting in multiple layers of indirection with multiple nested views, numerous scalar functions, procedurally coded triggers and a rat's nest of stored procedures. And they wonder why they have performance issues!
That's the way it was at the company I currently work for and one previous. We also had cursors everywhere for that one previous. They had also hired a contractor that advised them to replace the cursors with <major face palm><head-desk><head-desk><head-desk> temp tables and While loops and they spent a hell of a lot of money doing just that. It took substantial time (measured in years) and effort to clean most of that up and the company moved to another state before we could complete it. I did "follow" their efforts through some of the people that made the move with the company and it only took them a couple of months to revert back to those same poor practices because most of the developers they hired locally after the move were of the same gene pool as the originals. My only joy in that is it serves them right for moving to another state.

I would gladly trade slow apps for actually incorrect apps that you have to then try to make correct with manual database updates. I know everyone here harps about performance, but correctness is a bigger priority for me.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 14

patrickmcginnis59 10839 - Friday, November 24, 2017 9:15 AM
Jeff Moden - Thursday, November 23, 2017 11:44 AM
seo.reputeinfosystems - Thursday, November 23, 2017 12:01 AM
I am currently working on a monitoring tool where the database "architecture" was driven by the application development and objected-oriented methodologies were applied to the database resulting in multiple layers of indirection with multiple nested views, numerous scalar functions, procedurally coded triggers and a rat's nest of stored procedures. And they wonder why they have performance issues!
That's the way it was at the company I currently work for and one previous. We also had cursors everywhere for that one previous. They had also hired a contractor that advised them to replace the cursors with <major face palm><head-desk><head-desk><head-desk> temp tables and While loops and they spent a hell of a lot of money doing just that. It took substantial time (measured in years) and effort to clean most of that up and the company moved to another state before we could complete it. I did "follow" their efforts through some of the people that made the move with the company and it only took them a couple of months to revert back to those same poor practices because most of the developers they hired locally after the move were of the same gene pool as the originals. My only joy in that is it serves them right for moving to another state.
I would gladly trade slow apps for actually incorrect apps that you have to then try to make correct with manual database updates. I know everyone here harps about performance, but correctness is a bigger priority for me.

I absolutely agree. It's amazing how fast computers can generate bad data/information. Accuracy and correct data are absolutely imperative.

But, for me anyway, performance and code maintainability are both very close seconds. We import huge amounts of data and we have hundreds of people on the phone accessing the results of that data and entering new data. For me, settling for poor performance is no more of an option than settling for incorrect or only partially correct data. Settling for it taking two or more days to figure out what the code is doing to impart a small change because it has a gazillion layers of junk code is also unacceptable.

"Make it work, make it fast, make it pretty and it ain't done 'til it's pretty". 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

x SSC-Insane Points: 23660 More actions · Answer 15

Jeff Moden - Friday, November 24, 2017 9:28 AM
patrickmcginnis59 10839 - Friday, November 24, 2017 9:15 AM
Jeff Moden - Thursday, November 23, 2017 11:44 AM
seo.reputeinfosystems - Thursday, November 23, 2017 12:01 AM
I am currently working on a monitoring tool where the database "architecture" was driven by the application development and objected-oriented methodologies were applied to the database resulting in multiple layers of indirection with multiple nested views, numerous scalar functions, procedurally coded triggers and a rat's nest of stored procedures. And they wonder why they have performance issues!
That's the way it was at the company I currently work for and one previous. We also had cursors everywhere for that one previous. They had also hired a contractor that advised them to replace the cursors with <major face palm><head-desk><head-desk><head-desk> temp tables and While loops and they spent a hell of a lot of money doing just that. It took substantial time (measured in years) and effort to clean most of that up and the company moved to another state before we could complete it. I did "follow" their efforts through some of the people that made the move with the company and it only took them a couple of months to revert back to those same poor practices because most of the developers they hired locally after the move were of the same gene pool as the originals. My only joy in that is it serves them right for moving to another state.
I would gladly trade slow apps for actually incorrect apps that you have to then try to make correct with manual database updates. I know everyone here harps about performance, but correctness is a bigger priority for me.
I absolutely agree. It's amazing how fast computers can generate bad data/information. Accuracy and correct data are absolutely imperative.
But, for me anyway, performance and code maintainability are both very close seconds. We import huge amounts of data and we have hundreds of people on the phone accessing the results of that data and entering new data. For me, settling for poor performance is no more of an option than settling for incorrect or only partially correct data. Settling for it taking two or more days to figure out what the code is doing to impart a small change because it has a gazillion layers of junk code is also unacceptable.
"Make it work, make it fast, make it pretty and it ain't done 'til it's pretty". 😉

Incorrect code is what makes the headlines. I like how Steve focuses many of his editorials on it. Devops too, often making sure you have the code deployed that you need to have deployed is a big thing. You don't see many headlines about how a query took 10 minutes when it could take 10 seconds LOL

edit: I don't want to say that slow code is fun. What I've found out though is that slow code has value if its correct, because then you can make it a reference when trying to speed it up, the slow code can at least serve as a specification if it is at least correct. What really makes the headlines though are programming errors, and until the programming is correct, making it fast is moot.

I get the sense that programming correctness isn't important to you and that can only mean that its already a solved problem for you and I think that's great for you, you are very lucky in this area. Not everyone is so lucky, heck, read the headlines for some of the monsterous problems that have resulted from incorrect programming.