ChrisM@Work

Forum Replies Created

Viewing 15 posts - 6,481 through 6,495 (of 10,143 total)

RE: Tricky Calculation Part 2

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...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

November 8, 2011 at 5:59 am

#1404419
RE: Tricky Calculation Part 2

Looks about right.
Try
SUM(Coordinate/Total*100.00) Coordinate
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

November 8, 2011 at 5:35 am

#1404392
RE: Tricky Calculation Part 2

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)...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

November 8, 2011 at 5:12 am

#1404376
RE: How can I improve this query? Need to Create Aggregates in One Step - Currently Create a Query and Subsequently Use a #Temp Table to Perform Summing and Grouping

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...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

November 7, 2011 at 9:03 am

#1404016
RE: How can I improve this query? Need to Create Aggregates in One Step - Currently Create a Query and Subsequently Use a #Temp Table to Perform Summing and Grouping

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...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

November 7, 2011 at 6:28 am

#1403909
RE: Starting with "WITH"

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...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

November 7, 2011 at 3:25 am

#1403827
RE: order of criteria in where clause changes query palan

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...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

November 4, 2011 at 10:05 am

#1403359
RE: Better Query

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...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

November 4, 2011 at 8:45 am

#1403284
RE: order of criteria in where clause changes query palan

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...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

November 4, 2011 at 8:38 am

#1403278
RE: order of criteria in where clause changes query palan

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...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

November 4, 2011 at 5:13 am

#1403145
RE: Today's Random Word!

Revenant (11/3/2011)
SQLRNNR (11/3/2011)
Ray K (11/3/2011)
overwhelmed
overweight
Overfed
Crispy crackling
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

November 3, 2011 at 10:24 am

#1402765
RE: Help with performance - aggregating a lot of data

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. ...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

November 3, 2011 at 2:35 am

#1402518
RE: Concatenation insde Case Statement

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,...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

November 2, 2011 at 8:41 am

#1402175
RE: Views and Order By

Suresh B. (11/2/2011)
mohnish_khiani (11/2/2011)
Why the order by clause cannot be used in the query which makes up the query in a view??
Not only in SELECT statement, views be used in...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

November 2, 2011 at 7:45 am

#1402134
RE: Help with performance - aggregating a lot of data

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. Sure, I...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

November 2, 2011 at 7:33 am

#1402130

Viewing 15 posts - 6,481 through 6,495 (of 10,143 total)