Long running query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Long running query

Author

Topic

RichardSteele
Posting Yak Master

160 Posts

Posted - 2003-06-20 : 05:13:13

In a ColdFusion template, we have a sql query that inserts new customers from EXPORTCUSTEMAILS table if the customer is not already in the MAILINGLISTNEWMOM table. Both tables have 40,000 records.

The entire query takes 10 minutes to run and hangs our Coldfusion server and website while running.

Is there a better way to write this query?

Many thanks in advance.

INSERT MAILINGLISTNEWMOM (ADDRESS1, ADDRESS2, CITY, COMPANY, COUNTRY, CUSTTYPE,
EMAIL, FIRSTNAME, LASTNAME, PASSWORD, PHONE, STATE, ZIP, ALTNUM, CUSTNUMBER)

SELECT EXPORTCUSTEMAILS.ADDR, EXPORTCUSTEMAILS.ADDR2, EXPORTCUSTEMAILS.CITY, EXPORTCUSTEMAILS.COMPANY, EXPORTCUSTEMAILS.COUNTRY, EXPORTCUSTEMAILS.CUSTTYPE,
EXPORTCUSTEMAILS.EMAIL, EXPORTCUSTEMAILS.FIRSTNAME, EXPORTCUSTEMAILS.LASTNAME, EXPORTCUSTEMAILS.PASSWORD, EXPORTCUSTEMAILS.PHONE, EXPORTCUSTEMAILS.STATE, EXPORTCUSTEMAILS.ZIPCODE, EXPORTCUSTEMAILS.ALTNUM, EXPORTCUSTEMAILS.custnum
FROM EXPORTCUSTEMAILS
LEFT JOIN MAILINGLISTNEWMOM ON EXPORTCUSTEMAILS.custNum= MAILINGLISTNEWMOM.custnumber
WHERE MAILINGLISTNEWMOM.custnumber IS NULL

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2003-06-20 : 05:32:27

I think you have some issues here.

You are using left join.
from BOL :
****
LEFT JOIN or LEFT OUTER JOIN
The result set of a left outer join includes all the rows from the left table specified in the LEFT OUTER clause, not just the ones in which the joined columns match. When a row in the left table has no matching rows in the right table, the associated result set row contains null values for all select list columns coming from the right table.
****

In other words, you are getting all rows from EXPORTCUSTEMAILS, and then removing those you don't want via the Null.

What you want is "negative" sub-select I.e. the following pseudo-code :

Insert into TbB
Select TbA.col's from TbA
where TbA.UniqueId not in (Select TbB.FkId_TbA from TbB)

Now, I'm sure there are articles or threads dealing with better techniques... I don't know them well enough to write them here, but I'll search for links and add them.

One possible way is a bit messy, but has worked better than the negative sub-select for us, when you get to large volumes , is the following concept:

Insert into tempA
select * from TbA

Delete from tempA
where tempid in (select fkif_tbA from TbB)

Insert into TbB
select from TempA

The idea here is that the IN is indexable, whereas the NOT IN from the megative sub-select is not. Granted, you are using more memory for the extra table, but it certainly worked for us, with large volumes ( in the millions ...)

HTH

*#&#* *#&#* *#&#* *#&#*

Chaos, Disorder and Panic ... my work is done here!

Edited by - wanderer on 06/20/2003 05:53:58

Edited by - wanderer on 06/20/2003 05:59:25

mr_mist
Grunnio

1870 Posts

Posted - 2003-06-20 : 05:57:18

quote:

I think you have some issues here.

You are using left join.

In other words, you are getting all rows from EXPORTCUSTEMAILS, and then revoming those you don't want via the Null.

But that is the intended result, surely?

He is doing a select with a left join to MAILINGLISTNEWMOM where the customer number does not exist in MAILINGLISTNEWMOM. This will return all the rows from EXPORTCUSTEMAILS not already in MAILINGLISTNEWMOM, giving the same result as using NOT IN, but (in theory) more quickly.

How this theory works in practice, especially when the left joined table is itself the target of the INSERT, I do not know.

-------
Moo.

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2003-06-20 : 06:03:54

@ Mr Mist ,

