Slow cusror movement and typing with specific code

Provides intelligent code completion for SQL Server editors.

Moderators: JonathanWatts, David Atkinson, Anu Deshpande, Paul Stephenson, Michelle Taylor, Mike Upton, justin.caldicott, Aaron Law

Slow cusror movement and typing with specific code

Postby sdingle » Fri Feb 22, 2013 12:18 pm

I am eveluating SQL Prompt for my company, eveything working fine except when I open a specific SP in order to make a change. Moving around in the SP is very slow and typing is almpst impossible. I had someone else open the SP on a machine without SQL Prompt and it worked fine. Below is the code... which I didn't write I should add!

Any ideas?

Should add I have rebooted and restarted SSMS multiple times

;WITH Walk10 AS
(
SELECT DISTINCT
contact_number
FROM event_bookings eb INNER JOIN events e
on eb.event_number = e.event_number
WHERE e.event_desc LIKE '%Walk Ten %'
),
BGTP AS
(
SELECT DISTINCT
contact_number
FROM event_bookings eb INNER JOIN events e
on eb.event_number = e.event_number
WHERE e.event_desc LIKE '%Blooming Great Tea Party%'

),
InMem AS
(
SELECT DISTINCT
contact_number
FROM batch_transaction_analysis
WHERE source LIKE '608%' OR source LIKE '601%'

),
GenDon AS
(
SELECT DISTINCT
contact_number
FROM batch_transaction_analysis
WHERE product = 'DON'

),
MannedCol AS
(
SELECT DISTINCT
contact_number
FROM manned_collectors
),
CampaignRole AS
(
SELECT DISTINCT
contact_number
FROM contact_campaign_roles
),
H2H AS
(
SELECT DISTINCT
contact_number
FROM batch_transaction_analysis
WHERE source LIKE '%H2H%'

),
Marshal AS
(
SELECT DISTINCT
contact_number
FROM event_personnel
),
Abseil AS
(
SELECT DISTINCT
contact_number
FROM event_bookings eb INNER JOIN events e
on eb.event_number = e.event_number
WHERE e.event_desc LIKE '%Abseil%'

),
LDC AS
(
SELECT DISTINCT
contact_number
FROM event_bookings eb INNER JOIN events e
on eb.event_number = e.event_number
WHERE e.event_desc LIKE '%Ladies Driving Challenge%'

),
T2T3 AS
(
SELECT
ol.organisation_number_1,
t2.T2, af.external_reference AS T3
FROM dbo.organisation_links AS ol
LEFT OUTER JOIN dbo.organisations AS o
ON o.organisation_number = ol.organisation_number_2
LEFT OUTER JOIN dbo.contact_external_links AS af
ON o.organisation_number = af.contact_number
JOIN
(
SELECT DISTINCT
RIGHT(r.geographical_region ,2) AS T2,
RIGHT(a.geographical_region ,3) AS T3
FROM dbo.geographical_region_postcodes as r JOIN dbo.geographical_region_postcodes as a
ON r.postcode=a.postcode
WHERE r.geographical_region_type = 'MCFR'
AND a.geographical_region_type = 'FUNO'
) AS t2
ON af.external_reference = t2.T3
WHERE ol.relationship = 'FUNG'
AND GETDATE() BETWEEN valid_from AND ISNULL(valid_to,'21001231')
AND af.data_source = 'MT3'
),
InAid AS
(
SELECT DISTINCT
contact_number
FROM event_bookings eb INNER JOIN events e
on eb.event_number = e.event_number INNER JOIN event_sources es
on e.event_number = es.event_number
WHERE es.source LIKE '81%' OR es.source LIKE 'INAID%'

),
Overseas AS
(
SELECT DISTINCT
contact_number
FROM
event_bookings
WHERE
event_number IN('8117', '8535', '8228','8567','8581','8596','8708','8566','8563','8551',
'8478','8564','8565','8826','8852','8862',
'8860','8859','8854','8861','8851','8879')
),
Mountain AS
(
SELECT DISTINCT
contact_number
FROM
event_bookings
WHERE
event_number IN('7874', '7875', '7876', '7859','7860','7892','7873','8253','8254','8261',
'8263','8264','8268','8203','8262','8294','8647','8610','8611','8604','8605','8614','8606',
'8613','8612','8638')
),
Runs AS
(
SELECT DISTINCT
contact_number
FROM
event_bookings eb INNER JOIN events e
on eb.event_number = e.event_number
WHERE
event_desc LIKE '%5k%' OR event_desc LIKE '%10k%' OR event_desc LIKE '%Run%'
OR event_desc LIKE '%marathon%'
),
Other AS
(
SELECT DISTINCT
contact_number
FROM event_bookings
WHERE contact_number NOT IN(
SELECT contact_number
FROM BGTP)
AND contact_number NOT IN(
SELECT contact_number
FROM Walk10)
AND contact_number NOT IN(
SELECT contact_number
FROM Abseil)
AND contact_number NOT IN(
SELECT contact_number
FROM LDC)
AND contact_number NOT IN(
SELECT contact_number
FROM InAid)
AND contact_number NOT IN(
SELECT contact_number
FROM Overseas)
AND contact_number NOT IN(
SELECT contact_number
FROM Runs)
AND contact_number NOT IN(
SELECT contact_number
FROM Mountain)
)

