SQL Server Hanging after Insert Into Select

Question

Thanks for taking a look at this.

The issue has to do with the following sql query. Basically it recursively finds all pages underneath a certain page(master page), and then applies the master page's categories(looked up in the xref_pages_categories) to all of those pages.

Upon running the insert, querying the table for data that should have been inserted completely hangs.

If i run the query without the insert line, the results come back perfectly, like:

3245    490
3249    490
3252    490

But upon putting the insert in front of it and inserting, querying for pageid=3245 just hangs. Any ideas?

declare @page int;

set @page=3202;
begin transaction transki;

With FindAllPagesUnderneath(PageID, ParentID) as
(
    select id as PageID, ParentPageId as ParentID
    from pages where id=@page
    union all
    select id as PageID, ParentPageID as ParentID
    from pages p
    inner join FindAllPagesUnderneath mp on
    p.parentpageid=mp.PageID
)

insert into xref_pages_categories (PageID, CategoryID)


    --get records that definitely arent already in xref_pages_categories
select * from
(
    select distinct thegood.*
    from 
        (
        --get all pages under @page attached to new categoryid
        select distinct fap2.PageID, fapo.CategoryID
            from FindAllPagesUnderneath fap2
            cross join  (
                select fap.PageID,xpc.categoryid 
                from xref_pages_categories xpc
                inner join FindAllPagesUnderneath fap
                on fap.PageID=xpc.pageid
                where fap.pageid!=1 and
                fap.pageid!=1332
                and fap.parentid=1332
            ) fapo 
        where fap2.pageid !=@page
        ) thegood
        left outer join xref_pages_categories xpc
        on xpc.pageid = thegood.pageid
        where xpc.pageid is null
) final

Answer 1

without looking into all of your code, at execution plans, or locking/blocking issues, you could try this hack:

create this table before the CTE:

CREATE TABLE #TempResults 
(
PageID         ...
,CategoryID    ...
)

INSERT into it and not xref_pages_categories (using the CTE SELECT)

then put this at the end of the code block:

insert into xref_pages_categories (PageID, CategoryID)
    SELECT PageID, CategoryID FROM #TempResults 

This INSERT should run faster and hopefully not lock/block like they are doing now as part of the CTE.