I haven't got his volumes. Yes, his query works, and on a small table it is fast. I recommended the alternative to see if it was indeed faster than the "negative sub-select" since he says that it pull's his server down to the ground.

Another (better ?) question would be to ask him what indexes he has, and what the access apth looks like for them ...

*#&#* *#&#* *#&#* *#&#*

Chaos, Disorder and Panic ... my work is done here!

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-06-20 : 06:07:25

Some times a WHERE NOT EXISTS(<correlated suquery>) will provide a better execution plan than a LEFT JOIN WHERE <right side> is null ...

Jay White
{0}

Edited by - Page47 on 06/20/2003 06:07:40

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2003-06-20 : 06:16:10

Ok - some testing:

I set up tables, WITHOUT indexes, as I don't know what indexes he has.
One very low volumes (5 from EXPORT, 3 for MOM), the following results from SHOWPLANTEXT:

Net result = The NOT IN doesn't have a filter, the LEFT join does. NOT in uses ANTI-JOIN.I'll see if I can populate to larger volumes for a better test.

StmtText SELECT EXPORTCUSTEMAILS.ADDR, EXPORTCUSTEMAILS.ADDR2, EXPORTCUSTEMAILS.CITY, EXPORTCUSTEMAILS.COMPANY, EXPORTCUSTEMAILS.COUNTRY, EXPORTCUSTEMAILS.CUSTTYPE,
EXPORTCUSTEMAILS.EMAIL, EXPORTCUSTEMAILS.FIRSTNAME, EXPORTCUSTEMAILS.LASTNAME, EXPORTCUSTEMAILS.PASSWORD, EXPORTCUSTEMAILS.PHONE, EXPORTCUSTEMAILS.STATE, EXPORTCUSTEMAILS.ZIPCODE, EXPORTCUSTEMAILS.ALTNUM, EXPORTCUSTEMAILS.custnum
FROM EXPORTCUSTEMAILS
LEFT JOIN MAILINGLISTNEWMOM ON EXPORTCUSTEMAILS.custNum= MAILINGLISTNEWMOM.custnumber
WHERE MAILINGLISTNEWMOM.custnumber IS NULL

--Insert into TbB

(1 row(s) affected)

StmtText -----------------------------------------
|--Filter(WHERE:([MAILINGLISTNEWMOM].[CUSTNUMBER]=NULL))
|--Nested Loops(Left Outer Join, WHERE:([EXPORTCUSTEMAILS].[custnum]=[MAILINGLISTNEWMOM].[CUSTNUMBER]))
|--Table Scan(OBJECT:([Northwind].[dbo].[EXPORTCUSTEMAILS]))
|--Table Scan(OBJECT:([Northwind].[dbo].[MAILINGLISTNEWMOM]))

(4 row(s) affected)

StmtText Select EXPORTCUSTEMAILS.ADDR, EXPORTCUSTEMAILS.ADDR2, EXPORTCUSTEMAILS.CITY, EXPORTCUSTEMAILS.COMPANY, EXPORTCUSTEMAILS.COUNTRY, EXPORTCUSTEMAILS.CUSTTYPE,
EXPORTCUSTEMAILS.EMAIL, EXPORTCUSTEMAILS.FIRSTNAME, EXPORTCUSTEMAILS.LASTNAME, EXPORTCUSTEMAILS.PASSWORD, EXPORTCUSTEMAILS.PHONE, EXPORTCUSTEMAILS.STATE, EXPORTCUSTEMAILS.ZIPCODE, EXPORTCUSTEMAILS.ALTNUM, EXPORTCUSTEMAILS.custnum
from EXPORTCUSTEMAILS
where EXPORTCUSTEMAILS.custNum not in (Select MAILINGLISTNEWMOM.custnumber from MAILINGLISTNEWMOM)

(1 row(s) affected)

StmtText --------------------------------------------------
|--Nested Loops(Left Anti Semi Join, WHERE:(([EXPORTCUSTEMAILS].[custnum]=NULL OR [MAILINGLISTNEWMOM].[CUSTNUMBER]=NULL) OR [EXPORTCUSTEMAILS].[custnum]=[MAILINGLISTNEWMOM].[CUSTNUMBER]))
|--Table Scan(OBJECT:([Northwind].[dbo].[EXPORTCUSTEMAILS]))
|--Table Scan(OBJECT:([Northwind].[dbo].[MAILINGLISTNEWMOM]))