SELECT
T2T3.T2,
T2T3.T3,
fisc.FiscY,
o.name AS FundraisingGroup,
case when o.status = 'OC' then 'Yes' else null end AS Closed,
count(InMem.contact_number) AS InMem,
count(GenDon.contact_number) AS GenDon,
count(MannedCol.contact_number) AS MannedCollector,
count(CampaignRole.contact_number) AS CampaignRoles,
count(BGTP.contact_number) AS BGTP,
count(InAid.contact_number) AS InAidOf,
count(Marshal.contact_number) AS Marshall,
count(H2H.contact_number) AS H2H,
count(Walk10.contact_number) AS Walk10,
count(Abseil.contact_number) AS Abseil,
count(LDC.contact_number) AS LDC,
count(Overseas.contact_number) AS Overseas,
count(Runs.contact_number) AS Runs,
count(Mountain.contact_number) AS Mountain,
count(Other.contact_number) AS OtherEvent,
count(distinct case when c.source_date > o.source_date then c.contact_number else null end) AS NoPriorHistory
FROM
T2T3 RIGHT OUTER JOIN organisations o
on T2T3.organisation_number_1 = o.organisation_number LEFT OUTER JOIN contact_positions cp
on o.organisation_number = cp.organisation_number LEFT OUTER JOIN contacts c
on cp.contact_number = c.contact_number LEFT OUTER JOIN InMem
on cp.contact_number = InMem.contact_number LEFT OUTER JOIN GenDon
on cp.contact_number = GenDon.contact_number LEFT OUTER JOIN MannedCol
on cp.contact_number = MannedCol.contact_number LEFT OUTER JOIN CampaignRole
on cp.contact_number = CampaignRole.contact_number LEFT OUTER JOIN BGTP
on cp.contact_number = BGTP.contact_number LEFT OUTER JOIN H2H
on cp.contact_number = H2H.contact_number LEFT OUTER JOIN Walk10
on cp.contact_number = Walk10.contact_number LEFT OUTER JOIN Abseil
on cp.contact_number = Abseil.contact_number LEFT OUTER JOIN LDC
on cp.contact_number = LDC.contact_number LEFT OUTER JOIN Other
on cp.contact_number = Other.contact_number LEFT OUTER JOIN InAid
on cp.contact_number = InAid.contact_number LEFT OUTER JOIN Marshal
on cp.contact_number = Marshal.contact_number LEFT OUTER JOIN Overseas
on cp.contact_number = Overseas.contact_number LEFT OUTER JOIN Runs
on cp.contact_number = Runs.contact_number INNER JOIN dbo.ext_FiscalYearConverter_PJC fisc
on o.source_date = fisc.Date LEFT OUTER JOIN Mountain
on cp.contact_number = Mountain.contact_number
WHERE o.organisation_group = 'FGR' AND FiscY IN(@FiscY) AND T2 IN(@T2) AND T3 IN(@T3)
GROUP BY
T2T3.T2,
T2T3.T3,
fisc.FiscY,
o.name,
o.status
sdingle
 
Posts: 2
Joined: Fri Feb 22, 2013 12:14 pm

Postby Anu Deshpande » Mon Feb 25, 2013 10:29 am

Many thanks for your post sdingle!

I have emailed you a workaround let me know if that works for you!
Anuradha Deshpande
Product Support
Redgate Software Ltd.
E-mail: support@red-gate.com
Anu Deshpande
 
Posts: 692
Joined: Mon Apr 20, 2009 3:53 pm
Location: Cambridge


Return to SQL Prompt 5

Who is online

Users browsing this forum: Bing [Bot] and 0 guests