SQL Server transferring results slowly in table result set, but instantly with XML

Question

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

UPDATE

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.

Answer 1

Those XML results you keep mentioning. I do not think you are seeing what you think you are seeing. ;-)

Is FOR XML magic?

When you have the XML result in the grid, click on the left margin that contains the row number of the result set (there should be just 1 row). That should highlight the XML result. Now hit Control-C to copy the XML and then in the query editor above, put the cursor on the beginning of an empty line and hit Control-V to paste the XML. That should leave the cursor on the far right, at the end of the XML. Look down at the status bar that has the Line #, Column #, and Character #. What does it shown for Column # and Character #? Mine shows 2033, yet the XML got clipped and there should be more.

Now click on the XML itself in the result set. That takes you to another tab with the full XML result, unclipped. That is more than likely well over 2033 characters of text.

The "instantaneous" result you are seeing with the FOR XML is merely the fact that SSMS is only displaying the first 2033 characters of however many there are, where it is 5000 or 5,000,000 characters more in the actual result value. And in fact, if your query takes long enough, you can pay close attention to the query timer in the lower, right-hand corner and if you re-run the query and are returning 10+ rows, that counter can still be going after the result grid finishes displaying its truncated XML snippet.

Do the number of rows and columns greatly affect the time to display?

I put together some code (shown below), which returns 2300 columns, to test this behavior. It starts out with the FOR XML being uncommented. After the initial run, it returns immediately each time, no matter how you adjust the @Rows input param. Set it to 10, then 20, then 40. The XML always comes back immediately. But also watch the query timer as it will keep going, especially as you increase the value of @Rows.

Then comment-out the SET statement that adds the FOR XML so you get the 2300 individual fields in the grid. It might take 30 takes to run the first time. Running again it took me only 2 - 3 seconds each time, even as I increased the @Rows from 4 to 10, 20, 40, 50, even 80. Hence, the time it takes to display the results for 2000+ columns is not a function of how many rows are being returned, at least not at this small scale.

The Code

DECLARE @Select NVARCHAR(MAX) = N'SELECT TOP (@Rows)
         OBJECT_NAME(sac1.[object_id]) AS [ObjectName], *';
DECLARE @From NVARCHAR(MAX) = N'FROM   [sys].[all_columns] sac1';
DECLARE @JoinTemplate NVARCHAR(MAX) = N'
INNER JOIN [sys].[all_columns] sac{{counter}}
        ON sac{{counter}}.[object_id] = sac1.[object_id]
       AND sac{{counter}}.[column_id] = sac1.[column_id]';

DECLARE @CRLF NCHAR(2) = NCHAR(0x0D) + NCHAR(0x0A);
DECLARE @Counter INT = 2;

WHILE (@Counter < 93)
BEGIN
  SET @From += REPLACE(@JoinTemplate, N'{{counter}}', CONVERT(NVARCHAR(5), @Counter));

  SET @Counter += 1;
END;


SET @Select += @CRLF + @From;
--SET @Select += @CRLF + N'ORDER BY OBJECT_NAME(sac1.[object_id])';
SET @Select += @CRLF + N' FOR XML RAW, ELEMENTS';

EXEC sp_executesql
  @Select,
  N'@Rows INT',
  @Rows = 4;

Uncomment the SET line that has the ORDER BY to see that it takes the full 30 - 40 seconds every time you run the query (due to the sort).

Answer 2

This sounds like an overhead from ADO.Net in constructing the dataset for consumption within your application. With a single column being returned for XML, this overhead isn't necessary.

Your situation is rather extreme - that's a lot of columns to deal with. So I'd say, stick with using XML!