>Several ways...one is to take the query and run it in management studio with "include execution plan" turned on, to see the execution plan, and also SET IO STATISTICS ON to view reads/disk activity. (You could even post the code here)
>
>You can also turn on SQL Server Profiler to view the activity associated with the exection of the view.
Hi Kevin
I turned on the execution plan but I'm not sure what its trying to tell me.
Here's the code for the view.
Any thoughts are welcome.
SELECT ISNULL(dbo.MEMBER.MEMBERSHIP_NO, '') AS [Membership Number],
CASE WHEN contact.type = 8199 THEN 'Member' WHEN contact.type = 8751 THEN 'Member AFFILIATE' END AS [Member Type],
M_Status.LOOKUP_FULL_DESC AS Status, CASE WHEN charindex('Concession', description) > 0 THEN LEFT(description, charindex('Concession',
description) - 2) WHEN charindex('Concession', description) = 0 THEN description END AS Term, LookupMC.LOOKUP_FULL_DESC AS Type,
ISNULL(member2.MEMBERSHIP_NO, '') AS [Joint With], SPACE(120) AS [Company Name], ISNULL(dbo.INDIVIDUAL.TITLE, '') AS Title,
ISNULL(dbo.INDIVIDUAL.INITIALS, '') AS Initials, ISNULL(dbo.INDIVIDUAL.FORENAMES, '') AS Forenames, ISNULL(dbo.INDIVIDUAL.SURNAME, '')
AS Surname, location_i.ADDRESS1 AS Address1, location_i.ADDRESS2 AS Address2, location_i.ADDRESS3 AS Address3, location_i.TOWN AS Town,
location_i.COUNTY AS County, location_i.POSTCODE AS Postcode, ISNULL(location_i.COUNTRY, '') AS Country, ISNULL
((SELECT TOP (1) EMAIL_ADDRESS
FROM dbo.EMAIL AS PrivEmail
WHERE (dbo.INDIVIDUAL.INDIVIDUAL_REF = INDIVIDUAL_REF) AND (TYPE = 1126) AND (VALID_TO IS NULL)
ORDER BY VALID_FROM DESC), '') AS [Private Email], ISNULL
((SELECT TOP (1) EMAIL_ADDRESS
FROM dbo.EMAIL AS PubEmail
WHERE (dbo.INDIVIDUAL.INDIVIDUAL_REF = INDIVIDUAL_REF) AND (TYPE = 1125) AND (VALID_TO IS NULL)
ORDER BY VALID_FROM DESC), '') AS [Public Email], ISNULL(dbo.TELEPHONE.NUMBER, '') AS Telephone,
CASE WHEN (CASE WHEN isnull
((SELECT isnull(MAX(valid_to), '')
FROM membership_history AS mh INNER JOIN
membership_plan AS mp ON (mh.member_plan_ref = mp.member_plan_ref AND group_ref = 2 AND
member.member_ref = mh.member_ref AND mh.history_status = 249)
GROUP BY member_ref), '') <= isnull
((SELECT isnull(MAX(valid_to), '')
FROM membership_history AS mh INNER JOIN
membership_plan AS mp ON (mh.member_plan_ref = mp.member_plan_ref AND group_ref = 2 AND
member2.member_ref = mh.member_ref AND mh.history_status = 249)
GROUP BY member_ref), '') THEN
(SELECT MAX(valid_to)
FROM membership_history AS mh INNER JOIN
membership_plan AS mp ON (mh.member_plan_ref = mp.member_plan_ref AND group_ref = 2 AND
member2.member_ref = mh.member_ref AND mh.history_status = 249)
GROUP BY member_ref) ELSE
(SELECT MAX(valid_to)
FROM membership_history AS mh INNER JOIN
membership_plan AS mp ON (mh.member_plan_ref = mp.member_plan_ref AND group_ref = 2 AND
member.member_ref = mh.member_ref AND mh.history_status = 249)
GROUP BY member_ref) END) IS NOT NULL THEN (CASE WHEN isnull
((SELECT isnull(MAX(valid_to), '')
FROM membership_history AS mh INNER JOIN
membership_plan AS mp ON (mh.member_plan_ref = mp.member_plan_ref AND group_ref = 2 AND
member.member_ref = mh.member_ref AND mh.history_status = 249)
GROUP BY member_ref), '') <= isnull
((SELECT isnull(MAX(valid_to), '')
FROM membership_history AS mh INNER JOIN
membership_plan AS mp ON (mh.member_plan_ref = mp.member_plan_ref AND group_ref = 2 AND
member2.member_ref = mh.member_ref AND mh.history_status = 249)
GROUP BY member_ref), '') THEN
(SELECT MAX(valid_to)
FROM membership_history AS mh INNER JOIN
membership_plan AS mp ON (mh.member_plan_ref = mp.member_plan_ref AND group_ref = 2 AND
member2.member_ref = mh.member_ref AND mh.history_status = 249)
GROUP BY member_ref) ELSE
(SELECT MAX(valid_to)
FROM membership_history AS mh INNER JOIN
membership_plan AS mp ON (mh.member_plan_ref = mp.member_plan_ref AND group_ref = 2 AND
member.member_ref = mh.member_ref AND mh.history_status = 249)
GROUP BY member_ref) END) ELSE
(SELECT MAX(valid_to)
FROM membership_history AS mh INNER JOIN
membership_plan AS mp ON (mh.member_plan_ref = mp.member_plan_ref AND group_ref = 2 AND
mh.member_ref IN (member.member_ref, member2.member_ref) AND mh.history_status = 49)
GROUP BY member_ref) END AS [Expiry Date], dbo.MEMBER.JOIN_DATE AS [Ramblers Joined date],
CASE WHEN Organisation.Type = '8197' THEN organisation.Trading_as WHEN Organisation.Type = '8198' THEN AG_Organisation.trading_as ELSE '' END
AS Area,
CASE WHEN Organisation.Type = '8197' THEN contact.valid_from WHEN Organisation.Type = '8198' THEN ag_contact.valid_from ELSE '' END AS [Area Joined Date],
CASE WHEN Organisation.Type = '8197' THEN isnull(AG_Organisation.trading_as, Organisation.Trading_As + '98')
WHEN Organisation.Type = '8198' THEN Organisation.Trading_As ELSE '' END AS [Group],
CASE WHEN Organisation.Type = '8197' THEN ag_contact.valid_from WHEN Organisation.Type = '8198' THEN contact.valid_from ELSE '' END AS [Group Joined Date],
ISNULL(CASE WHEN NoMail.Attribute_Code = 'NOMAIL' THEN 'No mailing' END, '') AS [No Mailing],
ISNULL(CASE WHEN GoneAway.Attribute_Code = 'GA' THEN 'Gone away' END, '') AS [Gone Away], CASE WHEN
(SELECT COUNT(*)
FROM contact AS ContVolunteer
WHERE ContVolunteer.individual_ref = individual.individual_ref AND ContVolunteer.contact_status = '1123' AND ContVolunteer.type IN ('8584',
'8741', '8743', '8744', '8748', '8745', '8750', '1141', '8746', '8747', '8749', '9251', '8693', '8752', '8755', '8754', '8753', '8585', '1142',
'1144', '1146', '1152', '1148', '1149', '8742', '1150', '1151', '1153', '9252', '1154')) <> 0 THEN 'Yes' ELSE '' END AS Volunteer,
CONVERT(varchar(50), dbo.ORGANISATION.TYPE) AS Control_OrgType, dbo.ORGANISATION.TRADING_AS AS Control_Group_Area_Code
FROM dbo.CONTACT INNER JOIN
dbo.ORGANISATION ON dbo.CONTACT.ORGANISATION_REF = dbo.ORGANISATION.ORGANISATION_REF AND
dbo.ORGANISATION.STATUS = '11' INNER JOIN
dbo.INDIVIDUAL ON dbo.INDIVIDUAL.INDIVIDUAL_REF = dbo.CONTACT.INDIVIDUAL_REF LEFT OUTER JOIN
dbo.INDIVIDUAL AS individual2 ON dbo.INDIVIDUAL.FAMILY_REF = individual2.INDIVIDUAL_REF LEFT OUTER JOIN
dbo.ATTRIBUTE AS GoneAway ON dbo.INDIVIDUAL.INDIVIDUAL_REF = GoneAway.INDIVIDUAL_REF AND GoneAway.ATTR_CODE_REF = 1267 AND
GoneAway.VALID_FROM <= GETDATE() AND (GoneAway.VALID_TO IS NULL OR
GoneAway.VALID_TO >= GETDATE()) LEFT OUTER JOIN
dbo.ATTRIBUTE AS NoMail ON dbo.INDIVIDUAL.INDIVIDUAL_REF = NoMail.INDIVIDUAL_REF AND NoMail.ATTR_CODE_REF = 1242 AND
NoMail.VALID_FROM <= GETDATE() AND (NoMail.VALID_TO IS NULL OR
NoMail.VALID_TO >= GETDATE()) INNER JOIN
dbo.MEMBER ON dbo.INDIVIDUAL.INDIVIDUAL_REF = dbo.MEMBER.INDIVIDUAL_REF LEFT OUTER JOIN
dbo.MEMBER AS member2 ON individual2.INDIVIDUAL_REF = member2.INDIVIDUAL_REF INNER JOIN
dbo.LOOKUP AS M_Status ON dbo.MEMBER.MEMBER_STATUS = M_Status.LOOKUP_REF LEFT OUTER JOIN
dbo.INDIVIDUAL_LOC ON dbo.INDIVIDUAL.INDIVIDUAL_REF = dbo.INDIVIDUAL_LOC.INDIVIDUAL_REF AND
dbo.INDIVIDUAL_LOC.MAIN_LOCATION = 'Y' LEFT OUTER JOIN
dbo.LOCATION AS location_i ON dbo.INDIVIDUAL_LOC.LOCATION_REF = location_i.LOCATION_REF LEFT OUTER JOIN
dbo.CONTACT AS AG_Contact ON dbo.INDIVIDUAL.INDIVIDUAL_REF = AG_Contact.INDIVIDUAL_REF AND AG_Contact.CONTACT_STATUS = '1123' AND
(AG_Contact.TYPE = 8199 OR
AG_Contact.TYPE = 8751) LEFT OUTER JOIN
dbo.ORGANISATION AS AG_Organisation ON AG_Organisation.ORGANISATION_REF = AG_Contact.ORGANISATION_REF AND
AG_Organisation.STATUS = '11' AND (AG_Organisation.TYPE = 8197 OR
AG_Organisation.TYPE = 8198) AND AG_Organisation.TYPE <> dbo.ORGANISATION.TYPE LEFT OUTER JOIN
dbo.TELEPHONE ON dbo.INDIVIDUAL.INDIVIDUAL_REF = dbo.TELEPHONE.INDIVIDUAL_REF AND dbo.TELEPHONE.MAIN_NUMBER = 'Y' AND
dbo.TELEPHONE.VALID_FROM <= GETDATE() AND (dbo.TELEPHONE.VALID_TO >= GETDATE() OR
dbo.TELEPHONE.VALID_TO IS NULL) LEFT OUTER JOIN
dbo.LOOKUP AS LookupMC ON dbo.MEMBER.MEMBER_CLASS = LookupMC.LOOKUP_REF INNER JOIN
dbo.MEMBERSHIP_GRADE AS MG ON dbo.MEMBER.MEMBER_GRADE = MG.MEMBER_GRADE_REF
WHERE (dbo.ORGANISATION.TYPE = 8197 OR
dbo.ORGANISATION.TYPE = 8198) AND (dbo.CONTACT.TYPE = 8199 OR
dbo.CONTACT.TYPE = 8751) AND (dbo.CONTACT.CONTACT_STATUS = '1123') AND (dbo.MEMBER.MEMBER_STATUS = 33 OR
dbo.MEMBER.MEMBER_STATUS = 9246) AND
((SELECT COUNT(*) AS Expr1
FROM dbo.CONTACT AS contact_count
WHERE (TYPE = '8751' OR
TYPE = '8199') AND (VALID_FROM < GETDATE()) AND (VALID_TO >= GETDATE() OR
VALID_TO IS NULL) AND (INDIVIDUAL_REF = dbo.INDIVIDUAL.INDIVIDUAL_REF)) = 1) OR
(dbo.ORGANISATION.TYPE = 8197 OR
dbo.ORGANISATION.TYPE = 8198) AND (dbo.CONTACT.TYPE = 8199 OR
dbo.CONTACT.TYPE = 8751) AND (dbo.CONTACT.CONTACT_STATUS = '1123') AND (dbo.MEMBER.MEMBER_STATUS = 33 OR
dbo.MEMBER.MEMBER_STATUS = 9246) AND
((SELECT COUNT(*) AS Expr1
FROM dbo.CONTACT AS contact_count
WHERE (TYPE = '8751' OR
TYPE = '8199') AND (VALID_FROM < GETDATE()) AND (VALID_TO >= GETDATE() OR
VALID_TO IS NULL) AND (INDIVIDUAL_REF = dbo.INDIVIDUAL.INDIVIDUAL_REF)) <> 1) AND
(dbo.ORGANISATION.TRADING_AS <> AG_Organisation.TRADING_AS) AND (dbo.ORGANISATION.TYPE <> AG_Organisation.TYPE) AND
(LEN(dbo.ORGANISATION.TRADING_AS) + LEN(ISNULL(AG_Organisation.TRADING_AS, '')) <> 8)
UNION
SELECT dbo.MEMBER.MEMBERSHIP_NO AS [Membership Number], CASE WHEN org_affil.type = 1137 THEN 'Affiliated Club' END AS [Member Type],
M_Status.LOOKUP_FULL_DESC AS Status, CASE WHEN charindex('Concession', description) > 0 THEN LEFT(description, charindex('Concession',
description) - 2) WHEN charindex('Concession', description) = 0 THEN description END AS Term, LookupMC.LOOKUP_FULL_DESC AS Type,
'' AS [Joint With], org_affil.COMPANY_NAME AS [Company Name], '' AS Title, '' AS Initials, '' AS Forenames, '' AS Surname,
dbo.LOCATION.ADDRESS1 AS Address1, dbo.LOCATION.ADDRESS2 AS Address2, dbo.LOCATION.ADDRESS3 AS Address3,
dbo.LOCATION.TOWN AS Town, dbo.LOCATION.COUNTY AS County, dbo.LOCATION.POSTCODE AS Postcode, ISNULL(dbo.LOCATION.COUNTRY, '')
AS Country, ISNULL
((SELECT TOP (1) EMAIL_ADDRESS
FROM dbo.EMAIL AS PrivEmail
WHERE (org_affil.ORGANISATION_REF = ORGANISATION_REF) AND (TYPE = 1126) AND (VALID_TO IS NULL)
ORDER BY VALID_FROM DESC), '') AS [Private Email], ISNULL
((SELECT TOP (1) EMAIL_ADDRESS
FROM dbo.EMAIL AS PubEmail
WHERE (org_affil.ORGANISATION_REF = ORGANISATION_REF) AND (TYPE = 1125) AND (VALID_TO IS NULL)
ORDER BY VALID_FROM DESC), '') AS [Public Email], ISNULL(dbo.TELEPHONE.NUMBER, '') AS Telephone,
(SELECT MAX(mh.VALID_TO) AS Expr1
FROM dbo.MEMBERSHIP_HISTORY AS mh INNER JOIN
dbo.MEMBERSHIP_PLAN AS mp ON mh.MEMBER_PLAN_REF = mp.MEMBER_PLAN_REF AND mp.GROUP_REF = 2 AND
dbo.MEMBER.MEMBER_REF = mh.MEMBER_REF AND mh.HISTORY_STATUS = 249
GROUP BY mh.MEMBER_REF) AS [Expiry Date], dbo.MEMBER.JOIN_DATE AS [Ramblers Joined date], dbo.ORGANISATION.TRADING_AS AS Area,
dbo.MEMBER.JOIN_DATE AS [Area Joined date], dbo.ORGANISATION.TRADING_AS + '99' AS [Group], dbo.MEMBER.JOIN_DATE AS [Group Joined date],
ISNULL(CASE WHEN NoMail.Attribute_Code = 'NOMAIL' THEN 'No mailing' END, '') AS [No Mailing],
ISNULL(CASE WHEN GoneAway.Attribute_Code = 'GA' THEN 'Gone away' END, '') AS [Gone Away], '' AS Volunteer,
dbo.ORGANISATION.TYPE AS Control_OrgType, dbo.ORGANISATION.TRADING_AS AS Control_Group_Area_Code
FROM dbo.ORGANISATION INNER JOIN
dbo.ORGANISATION AS org_affil ON dbo.ORGANISATION.ORGANISATION_REF = org_affil.PARENT_REF AND org_affil.TYPE = '1137' AND
org_affil.STATUS = '11' INNER JOIN
dbo.MEMBER ON org_affil.ORGANISATION_REF = dbo.MEMBER.ORGANISATION_REF LEFT OUTER JOIN
dbo.ORGANISATION_LOC ON org_affil.ORGANISATION_REF = dbo.ORGANISATION_LOC.ORGANISATION_REF AND
dbo.ORGANISATION_LOC.MAIN_LOCATION = 'Y' LEFT OUTER JOIN
dbo.LOCATION ON dbo.ORGANISATION_LOC.LOCATION_REF = dbo.LOCATION.LOCATION_REF LEFT OUTER JOIN
dbo.LOOKUP AS M_Status ON M_Status.LOOKUP_REF = dbo.MEMBER.MEMBER_STATUS LEFT OUTER JOIN
dbo.ATTRIBUTE AS GoneAway ON org_affil.ORGANISATION_REF = GoneAway.ORGANISATION_REF AND GoneAway.ATTR_CODE_REF = 1267 AND
GoneAway.VALID_FROM <= GETDATE() AND (GoneAway.VALID_TO IS NULL OR
GoneAway.VALID_TO >= GETDATE()) LEFT OUTER JOIN
dbo.ATTRIBUTE AS NoMail ON org_affil.ORGANISATION_REF = NoMail.ORGANISATION_REF AND NoMail.ATTR_CODE_REF = 1242 AND
NoMail.VALID_FROM <= GETDATE() AND (NoMail.VALID_TO IS NULL OR
NoMail.VALID_TO >= GETDATE()) LEFT OUTER JOIN
dbo.TELEPHONE ON org_affil.ORGANISATION_REF = dbo.TELEPHONE.ORGANISATION_REF AND dbo.TELEPHONE.MAIN_NUMBER = 'Y' AND
dbo.TELEPHONE.VALID_FROM <= GETDATE() AND (dbo.TELEPHONE.VALID_TO >= GETDATE() OR
dbo.TELEPHONE.VALID_TO IS NULL) LEFT OUTER JOIN
dbo.LOOKUP AS LookupMC ON dbo.MEMBER.MEMBER_CLASS = LookupMC.LOOKUP_REF INNER JOIN
dbo.MEMBERSHIP_GRADE AS MG ON dbo.MEMBER.MEMBER_GRADE = MG.MEMBER_GRADE_REF
WHERE (dbo.MEMBER.MEMBER_STATUS = 33) OR
(dbo.MEMBER.MEMBER_STATUS = 9246)
