June 18, 2018 at 5:07 pm
I am trying to convert a MTVF into an In-Line TVF. I already went through and changed a whole lot but still I don't see the result sets aligning correctly. I believe I am missing some fields that needs to be part of the In-Line TVF which is par tof the MTVF. Any guidelines or suggestions on how to get this fixed. Also, do let me know if any further details are needed.
Note - I've commented out a few columns and those are the columns that I couldn't convert from the original MTVF into the in-Line TVF and need help to align them.
June 19, 2018 at 6:35 am
With no data to use to compare these, it's a little harder to ferret things out, but at a quick glance, you have a column name that is different in your new function than in the old. The old was VolumeCompleted, and the new uses DealVolumeCompleted. After that, however, there are missing columns, and it becomes hard to tell whether it's intentional or not, because at least one of the missing columns is commented out in the new code. That's the point at which, with no sample data or table create statements to work with, I kind of have to give up due to time constraints. You can easily figure out what columns are missing just by comparing the SELECT list, as I just did (briefly, and not in full). You can also view the data - something I can't do.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 19, 2018 at 8:57 am
sgmunson - Tuesday, June 19, 2018 6:35 AMWith no data to use to compare these, it's a little harder to ferret things out, but at a quick glance, you have a column name that is different in your new function than in the old. The old was VolumeCompleted, and the new uses DealVolumeCompleted. After that, however, there are missing columns, and it becomes hard to tell whether it's intentional or not, because at least one of the missing columns is commented out in the new code. That's the point at which, with no sample data or table create statements to work with, I kind of have to give up due to time constraints. You can easily figure out what columns are missing just by comparing the SELECT list, as I just did (briefly, and not in full). You can also view the data - something I can't do.
Columns which are commented out are the ones I wasn't able to convert it from the original MTVF function and that is where I needed help. Also, I can work on the data validation piece but that would be later step...right now I am more keen on getting the columns aligned. Also, I've already gone through the SELECT list and as mentioned above I've commented those columns out but since those are based off of expression I am not really sure how to convert/align them in the new function.
June 19, 2018 at 10:51 am
Feivel - Tuesday, June 19, 2018 8:57 AMsgmunson - Tuesday, June 19, 2018 6:35 AMWith no data to use to compare these, it's a little harder to ferret things out, but at a quick glance, you have a column name that is different in your new function than in the old. The old was VolumeCompleted, and the new uses DealVolumeCompleted. After that, however, there are missing columns, and it becomes hard to tell whether it's intentional or not, because at least one of the missing columns is commented out in the new code. That's the point at which, with no sample data or table create statements to work with, I kind of have to give up due to time constraints. You can easily figure out what columns are missing just by comparing the SELECT list, as I just did (briefly, and not in full). You can also view the data - something I can't do.Columns which are commented out are the ones I wasn't able to convert it from the original MTVF function and that is where I needed help. Also, I can work on the data validation piece but that would be later step...right now I am more keen on getting the columns aligned. Also, I've already gone through the SELECT list and as mentioned above I've commented those columns out but since those are based off of expression I am not really sure how to convert/align them in the new function.
As I recall, many of those commented out lines were pointing to other functions, which from a performance perspective, isn't likely to be good. You would have to reverse engineer each such reference, and then integrate that code into the query, or simply bring those along for the potentially ugly ride, and only reverse engineer when it becomes necessary to do so. The other thing I'll mention is why are there so many columns that rely on functions? It makes me wonder about whether the database was designed correctly in the first place, or if the database is necessarily the right place for those things to be computed. Piling one function on top of another is usually a bad idea gone wrong right from the start, and when I see this kind of code, I'm guessing that such code permeates your database. Somebody has a bad habit and it needs to stop before it becomes unmanageable.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 19, 2018 at 3:58 pm
sgmunson - Tuesday, June 19, 2018 10:51 AMFeivel - Tuesday, June 19, 2018 8:57 AMsgmunson - Tuesday, June 19, 2018 6:35 AMWith no data to use to compare these, it's a little harder to ferret things out, but at a quick glance, you have a column name that is different in your new function than in the old. The old was VolumeCompleted, and the new uses DealVolumeCompleted. After that, however, there are missing columns, and it becomes hard to tell whether it's intentional or not, because at least one of the missing columns is commented out in the new code. That's the point at which, with no sample data or table create statements to work with, I kind of have to give up due to time constraints. You can easily figure out what columns are missing just by comparing the SELECT list, as I just did (briefly, and not in full). You can also view the data - something I can't do.Columns which are commented out are the ones I wasn't able to convert it from the original MTVF function and that is where I needed help. Also, I can work on the data validation piece but that would be later step...right now I am more keen on getting the columns aligned. Also, I've already gone through the SELECT list and as mentioned above I've commented those columns out but since those are based off of expression I am not really sure how to convert/align them in the new function.
As I recall, many of those commented out lines were pointing to other functions, which from a performance perspective, isn't likely to be good. You would have to reverse engineer each such reference, and then integrate that code into the query, or simply bring those along for the potentially ugly ride, and only reverse engineer when it becomes necessary to do so. The other thing I'll mention is why are there so many columns that rely on functions? It makes me wonder about whether the database was designed correctly in the first place, or if the database is necessarily the right place for those things to be computed. Piling one function on top of another is usually a bad idea gone wrong right from the start, and when I see this kind of code, I'm guessing that such code permeates your database. Somebody has a bad habit and it needs to stop before it becomes unmanageable.
I totally get that but my focus is right now on the task at hand as I am in a tight spot and needs to get the work done of optimizing the MTVF to convert it into an InlineTVF. Major problem with these MTVFs is that it doesn't give me any specifics on where exactly it is spending most time on so I can focus on that specific piece of code rather than go over through this whole bunch of crap. Anyways, I would appreciate if you could provide some help or guidelines so I can get started on converting this into an Inline TVF.
June 20, 2018 at 6:42 am
Feivel - Tuesday, June 19, 2018 3:58 PMsgmunson - Tuesday, June 19, 2018 10:51 AMFeivel - Tuesday, June 19, 2018 8:57 AMsgmunson - Tuesday, June 19, 2018 6:35 AMWith no data to use to compare these, it's a little harder to ferret things out, but at a quick glance, you have a column name that is different in your new function than in the old. The old was VolumeCompleted, and the new uses DealVolumeCompleted. After that, however, there are missing columns, and it becomes hard to tell whether it's intentional or not, because at least one of the missing columns is commented out in the new code. That's the point at which, with no sample data or table create statements to work with, I kind of have to give up due to time constraints. You can easily figure out what columns are missing just by comparing the SELECT list, as I just did (briefly, and not in full). You can also view the data - something I can't do.Columns which are commented out are the ones I wasn't able to convert it from the original MTVF function and that is where I needed help. Also, I can work on the data validation piece but that would be later step...right now I am more keen on getting the columns aligned. Also, I've already gone through the SELECT list and as mentioned above I've commented those columns out but since those are based off of expression I am not really sure how to convert/align them in the new function.
As I recall, many of those commented out lines were pointing to other functions, which from a performance perspective, isn't likely to be good. You would have to reverse engineer each such reference, and then integrate that code into the query, or simply bring those along for the potentially ugly ride, and only reverse engineer when it becomes necessary to do so. The other thing I'll mention is why are there so many columns that rely on functions? It makes me wonder about whether the database was designed correctly in the first place, or if the database is necessarily the right place for those things to be computed. Piling one function on top of another is usually a bad idea gone wrong right from the start, and when I see this kind of code, I'm guessing that such code permeates your database. Somebody has a bad habit and it needs to stop before it becomes unmanageable.
I totally get that but my focus is right now on the task at hand as I am in a tight spot and needs to get the work done of optimizing the MTVF to convert it into an InlineTVF. Major problem with these MTVFs is that it doesn't give me any specifics on where exactly it is spending most time on so I can focus on that specific piece of code rather than go over through this whole bunch of crap. Anyways, I would appreciate if you could provide some help or guidelines so I can get started on converting this into an Inline TVF.
Sorry to disappoint you, but just having your somewhat complex query doesn't make it all that easy to help, because I don't have access to your tables, and I don't have the kind of time it would likely take even if I did have such access. However, you may be able to get a better idea of where the time consumption is by running a SQL Server Profiler trace. That would show you exactly where the grief is. Alternatively, if you "include live statistics" with your execution of a query that selects from the function, that can also show you where the most time is being spent, but you will need to observe closely. Additionally, if you can post an actual execution plan for either of your functions, that would be helpful because then we can at least see where the likely causes of grief are. It's also usually an easier thing to get your hands on. Post it as a .sqlplan file rather than posting a picture.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply