May 18, 2018 at 7:58 am
sgmunson - Friday, May 18, 2018 6:51 AMLuis Cazares - Thursday, May 17, 2018 6:51 PMI agree on what you're saying, but I believe that the queries should be rewritten from scratch. Going over 10 iTVF might start to become a problem when generating execution plans by the optimizer.
Yep, figured that would be likely. However, these functions may be used elsewhere, so they might not be able to simply "go away". That said, it seems likely that even after they were all converted to ITVF's, that it may be necessary to go in an entirely new direction for the purposes of what would otherwise be joining a crap ton of them together in a single query.
This route was taken because the same thing was being recalculated in a dozen different places and when the formula changed, half of them would get missed. It looks like I am in for a major rewrite.
May 18, 2018 at 8:39 am
GBimberg - Friday, May 18, 2018 7:58 AMsgmunson - Friday, May 18, 2018 6:51 AMLuis Cazares - Thursday, May 17, 2018 6:51 PMI agree on what you're saying, but I believe that the queries should be rewritten from scratch. Going over 10 iTVF might start to become a problem when generating execution plans by the optimizer.
Yep, figured that would be likely. However, these functions may be used elsewhere, so they might not be able to simply "go away". That said, it seems likely that even after they were all converted to ITVF's, that it may be necessary to go in an entirely new direction for the purposes of what would otherwise be joining a crap ton of them together in a single query.
This route was taken because the same thing was being recalculated in a dozen different places and when the formula changed, half of them would get missed. It looks like I am in for a major rewrite.
Yeah, unfortantely... However, it might even go deeper, as you may have a tad too much of your business logic in your database. There are some things that it just doesn't make sense to have in the database, and others that it's critical to have in the database. Knowing the difference is critical to getting a good overall architecture for your application functionality.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 18, 2018 at 9:14 am
Why not run each TVF separately, capture the output to temporary tables, then join the temporary tables? You can optimize the performance of the final large join by adding suitable indexes to the temporary tables, then you won't have to rewrite anything and you are only limited by the running time of each TVF.
May 18, 2018 at 9:17 am
William Rayer - Friday, May 18, 2018 9:14 AMWhy not run each TVF separately, capture the output to temporary tables, then join the temporary tables? You can optimize the performance of the final large join by adding suitable indexes to the temporary tables, then you won't have to rewrite anything and you are only limited by the running time of each TVF.
It's not a bad idea in and of itself, but as joining them causes grief, they are probably poorly written to begin with, and running all of them into temp tables will likely be more expensive than might be thought. It's something I would willingly test, given the opportunity though.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 18, 2018 at 1:49 pm
sgmunson - Friday, May 18, 2018 9:17 AMWilliam Rayer - Friday, May 18, 2018 9:14 AMWhy not run each TVF separately, capture the output to temporary tables, then join the temporary tables? You can optimize the performance of the final large join by adding suitable indexes to the temporary tables, then you won't have to rewrite anything and you are only limited by the running time of each TVF.It's not a bad idea in and of itself, but as joining them causes grief, they are probably poorly written to begin with, and running all of them into temp tables will likely be more expensive than might be thought. It's something I would willingly test, given the opportunity though.
I have found that setting the Legacy Cardinality Estimation option to On gets me around the issue for now. In the long term I am going to have to use a combination of In-Line functions and running the Multi-Statement functions in small groups.
May 19, 2018 at 7:06 am
GBimberg - Friday, May 18, 2018 1:49 PMsgmunson - Friday, May 18, 2018 9:17 AMWilliam Rayer - Friday, May 18, 2018 9:14 AMWhy not run each TVF separately, capture the output to temporary tables, then join the temporary tables? You can optimize the performance of the final large join by adding suitable indexes to the temporary tables, then you won't have to rewrite anything and you are only limited by the running time of each TVF.It's not a bad idea in and of itself, but as joining them causes grief, they are probably poorly written to begin with, and running all of them into temp tables will likely be more expensive than might be thought. It's something I would willingly test, given the opportunity though.
I have found that setting the Legacy Cardinality Estimation option to On gets me around the issue for now. In the long term I am going to have to use a combination of In-Line functions and running the Multi-Statement functions in small groups.
Don't feel alone there. The new and "improved" cardinality guess-timator screwed a fair bit of our core code to the floor and we had to go back to the old one. I hope they keep the old one around permanently because we just don't have the time to go and change (notice I did NOT say "fix" because it was running fast and fine) all the code that this affected.
This is all continued proof that "Change is inevitable... change for the better is not". 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
May 21, 2018 at 11:20 am
GBimberg - Thursday, May 17, 2018 2:24 PM
Joe, enough of bashing COBOL programmers!!! You are so stuck in the past and thinking that those of us who made a living writing COBOL have absolutely NO IDEA how to write (or develop) good, high performing, scalable T-SQL code.
Just go away. You aren't helpful.
I'm not bashing COBOL programmers. If we were talking about English language learners, who insisted on using, say, Japanese verb tenses, I would not be criticizing Japanese. I'm simply saying they're using the wrong mindset for the language is supposed to be working in.
I've been teaching SQL to a few hundred people in person, and, and more, in my books for a few decades. This is given me a chance to learn that people make characteristic errors, usually based on the first programming language they learned or the first programming language of people that taught them. It actually goes deeper than that. Procedural languages are fundamentally different from declarative languages. Just as Japanese does not have subjects in sentences, or articles or plurals, or the way that English is not heavily gendered or inflected, SQL is not like other programming languages.
Sorry, Lynn, but why don't you go away until you have at least 50 years in IT? Or until you made a contribution to industry standards and techniques?
Please post DDL and follow ANSI/ISO standards when asking for help.
May 21, 2018 at 11:51 am
sgmunson - Thursday, May 17, 2018 2:56 PMGBimberg - Thursday, May 17, 2018 2:24 PM
Joe, enough of bashing COBOL programmers!!! You are so stuck in the past and thinking that those of us who made a living writing COBOL have absolutely NO IDEA how to write (or develop) good, high performing, scalable T-SQL code.
Just go away. You aren't helpful.
I'm not bashing COBOL programmers. If we were talking about English language learners, who insisted on using, say, Japanese verb tenses, I would not be criticizing Japanese. I'm simply saying they're using the wrong mindset for the language is supposed to be working in.
I've been teaching SQL to a few hundred people in person, and, and more, in my books for a few decades. This is given me a chance to learn that people make characteristic errors, usually based on the first programming language they learned or the first programming language of people that taught them. It actually goes deeper than that. Procedural languages are fundamentally different from declarative languages. Just as Japanese does not have subjects in sentences, or articles or plurals, or the way that English is not heavily gendered or inflected, SQL is not like other programming languages.
Sorry, Lynn, but why don't you go away until you have at least 50 years in IT? Or until you made a contribution to industry standards and techniques?
You know, Mr. Celko, I realize that you have a lot of good knowledge to pass on to others. Too bad you choose to bash people over the head and degrade them and their knowledge (and sometimes lack of knowledge).
Perhaps if you changed your approach you'd find more people open to learning from you.
I don't need at least 50 years experience in IT or contribute to industry standards to be an active and contributing member of this community.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply