August 20, 2020 at 2:04 pm
There are SSIS packages for other purpose of this project, but not for this process.
Heh... there's no way that I'm even coming close to suggesting SSIS for this or anything else. I'll also state that if I were a DBA at the company, I'd be working my butt off to replace all the SSIS packages with stored procedures, including the imports and exports if there were any. 😉
Shifting gears back to your original problem, if you were to post more about the "very complicated business process" along with the rules you've incorporated into a matrix, we just might be able to help in a manner not unlike what Sergiy described above.
Complexity doesn't mean you have to avoid the power of stored procedures... in fact, complexity can often be greatly simplified in stored procedures. A lot of people forget that things like C# are a 3rd generation language not dedicated to handling data whereas languages like T-SQL are actually 4th generation and have a high dedication to working with data.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 20, 2020 at 3:56 pm
Thanks, I also heard complex logic should handle in business layer not on database layer. Is that just some people's preference?
August 20, 2020 at 4:07 pm
Thanks, I also heard complex logic should handle in business layer not on database layer. Is that just some people's preference?
In my opinion that's a big "it depends"! 🙂
Some set based operations (or operations that can be made set based) lend themselves to being done in SQL.
If processing is best done outside of SQL, but each complex operation results in multiple calls to the DB, for each of the 50,000 rows you'll get a dreadful performance overall but a light touch on the SQL server for each one - or rather just spaced out.
"Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
— Samuel Johnson
I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?
August 20, 2020 at 4:54 pm
Thanks, I also heard complex logic should handle in business layer not on database layer. Is that just some people's preference?
Don't get caught in absolutes. Put the logic where it makes sense. If the logic is data access specific, put it as close to the database as possible. If it's not, then don't put it in the database. There really isn't a single correct answer. Anyone providing a single correct answer probably has a radically incomplete picture.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 20, 2020 at 6:00 pm
Thanks, I also heard complex logic should handle in business layer not on database layer. Is that just some people's preference?
Personally, I've found that people's opinion of what the "business layer" is to be faulty. A lot of people think the only place the "business layer" can exist is outside of the database and they're wrong about that just as they're wrong that the "presentation layer" must never exist in the database. There are some really good notions as to why and when the "business layer" should exist outside of the database but I find the people get overly religious about some things and that certainly includes the "business layer".
On a more critical side, some people claim that the "business layer" cannot exist in the database but the real reason is because they don't actually know how to do something in the database.
One of my personal examples of this was a dupe check of CDRs (Call Detail Records). Each day's worth of CDRs was stored in a separate database and we had to do a dupe check against 3 months worth (31 days per month) and a separate database that contained some "specials" for a total of 94 databases. That setup was due to some 3rd party code we'd bought to manage CDRS.
The dupe check (which is business logic) was done by the 3rd party application. Each database contained about 4 million CDRs. It would take anywhere from 10 to 24 hours to process the whole lot and it would usually fail at least once, usually "near the end of the run".
I offered to replace that functionality but was assured by the 3rd party application provider AND all of the people that used it in-house AND every Developer that ever had to work on it that the logic was "way too complex to do in a database". My boss at the time actually forbade me to waste my time try to make it faster.
I didn't listen, mostly because people kept getting me involved but I couldn't fix things the way they were currently running. I also got very tired of the 3rd party software vendor saying "Well, it is a lot of data and you just can't do better with that much data".
I went to the CFO (the guy that knew the most about it because it was his people that were responsible for it) and asked him what it compared at the table level (you just gotta love a "working" CFO) and told him my plan to fix it. He agreed.
Less than 24 hours, I asked him to have his people test my fix (100% done in T-SQL). Their first response was that it didn't work. When I asked by, the literally said "Because it's not possible that it could have done the job correctly in the time it took to run", which was 45 minutes on the slower Dev box for the full monthly run. They hadn't even checked the data.
I asked them to actually check the data and, to their great surprise, not only did it work, but it caught a couple of things that the 3rd party boxed code hadn't. The other thing that surprised them was I actually did it for the full 3 months. Not even the CFO would tell me that they had set the 3rd party boxed code to only look back for two months.
And THAT was years back on a SQL Server 2000 box with only 8 32 bit CPUs.
To the best of my knowledge, the code never failed for the more than half decade I know it was in use and it never slowed down.
Another example can be found in the following articles...
Prior to that article, it would take about 2.5 DAYS to convert a million node "Adjacency List" hierarchy to "Nested Sets", which is kind of "business logic" on steroids especially in it's complexity. Using a little special logic and a little math, that was knocked down to 54 SECONDS and that was on twin i5 processors with only 6GB of RAM (not all of which was used). I ran that code on a more modern machine with more RAM and it now only takes 19 SECONDs.
Not complex enough for ya to qualify as "business logic"? Then check out the second article in that series (link below). Same 19 Seconds or so to do all of that.
Don't limit yourself with some of the "sillier" supposed "Best Practices". I agree that doing things like formatting dates and times for presentation on the front end is a serious "Bozo No-No" but the "business layer" doesn't belong only to front-end or other managed code.
Yeah, yeah... I hear it all the time... "Yeah Jeff but, to a hammer, everything is a nail". Damned straight and hammers are absolutely the tool to use when you trying to drive nails, especially big ones AND it works on some screws, as well!!!. Stop using rocks and low test fishing line to do the same thing. 😀
I've also heard the tired old saw of "Well Jeff, just because you can do something in SQL Server, doesn't mean you should". My retort is that "Just because you can do something in SQL Server, doesn't mean you SHOULDN'T".
That usually ends up with "Well Jeff, SQL Server isn't the center of the Universe". BWAAAA-HAAAA-HAAAA!!!! Let's turn it off and see if that's true. 😀 😀 😀
"But we don't know how to do it in Stored Procedures". Heh... then you're stuck with the slow and traffic intensive code you wrote or you need to learn how to do it or you need to hire someone that does. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
August 20, 2020 at 9:26 pm
I agree with the "it depends" crowd. I've had this discussion on a number of occasions, especially with younger developers who see everything in object oriented terms, and often consider the database as merely a repository for data. Many of these devs dislike stored procedures, because they don't know how to write good SQL code that is performant. They don't have a view of data from a set based perspective, and think about what to do with each record, as opposed to what to do with thousands, or millions, of records. This extends to even simple tasks. I support financial systems, which may skew my perspective a bit.
I asked a dev one time to copy data from an existing database to a new database, and gave him a list of tables. Instead of using an insert DB2.dbo.tablename select * from DB1.dbo.tablename, he wrote a cursor for each table, and wrote the records one at a time. This was what he would do in C#, so he did it in SQL. He was offended when I explained that there was a better way to do it, saying "the cursors worked, didn't they?".
I had a lead developer tell me that his new EAV(entity, attribute, value) table meant we didn't need to create any new tables when developing new features, because everything could be stored in the EAV table.He literally screamed at me when I told him I was not going to follow that pattern, and that in SQL it is actually an anti-pattern that kills performance. He hated stored procedures as well, and was very upset when he was told he could not replace SP's with code when we migrated an app from the desktop to a web interface. That app has been running those SP's for 19 years, they do exactly what they need to do, and are high performance. I can't imagine they can be rewritten in C# and LINQ and run any better.
In another case, we had a process that was taking 10 to 30 minutes to insert 10 to 100 records in a table. The logic is fairly complex, but straightforward. After troubleshooting the LINQ code, for a week, they decided to get rid of a comma, and the process dropped to 15 seconds. In SQL, that troubleshooting would have been a matter of minutes, and that's only if the original SQL was badly written.
Viewing 6 posts - 31 through 35 (of 35 total)
You must be logged in to reply to this topic. Login to reply