I have a query that left joins 100+ tables and returns 2,000+ columns, but only a few rows (10-20). This part is out of my control at the moment and it used to run ok with a bit fewer columns (maybe under the normal non-wide table width). The execution is "instant" after the initial plan is compiled and I don't actually return all the results to the client. If I return 1 row, it is instant and returns the result instantly.
It's when I return all 11 or so results it goes down to taking about 15 seconds. BUT, if I add any of the "FOR XML [RAW [ELEMENTS, XSINIL]]" iterations, it returns all the results plus the XML overhead instantly as well. This rules out the actual execution of the query, as far as I can tell, and isolates it to something between the query results being calculated and them being sent to the client.
I've been looking for about 4 days for anything to explain this. Is there a way to get the XML speeds with the result sets? Why is it doing this?
Have tried TCP/IP and Named Pipes on 2008R2 and 2016
Based on the feedback, for the tabular results, what happens is the first time through, it takes about 20 some seconds. The timer in SSMS keeps running until about 11 seconds, then it stops. Then, when the results are displayed, the timer jumps to the 20 seconds. The next run the timer just hangs, then when the results are show, the timer pops up to around 9-11 seconds.
The way the 11 rows are selected by the joining the unique id of the main table against a table function. I have tried replacing the table function with a temp table. For selecting one record I basically pass data into the table function that results in one result. I also tried using a straight id = literal number. For those, the timer doesn't even leave 0 before the result is shown.
I can't "insert into" a temp table because there are too many columns for a regular table. I also can't create a indexed view on it to check that out.
I ran the query with FOR XML again and checked for the timer. It sits at 0:00 and says query complete. I try again and immediately open the result in xml and it shows 11 rows of xml rows, with the last one of them 54,000 characters. It does indeed only show 2023 if you cut and paste the tabular result. The timing differences might be the amount of data in each row? There are multiple columns with text types.
Speaking of text types, it keeps me from doing a "count distinct" on the full query to pull all the columns but not send them at all. Otherwise I used SSMS and told it to discard the results. I'm not sure if that affects the query plan. It runs fast.
I was hoping to get this thing limping along for enough to change the way it was architected. This simply isn't acceptable in the medium to long term.
THANKS for everyone's insight and help so far.