(3 row(s) affected)

*#&#* *#&#* *#&#* *#&#*

Chaos, Disorder and Panic ... my work is done here!

Edited by - wanderer on 06/20/2003 06:17:02

Edited by - wanderer on 06/20/2003 06:18:16

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2003-06-20 : 06:42:37

What a difference an index makes !

On the same test data (3 rows in MOM, 5 rows in EXP) , I added an unique, clustered index on custnum on the 2 tables.

The resulting estimated was 43.6 % of time on the LEFT JOIN, vs. 56.7 % to the NOT IN ! Oh well...

So, what indexes are there on this table, Richard ?

The showplan, for those interested :

StmtText
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT EXPORTCUSTEMAILS.ADDR, EXPORTCUSTEMAILS.ADDR2, EXPORTCUSTEMAILS.CITY, EXPORTCUSTEMAILS.COMPANY, EXPORTCUSTEMAILS.COUNTRY, EXPORTCUSTEMAILS.CUSTTYPE,
EXPORTCUSTEMAILS.EMAIL, EXPORTCUSTEMAILS.FIRSTNAME, EXPORTCUSTEMAILS.LASTNAME, EXPORTCUSTEMAILS.PASSWORD, EXPORTCUSTEMAILS.PHONE, EXPORTCUSTEMAILS.STATE, EXPORTCUSTEMAILS.ZIPCODE, EXPORTCUSTEMAILS.ALTNUM, EXPORTCUSTEMAILS.custnum
FROM EXPORTCUSTEMAILS
LEFT JOIN MAILINGLISTNEWMOM ON EXPORTCUSTEMAILS.custNum= MAILINGLISTNEWMOM.custnumber
WHERE MAILINGLISTNEWMOM.custnumber IS NULL

--Insert into TbB

(1 row(s) affected)

StmtText
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Filter(WHERE:([MAILINGLISTNEWMOM].[CUSTNUMBER]=NULL))
|--Nested Loops(Left Outer Join, OUTER REFERENCES:([EXPORTCUSTEMAILS].[custnum]))
|--Clustered Index Scan(OBJECT:([Northwind].[dbo].[EXPORTCUSTEMAILS].[XcustClust]))
|--Clustered Index Seek(OBJECT:([Northwind].[dbo].[MAILINGLISTNEWMOM].[XcustClustMOM]), SEEK:([MAILINGLISTNEWMOM].[CUSTNUMBER]=[EXPORTCUSTEMAILS].[custnum]) ORDERED FORWARD)

(4 row(s) affected)

StmtText
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Select EXPORTCUSTEMAILS.ADDR, EXPORTCUSTEMAILS.ADDR2, EXPORTCUSTEMAILS.CITY, EXPORTCUSTEMAILS.COMPANY, EXPORTCUSTEMAILS.COUNTRY, EXPORTCUSTEMAILS.CUSTTYPE,
EXPORTCUSTEMAILS.EMAIL, EXPORTCUSTEMAILS.FIRSTNAME, EXPORTCUSTEMAILS.LASTNAME, EXPORTCUSTEMAILS.PASSWORD, EXPORTCUSTEMAILS.PHONE, EXPORTCUSTEMAILS.STATE, EXPORTCUSTEMAILS.ZIPCODE, EXPORTCUSTEMAILS.ALTNUM, EXPORTCUSTEMAILS.custnum
from EXPORTCUSTEMAILS
where EXPORTCUSTEMAILS.custNum not in (Select MAILINGLISTNEWMOM.custnumber from MAILINGLISTNEWMOM)

(1 row(s) affected)

