Update Script taking hours to complete

Question

Post reply

Update Script taking hours to complete

Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Mohamed I. SSCrazy Points: 2774 More actions · Answer 1

Normal you then need to add up update command after the cte

:w00t: !!!GOOGLE IS YOUR BEST FRIEND!!! :w00t:

bryan.holmstrom SSC Enthusiast Points: 107 More actions · Answer 2

You have been very helpful, but sorry I don't know what you mean by "update"

Michael L John One Orange Chip Points: 26551 More actions · Answer 3

You need to do something with the CTE. Add the update after you create the CTE

WITH CTE_Concat AS (

SELECT lb.ListID, lmb.EntityID,

STUFF((SELECT '; ' + lb.ListName

FROM ReznickGroup_MSCRM.dbo.ListMemberBase lmb

INNER JOIN ReznickGroup_MSCRM.dbo.ListBase lb ON lb.listid = lmb.listid

WHERE lmb.entityid = cb.contactid

FOR XML PATH('')),1,1,'') AS ConcatValues

FROM ReznickGroup_MSCRM.dbo.ContactBase cb)

UPDATE Foo

SET Foo.foo1 = CTE_Concat.ConcatValues

WHERE Foo.ListID =CTE_Concat.ListID

AND Foo.EntityID = CTE_Concat.EntityID

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

bryan.holmstrom SSC Enthusiast Points: 107 More actions · Answer 4

HI,

I have added the update as follows:

WITH CTE_Concat AS

(

SELECT lb.ListID, lmb.EntityID,

STUFF((SELECT '; ' + lb.ListName

FROM ReznickGroup_MSCRM.dbo.ListMemberBase lmb

INNER JOIN ReznickGroup_MSCRM.dbo.ListBase lb ON lb.listid = lmb.listid

WHERE lmb.entityid = cb.contactid

FOR XML PATH('')),1,1,'') AS ConcatValues

FROM ReznickGroup_MSCRM.dbo.ContactBase cb

)

UPDATE a

SET Con_Mailings = z.ConcatValues

FROM [Ster-SQL02].[Lyris_Temp].[dbo].Lyris_CombinedTest a

INNER JOIN CTE_Concat z ON z.EntityID = a.TmpContactID

Now I am getting the following error:

Msg 4104, Level 16, State 1, Line 4

The multi-part identifier "lb.ListID" could not be bound.

Msg 4104, Level 16, State 1, Line 4

The multi-part identifier "lmb.EntityID" could not be bound.

Michael L John One Orange Chip Points: 26551 More actions · Answer 5

bryan.holmstrom (7/30/2015)
HI,
I have added the update as follows:
WITH CTE_Concat AS
(
SELECT lb.ListID, lmb.EntityID,
STUFF((SELECT '; ' + lb.ListName
FROM ReznickGroup_MSCRM.dbo.ListMemberBase lmb
INNER JOIN ReznickGroup_MSCRM.dbo.ListBase lb ON lb.listid = lmb.listid
WHERE lmb.entityid = cb.contactid
FOR XML PATH('')),1,1,'') AS ConcatValues
FROM ReznickGroup_MSCRM.dbo.ContactBase cb
)
UPDATE a
SET Con_Mailings = z.ConcatValues
FROM [Ster-SQL02].[Lyris_Temp].[dbo].Lyris_CombinedTest a
INNER JOIN CTE_Concat z ON z.EntityID = a.TmpContactID
Now I am getting the following error:
Msg 4104, Level 16, State 1, Line 4
The multi-part identifier "lb.ListID" could not be bound.
Msg 4104, Level 16, State 1, Line 4
The multi-part identifier "lmb.EntityID" could not be bound.

The fields lb.listid and lmb.Entityid do not exist in the scope of where you are trying to use them.

The way that this is written, only the fields from ContactBase table would be available in this context.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

bryan.holmstrom SSC Enthusiast Points: 107 More actions · Answer 6

bryan.holmstrom

SSC Enthusiast

Points: 107

July 30, 2015 at 11:44 am

#1816244

any suggestions on how to resolve this?

Thanks

Mohamed I. SSCrazy Points: 2774 More actions · Answer 7

On the cte keep the contactid field and concatened field only

Then you can use the contactid field of the cte to join the update table a

:w00t: !!!GOOGLE IS YOUR BEST FRIEND!!! :w00t:

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 8

bryan.holmstrom (7/30/2015)
any suggestions on how to resolve this?
Thanks

post some sample data in the form of CREATE TABLE / INSERT DATA scripts

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

mister.magoo SSC-Forever Points: 47068 More actions · Answer 9

mister.magoo (7/30/2015)
Please post the execution plan (.sqlplan, not an image) and the table definitions.
How many rows are in the tables on the remote server?

If you answer this, I may be able to help, but just changing the structure of your query from a subselect to a cte is not going to solve the performance problem.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);