5

I've got a schema roughly like this (this is a simplification of my actual schema):

CREATE TABLE foo (
    key1 NUMERIC(6) NOT NULL,
    key2 VARCHAR(32) NOT NULL,
    val VARCHAR(255) NULL,
    CONSTRAINT foo_pk PRIMARY KEY (key1, key2) --PK on key1, key2
    )
GO
CREATE TABLE bar (
    key1 NUMERIC(6) NOT NULL,
    key2 VARCHAR(32) NOT NULL,
    val VARCHAR(255) NULL,
    CONSTRAINT bar_pk PRIMARY KEY (key1, key2) --PK on key1, key2
    )
GO
CREATE TABLE aliases (
    id VARCHAR(32) NOT NULL PRIMARY KEY,
    text VARCHAR(255) NOT NULL,
    CONSTRAINT aliases_uk UNIQUE (text) --PK on id, unique constraint on text
    )
GO

INSERT INTO aliases (id, text)
SELECT REPLACE(NEWID(), '-', ''), 'Text1' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text2' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text3' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text4' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text5' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text6' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text7' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text8' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text9' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text10' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text11' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text12' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text13' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text14' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text15' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text16';
GO

BEGIN
    DECLARE @i INT = 1;
    WHILE (@i <= 234)
    BEGIN
        INSERT INTO foo (key1, key2, val) SELECT @i, id, id FROM aliases;
        SET @i = @i + 1;
    END;
    INSERT INTO bar (key1, key2, val) SELECT key1 + 234, key2, val FROM foo;
END;
GO

I want to get a list of all key1 and key2 pairs (using the user-friendly names for key2, with the user-friendly aliases for the vals concatenated. My initial attempt looks like this:

WITH
foos_and_bars (key1, key2, val) AS (
    SELECT key1, key2, val FROM foo UNION ALL
    SELECT key1, key2, val FROM bar),
texts (key1, key2_name, val_text, rnum) AS (
    SELECT key1, a1.text, a2.text, ROW_NUMBER() OVER (PARTITION BY key1, key2 ORDER BY val)
    FROM foos_and_bars
    JOIN aliases a1 ON a1.id = foos_and_bars.key2
    LEFT JOIN aliases a2 ON a2.id = foos_and_bars.val),
partitioned (key1, key2_name, val_text, rnum, maxnum) AS (
    SELECT key1, key2_name, val_text, rnum, MAX(rnum) OVER (PARTITION BY key1, key2_name)
    FROM texts),
recurse (key1, key2_name, val_texts, rnum, maxnum) AS (
    SELECT key1, key2_name, val_text, rnum, maxnum
    FROM partitioned
    WHERE rnum = 1
    UNION ALL
    SELECT r.key1,
        r.key2_name, 
        CAST(r.val_texts + CHAR(13) + CHAR(10) + p.val_text AS VARCHAR(255)),
        p.rnum,
        r.maxnum
    FROM recurse r
    JOIN partitioned p
    ON p.key1 = r.key1
    AND p.key2_name = r.key2_name
    AND p.rnum = r.rnum + 1)
SELECT * FROM recurse WHERE rnum = maxnum;

It works, and I get 7480 rows in less than a second, but then SSMS runs for no less than 2 minutes and 40 seconds puzzling out the last 8 rows. These last rows are no more complex than the first (in fact, with the data currently in the system, rnum and maxnum are never greater than 1), and as these tables were created exclusively for mocking on this SE question, there should be no locks.

The issue seems to be limited to the recursive CTE, as selecting from the top down to partitioned yields 7488 rows in less than a second. What could the CTE be getting stuck on?

Execution plans (using a setup that matches the simplified schema):

The output is going into a view that is going to back a Sybase window, which will be used for searching records. As such, it needs to be capable of retrieving all the rows, though the program pulls 1000 rows at a time by default.

  • It's possible you're just hitting a buffering limit, see if the wait type in sys.dm_exec_requests during those 17 minutes is ASYNC_NETWORK_IO. Also see if eliminating one or more columns from the result changes the behavior. But really, what are you going to do with 8,000 rows in an SSMS grid? Is this testing/demonstrating a real world use case? – Aaron Bertrand Feb 7 '18 at 17:40
  • 2
    Demonstrating a problem in SSMS might just be showing that SSMS is the problem, and isn't necessarily a fair comparison to your application. Hopefully your program isn't as inefficient at retrieving and rendering results as SSMS. Another thing to try is to return results to text or file instead of to grid (this will let you know in another way that the grid rendering is the problem, not the query). – Aaron Bertrand Feb 7 '18 at 18:27
  • If the recursive CTE over the (other CTEs) is the problem, why not try to put the partitioned result set into a temp table (and index accordingly) and then run the resursive CTE over that? – ypercubeᵀᴹ Feb 7 '18 at 20:52
  • I tried your query and got Msg 240, Level 16, State 1, Line 1 Types don't match between the anchor and the recursive part in column "val_texts" of recursive query "recurse". Can you show us the desired results? – Aaron Bertrand Feb 7 '18 at 23:15
  • For future readers, this was eventually solved by persisting the data to a table. – JAF Apr 25 '18 at 12:49
6

The anchor part of the recursive SQL query produces all 7488 rows. On my machine, that part of the query finishes in under 100 ms. SSMS does not immediately show all 7488 rows in the grid results. It only shows 7480 for me as well. I suspect this happens because the results are sent in packets and the remaining 8 rows aren't enough to fill a packet.

The second half of the query is extremely expensive compared to the first. Looking at the execution plan you can see six clustered index scans on the inner side of a nested loop. Those scans will execute at least once for every row from the anchor part. SQL Server does a total of 44928 scans over a few minutes to ultimately produce 0 rows from that part. Once that part of the execution is complete SQL Server sends the remaining packet of rows to the client and you see all 7488 rows in the results grid of the completed query. It's not true that it takes three minutes to find the last 8 eight rows. It finds them almost instantly. It just takes three minutes to send them.

You can see a large amount of work performed in the bottom half of the query by looking at the actual plan. Note the thickness of the arrows and the number of executions for the scan operators:

actual plan

That's why the query is slow. I don't have any advice for you to make it fast because I don't understand what you're trying to accomplish with this query.

  • Thank you for your reply. This query is going to become a view that will populate a search grid in an application, and the recursive part is meant to concatenate all the vals together into one row, separated by line returns (putting line returns in a grid view was a decision by the powers-that-be), e.g. | Key1 | Key2 | val1 \r\n val2 \r\n val3 | (forgive this horrible formatting!) – JAF Feb 8 '18 at 18:16
  • Since it's joining to itself so often, perhaps caching the results into a table would be a viable workaround. – JAF Feb 8 '18 at 18:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.