StmtText
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Nested Loops(Left Anti Semi Join, OUTER REFERENCES:([EXPORTCUSTEMAILS].[custnum]))
|--Nested Loops(Left Anti Semi Join)
| |--Nested Loops(Left Anti Semi Join, WHERE:([EXPORTCUSTEMAILS].[custnum]=NULL))
| | |--Clustered Index Scan(OBJECT:([Northwind].[dbo].[EXPORTCUSTEMAILS].[XcustClust]))
| | |--Row Count Spool
| | |--Top(1)
| | |--Clustered Index Scan(OBJECT:([Northwind].[dbo].[MAILINGLISTNEWMOM].[XcustClustMOM]))
| |--Row Count Spool
| |--Clustered Index Seek(OBJECT:([Northwind].[dbo].[MAILINGLISTNEWMOM].[XcustClustMOM]), SEEK:([MAILINGLISTNEWMOM].[CUSTNUMBER]=NULL) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([Northwind].[dbo].[MAILINGLISTNEWMOM].[XcustClustMOM]), SEEK:([MAILINGLISTNEWMOM].[CUSTNUMBER]=[EXPORTCUSTEMAILS].[custnum]) ORDERED FORWARD)

(10 row(s) affected)

*#&#* *#&#* *#&#* *#&#*

Chaos, Disorder and Panic ... my work is done here!

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2003-06-20 : 08:06:28

@ richard et al.,

Interesting results!

Method A = Richard's LEFT OUTER
Method B = Wanderer's NOT IN
Method C = Wanderer's insert into temp, delete from temp

