SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Long query with Table Spool


Long query with Table Spool

Author
Message
David Taylor-256418
David Taylor-256418
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 82
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)
PW-201837
PW-201837
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4495 Visits: 1228

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
)


David Taylor-256418
David Taylor-256418
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 82
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: ([B].[branchid]))
|--Table Scan(OBJECT: ([tempdb].[dbo].[#branch_users_______________________________________________________________________________________________________0000000001D9] AS [B]))
|--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]=[B].[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]=[B].[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)
PW-201837
PW-201837
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4495 Visits: 1228

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.


David Taylor-256418
David Taylor-256418
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 82
What's the best way to get the DDL for you?

The Loginlog table contains 2,025,274 records.
PW-201837
PW-201837
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4495 Visits: 1228

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.


Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)

Group: General Forum Members
Points: 207342 Visits: 41961

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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Sergiy
Sergiy
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24713 Visits: 12464

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.


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search