Jeff Moden (11/7/2011)

---

...like Cursors and WHILE loops, I just want to make sure that people understand that the use of rCTEs should probably be avoided if there's a decent set...

dave_vicary (11/8/2011)

---

I placed this around the functions in the derived table

CAST (SUM(case when GeoResolutionCode = 1 then 1 else 0 end)AS FLOAT) Coordinate,

and then rounded them on the outer selection...

Looks about right.

Try

SUM(Coordinate/Total*100.00) Coordinate

Put another SELECT around it Dave, like this:

SELECT

CONTRY_NAME_LC,

Total,

Coordinate,

Street,

Hi_Res,

Street_Name,

Postcode,

District,

City,

County,

Region,

[State],

CRESTA,

Country,

[None]

FROM (

SELECT

CONTRY_NAME_LC,

COUNT(GeoResolutionCode) as Total,

SUM(case when GeoResolutionCode = 1 then 1 else 0 end)...

Welsh Corgi (11/7/2011)

---

ChrisM@Work (11/7/2011)

---

Changing the query in the first post on this thread from using a temp table to a derived table is this straightforward:

1.Change FROM #TXActivityTemp to FROM...

Changing the query in the first post on this thread from using a temp table to a derived table is this straightforward:

1.Change FROM #TXActivityTemp to FROM () d

2.Copy the...

Perhaps rCTE's provide some incentive for tightening up the definition of RBAR. I've always read RBAR as meaning row by row coding. rCTE's are not - they're row by row...

GilaMonster (11/4/2011)

---

Lowell (11/4/2011)

---

well, the part i was trying to talk about was the screenshot below; see the difference for the "good enough plan found" vs the other that did not...

This might yield a little improvement:

FROM Email AS E

LEFT OUTER JOIN UserContact AS UC

ON E.UserID = UC.UserID

LEFT OUTER JOIN ContactDetail AS CD

ON UC.ContactID = CD.ContactID

AND CD.StartDate <= @pEffectiveDate

AND CD.EndDate...

Adi Cohn-120898 (11/4/2011)

---

Lowell (11/4/2011)

---

A couple of Grant Fitychey's presentations he did on execution plans might be relevant;

as i remember it, the key to look for is the property "Reason For...

Save the two different plans.

Swap the two queries around in the script, run until you get two different plans again.

Compare the four plans.

The results suggest that plan generation is dependant...

Crispy crackling

Kiara (11/2/2011)

---

ChrisM@Work (11/2/2011)

---

Jeff Moden (11/2/2011)

---

I just got done with some testing of my theory that having temporal based and persisted calculated columns in the detail would help this problem. ...

select

top 1

396 as Feed_Sequence_Number,

'DT' as Detail_Record_Indicator,

'000077647' as Producer_Number,

convert(varchar(8), dateadd(s,-1,dateadd(mm,...

Suresh B. (11/2/2011)

---

Not only in SELECT statement, views be used in...