On 5 rows in EXP, and 3 rows in MOM, estimated comparison is (using % from estimated access path's):

Method A = 20.5%
Method B = 26.5%
Method C = 53 % (sum of 2 statement's)

Then I though, let's try 40000 rows (actually 39999 - 1 of on the Loop 0-( )

Method A = 36.35%
Method B = 44.16%
Method C = 19.69%

Richard, the temp table I created is :
create table #tempExp
( ADDR char (10) null,
ADDR2 char (10) null,
CITY char (10) null,
COMPANY char (10) null,
COUNTRY char (10) null,
CUSTTYPE char (10) null,
EMAIL char (10) null,
FIRSTNAME char (10) null,
LASTNAME char (10) null,
[PASSWORD] char (10) null,
PHONE char (10) null,
STATE char (10) null,
ZIPCODE char (10) null,
ALTNUM char (10) null,
custnum int PRIMARY KEY CLUSTERED)

- obviously, you'll have different data types and length's. Check to see if the results hold true.

The showplantext of the 3 :

******************************

StmtText --dbcc dropcleanbuffers
insert into MAILINGLISTNEWMOM
SELECT EXPORTCUSTEMAILS.ADDR, EXPORTCUSTEMAILS.ADDR2, EXPORTCUSTEMAILS.CITY, EXPORTCUSTEMAILS.COMPANY, EXPORTCUSTEMAILS.COUNTRY, EXPORTCUSTEMAILS.CUSTTYPE,
EXPORTCUSTEMAILS.EMAIL, EXPORTCUSTEMAILS.FIRSTNAME, EXPORTCUSTEMAILS.LASTNAME, EXPORTCUSTEMAILS.PASSWORD, EXPORTCUSTEMAILS.PHONE, EXPORTCUSTEMAILS.STATE, EXPORTCUSTEMAILS.ZIPCODE, EXPORTCUSTEMAILS.ALTNUM, EXPORTCUSTEMAILS.custnum
FROM EXPORTCUSTEMAILS
LEFT JOIN MAILINGLISTNEWMOM ON EXPORTCUSTEMAILS.custNum= MAILINGLISTNEWMOM.custnumber
WHERE MAILINGLISTNEWMOM.custnumber IS NULL

--Insert into TbB

(1 row(s) affected)

StmtText ---------------------------------------------------------------------------
|--Clustered Index Insert(OBJECT:([Northwind].[dbo].[MAILINGLISTNEWMOM].[XcustClustMOM]), SET:([MAILINGLISTNEWMOM].[CUSTNUMBER]=[EXPORTCUSTEMAILS].[custnum], [MAILINGLISTNEWMOM].[ALTNUM]=[EXPORTCUSTEMAILS].[ALTNUM], [MAILINGLISTNEWMOM].[ZIP]=[EXPORTCUSTEMAILS].[ZIPCODE], [MAILINGLISTNEWMOM].[STATE]=[EXPORTCUSTEMAILS].[STATE], [MAILINGLISTNEWMOM].[PHONE]=[EXPORTCUSTEMAILS].[PHONE], [MAILINGLISTNEWMOM].[PASSWORD]=[EXPORTCUSTEMAILS].[PASSWORD], [MAILINGLISTNEWMOM].[LASTNAME]=[EXPORTCUSTEMAILS].[LASTNAME], [MAILINGLISTNEWMOM].[FIRSTNAME]=[EXPORTCUSTEMAILS].[FIRSTNAME], [MAILINGLISTNEWMOM].[EMAIL]=[EXPORTCUSTEMAILS].[EMAIL], [MAILINGLISTNEWMOM].[CUSTTYPE]=[EXPORTCUSTEMAILS].[CUSTTYPE], [MAILINGLISTNEWMOM].[COUNTRY]=[EXPORTCUSTEMAILS].[COUNTRY], [MAILINGLISTNEWMOM].[COMPANY]=[EXPORTCUSTEMAILS].[COMPANY], [MAILINGLISTNEWMOM].[CITY]=[EXPORTCUSTEMAILS].[CITY], [MAILINGLISTNEWMOM].[ADDRESS2]=[EXPORTCUSTEMAILS].[ADDR2], [MAILINGLISTNEWMOM].[ADDRESS1]=[EXPORTCUSTEMAILS].[ADDR]))
|--Table Spool
|--Top(ROWCOUNT est 0)
|--Filter(WHERE:([MAILINGLISTNEWMOM].[CUSTNUMBER]=NULL))
|--Merge Join(Left Outer Join, MERGE:([EXPORTCUSTEMAILS].[custnum])=([MAILINGLISTNEWMOM].[CUSTNUMBER]), RESIDUAL:([EXPORTCUSTEMAILS].[custnum]=[MAILINGLISTNEWMOM].[CUSTNUMBER]))
|--Clustered Index Scan(OBJECT:([Northwind].[dbo].[EXPORTCUSTEMAILS].[XcustClust]), ORDERED FORWARD)
|--Clustered Index Scan(OBJECT:([Northwind].[dbo].[MAILINGLISTNEWMOM].[XcustClustMOM]), ORDERED FORWARD)

(7 row(s) affected)

StmtText ----------------------------------------------------------------------------------
insert into MAILINGLISTNEWMOM
Select EXPORTCUSTEMAILS.ADDR, EXPORTCUSTEMAILS.ADDR2, EXPORTCUSTEMAILS.CITY, EXPORTCUSTEMAILS.COMPANY, EXPORTCUSTEMAILS.COUNTRY, EXPORTCUSTEMAILS.CUSTTYPE,
EXPORTCUSTEMAILS.EMAIL, EXPORTCUSTEMAILS.FIRSTNAME, EXPORTCUSTEMAILS.LASTNAME, EXPORTCUSTEMAILS.PASSWORD, EXPORTCUSTEMAILS.PHONE, EXPORTCUSTEMAILS.STATE, EXPORTCUSTEMAILS.ZIPCODE, EXPORTCUSTEMAILS.ALTNUM, EXPORTCUSTEMAILS.custnum
from EXPORTCUSTEMAILS
where EXPORTCUSTEMAILS.custNum not in (Select MAILINGLISTNEWMOM.custnumber from MAILINGLISTNEWMOM)

--use temp table
/*
create table #tempExp
( ADDR char (10) null,
ADDR2 char (10) null,
CITY char (10) null,
COMPANY char (10) null,
COUNTRY char (10) null,
CUSTTYPE char (10) null,
EMAIL char (10) null,
FIRSTNAME char (10) null,
LASTNAME char (10) null,
[PASSWORD] char (10) null,
PHONE char (10) null,
STATE char (10) null,
ZIPCODE char (10) null,
ALTNUM char (10) null,
custnum int PRIMARY KEY CLUSTERED)
*/

(1 row(s) affected)

StmtText ---------------------------------------------------------------------------
|--Clustered Index Insert(OBJECT:([Northwind].[dbo].[MAILINGLISTNEWMOM].[XcustClustMOM]), SET:([MAILINGLISTNEWMOM].[CUSTNUMBER]=[EXPORTCUSTEMAILS].[custnum], [MAILINGLISTNEWMOM].[ALTNUM]=[EXPORTCUSTEMAILS].[ALTNUM], [MAILINGLISTNEWMOM].[ZIP]=[EXPORTCUSTEMAILS].[ZIPCODE], [MAILINGLISTNEWMOM].[STATE]=[EXPORTCUSTEMAILS].[STATE], [MAILINGLISTNEWMOM].[PHONE]=[EXPORTCUSTEMAILS].[PHONE], [MAILINGLISTNEWMOM].[PASSWORD]=[EXPORTCUSTEMAILS].[PASSWORD], [MAILINGLISTNEWMOM].[LASTNAME]=[EXPORTCUSTEMAILS].[LASTNAME], [MAILINGLISTNEWMOM].[FIRSTNAME]=[EXPORTCUSTEMAILS].[FIRSTNAME], [MAILINGLISTNEWMOM].[EMAIL]=[EXPORTCUSTEMAILS].[EMAIL], [MAILINGLISTNEWMOM].[CUSTTYPE]=[EXPORTCUSTEMAILS].[CUSTTYPE], [MAILINGLISTNEWMOM].[COUNTRY]=[EXPORTCUSTEMAILS].[COUNTRY], [MAILINGLISTNEWMOM].[COMPANY]=[EXPORTCUSTEMAILS].[COMPANY], [MAILINGLISTNEWMOM].[CITY]=[EXPORTCUSTEMAILS].[CITY], [MAILINGLISTNEWMOM].[ADDRESS2]=[EXPORTCUSTEMAILS].[ADDR2], [MAILINGLISTNEWMOM].[ADDRESS1]=[EXPORTCUSTEMAILS].[ADDR]))
|--Table Spool
|--Top(ROWCOUNT est 0)
|--Merge Join(Left Anti Semi Join, MERGE:([EXPORTCUSTEMAILS].[custnum])=([MAILINGLISTNEWMOM].[CUSTNUMBER]), RESIDUAL:([EXPORTCUSTEMAILS].[custnum]=[MAILINGLISTNEWMOM].[CUSTNUMBER]))
|--Nested Loops(Left Anti Semi Join)
| |--Nested Loops(Left Anti Semi Join, WHERE:([EXPORTCUSTEMAILS].[custnum]=NULL))
| | |--Clustered Index Scan(OBJECT:([Northwind].[dbo].[EXPORTCUSTEMAILS].[XcustClust]), ORDERED FORWARD)
| | |--Row Count Spool
| | |--Top(1)
| | |--Clustered Index Scan(OBJECT:([Northwind].[dbo].[MAILINGLISTNEWMOM].[XcustClustMOM]))
| |--Row Count Spool
| |--Clustered Index Seek(OBJECT:([Northwind].[dbo].[MAILINGLISTNEWMOM].[XcustClustMOM]), SEEK:([MAILINGLISTNEWMOM].[CUSTNUMBER]=NULL) ORDERED FORWARD)
|--Clustered Index Scan(OBJECT:([Northwind].[dbo].[MAILINGLISTNEWMOM].[XcustClustMOM]), ORDERED FORWARD)

(13 row(s) affected)

StmtText
---------------------------------------------------------
insert into #tempExp
select * from EXPORTCUSTEMAILS

(1 row(s) affected)

StmtText ----------------------------------------------------------------
|--Clustered Index Insert(OBJECT:([tempdb].[dbo].[#tempExp____________________________________________________________________________________________________________000000000016].[PK__#tempExp__303D2791]), SET:([#tempExp].[custnum]=RaiseIfNull([EXPORTCUSTEMAILS].[custnum]), [#tempExp].[ALTNUM]=[EXPORTCUSTEMAILS].[ALTNUM], [#tempExp].[ZIPCODE]=[EXPORTCUSTEMAILS].[ZIPCODE], [#tempExp].[STATE]=[EXPORTCUSTEMAILS].[STATE], [#tempExp].[PHONE]=[EXPORTCUSTEMAILS].[PHONE], [#tempExp].[PASSWORD]=[EXPORTCUSTEMAILS].[PASSWORD], [#tempExp].[LASTNAME]=[EXPORTCUSTEMAILS].[LASTNAME], [#tempExp].[FIRSTNAME]=[EXPORTCUSTEMAILS].[FIRSTNAME], [#tempExp].[EMAIL]=[EXPORTCUSTEMAILS].[EMAIL], [#tempExp].[CUSTTYPE]=[EXPORTCUSTEMAILS].[CUSTTYPE], [#tempExp].[COUNTRY]=[EXPORTCUSTEMAILS].[COUNTRY], [#tempExp].[COMPANY]=[EXPORTCUSTEMAILS].[COMPANY], [#tempExp].[CITY]=[EXPORTCUSTEMAILS].[CITY], [#tempExp].[ADDR2]=[EXPORTCUSTEMAILS].[ADDR2], [#tempExp].[ADDR]=[EXPORTCUSTEMAILS].[ADDR]))
|--Top(ROWCOUNT est 0)
|--Clustered Index Scan(OBJECT:([Northwind].[dbo].[EXPORTCUSTEMAILS].[XcustClust]), ORDERED FORWARD)

(3 row(s) affected)

StmtText -------------
delete from #tempExp
where #tempExp.custnum in (Select MAILINGLISTNEWMOM.custnumber from MAILINGLISTNEWMOM)

(1 row(s) affected)

StmtText --------------------
|--Clustered Index Delete(OBJECT:([tempdb].[dbo].[#tempExp____________________________________________________________________________________________________________000000000016].[PK__#tempExp__303D2791]))
|--Top(ROWCOUNT est 0)
|--Nested Loops(Left Semi Join, OUTER REFERENCES:([#tempExp].[custnum]))
|--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#tempExp____________________________________________________________________________________________________________000000000016].[PK__#tempExp__303D2791]))
|--Clustered Index Seek(OBJECT:([Northwind].[dbo].[MAILINGLISTNEWMOM].[XcustClustMOM]), SEEK:([MAILINGLISTNEWMOM].[CUSTNUMBER]=[#tempExp].[custnum]) ORDERED FORWARD)

(5 row(s) affected)

StmtText ----------
insert into MAILINGLISTNEWMOM
select * from #tempExp

(1 row(s) affected)

StmtText --------------------------------------------------------
|--Clustered Index Insert(OBJECT:([Northwind].[dbo].[MAILINGLISTNEWMOM].[XcustClustMOM]), SET:([MAILINGLISTNEWMOM].[CUSTNUMBER]=[#tempExp].[custnum], [MAILINGLISTNEWMOM].[ALTNUM]=[#tempExp].[ALTNUM], [MAILINGLISTNEWMOM].[ZIP]=[#tempExp].[ZIPCODE], [MAILINGLISTNEWMOM].[STATE]=[#tempExp].[STATE], [MAILINGLISTNEWMOM].[PHONE]=[#tempExp].[PHONE], [MAILINGLISTNEWMOM].[PASSWORD]=[#tempExp].[PASSWORD], [MAILINGLISTNEWMOM].[LASTNAME]=[#tempExp].[LASTNAME], [MAILINGLISTNEWMOM].[FIRSTNAME]=[#tempExp].[FIRSTNAME], [MAILINGLISTNEWMOM].[EMAIL]=[#tempExp].[EMAIL], [MAILINGLISTNEWMOM].[CUSTTYPE]=[#tempExp].[CUSTTYPE], [MAILINGLISTNEWMOM].[COUNTRY]=[#tempExp].[COUNTRY], [MAILINGLISTNEWMOM].[COMPANY]=[#tempExp].[COMPANY], [MAILINGLISTNEWMOM].[CITY]=[#tempExp].[CITY], [MAILINGLISTNEWMOM].[ADDRESS2]=[#tempExp].[ADDR2], [MAILINGLISTNEWMOM].[ADDRESS1]=[#tempExp].[ADDR]))
|--Top(ROWCOUNT est 0)
|--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#tempExp____________________________________________________________________________________________________________000000000016].[PK__#tempExp__303D2791]))

(3 row(s) affected)

*#&#* *#&#* *#&#* *#&#*

Chaos, Disorder and Panic ... my work is done here!

RichardSteele
Posting Yak Master

160 Posts

Posted - 2003-06-20 : 10:48:52

Thanks for all of the creative ideas. This forum is awesome! Gasp! Custnumber was not indexed in either table. I will create those indexes and run it again next week and report back to the group.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-06-21 : 05:46:01

Richard,

maybe spAsyncSQL is just your case?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=26889

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources