Can I improve this? - 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)

Can I improve this?

Author

Topic

Cyclonik
Posting Yak Master

114 Posts

Posted - 2006-01-31 : 16:47:41

There is a non-clustered index on the field MatchGroupID. There is a Table Spool/Eager Spool which has a cost of 65%. ( 25% in an index update)

Is there anything left I can optimize?

UPDATE MatchPartyTempTest
SET MatchGroupID = @Group1
FROM
MatchPartyTempTest
WHERE
MatchGroupID = @Group2

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-01-31 : 17:21:08

Perhaps...do you have clustered index already?

The query can be simplified since you aren't using a join or a derived table.

UPDATE MatchPartyTempTest
SET MatchGroupID = @Group1
WHERE MatchGroupID = @Group2

Is the query performing slow?

Tara Kizer
aka tduggan

Cyclonik
Posting Yak Master

114 Posts

Posted - 2006-01-31 : 17:25:57

Well, it's not that its terribly slow on its own. It resides in a while loop below.

DECLARE @Group1 INT
DECLARE @Group2 INT

SET @Group1 = 1

WHILE @Group1 <> 0
BEGIN
SET @Group1 = 0
SELECT TOP 1
@Group1 = MPT1.MatchGroupID,
@Group2 = MPT2.MatchGroupID
FROM
MatchPartyTempTest MPT1 INNER JOIN
MatchPartyTempTest MPT2 ON MPT1.PartyID = MPT2.PartyID AND
MPT1.MatchGroupID <> MPT2.MatchGroupID

UPDATE MatchPartyTempTest
SET MatchGroupID = @Group1
FROM
MatchPartyTempTest
WHERE MatchGroupID = @Group2
END

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-31 : 17:33:04

quote:
Originally posted by tkizer
UPDATE MatchPartyTempTest
SET MatchGroupID = @Group1
WHERE MatchGroupID = @Group2

Syntactically simpler, but identical inexecution plan.
I'd have the same question as Tara, though. Is it all that slow right now? Are you dealing with an enormous dataset, or a database which is in heavy use, or are you running this query within a loop?

Cyclonik
Posting Yak Master

114 Posts

Posted - 2006-01-31 : 17:36:24

see my previous post. The update looks like that as we were playing with a correlated update syntax and hadn't cleaned up the new update.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-01-31 : 17:37:01

quote:

Syntactically simpler, but identical inexecution plan.

Most definitely. I just hate to see more lines of code when it can be written in fewer.

Tara Kizer
aka tduggan

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-31 : 17:37:07

quote:
Originally posted by blindman
...or are you running this query within a loop?

Bingo.

Can you supply us with a small set of sample data and a brief description of what you are trying to do, and maybe we can get you out of the loop business.

Cyclonik
Posting Yak Master

114 Posts

Posted - 2006-01-31 : 17:47:04

Ok this is a little tricky to explain.

I am merging customer data where there may be many duplicate customers.
Each customer can have any number of 3 contact types, Email, Address, and Telephone.

I am matching customers first where (emailaddress + firstname + lastname) match. I assign a groupid to each unique person emailaddress+firstname+lastname combo, then assign those groups to all other records that match. I do the same thing for name+address and name+phonenumber then dump them all together into a table. This is where the loop in the previous post comes in. I end up with the same person in multiple groups because they match to other people on other criteria ( on email, on address).

Some sample data.

P1, G1
P1, G2
P2, G2
P3, G3
P3, G4

The final result needs to look like

P1, G1
P1, G1
P2, G1
P3, G3
P3, G4

I hope that makes sense.

socnob
Starting Member

10 Posts

Posted - 2007-03-08 : 23:21:52

Has anyone come up with a solution to this? I am trying to do practically the same thing. My issue is that I have close to 1 million records that need to be scanned for duplicate entries and it is estimated to take weeks to complete. I too noticed that the "Table Spool" is a large percentage of the time in updates (according tho the Execution Plan). I'm basically just trying to figure out any way to speed up each update.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-08 : 23:32:31

why don you start a new thread, state your sitatuion, post the table structure, sample data and the expected result. Also post your current query.

socnob
Starting Member

10 Posts

Posted - 2007-03-09 : 16:58:12

Ok, I just posted a new thread called "Speed up Updates and Deletes?".

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources