Alan.B (5/29/2015)
Yeah, unfortunately make_parallel can't help you with that:-PI have a couple functions for digging out data sets, parameter info and stuff like that. I'll post them here when I find them. They may help a little. In the meantime I did notice that you are converting some values to nvarchar(max). If you can get away with it change those to varchar(something else) - that may help a little (not much).
Good call on nvarchar(max) data types... Considering the data, they don't need to be nvarchar and they don't need to be max. Small wins are still wins...
If you have some same code and/or functions that make life with XML easier, that you're willing to share, that would be awesome... I despise the very idea of XML being in a RDBMS. Unfortunately, that doesn't exempt me from having to contend with it from time to time (it just keeps me from dedicating time to learning how to work with it). Having a good code reference on hand would be nice. 😀
Alan.B (5/29/2015)
One thing I have done in the past is created a SQL job and had it run at night to run the query and write the results to a table. That way you have good info through yesterday that you can further filter on. Not the most ideal solution but it's better than listening to that one little CPU break it's back for 30 minutes.
That was the plan from the beginning. I just wanted to see if I could get the execution times down before I started cramming things into SSIS packages.