Long query with Table Spool

Question

Post reply

Long query with Table Spool

David Taylor-256418

SSC Enthusiast

Points: 137
More actions
September 9, 2005 at 1:43 pm

#102785

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)

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

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

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

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 SSC Enthusiast Points: 137 More actions · Answer 2

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)

PW-201837 SSC-Insane Points: 20805 More actions · Answer 3

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 SSC Enthusiast Points: 137 More actions · Answer 4

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

The Loginlog table contains 2,025,274 records.

PW-201837 SSC-Insane Points: 20805 More actions · Answer 5

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 SSC Guru Points: 1004365 More actions · Answer 6

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)

Sergiy SSC Guru Points: 110208 More actions · Answer 7

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