April 18, 2018 at 12:09 pm
funbi - Wednesday, April 18, 2018 3:07 AMSergiy - Tuesday, April 17, 2018 4:55 PMfunbi - Tuesday, April 17, 2018 6:00 AMEver heard of ORMs/Linq? Does anyone cobble together SQL in code any more? Use SP's for heavy lifting for sure but the problems you are listing have been solved in the application layer some time ago.Ever heard of Access?
Pretty nice thing.
Comfortable design tools, front end and reporting - all in one package.
And the best part: it will beat any ORM/Linq based solutions in terms of performance and scalability hands down.ORM/Linq solutions only good until the whole database fits in memory.
Linq uses deferred execution so never holds more than it needs to in memory. As I said, and we agree, one should use SPs for the heavy lifting. The issues that Hugo was talking about (joining to other tables, filtering, aggregation, preventing sql injection & server round trips per row, storing logic etc) are all now handled very easily in the ORM/application layers.
Not buying your final conclusion under any circumstances. ORMs are not designed for specifics, but instead are designed for generics, and rarely are they well-designed, even for that purpose. In most cases, some critical functionality has to use a query building engine that is not very good and the queries created are crap from a performance perspective. It's consistent, and repeated. And clearly, they don't all solve all of those problems. Very few solve any of them. And the more they stay away from stored procs, the worse they are at solving them.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 18, 2018 at 3:56 pm
Sergiy - Tuesday, April 17, 2018 4:55 PMfunbi - Tuesday, April 17, 2018 6:00 AMEver heard of ORMs/Linq? Does anyone cobble together SQL in code any more? Use SP's for heavy lifting for sure but the problems you are listing have been solved in the application layer some time ago.Ever heard of Access?
Pretty nice thing.
Comfortable design tools, front end and reporting - all in one package.
And the best part: it will beat any ORM/Linq based solutions in terms of performance and scalability hands down.ORM/Linq solutions only good until the whole database fits in memory.
heh I don't know if that reply is totally serious or not but man I loved access. I sure get why IT departments aren't a fan, and I've created my share of rube goldberg looking apps and nowadays I'm sort of in agreement but access could sure let you create some pretty interesting gadgets.
April 18, 2018 at 7:00 pm
Eric M Russell - Wednesday, April 18, 2018 9:52 AMI love SQL, but I don't want to fight JSON, JavaScript, and NoSQL databases. I'd rather ignore the ideological arguments on both sides of the NoSQL debate and instead press these tools into those use cases where they are needed. Imagine having 10,000 mobile app users all checking their account at the same time,or 1,000,000 users all hitting the database within an hour, because someone in marketing decided to blast out a promotional notification to the entire global user base. It makes sense to leverage a service oriented document database like Cosmos DB to contain your websites shopping cart or serve as a high transaction volume data mart for a mobile application.
it's not quite correct to compare SQL and JSON.
SQL is a way to access and manipulate data, when JSON is a data storage format.
You can access data stored in JSON format using different programming languages, including SQL or VBS.
As for a fight - waht does it take to retrieve a set of records JSON storage which represent all transactions made by a user "Eric M Russel" during the first month of previous financial year?
And should we compare the amount of reading required to retrieve an 8 byte DATETIME value comparing to this?"StartDate": { "__type": "Date", "iso": "2017-08-22T06:11:00.000Z" }
Not to mention the extra CPU cycles required to interpret this string and turn it into a binary value representing date-time.
What to fight here about?
_____________
Code for TallyGenerator
April 19, 2018 at 6:23 am
sgmunson - Wednesday, April 18, 2018 12:09 PMfunbi - Wednesday, April 18, 2018 3:07 AMSergiy - Tuesday, April 17, 2018 4:55 PMfunbi - Tuesday, April 17, 2018 6:00 AMEver heard of ORMs/Linq? Does anyone cobble together SQL in code any more? Use SP's for heavy lifting for sure but the problems you are listing have been solved in the application layer some time ago.Ever heard of Access?
Pretty nice thing.
Comfortable design tools, front end and reporting - all in one package.
And the best part: it will beat any ORM/Linq based solutions in terms of performance and scalability hands down.ORM/Linq solutions only good until the whole database fits in memory.
Linq uses deferred execution so never holds more than it needs to in memory. As I said, and we agree, one should use SPs for the heavy lifting. The issues that Hugo was talking about (joining to other tables, filtering, aggregation, preventing sql injection & server round trips per row, storing logic etc) are all now handled very easily in the ORM/application layers.
Not buying your final conclusion under any circumstances. ORMs are not designed for specifics, but instead are designed for generics, and rarely are they well-designed, even for that purpose. In most cases, some critical functionality has to use a query building engine that is not very good and the queries created are crap from a performance perspective. It's consistent, and repeated. And clearly, they don't all solve all of those problems. Very few solve any of them. And the more they stay away from stored procs, the worse they are at solving them.
ORM's are designed for specifics too. However, at the end of the day, you just define a class model in your application. Either you as the database professional is going to define it or the ORM is going to define it. IN both cases, it's likely going to be the exact same approach minus data size limits and maybe data types, but even then, you can give the programmer recommendations and process on what to follow for best practices there when he defines the class.
The major issue is the methods of the classes and how people read and write data from those models. This is where things can get hairy, but for the most part, they are mostly pretty straight forward approaches with interacting with those data models you would create anyways. It's not rocket science for the ORM. The only major issue there is how poorly those models are designed along with the methods, but that's no different than poorly designed models and SQL from database professionals that happen all of the time too. It's not SQL or ORM that's the problem, it's the people and their ability to use these tools.
April 19, 2018 at 7:28 am
Sergiy - Wednesday, April 18, 2018 7:00 PMEric M Russell - Wednesday, April 18, 2018 9:52 AMI love SQL, but I don't want to fight JSON, JavaScript, and NoSQL databases. I'd rather ignore the ideological arguments on both sides of the NoSQL debate and instead press these tools into those use cases where they are needed. Imagine having 10,000 mobile app users all checking their account at the same time,or 1,000,000 users all hitting the database within an hour, because someone in marketing decided to blast out a promotional notification to the entire global user base. It makes sense to leverage a service oriented document database like Cosmos DB to contain your websites shopping cart or serve as a high transaction volume data mart for a mobile application.it's not quite correct to compare SQL and JSON.
SQL is a way to access and manipulate data, when JSON is a data storage format.
You can access data stored in JSON format using different programming languages, including SQL or VBS.As for a fight - waht does it take to retrieve a set of records JSON storage which represent all transactions made by a user "Eric M Russel" during the first month of previous financial year?
And should we compare the amount of reading required to retrieve an 8 byte DATETIME value comparing to this?
"StartDate": { "__type": "Date", "iso": "2017-08-22T06:11:00.000Z" }
Not to mention the extra CPU cycles required to interpret this string and turn it into a binary value representing date-time.
What to fight here about?
In a document database like MongoDB or DocumentDB, all of the data related to an entity (for a specific application use case) is contained in a single JSON object. So, for example, a mobile app designed to help customers manage their profile and payment history would perform a single indexed lookup on CustomerID, and then retrieve the customer information in the form of a single JSON object. It's not scanning across a collection of documents on StartDate, everything related to that customer spans only a few blocks of contiguous data. I'm not saying that a document database should replace SQL Server as the primary line of business database. Rather, I'm saying that DocumentDB is a better replacement for something like Riak, Redis, or Memcache in the middle tier.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 19, 2018 at 7:42 am
xsevensinzx - Thursday, April 19, 2018 6:23 AMsgmunson - Wednesday, April 18, 2018 12:09 PMfunbi - Wednesday, April 18, 2018 3:07 AMSergiy - Tuesday, April 17, 2018 4:55 PMfunbi - Tuesday, April 17, 2018 6:00 AMEver heard of ORMs/Linq? Does anyone cobble together SQL in code any more? Use SP's for heavy lifting for sure but the problems you are listing have been solved in the application layer some time ago.Ever heard of Access?
Pretty nice thing.
Comfortable design tools, front end and reporting - all in one package.
And the best part: it will beat any ORM/Linq based solutions in terms of performance and scalability hands down.ORM/Linq solutions only good until the whole database fits in memory.
Linq uses deferred execution so never holds more than it needs to in memory. As I said, and we agree, one should use SPs for the heavy lifting. The issues that Hugo was talking about (joining to other tables, filtering, aggregation, preventing sql injection & server round trips per row, storing logic etc) are all now handled very easily in the ORM/application layers.
Not buying your final conclusion under any circumstances. ORMs are not designed for specifics, but instead are designed for generics, and rarely are they well-designed, even for that purpose. In most cases, some critical functionality has to use a query building engine that is not very good and the queries created are crap from a performance perspective. It's consistent, and repeated. And clearly, they don't all solve all of those problems. Very few solve any of them. And the more they stay away from stored procs, the worse they are at solving them.
ORM's are designed for specifics too. However, at the end of the day, you just define a class model in your application. Either you as the database professional is going to define it or the ORM is going to define it. IN both cases, it's likely going to be the exact same approach minus data size limits and maybe data types, but even then, you can give the programmer recommendations and process on what to follow for best practices there when he defines the class.
The major issue is the methods of the classes and how people read and write data from those models. This is where things can get hairy, but for the most part, they are mostly pretty straight forward approaches with interacting with those data models you would create anyways. It's not rocket science for the ORM. The only major issue there is how poorly those models are designed along with the methods, but that's no different than poorly designed models and SQL from database professionals that happen all of the time too. It's not SQL or ORM that's the problem, it's the people and their ability to use these tools.
And until you wipe out all the insufficiently database-qualified developers, ORM's will continue to cause more trouble than SQL Server alone will. Not having the ORM around tends to force you to hire better talent - at least if you want to not destroy your business through IT failure, anyway...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 19, 2018 at 8:37 am
I know I'm going to regret adding my 2 cents to the mix... but... here goes...
I've only heard one legitimate reason for for using an ORM solution... You're a small shop with an even smaller budget and you need to get your application stood up as quickly as possible. In that situation I think it makes perfect sense to devote 100% of you dev resources toward a few, really good, application developers and leave your network admin play the role of "accidental DBA"... In this situation, an ORM solution makes sense.
Had Amazon.com waited until it had the website that it has today, before opening it's doors, there never would have been an Amazon.com.
That said, application devs, on the whole, are not good at data/schema design. It's been my experience (yes, I know anecdotal evidence isn't evidence) that app developers see databases as little more that storage mechanisms for their applications. That is to say that tables tend to modeled to match application forms with absolutely no regard for normalization.
This will usually work for small companies who are handling small transaction numbers... but... if the company grows, and the number of transactions increases, they find that the poor design simply doesn't scale... no matter how much hardware gets thrown at it.
In that light, I would suggest that, once a company grows to point where it can support dedicated database developers, they do so. They should also plan to build out a new database, from scratch and plan a new application to sit on top of the new database. And, they should do it BEFORE they begin experiencing scalability issues with the existing application/database combo.
I would also go so far as to insist that the new database be designed so that it satisfies the 4th normal form and that ALL database logic be handled at the database tier... IE use stored procedures... and... that the only access to the database from the application should be the ability to execute stored procedures... No ad-hoc SQL whatsoever.
Am I saying that there are no full stack developers who are also 1st rate database professionals... No, absolutely not!
That said, they are rare. It's been my experience that, to get really good at anything, a person has to be passionate about that thing and be willing to dedicate hundreds (if not thousands) of hours to honing their skills and acquiring knowledge. Most people, are lucky to find just one thing that they can be passionate about and devote that time too and there are VERY few who have either the time or inclination to devote those resources to more than a small handful of things.
For the average human, it's a decision... Either be a "Jack of all trades but master of none" or be a "Master of one".
Again, Just my 2 cents...
April 19, 2018 at 9:16 am
That said, application devs, on the whole, are not good at data/schema design. It's been my experience (yes, I know anecdotal evidence isn't evidence) that app developers see databases as little more that storage mechanisms for their applications. That is to say that tables tend to modeled to match application forms with absolutely no regard for normalization.
This is true and for a pretty darn compelling reason, well normalized relational databases are a subset of what can be efficiently expressed with todays modern programming languages, and its a real gap, a real problem, a real object relational impedance mismatch (hence the phrase we see so much).
Theres 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.
If your screen and application programming instead internalize the relational model, I'm betting you get a much better match, stored procedures or not, HOWEVER, you must then limit yourself to programming only what the relational model says you can program.
April 19, 2018 at 11:05 am
Jason A. Long - Thursday, April 19, 2018 8:37 AMI know I'm going to regret adding my 2 cents to the mix... but... here goes...I've only heard one legitimate reason for for using an ORM solution... You're a small shop with an even smaller budget and you need to get your application stood up as quickly as possible. In that situation I think it makes perfect sense to devote 100% of you dev resources toward a few, really good, application developers and leave your network admin play the role of "accidental DBA"... In this situation, an ORM solution makes sense.
Had Amazon.com waited until it had the website that it has today, before opening it's doors, there never would have been an Amazon.com.
That said, application devs, on the whole, are not good at data/schema design. It's been my experience (yes, I know anecdotal evidence isn't evidence) that app developers see databases as little more that storage mechanisms for their applications. That is to say that tables tend to modeled to match application forms with absolutely no regard for normalization.
This will usually work for small companies who are handling small transaction numbers... but... if the company grows, and the number of transactions increases, they find that the poor design simply doesn't scale... no matter how much hardware gets thrown at it.In that light, I would suggest that, once a company grows to point where it can support dedicated database developers, they do so. They should also plan to build out a new database, from scratch and plan a new application to sit on top of the new database. And, they should do it BEFORE they begin experiencing scalability issues with the existing application/database combo.
I would also go so far as to insist that the new database be designed so that it satisfies the 4th normal form and that ALL database logic be handled at the database tier... IE use stored procedures... and... that the only access to the database from the application should be the ability to execute stored procedures... No ad-hoc SQL whatsoever.
Am I saying that there are no full stack developers who are also 1st rate database professionals... No, absolutely not!
That said, they are rare. It's been my experience that, to get really good at anything, a person has to be passionate about that thing and be willing to dedicate hundreds (if not thousands) of hours to honing their skills and acquiring knowledge. Most people, are lucky to find just one thing that they can be passionate about and devote that time too and there are VERY few who have either the time or inclination to devote those resources to more than a small handful of things.For the average human, it's a decision... Either be a "Jack of all trades but master of none" or be a "Master of one".
Again, Just my 2 cents...
+ A Googolplex to the Googolplex power (Googolplex = Googol to the Googol power, and a Googol = 1 with 100 zeroes.)
Now maybe I can tell you how I really feel ??? :hehe::hehe::hehe:
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 19, 2018 at 2:17 pm
sgmunson - Thursday, April 19, 2018 11:05 AM+ A Googolplex to the Googolplex power (Googolplex = Googol to the Googol power, and a Googol = 1 with 100 zeroes.)
Now maybe I can tell you how I really feel ??? :hehe::hehe::hehe:
LOL... Well, I'm glad to know that if I'm wrong, I'm at least keeping good company. 😀
April 19, 2018 at 6:28 pm
Jason A. Long - Thursday, April 19, 2018 8:37 AMI know I'm going to regret adding my 2 cents to the mix... but... here goes...I've only heard one legitimate reason for for using an ORM solution... You're a small shop with an even smaller budget and you need to get your application stood up as quickly as possible. In that situation I think it makes perfect sense to devote 100% of you dev resources toward a few, really good, application developers and leave your network admin play the role of "accidental DBA"... In this situation, an ORM solution makes sense.
Had Amazon.com waited until it had the website that it has today, before opening it's doors, there never would have been an Amazon.com.
That said, application devs, on the whole, are not good at data/schema design. It's been my experience (yes, I know anecdotal evidence isn't evidence) that app developers see databases as little more that storage mechanisms for their applications. That is to say that tables tend to modeled to match application forms with absolutely no regard for normalization.
This will usually work for small companies who are handling small transaction numbers... but... if the company grows, and the number of transactions increases, they find that the poor design simply doesn't scale... no matter how much hardware gets thrown at it.In that light, I would suggest that, once a company grows to point where it can support dedicated database developers, they do so. They should also plan to build out a new database, from scratch and plan a new application to sit on top of the new database. And, they should do it BEFORE they begin experiencing scalability issues with the existing application/database combo.
I would also go so far as to insist that the new database be designed so that it satisfies the 4th normal form and that ALL database logic be handled at the database tier... IE use stored procedures... and... that the only access to the database from the application should be the ability to execute stored procedures... No ad-hoc SQL whatsoever.
Am I saying that there are no full stack developers who are also 1st rate database professionals... No, absolutely not!
That said, they are rare. It's been my experience that, to get really good at anything, a person has to be passionate about that thing and be willing to dedicate hundreds (if not thousands) of hours to honing their skills and acquiring knowledge. Most people, are lucky to find just one thing that they can be passionate about and devote that time too and there are VERY few who have either the time or inclination to devote those resources to more than a small handful of things.For the average human, it's a decision... Either be a "Jack of all trades but master of none" or be a "Master of one".
Again, Just my 2 cents...
I will just say that normalization is a methodology, not a piece of tech. Therefore, not normalizing a database is a business process problem, not a tech problem unless the specific tech you are using does not allow normalization.
April 19, 2018 at 8:31 pm
xsevensinzx - Thursday, April 19, 2018 6:28 PMJason A. Long - Thursday, April 19, 2018 8:37 AMI know I'm going to regret adding my 2 cents to the mix... but... here goes...I've only heard one legitimate reason for for using an ORM solution... You're a small shop with an even smaller budget and you need to get your application stood up as quickly as possible. In that situation I think it makes perfect sense to devote 100% of you dev resources toward a few, really good, application developers and leave your network admin play the role of "accidental DBA"... In this situation, an ORM solution makes sense.
Had Amazon.com waited until it had the website that it has today, before opening it's doors, there never would have been an Amazon.com.
That said, application devs, on the whole, are not good at data/schema design. It's been my experience (yes, I know anecdotal evidence isn't evidence) that app developers see databases as little more that storage mechanisms for their applications. That is to say that tables tend to modeled to match application forms with absolutely no regard for normalization.
This will usually work for small companies who are handling small transaction numbers... but... if the company grows, and the number of transactions increases, they find that the poor design simply doesn't scale... no matter how much hardware gets thrown at it.In that light, I would suggest that, once a company grows to point where it can support dedicated database developers, they do so. They should also plan to build out a new database, from scratch and plan a new application to sit on top of the new database. And, they should do it BEFORE they begin experiencing scalability issues with the existing application/database combo.
I would also go so far as to insist that the new database be designed so that it satisfies the 4th normal form and that ALL database logic be handled at the database tier... IE use stored procedures... and... that the only access to the database from the application should be the ability to execute stored procedures... No ad-hoc SQL whatsoever.
Am I saying that there are no full stack developers who are also 1st rate database professionals... No, absolutely not!
That said, they are rare. It's been my experience that, to get really good at anything, a person has to be passionate about that thing and be willing to dedicate hundreds (if not thousands) of hours to honing their skills and acquiring knowledge. Most people, are lucky to find just one thing that they can be passionate about and devote that time too and there are VERY few who have either the time or inclination to devote those resources to more than a small handful of things.For the average human, it's a decision... Either be a "Jack of all trades but master of none" or be a "Master of one".
Again, Just my 2 cents...
I will just say that normalization is a methodology, not a piece of tech. Therefore, not normalizing a database is a business process problem, not a tech problem unless the specific tech you are using does not allow normalization.
Yes and no... MS SQL Server is a tool designed specifically to work with relational databases. That is to say, databases designed around the relational model.
If you want to build your house with nails instead of screws, go for it. You'll get no judgement from me. I would simply advise you to use a hammer rather than a screwdriver.
If you don't want to build relational database, I'd advise you not to use a RDBMS... It's not rocket surgery.
April 19, 2018 at 9:33 pm
Jason A. Long - Thursday, April 19, 2018 8:31 PMxsevensinzx - Thursday, April 19, 2018 6:28 PMJason A. Long - Thursday, April 19, 2018 8:37 AMI know I'm going to regret adding my 2 cents to the mix... but... here goes...I've only heard one legitimate reason for for using an ORM solution... You're a small shop with an even smaller budget and you need to get your application stood up as quickly as possible. In that situation I think it makes perfect sense to devote 100% of you dev resources toward a few, really good, application developers and leave your network admin play the role of "accidental DBA"... In this situation, an ORM solution makes sense.
Had Amazon.com waited until it had the website that it has today, before opening it's doors, there never would have been an Amazon.com.
That said, application devs, on the whole, are not good at data/schema design. It's been my experience (yes, I know anecdotal evidence isn't evidence) that app developers see databases as little more that storage mechanisms for their applications. That is to say that tables tend to modeled to match application forms with absolutely no regard for normalization.
This will usually work for small companies who are handling small transaction numbers... but... if the company grows, and the number of transactions increases, they find that the poor design simply doesn't scale... no matter how much hardware gets thrown at it.In that light, I would suggest that, once a company grows to point where it can support dedicated database developers, they do so. They should also plan to build out a new database, from scratch and plan a new application to sit on top of the new database. And, they should do it BEFORE they begin experiencing scalability issues with the existing application/database combo.
I would also go so far as to insist that the new database be designed so that it satisfies the 4th normal form and that ALL database logic be handled at the database tier... IE use stored procedures... and... that the only access to the database from the application should be the ability to execute stored procedures... No ad-hoc SQL whatsoever.
Am I saying that there are no full stack developers who are also 1st rate database professionals... No, absolutely not!
That said, they are rare. It's been my experience that, to get really good at anything, a person has to be passionate about that thing and be willing to dedicate hundreds (if not thousands) of hours to honing their skills and acquiring knowledge. Most people, are lucky to find just one thing that they can be passionate about and devote that time too and there are VERY few who have either the time or inclination to devote those resources to more than a small handful of things.For the average human, it's a decision... Either be a "Jack of all trades but master of none" or be a "Master of one".
Again, Just my 2 cents...
I will just say that normalization is a methodology, not a piece of tech. Therefore, not normalizing a database is a business process problem, not a tech problem unless the specific tech you are using does not allow normalization.
Yes and no... MS SQL Server is a tool designed specifically to work with relational databases. That is to say, databases designed around the relational model.
If you want to build your house with nails instead of screws, go for it. You'll get no judgement from me. I would simply advise you to use a hammer rather than a screwdriver.
If you don't want to build relational database, I'd advise you not to use a RDBMS... It's not rocket surgery.
Not sure I follow. We are talking about normalization, which is the methodology of restructuring a relational model to reduce data redundancy and improve data integrity. We are not talking about using a RDBMS without said relational model. But even then, have a relational model just like the process of normalizing said relational model are mostly again, methodologies. We can define a relational model with ZERO primary key and foreign key constraints in terms of the tech. We however can rely on the tech (i.e.: SQL query language) as well those define constraints to help ensure the relational model is enforced and defined. This is where SQL Server as a tool becomes a great option in that's your methodology. Yet, the tech in either ORM or your chosen RDBMS is not exactly preventing you from doing so even before you get into the quality of each approach with said tech.
I don't want to sound like I'm going around in circles here, but I feel I'm talking about facts here and you're talking about opinions and preferences. It does not matter if you think using SQL Server with a physical body is your preferred preference to define a relational model. What matters is if you can achieve the same output with SQL Server as you can with the ORM. As they are mostly methodologies, you pretty much can.
I would say that taking the approach of using methodologies to argue this is not a good stance. This is because it entirely depends on the person using the tech. A former DBA using ORM is going to be different than an application developer who knows nothing about the DBA side of things. This is where you are hung up on, which is a flawed argument and stance because people can be improved, your tech, unless you have the source code or control over the owners, cannot.
Should stick to the fact stored procedures is native to SQL Server and not native to ORM in which the ORM is restricted on that tech if said stored procedure execution is thrown out the window. Then it moves into the quality of using stored procedure over straight raw SQL. That's ideally the core of this argument on top of the raw SQL generated from ORM may not be of quality versus a human writing it for you.
As I said before, stored procedures being written by a human can be a bottleneck just as poor SQL generation from ORM can also be a bottleneck -- on performance.
P.S
I just want to emphasize that I love SQL Server, usage of stored procedures, and so forth. I'm just pushing this for the fact it's a good exercise to highlight how similar arguments hinder innovation and new approaches in IT.
April 19, 2018 at 11:12 pm
xsevensinzx - Thursday, April 19, 2018 9:33 PMNot sure I follow. We are talking about normalization, which is the methodology of restructuring a relational model to reduce data redundancy and improve data integrity. We are not talking about using a RDBMS without said relational model. But even then, have a relational model just like the process of normalizing said relational model are mostly again, methodologies. We can define a relational model with ZERO primary key and foreign key constraints in terms of the tech. We however can rely on the tech (i.e.: SQL query language) as well those define constraints to help ensure the relational model is enforced and defined. This is where SQL Server as a tool becomes a great option in that's your methodology. Yet, the tech in either ORM or your chosen RDBMS is not exactly preventing you from doing so even before you get into the quality of each approach with said tech.I don't want to sound like I'm going around in circles here, but I feel I'm talking about facts here and you're talking about opinions and preferences. It does not matter if you think using SQL Server with a physical body is your preferred preference to define a relational model. What matters is if you can achieve the same output with SQL Server as you can with the ORM. As they are mostly methodologies, you pretty much can.
I would say that taking the approach of using methodologies to argue this is not a good stance. This is because it entirely depends on the person using the tech. A former DBA using ORM is going to be different than an application developer who knows nothing about the DBA side of things. This is where you are hung up on, which is a flawed argument and stance because people can be improved, your tech, unless you have the source code or control over the owners, cannot.
Should stick to the fact stored procedures is native to SQL Server and not native to ORM in which the ORM is restricted on that tech if said stored procedure execution is thrown out the window. Then it moves into the quality of using stored procedure over straight raw SQL. That's ideally the core of this argument on top of the raw SQL generated from ORM may not be of quality versus a human writing it for you.
As I said before, stored procedures being written by a human can be a bottleneck just as poor SQL generation from ORM can also be a bottleneck -- on performance.
P.S
I just want to emphasize that I love SQL Server, usage of stored procedures, and so forth. I'm just pushing this for the fact it's a good exercise to highlight how similar arguments hinder innovation and new approaches in IT.
Keven Boles is far more eloquent than I am, so I'll just leave this here... Know What Your Code is Doing to SQL Server!
April 21, 2018 at 9:21 pm
patrickmcginnis59 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 desc
WHERE 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.
_____________
Code for TallyGenerator
Viewing 15 posts - 76 through 90 (of 191 total)
You must be logged in to reply to this topic. Login to reply