July 29, 2015 at 10:42 am
I have a script that is supposed to run thru 2 joined tables and update a field in the 3rd table. The script works but takes approx. 4 hours to run against 250k records.
Maybe someone can see if I have something wrong:
UPDATE a
SET Con_Mailings = STUFF((SELECT '; ' + c.ListName
FROM [server].[xxxxx_MSCRM].[dbo].ListBase c with (nowait)
INNER JOIN [server].[xxxxxx_MSCRM].[dbo].[ListMemberBase] b with (nowait)
ON b.ListID = c.ListID
WHERE b.EntityID = a.TmpContactID
FOR XML PATH('')),1,1,'')
FROM [xx_Temp].[dbo].[Lyris_CombinedTest] a
I should end up with something like this in the con_mailings field:
'Mailing1, Mailing2, Mailing3'
July 30, 2015 at 2:37 am
Hi
Maybe you can try to split your query and use a cte ?
for example:
WITH CTE_Concat AS (
SELECT b.ListID,b.EntityID, STUFF(
(SELECT '; ' + c.ListName
FROM [server].[xxxxx_MSCRM].[dbo].ListBase c with (nowait)
WHERE b.ListID = c.ListID
FOR XML PATH('')
),1,1,'') AS ConcatValues
FROM server].[xxxxxx_MSCRM].[dbo].[ListMemberBase] b with (nowait)
)
UPDATE a
SET Con_Mailings = z.ConcatValues
FROM [xx_Temp].[dbo].[Lyris_CombinedTest] a INNER JOIN CTE_Concat z ON a.TmpContactID = z.EntityID
:w00t: !!!GOOGLE IS YOUR BEST FRIEND!!! :w00t:
July 30, 2015 at 2:41 am
Please post the execution plan (.sqlplan, not an image) and the table definitions.
How many rows are in the tables on the remote server?
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);July 30, 2015 at 7:46 am
SSC-Enthusiastic:
This runs very quickly but I get one contacted and one mailing in cte_concat
The second part only adds the ConcatValues value of the mailing to the first contact
July 30, 2015 at 7:59 am
Maybe you have to invert b and c position ?
I see that the c table appears to be the base table :
WITH CTE_Concat AS (
SELECT c.ListID,t.EntityID, STUFF(
(SELECT '; ' + c.ListName
FROM [server].[xxxxxx_MSCRM].[dbo].[ListMemberBase] b with (nowait)
WHERE b.ListID = c.ListID
FOR XML PATH('')
),1,1,'') AS ConcatValues
FROM [server].[xxxxx_MSCRM].[dbo].ListBase c with (nowait) INNER JOIN [server].[xxxxxx_MSCRM].[dbo].[ListMemberBase] b with (nowait) t ON c.ListID = b.ListID
)
UPDATE a
SET Con_Mailings = z.ConcatValues
FROM [xx_Temp].[dbo].[Lyris_CombinedTest] a INNER JOIN CTE_Concat z ON a.TmpContactID = z.EntityID
:w00t: !!!GOOGLE IS YOUR BEST FRIEND!!! :w00t:
July 30, 2015 at 8:24 am
Thanks its getting better, but I am still seeing multiple contacts with lists repeated over and over
Example:
Abe, Deutsch Real Estate, Commercial Real Estate Alerts and Events Notifications; Real Estate,
Abe, Deutsch NMTC_New Markets Tax Credit Summit Invitee List 2013; NMTC_New Markets Tax Credit Summit Invitee List 2013;
The list just keeps repeating (stops at column 43696
July 30, 2015 at 8:55 am
The previous post had some examples of what is being pulled:
Here is the actual data that it should pulling
Abe, DeutschNMTC_New Markets Tax Credit Summit Invitee List 2013
Abe, DeutschReal Estate, Commercial Real Estate Alerts and Events Notifications
So we would end up with:
Abe, DeutschNMTC_New Markets Tax Credit Summit Invitee List 2013; Real Estate, Commercial Real Estate Alerts and Events Notifications
July 30, 2015 at 8:59 am
Can you post sample lines for each table please ?
Is ListBase a 1-N relation to ListMemberBase?
How do you connect to your final table ?
:w00t: !!!GOOGLE IS YOUR BEST FRIEND!!! :w00t:
July 30, 2015 at 9:09 am
Listbase is a N:1 to ListMemberBase
ListMemberBase is connected to the contact table on entityid = contactid
select c.contactid, c.fullname, lb.listname as 'List Base Name'
,lb.listid as 'ListBase ID', lmb.listid as 'List Member Base ID', lmb.entityID as 'Contact Hook'
FROM xxx.dbo.contactbase c
JOIN xxx.dbo.ListMemberBase lmb
ON lmb.entityid = c.contactid
JOIN xxx.dbo.ListBase lb
ON lb.listid = lmb.listid
where c.fullname = 'Abe, Deutsch'
order by c.fullname
contactidfullnameList Base NameListBase IDList Member Base IDContact Hook
EB900EF8-F3DE-DF11-955A-005056BE2DEFAbe, DeutschNMTC_New Markets Tax Credit Summit Invitee List 2013398BF359-767F-E211-90EA-005056BE3808398BF359-767F-E211-90EA-005056BE3808EB900EF8-F3DE-DF11-955A-005056BE2DEF
EB900EF8-F3DE-DF11-955A-005056BE2DEFAbe, DeutschReal Estate, Commercial Real Estate Alerts and Events Notifications2B29B6F3-9EE0-DF11-ACF4-005056BE38082B29B6F3-9EE0-DF11-ACF4-005056BE3808EB900EF8-F3DE-DF11-955A-005056BE2DEF
July 30, 2015 at 9:26 am
Ok.
I think the join key was not correct on the CTE.
What does this query returns ?
SELECT cb.fullname, STUFF(
(SELECT '; ' + lb.ListName
FROM xxx.dbo.ListMemberBase lmb
INNER JOIN xxx.dbo.ListBase lb ON lb.listid = lmb.listid
WHERE ON lmb.entityid = cb.contactid
FOR XML PATH('')
),1,1,'') AS ConcatValues
FROM xxx.dbo.contactbase cb
where cb.fullname = 'Abe, Deutsch'
order by cb.fullname
:w00t: !!!GOOGLE IS YOUR BEST FRIEND!!! :w00t:
July 30, 2015 at 9:45 am
Exactly what I thought I should see. Should I put that in the CTE_Concat code?
July 30, 2015 at 9:49 am
Yes and you will join the update with the contactid (you have to add the contactid on the select of the CTE)
I think this will do the job
If still slow you can replace the CTE by a temptable
:w00t: !!!GOOGLE IS YOUR BEST FRIEND!!! :w00t:
July 30, 2015 at 9:59 am
This code sends an error: I must have something coded wrong?
Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'FROM'.
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
July 30, 2015 at 10:03 am
bryan.holmstrom (7/30/2015)
This code sends an error: I must have something coded wrong?Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'FROM'.
WITH CTE_Concat ASSELECT 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
You have to put parenthesis after the AS of 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
)
:w00t: !!!GOOGLE IS YOUR BEST FRIEND!!! :w00t:
July 30, 2015 at 10:10 am
Still throwing an error:
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near ')'.
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
)
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply