ChrisM@Work

Forum Replies Created

Viewing 15 posts - 3,541 through 3,555 (of 10,143 total)

RE: Core T-SQL

Greg Edwards-268690 (3/12/2014)
Although I will admit - Core skills, and 'Nice to Have - you will need these skills' - will drive a lot of debate at times.
Kind of like...
^{“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

March 12, 2014 at 8:18 am

#1696406
RE: Core T-SQL

Neither do I Greg and that’s an excellent point you’ve made.
An article claiming to list the required skill set for a general-purpose TSQL developer, published here on ssc, absolutely...
^{“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

March 12, 2014 at 7:30 am

#1696381
RE: Core T-SQL

Jeff Moden (3/11/2014)
@chrism-2,
I noticed that you've not written an article on this subject yet and thought I'd give you a nudge. I think the list you came up with...
^{“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

March 12, 2014 at 2:47 am

#1696245
RE: Why does my query use MAXDOP = 0?

curious_sqldba (3/11/2014)
ChrisM@Work (3/11/2014)
curious_sqldba (3/11/2014)
ChrisM@Work (3/11/2014)
curious_sqldba (3/11/2014)
sqlbuddy123 (3/10/2014)
MAXDOP =1 means disabling the parallelism. That's why SQL Server doesn't consider parallelizing the query.
That's why I couldn't see that in the execution plan...
^{“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

March 11, 2014 at 9:26 am

#1696037
RE: Why does my query use MAXDOP = 0?

curious_sqldba (3/7/2014)
I don't have any query hints being used, at server level i have MAXDOP = 5 and CTP = 1, in the exec plan i see that query is...
^{“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

March 11, 2014 at 9:03 am

#1696025
RE: Why does my query use MAXDOP = 0?

curious_sqldba (3/11/2014)
ChrisM@Work (3/11/2014)
curious_sqldba (3/11/2014)
sqlbuddy123 (3/10/2014)
MAXDOP =1 means disabling the parallelism. That's why SQL Server doesn't consider parallelizing the query.
That's why I couldn't see that in the execution plan you posted.
--
SQLBuddy
Exactly,...
^{“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

March 11, 2014 at 8:44 am

#1696016
RE: how to GROUP BY with shortest Distance by AccountNumber

It does go around the houses a bit doesn't it. This is probably worth a try:
Start with a preaggregate
SELECT
a.AccountNumber,
s.StoreId3,
Distance = MIN(a.LocationPoint.STDistance(s.LocationPoint) / 1609.344)
FROM prod_Accuzip a
INNER Join StoreListMaster s ON (s.St...
^{“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

March 11, 2014 at 8:38 am

#1696012
RE: Why does my query use MAXDOP = 0?

curious_sqldba (3/11/2014)
sqlbuddy123 (3/10/2014)
MAXDOP =1 means disabling the parallelism. That's why SQL Server doesn't consider parallelizing the query.
That's why I couldn't see that in the execution plan you posted.
--
SQLBuddy
Exactly, but my...
^{“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

March 11, 2014 at 8:30 am

#1696009
RE: Why does my query use MAXDOP = 0?

curious_sqldba (3/11/2014)
sqlbuddy123 (3/10/2014)
MAXDOP =1 means disabling the parallelism. That's why SQL Server doesn't consider parallelizing the query.
That's why I couldn't see that in the execution plan you posted.
--
SQLBuddy
Exactly, but my...
^{“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

March 11, 2014 at 8:10 am

#1695997
RE: how to GROUP BY with shortest Distance by AccountNumber

You're welcome Geoff. Which part helped?
^{“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

March 11, 2014 at 7:19 am

#1695959
RE: how to GROUP BY with shortest Distance by AccountNumber

DROP table #CustIfno;
CREATE table #CustIfno (AccountNumber int, StoreID int, Distance decimal(14,10));
insert into #CustIfno values
('1','44','2.145223'), --
('1','45','4.567834'),
('1','46','8.4325654'),
('2','44','7.8754345'),
('2','45','1.54654323'), --
('2','46','11.5436543'),
('3','44','9.145223'),
('3','45','8.567834'), --
('3','46','17.4325654'),
('4','44','7.8754345'),
('4','45','1.54654323'), --
('4','46','11.5436543');
-- without index
WITH CTE AS (
SELECT AccountNumber,...
^{“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

March 11, 2014 at 6:36 am

#1695938
RE: Filtering a Concatenated List Help

amy26 (3/6/2014)
I suppose of there was another way to loop through the original data structure by issue_num and find all instances where the column_value was all 0's or all -'s...
^{“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

March 6, 2014 at 9:48 am

#1694788
RE: To dynamically change values in this short script based on 3 values

pharmkittie (3/6/2014)
Hello,
Is my question too general or do I have too much detail? If anyone could please tell me if there's something I could do to make my post...
^{“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

March 6, 2014 at 8:51 am

#1694756
RE: Filtering a Concatenated List Help

SELECT issue_num
FROM (
SELECT issue_num, column_value
FROM mydata
GROUP BY issue_num, column_value
) d
GROUP BY issue_num
HAVING COUNT(*) =1
The code bit is trivial - this is one of many ways of getting the same result...
^{“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

March 6, 2014 at 7:21 am

#1694709
RE: Are the posted questions getting worse?

rodjkidd (3/6/2014)
ChrisM@Work (3/6/2014)
Are you working in Maidenhead, Rod?
Chris,
Work in Bracknell, stay near Bray in Maidenhead at the moment.
Rodders...
Not much of a hike then. Bray, home of the Fat Duck. 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

March 6, 2014 at 6:21 am

#1694677

Viewing 15 posts - 3,541 through 3,555 (of 10,143 total)