Speeding up this sql view....
Posted on 2012-03-30
I was wondering if someone could quickly help me rewrite this sql view to speed things up... we have determined this view is a bottleneck in another view that is calling it to get a count for the others views one column.... The view is so slow it times out while trying to run it straight in enterprise manager and or through the webpage that uses it....
dbo.avoice_contact.candidate_id, dbo.avoice_candidate.location_id, dbo.avoice_candidate.campaign_id,
CASE WHEN dbo.avoice_candidate.term_dte = CONVERT(datetime, '01/01/1900', 101) THEN NULL ELSE CONVERT(VARCHAR(10), dbo.avoice_candidate.term_dte, 101)
END AS term_dte
FROM dbo.avoice_contact INNER JOIN
dbo.avoice_candidate ON dbo.avoice_contact.candidate_id = dbo.avoice_candidate.candidate_id INNER JOIN
dbo.avoice_location ON dbo.avoice_candidate.location_id = dbo.avoice_location.location_id
WHERE (dbo.avoice_contact.candidate_id IN
FROM dbo.avoice_candidate AS avoice_candidate_2 INNER JOIN
dbo.avoice_location AS avoice_location_1 ON avoice_candidate_2.location_id = avoice_location_1.location_id AND
avoice_candidate_2.location_id = avoice_location_1.location_id AND avoice_candidate_2.campaign_id = avoice_location_1.campaign_id
WHERE (avoice_location_1.status = 'A'))) AND (dbo.avoice_contact.signed_card = 1)