Long query with Table Spool

  • I'm having trouble with this query. It seems to be running for much longer than should be needed. I'm not familiar with "Table Spools", what causes them or how to resolve them.

    Below is my query and the plan text. Thanks in advance.

    StmtText

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    DECLARE@last_x_days int

    SELECT @last_x_days = 7

    DECLARE @date smalldatetime

    SELECT @date = getdate()

    --Next, fill with active users (users that have logged in w/i the past x days)

    UPDATE #branch_users

    SET greenlight = 1

    WHERE branchid in (

    SELECT db.branchid

    FROM appian.dbo.users USERS

    JOIN dealerinfo.dbo.v_DealerBranches DB ON DB.dlrcode = USERS.dlrcode

    JOIN dealernet.dbo.v_loginlog LL ON LL.auid = appian.dbo.udf_A

    (3 row(s) affected)

    StmtText

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    |--Table Update(OBJECT: ([tempdb].[dbo].[#branch_users_______________________________________________________________________________________________________0000000001B2]), SET: ([#branch_users].[greenlight]=1))

    |--Top(ROWCOUNT est 0)

    |--Nested Loops(Left Semi Join, OUTER REFERENCES: ([#branch_users].[branchid]))

    |--Table Scan(OBJECT: ([tempdb].[dbo].[#branch_users_______________________________________________________________________________________________________0000000001B2]))

    |--Row Count Spool

    |--Nested Loops(Inner Join, WHERE: ([appian].[dbo].[udf_AUID]([login_log].[user_id])=[appian].[dbo].[udf_AUID]([USERS].[user_id])))

    |--Nested Loops(Inner Join, WHERE: ([DlrCodes].[DlrCode]=Convert([USERS].[dlrcode])))

    | |--Nested Loops(Inner Join)

    | | |--Index Seek(OBJECT: ([dealerinfo].[dbo].[Branches].[PK_Branches]), SEEK: ([Branches].[BranchID]=[#branch_users].[branchid]) ORDERED FORWARD)

    | | |--Nested Loops(Inner Join, OUTER REFERENCES: ([DlrPrefixes].[DlrID]))

    | | |--Nested Loops(Inner Join, OUTER REFERENCES: ([DlrPrefixes].[RegionID]))

    | | | |--Nested Loops(Inner Join, OUTER REFERENCES: ([DlrCodes].[DlrPrefix]))

    | | | | |--Index Spool(SEEK: ([DlrCodes].[BranchID]=[#branch_users].[branchid]))

    | | | | | |--Clustered Index Scan(OBJECT: ([dealerinfo].[dbo].[DlrCodes].[PK_DlrCodes]))

    | | | | |--Clustered Index Seek(OBJECT: ([dealerinfo].[dbo].[DlrPrefixes].[PK_DlrPrefixes]), SEEK: ([DlrPrefixes].[DlrPrefix]=[DlrCodes].[DlrPrefix]) ORDERED FORWARD)

    | | | |--Index Seek(OBJECT: ([dealerinfo].[dbo].[Regions].[PK_Regions]), SEEK: ([Regions].[RegionId]=[DlrPrefixes].[RegionID]) ORDERED FORWARD)

    | | |--Clustered Index Seek(OBJECT: ([dealerinfo].[dbo].[Dealers].[PK_Dealers]), SEEK: ([Dealers].[DlrID]=[DlrPrefixes].[DlrID]) ORDERED FORWARD)

    | |--Table Spool

    | |--Clustered Index Scan(OBJECT: ([appian].[dbo].[users].[PK_users] AS [USERS]), WHERE: (Convert([USERS].[is_active])=1))

    |--Table Spool

    |--Clustered Index Scan(OBJECT: ([appian].[dbo].[login_log].[PK_login_log]), WHERE: ((([login_log].[site_id]='MSDNET' OR [login_log].[site_id]='PMDNET') AND datediff(day, [login_log].[dt_login], Convert([@date]))<=[@last_x_day (21 row(s) affected)

  • Re-write as an EXISTS, rather than IN sub-query:

    UPDATE #branch_users

    SET greenlight = 1

    FROM #branch_users As B

    WHERE EXISTS (

      SELECT *

      FROM appian.dbo.users USERS

        JOIN dealerinfo.dbo.v_DealerBranches DB ON DB.dlrcode = USERS.dlrcode

        JOIN dealernet.dbo.v_loginlog LL ON LL.auid = appian.dbo.udf_A

      WHERE DB.branchid = B.branchid

    )

  • That doesn't make any difference in the query performance.

    The query is still performing TABLE SPOOLS and LAZY SPOOLS.

    Any other ideas?

    StmtText

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    DECLARE@last_x_days int

    SELECT @last_x_days = 7

    DECLARE @date smalldatetime

    SELECT @date = getdate()

    --NEXT, FILL TEMP TABLE WITH ACTIVE USERS (USERS THAT HAVE LOGGED IN WITHIN THE PAST x DAYS)

    UPDATE #branch_users

    SET greenlight = 1

    FROM #branch_users As B

    WHERE EXISTS (

    SELECT *

    FROM appian.dbo.users USERS

    JOIN dealerinfo.dbo.v_DealerBranches DB ON

    DB.dlrcode = USERS.dlrcode

    JOIN dealernet.dbo.v_loginlog LL ON

    LL.auid = appian.dbo.udf_AUID(USERS.user_id)

    WHERE DB.branchid = B.branchid

    AND (USERS.is_active = 1) -- and users.approved=1)

    AND ( datediff(d,LL.dt_login,@date) <= @last_x_days

    AND datediff(d,@date,LL.dt_login) < 1))

    (3 row(s) affected)

    StmtText

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    |--Table Update(OBJECT: ([tempdb].[dbo].[#branch_users_______________________________________________________________________________________________________0000000001D9]), SET: ([#branch_users].[greenlight]=1))

    |--Top(ROWCOUNT est 0)

    |--Nested Loops(Left Semi Join, OUTER REFERENCES: (.[branchid]))

    |--Table Scan(OBJECT: ([tempdb].[dbo].[#branch_users_______________________________________________________________________________________________________0000000001D9] AS ))

    |--Row Count Spool

    |--Nested Loops(Inner Join, WHERE: ([appian].[dbo].[udf_AUID]([login_log].[user_id])=[appian].[dbo].[udf_AUID]([USERS].[user_id])))

    |--Nested Loops(Inner Join, WHERE: ([DlrCodes].[DlrCode]=Convert([USERS].[dlrcode])))

    | |--Nested Loops(Inner Join)

    | | |--Index Seek(OBJECT: ([dealerinfo].[dbo].[Branches].[PK_Branches]), SEEK: ([Branches].[BranchID]=.[branchid]) ORDERED FORWARD)

    | | |--Nested Loops(Inner Join, OUTER REFERENCES: ([DlrPrefixes].[DlrID]))

    | | |--Nested Loops(Inner Join, OUTER REFERENCES: ([DlrPrefixes].[RegionID]))

    | | | |--Nested Loops(Inner Join, OUTER REFERENCES: ([DlrCodes].[DlrPrefix]))

    | | | | |--Index Spool(SEEK: ([DlrCodes].[BranchID]=.[branchid]))

    | | | | | |--Clustered Index Scan(OBJECT: ([dealerinfo].[dbo].[DlrCodes].[PK_DlrCodes]))

    | | | | |--Clustered Index Seek(OBJECT: ([dealerinfo].[dbo].[DlrPrefixes].[PK_DlrPrefixes]), SEEK: ([DlrPrefixes].[DlrPrefix]=[DlrCodes].[DlrPrefix]) ORDERED FORWARD)

    | | | |--Index Seek(OBJECT: ([dealerinfo].[dbo].[Regions].[PK_Regions]), SEEK: ([Regions].[RegionId]=[DlrPrefixes].[RegionID]) ORDERED FORWARD)

    | | |--Clustered Index Seek(OBJECT: ([dealerinfo].[dbo].[Dealers].[PK_Dealers]), SEEK: ([Dealers].[DlrID]=[DlrPrefixes].[DlrID]) ORDERED FORWARD)

    | |--Table Spool

    | |--Clustered Index Scan(OBJECT: ([appian].[dbo].[users].[PK_users] AS [USERS]), WHERE: (Convert([USERS].[is_active])=1))

    |--Table Spool

    |--Clustered Index Scan(OBJECT: ([appian].[dbo].[login_log].[PK_login_log]), WHERE: ((([login_log].[site_id]='MSDNET' OR [login_log].[site_id]='PMDNET') AND datediff(day, [login_log].[dt_login], Convert([@date]))<=[@last_x_day

    (21 row(s) affected)

  • Probably best to post the DDL of all tables/views involved.

    What size is the loginlog table ? It has a clustered index scan which migth be costly depending on its size.

     

  • What's the best way to get the DDL for you?

    The Loginlog table contains 2,025,274 records.

  • Just select the objects in EM and generate a SQL script, setting the options to script keys & indexes.

    A table of 2+ million rows being scanned is definitely not desirable. Finding a way to get the optimiser to perform an index seek on the loginlog table is bound to improve things.

     

  • David,

    According to your profiler trace, the pepper doesn't turn to fly specs until the appian.dbo.udf_A User Defined Function hits the fan... that's where your problem is and you should probably post the code for THAT.  If you don't think so, tell me where in the script you submitted that dealer regions are found and the IsActive flag is interrogated...

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Clause in your UDF appian.dbo.udf_A

    "WHERE ... datediff(day, [login_log].[dt_login], Convert([@date]))<=[@last_x_day"

    eliminates all indexes and makes server scan whole table and return all rows, because it cannot predict result of the calculation.

    Change it to

    [login_log].[dt_login]>= datediff(day, - @last_x_day, Convert([@date]))

    and make sure you have clustered index on column dt_login.

    _____________
    Code for TallyGenerator

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply